write a XSSFWorkbook to a zip file

Question

I now have this problem. I want to write a excel file hold in this XSSFWorkbook (workbook) obj into a zip file eg(example.zip while contain this example.xlsx file) to a remote server. I have tried following but not working, it created a folder with some odd files in the zip file

  XSSFWorkbook workbook = new XSSFWorkbook();
  //add some data
  Zipoutputstream zipstream=new Zipoutputstream(//destination outputstream);
  workbook.write(zipstream);

So do anyone knows what's the right way to do this? Thanks in advance

ps workbook.write(fileoutputstream) works but it only write to local disk as a flat file eg test.xlsx instead of inside a zip as I need.

Answer 1

Passing a a ZipOutputStream to XSSFWorkbook.write will result in the stream being hijacked and closed by the workbook. This is because an XSSFWorkbook writes a .xlsx which is itself a zip archive of xml and other files (you can unzip any .xslx to see what's in there). If you're able to fit the excel file in memory, I've found this to work well:

ZipOutputStream zos = new ZipOutputStream(//destination outputstream);
zos.putNextEntry(new ZipEntry("AnExcelFile.xlsx"));
ByteArrayOutputStream bos = new ByteArrayOutputStream();
workbook.write(bos);
bos.writeTo(zos);
zos.closeEntry();
// Add other entries as needed
zos.close();

Calling close on ByteArrayOutputStream has no effect and can still be written to zos.

Answer 2

You are missing some necessary calls on your ZipOutputStream. You will need to create a ZipEntry for your spreadsheet file, then write it out. You'll need something like

zipstream.putNextEntry(new ZipEntry("example.xlsx"));

Then you should be able to call

workbook.write(zipstream);

But after that you'll need to close the entry before closing the stream.

zipstream.closeEntry();

Please see "Write And Read .Zip File From Java" for details on how to use Java's ZipOutputStream.

Also, be aware that .xlsx files are already compressed zip files, so placing it in a .zip file may not compress it very much.