如何获取空单元格apache POI的单元格样式 [英] How to get cell style of empty cell apache POI

问题描述

我使用poi-ooxml@3.17来读写excel文件。我在一些单元格上添加了一些样式/保护。当我读取文件时，我无法将单元格样式应用于没有值的单元格，因为当我尝试访问具有空值的行/单元格时，它返回null。

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.

下面是在同一个excel文件中写入数据的代码。

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?

谢谢。

推荐答案

没有内容的单元格或由于不会不必要地增加文件大小，因此表单中不存在应用的显式样式。所以 apache poi 为这些单元格返回 null 。

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.

如果需要创建新单元格，则电子表格应用程序将获得该单元格的首选样式。这是已应用的单元格样式或如果不存在，则为行样式（此行的默认单元格样式）或如果不存在，则为列样式（此列的默认单元格样式）。不幸的是 apache poi 没有这样做。所以我们需要自己做这件事：

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

这篇关于如何获取空单元格apache POI的单元格样式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！