i am trying to get all column records in excel using apache poi. But i am getting only last column data in excel. Below is my code. In below code i am trying to create new row when it reaches specific number of columns. and in each row it as to put all column data.
public static ByteArrayInputStream export(List<String> records) { int TOTAL_COLUMN = 8; ByteArrayInputStream output = null; XSSFWorkbook workbook = null; InputStream inputStream = null; try (ByteArrayOutputStream excelFileStream = new ByteArrayOutputStream();) { inputStream = new ClassPathResource(TEMPLATE_PATH).getInputStream(); workbook = new XSSFWorkbook(inputStream); XSSFSheet sheet = workbook.getSheetAt(DATA_SHEET_INDEX); XSSFCellStyle evenRowCellStyle = createCellStyle(workbook, EVEN_ROW_CELL_COLOR); XSSFCellStyle oddRowCellStyle = createCellStyle(workbook, ODD_ROW_CELL_COLOR); Integer rowIndex = STARTING_ROW; int numberOfColumn = 0; XSSFCellStyle cellStyle = oddRowCellStyle; /** * Populates row cell details with list data */ int totalColumn = TOTAL_COLUMN; for (String data : records) { if (numberOfColumn == totalColumn) { rowIndex++; numberOfColumn = 0; } cellStyle = (rowIndex % 2 == 0) ? evenRowCellStyle : oddRowCellStyle; CellUtil.createCell(sheet.createRow(rowIndex), numberOfColumn, data, cellStyle); numberOfColumn++; } workbook.write(excelFileStream); output = new ByteArrayInputStream(excelFileStream.toByteArray()); } catch (Exception e) { output = null; log.info("Error occurred while exporting to excel sheet."); } finally { if (workbook != null) { try { workbook.close(); } catch (IOException e) { log.error("Error occurred while closing excel."); } } Utils.closeInputStream(inputStream); } return output; }
above code is giving only last column data in each row.
Advertisement
Answer
In your code the call sheet.createRow(rowIndex)
always creates a new empty row. So all formerly set cell values in that row get lost.
You are using CellUtil
already. There is CellUtil.getRow what does the following:
Get a row from the spreadsheet, and create it if it doesn’t exist.
This not always creates a new empty row. Instead it tries to get the row at first and only creates a new row if the row does not exists already,
So do using:
CellUtil.createCell(CellUtil.getRow(rowIndex, sheet), numberOfColumn, data, cellStyle);