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

Alex Man picture Alex Man · Oct 26, 2016 · Viewed 20.2k times · Source

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

usr_11 picture usr_11 · Sep 30, 2017

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();