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

2

12

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

Marcmarcano answered 26/10, 2016 at 4:0 Comment(1)
hello, what's the out in your codes? thanksIstria
A
15

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.

Accompanist answered 26/10, 2016 at 4:7 Comment(3)
Is there a way to achieve this with Apache POI? thanksPapa
The order of closing the workbook and the outputstream is very important here, it breaks in the latest versions of a lot of browsers if you close the outputstream before the workbookBotnick
hello, what's the out in your codes? thanksIstria
G
16

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();
Gaiser answered 30/9, 2017 at 10:22 Comment(0)
A
15

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.

Accompanist answered 26/10, 2016 at 4:7 Comment(3)
Is there a way to achieve this with Apache POI? thanksPapa
The order of closing the workbook and the outputstream is very important here, it breaks in the latest versions of a lot of browsers if you close the outputstream before the workbookBotnick
hello, what's the out in your codes? thanksIstria

© 2022 - 2024 — McMap. All rights reserved.