creating an excel file in memory using java and pass as bytes for downloading

Question

I have created a excel file using jxl library. The code is working fine but the only problem is that each time for building an excel file from the dynamic values coming from a service, the excel content is overwriting onto a test.xls like as shown below. Is there any way to build an excel in memory and pass the byte for downloading it instead of creating an external file ("test.xls")

File file = new File("test.xls");
WritableWorkbook workbook = Workbook.createWorkbook(file);
:
:
:
:

InputStream in = new FileInputStream(file);

if (in == null) {
    out.close();
}
else 
{
    byte[] buffer = new byte[4096];
    int len;

    while ((len = in.read(buffer)) != -1) {
        out.write(buffer, 0, len);
    }

    out.flush();
    in.close();
    out.close();
}

Can anyone please help me on this

Answer 1

Use a ByteArrayOutputStream in combination with the Workbook.createWorkbook(OutputStream os) method to create the workbook in memory, and dump the created byte array to whatever output stream you want.

ByteArrayOutputStream baos = new ByteArrayOutputStream();
WritableWorkbook workbook = Workbook.createWorkbook(baos);

// ...

workbook.close();
out.write(baos.toByteArray());
out.flush();
out.close();

Alternatively, you could do it on the fly, without using the intermediate byte array:

WritableWorkbook workbook = Workbook.createWorkbook(out);

// ...

workbook.close();
out.flush();
out.close();

This method may be preferable as JXL is keeping the workbook in memory anyway, and only flushes it to he output stream when the workbook is closed.

Answer 2

Here is example to create excel using POI and converting it to bytes.

    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet("Sheet 1");

    Row row = sheet.createRow(1);
    Cell cell = row.createCell(cellnum++);
    cell.setCellValue((String) obj);
    ByteArrayOutputStream bos = new ByteArrayOutputStream();
    try {
        workbook.write(bos);
    } catch (IOException e) {
    } finally {
        try {
            bos.close();
            workbook.close();
        } catch (IOException e) {
        }
    }
    byte[] bytes = bos.toByteArray();