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.

Brooksbrookshire

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.

Neuroblast

This solution could need a bit cleanup and inline comment since it is very hard to read. – Jonna

Hey @AttitudeL, I'm sorry you're finding this answer difficult to read. How do you propose changing it to make it clearer? – Biancabiancha

Your solution works as charm. My suggestion is to add a few more comments due to multiple streams coexisting at the same time. – Jonna

Thanks a lot, @Klugscheißer. I wasted many hours trying to understand the behaviour of these streams and your solution worked!! – Ingeborgingelbert

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.

Jocular

Hi It's my mistake to mention that I did it already, what I actually did is that XSSFWorkbook workbook = new XSSFWorkbook(); //add some data Zipoutputstream zipstream=new Zipoutputstream(//destination outputstream); zipstream.putNextEntry(new ZipEntry("example.xlsx")); workbook.write(zipstream); zipstream.closeEntry(); the problem with that is XSSFWorkbook.write will automatically close the stream. So I change it to HSSFWorkbook. Now I have the file but I can't open it, it says the content has been corrupted something. Do any one knows what's wrong? Thanks. – Brooksbrookshire

Does the *.zip file contains the Spreadsheet with the filename of its absolute path? – Raker

Answer 3

A colleague of mine, M. Bunshaft, suggested a solution similar to that of Klugscheißer but that does not require the use of a ByteArrayOutputStream, and hence can accommodate larger output. The idea is to subclass ZipOutputStream, overriding the close() method so it will not do a close.

public class UncloseableZipOutputStream extends ZipOutputStream
{
	OutputStream os;
	
	public UncloseableZipOutputStream( OutputStream os )
	{
		super(os);
	}
	
	@Override
	/** just flush but do not close */
	public void close() throws IOException
	{
		flush();
	}
	
	public void reallyClose() throws IOException
	{
		super.close();
	}
}

Then, simply use it the way you would use the ZipOutputStream.

UncloseableZipOutputStream zos = new UncloseableZipOutputStream(//destination outputstream);
zos.putNextEntry(new ZipEntry("AnExcelFile.xlsx"));
workbook.write(zos);
zos.closeEntry();      // now this will not cause a close of the stream
// Add other entries as needed
zos.reallyClose();

Supercolumnar

I am able to get a zip file, where the content of the XLSX file is at the same level, instead of being inside. – Mountainside

Actually, you can just wrap the ZipOutputStream in a FilterOutputStream rather than writing your own class. It just acts as a pass-through and prevents the stream instanceof ZipOutputStream from succeeding in the POI code. – Primrose

Answer 4

this method works for me: (input is byteArray of the file you have)

public static byte[] zipBytes(String filename, byte[] input) throws IOException {
    ByteArrayOutputStream baos = new ByteArrayOutputStream();
    ZipOutputStream zos = new ZipOutputStream(baos);
    ZipEntry entry = new ZipEntry(filename);
    entry.setSize(input.length);
    zos.putNextEntry(entry);
    zos.write(input);
    zos.closeEntry();
    zos.close();
    return baos.toByteArray();
}

Solutrean