How to get cell style of empty cell apache POI

问题:

I am using poi-ooxml@3.17 to read and write excel file. I have added some styles/protection on some of cells. When i read the file i am not able to get cell styles applied to cells with no value as when i tries to access row/cell with empty value it returns null.

Below is code to write data in same excel file.

public static void writeDataToSheet(final Sheet sheet, final List<Map<String, Object>> sheetData) {

    List<String> columns =  getColumnNames(sheet);
    LOGGER.debug("Inside XLSXHelper writeDataToSheet {}", Arrays.asList(columns));
    IntStream.range(0, sheetData.size()).forEach((index) -> {
        if (Objects.isNull(sheet.getRow(index + 1))) {
            sheet.createRow(index + 1);
        }
        Row row = sheet.getRow(index + 1);
        Map<String, Object> data = sheetData.get(index);
        IntStream.range(0, columns.size()).forEach((colIndex) -> {
            String column = columns.get(colIndex);

            Cell cell = row.getCell(colIndex);
            if (Objects.isNull(cell)) {
                cell = row.createCell(colIndex);
            }
            cell.setCellValue(data.get(column) != null ? data.get(column).toString() : null);
        });
    });
}

Could anyone provide me a solution where i can read the styles applied to cell when cell is empty?

Thanks.

回答1:

Cells without content or explicit style applied are not present in the sheet because of not to increase the file size unnecessarily. So apache poi returns null for such cells.

If you are looking at the sheet in spreadsheet application, then maybe it looks as if all cells in a row or all cells in a column have the same style applied to. But this is not the case. In real the row and/or the column has the style applied to. Only cells in intersection of styled rows and columns must be present in the sheet having the last applied style.

If a new cell needs to be created, then the spreadsheet application gets the preferred style for that cell. This is either the already applied cell style or if that not present, then the row style (default cell style for this row) or if that not present, then the column style (default cell style for this column). Unfortunately apache poi does not do so. So we need doing this ourself:

 public CellStyle getPreferredCellStyle(Cell cell) {
  // a method to get the preferred cell style for a cell
  // this is either the already applied cell style
  // or if that not present, then the row style (default cell style for this row)
  // or if that not present, then the column style (default cell style for this column)
  CellStyle cellStyle = cell.getCellStyle();
  if (cellStyle.getIndex() == 0) cellStyle = cell.getRow().getRowStyle();
  if (cellStyle == null) cellStyle = cell.getSheet().getColumnStyle(cell.getColumnIndex());
  if (cellStyle == null) cellStyle = cell.getCellStyle();
  return cellStyle;
 }

This method may be used in code every time a new cell needs to be created:

...
   if (Objects.isNull(cell)) {
    cell = row.createCell(colIndex);
    cell.setCellStyle(getPreferredCellStyle(cell));
   }
...