How can I convert POI HSSFWorkbook to bytes?

Question

Calling Simple toBytes() does produce the bytes, but Excel throws Warning.

Lost Document information

Googling around gave me this link and looking at Javadocs for worksheet and POI HOW-TO say similar things. Basically, I cannot get Bytes without losing some information and should use the write method instead.

While write does work fine, I really need to send the bytes over. Is there any way I can do that? That is, get the bytes without getting any warning.

score 123 · Accepted Answer · answered Apr 17 '12 at 08:45

123

As that mailing list post said

Invoking HSSFWorkbook.getBytes() does not return all of the data necessary to re- construct a complete Excel file.

You can use the write method with a ByteArrayOutputStream to get at the byte array.

ByteArrayOutputStream bos = new ByteArrayOutputStream();
try {
    workbook.write(bos);
} finally {
    bos.close();
}
byte[] bytes = bos.toByteArray();

(The close call is not really needed for a ByteArrayOutputStream, but imho it is good style to include anyway in case its later changed to a different kind of stream.)

answered Apr 17 '12 at 08:45

Jörn Horstmann

33,639
11
75
118

1

How can we do it in reverse way..? I mean Byte array to HSSFWorkbook? – Chetan Nellekeri Mar 05 '19 at 07:43
@ChetanNellekeri It's probably better to ask that as a separate question – Jörn Horstmann Mar 05 '19 at 10:12
1

For anyone using C# this pretty much works there also, ByteArrayOutputStream is available in NPOI.utils – Max Carroll Jun 11 '20 at 16:17

score 23 · Answer 2 · answered Apr 17 '12 at 08:45

How about:

ByteArrayOutputStream baos = new ByteArrayOutputStream();
workbook.write(baos);
byte[] xls = baos.toByteArray();

In order to get a full excel file out, you must call the write(OutputStream) method. If you want bytes from that, just give a ByteArrayOutputStream

How can I convert POI HSSFWorkbook to bytes?

2 Answers2

Linked