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

问题描述

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

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

public static void writeDataToSheet(final Sheet sheet, final List> sheetData) {列表<字符串>列 = getColumnNames(sheet);LOGGER.debug("内部 XLSXHelper writeDataToSheet {}", Arrays.asList(columns));IntStream.range(0, sheetData.size()).forEach((index) -> {if (Objects.isNull(sheet.getRow(index + 1))) {sheet.createRow(index + 1);}行行 = sheet.getRow(index + 1);映射<字符串，对象>数据 = sheetData.get(index);IntStream.range(0, columns.size()).forEach((colIndex) -> {字符串列 = columns.get(colIndex);Cell cell = row.getCell(colIndex);如果(Objects.isNull(单元格)){单元格 = row.createCell(colIndex);}cell.setCellValue(data.get(column) != null ? data.get(column).toString() : null);});});}

谁能为我提供一个解决方案，让我可以在单元格为空时读取应用于单元格的样式?

谢谢.

解决方案

没有应用内容或显式样式的单元格不会出现在工作表中，因为不会不必要地增加文件大小.所以 apache poi 为这样的单元返回 null .

如果您在电子表格应用程序中查看工作表，那么看起来好像一行中的所有单元格或列中的所有单元格都应用了相同的样式.但这种情况并非如此.实际上，行和/或列应用了样式.只有样式行和列交叉处的单元格必须存在于具有最后应用样式的工作表中.

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

 public CellStyle getPreferredCellStyle(Cell cell) {//获取单元格首选单元格样式的方法//这是已经应用的单元格样式//或者如果不存在，则行样式(该行的默认单元格样式)//或者如果不存在，则列样式(此列的默认单元格样式)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();返回单元格样式；}

每次需要创建新单元格时，可以在代码中使用此方法:

<预><代码>...如果(Objects.isNull(单元格)){单元格 = row.createCell(colIndex);cell.setCellStyle(getPreferredCellStyle(cell));}...

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.

解决方案

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

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