get all column records in excel instead of getting only last column record using apache poi

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.

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

Advertisement

Answer