Excel file created by poi API: data is there but o

问题:

I used a simple code from online code and I was able to call the method in the code in a for loop and passing it data and row number. The file is created properly, the file's size (after refreshing my file explorer) is changed upon each insert but when i am done I open the file and I can only see the first row in there and the moment i close it the size of the file decreases from 1.5MB to 5bytes. I opened the 1.5 MB file in notepadd++ (ofcourse it opened with byte characters) I could actually see the data that i have been writing into the file besides the first row, All my rows were there. but when i open in excel it doesnt.

Is there some thing I am missing here. At each insert into the excel file I am doing this :

fileOutputStream = new FileOutputStream(fileName,true);
sampleWorkbook.write(fileOutputStream);

and after that in the finally block of the try block :

finally {
    /**
     * Close the fileOutputStream.
    */
    try {
        if (fileOutputStream != null) {
            fileOutputStream.flush();
            fileOutputStream.close();
        }
    } catch (IOException ex) {
        ex.printStackTrace();
    }
}

but if the file size is changing and I can view the data in a notepad why is excel treating the rows other than the first one as temporary data (that is what I'm assuming it's doing as it reverts back to only 5 bytes when I close the file)? What's wrong here?

Thanks for your help, in advance.

syed.

回答1:

The problem here is that xls isn't a simple format and adding a row into a xls doesn't mean appending several bytes in the end of the file. The new data is inserted in the middle of the file so you have to completely rewrite the whole file. You code sample just writes another workbook after already existing workbook, which makes file content invalid. So when you open it in Excel it automatically corrects file content removing all added data except the first workbook.

Use this code sample:

InputStream in = new FileInputStream (path);
Workbook sampleWorkbook;
try{
  sampleWorkbook = new HSSFWorkbook (in);//or XSSFWorkbook depending on whether you use xls or xlsx
} finally
{
  in.close ();
}

//Add some rows into the workbook

OutputStream out = new FileOutputStream (path);
try{
  sampleWorkbook.write (out);
} finally
{
  out.close ();
}

回答2:

Might not that relevant to your question, but for easy Excel generating, it's recommended to use JXLS (http://jxls.sourceforge.net/) which is built on top of POI. The beautiful part of JXLS is it enable you to create an Excel template (using Excel of course), and then you just put the data into the template. It supports basic logic operation including if else and loop.

回答3:

If you can afford commercial solution take a look at Templater. It's very easy to use. Disclaimer: I'm the author.