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