6

In my system, I have a class that creates an excel with some data.

Basically I read all String values from a variable ArrayList> and write them in excel cells.

public void writeData(Data data, int sheetNumber)
        throws EncryptedDocumentException, InvalidFormatException, IOException {
    org.apache.poi.ss.usermodel.Workbook workbook;

    try {
        workbook = WorkbookFactory.create(new File(path));
    } catch (FileNotFoundException e) {
        workbook = new HSSFWorkbook();
    }

    org.apache.poi.ss.usermodel.Sheet sheet;
    try {
        sheet = workbook.createSheet("Sheet" + sheetNumber);
    } catch (IllegalArgumentException e) {
        sheet = workbook.getSheet("Sheet" + sheetNumber);
    }

    int dataListSize = data.getData().size();
    for (int i = 0; i < dataListSize; i++) {
        Row row = sheet.createRow(i);
        int rowSize = data.getData().get(i).size();
        for (int j = 0; j < rowSize; j++) {
            row.createCell(j);
            row.getCell(j).setCellValue(String.valueOf(data.getData().get(i).get(j)));
        }
    }
    FileOutputStream fos = null;
    try {
        fos = new FileOutputStream(new File(path));
        workbook.write(fos);
    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        workbook.close();
        if (fos != null) {
            try {

                fos.flush();
                fos.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

}

The code works fine as far as I know, I develop on Ubuntu and always try the code here first, the created excels are fine, and I have no problem at all.

When I take one of these to Windows (XP and 7, tried on both), I can't open any of them using Microsoft Excel.

Does anyone have any experience with this?

Thank you.

Mayuso
  • 1,291
  • 4
  • 19
  • 41
  • 2
    "I can't open any of them using Microsoft Excel." Why not? What error message? Please show an example of what is in `path`. – Axel Richter Jun 15 '16 at 11:56
  • What version of Apache POI are you using? And if it isn't the latest, what happens when you upgrade? – Gagravarr Jun 15 '16 at 15:47
  • 1
    I don't have the error message, sorry. As I said, I use ubuntu, some coworkers use Windows. I will have those messages for tomorrow (I hope). – Mayuso Jun 15 '16 at 16:27
  • I use the 3.14 version. (Which I believe is the latest (excluding the beta version)) – Mayuso Jun 15 '16 at 16:27
  • 3
    If `File(path)` does not exists, then you are creating `workbook = new HSSFWorkbook();` A `HSSFWorkbook` is an Excel workbook in `BIFF` format. This must be named `*.xls`. If you name it `*.xlsx`, then Excel will not open it because of the file format and file extension does not match. Thats why I asked for an example of what is in `path`. – Axel Richter Jun 16 '16 at 05:15

2 Answers2

5

HSSF is the Office 97 *.xls format. (It stands for Horrible Spreadsheet Format.)

} catch (FileNotFoundException e) {
    workbook = new HSSFWorkbook();
}

When you did that, you chose the *.xls format. You need to use XSSFWorkbook if you want *.xlsx format.

https://poi.apache.org/components/spreadsheet/quick-guide.html#NewWorkbook

enter image description here

https://poi.apache.org/components/spreadsheet/

procrastinate_later
  • 659
  • 7
  • 16
4

As Axel mentioned, the problem was the file extension.

I can open the files created this way in Ubuntu (Both 14.04 and 16.04), but not in Windows (7, 8 and 10).

The solution is to use the .xls extension and NOT .xlsx, that way I can open and use the files in any OS.

Mayuso
  • 1,291
  • 4
  • 19
  • 41
  • Mayuso, i use xls instead of xlsx it still doesn't work for me please see, https://stackoverflow.com/questions/60831645/apache-poi-export-problem-from-linux-and-reading-from-windows/60831973#60831973 – CHARAFI Saad Mar 24 '20 at 15:17