Excel 97-2003 (.xls)文件中单元格样式丢失或未显示

Question

我正在使用Apache POI库将数据导出到Excel。我已经尝试了所有最新版本(3.17、4.1.2和5.2.1)。

在与单元格样式有关的Excel 97(.xls)格式中，我遇到了问题。在一定数量的列之后，单元格样式会丢失(或未显示)。

以下是我的示例代码:

private void exportXls() {
  try (
      OutputStream os = new FileOutputStream("test.xls");
      Workbook wb = new HSSFWorkbook();) {
    Sheet sh = wb.createSheet("test");
    Row r = sh.createRow(0);
    for (int i = 0; i < 50; i++) {
      Cell c = r.createCell(i);
      c.setCellValue(i + 1);
      
      CellStyle cs = wb.createCellStyle();
      cs.setFillBackgroundColor(IndexedColors.WHITE.index);
      cs.setFillPattern(FillPatternType.SOLID_FOREGROUND);
      cs.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
      c.setCellStyle(cs);
    }
    wb.write(os);
    os.flush();
  } catch (Exception e) {
    e.printStackTrace();
  }
}

并且在MS Excel 2019中查看的结果如下图所示： 在MS Excel中查看

可以看到，在第43个单元格之后，样式/格式已经丢失。

但是，当我使用其他应用程序打开相同的文件，比如来自Microsoft Store的XLS Viewer Free或Google Sheets（在线），样式/格式仍然存在，并且显示良好。 在XLS Viewer Free中查看 在Google Sheets中查看

请问有人能告诉我这里发生了什么情况吗？

我的代码有什么问题吗？

MS Excel中是否有任何隐藏的设置导致了这个问题？

谢谢。

- Hoang Ngo

---

你可能已经用完了单元格样式 - Excel有一个限制。在循环外创建样式？ - Gagravarr

https://stackoverflow.com/search?q=%5Bapache-poi%5D+cell+style+limit+workbook+level - Axel Richter

@Gagravarr，这怎么可能呢？如果您查看我的示例代码，您会发现我只创建了50个单元格对应的50个单元格样式。而POI文档显示HSSF最多可以有4000种独特的样式。这是我的文件链接：https://drive.google.com/file/d/1ooU5hQBEiDyVac2WjGL7PO8PQNB7uEOA/view?usp=sharing - Hoang Ngo

Answer 1

使用apache poi为每个单元格创建单独的单元格样式并不是一个好主意。在Excel中，单元格样式存储在工作簿级别上。如果可能，工作表和单元格共享单元格样式。

在所有Excel版本中，不同单元格样式的最大计数都有限制。二进制*.xls的限制小于OOXML*.xlsx的限制。

仅仅因为这个限制不能成为你得到的结果的唯一原因。但似乎Excel对工作簿中完全相同的50个单元格样式并不太满意。这些是内存浪费，因为只需要一个共享样式，所有50个单元格共享相同的样式。

解决方案如下：

在单元格创建循环之外，在工作簿级别上创建单元格样式，并仅在循环中将样式设置为单元格。

例如：

 private static void exportXlsCorrect() {
  try (
   OutputStream os = new FileOutputStream("testCorrect.xls");
   Workbook wb = new HSSFWorkbook();) {
       
   CellStyle cs = wb.createCellStyle();
   cs.setFillBackgroundColor(IndexedColors.WHITE.index);
   cs.setFillPattern(FillPatternType.SOLID_FOREGROUND);
   cs.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
    
   Sheet sh = wb.createSheet("test");
   Row r = sh.createRow(0);
   for (int i = 0; i < 50; i++) {
    Cell c = r.createCell(i);
    c.setCellValue(i + 1);
      
    c.setCellStyle(cs);
   }
   wb.write(os);
   os.flush();
  } catch (Exception e) {
   e.printStackTrace();
  }
 }

有时，在创建单元格之前，不可能知道所有可能需要的单元格样式。在这种情况下，可以使用CellUtil。它具有一种CellUtil.setCellStyleProperties方法，能够向单元格设置特定的样式属性。这样做只会在需要时在工作簿级别上创建新的单元格样式。如果已经存在，则使用现有的单元格样式。

示例：

 private static void exportXlsUsingCellUtil() {
  try (
   OutputStream os = new FileOutputStream("testUsingCellUtil.xls");
   Workbook wb = new HSSFWorkbook();) {
            
   Sheet sh = wb.createSheet("test");
   Row r = sh.createRow(0);
   for (int i = 0; i < 50; i++) {
    Cell c = r.createCell(i);
    c.setCellValue(i + 1);
    
    java.util.Map<java.lang.String,java.lang.Object> properties = new java.util.HashMap<java.lang.String,java.lang.Object>();
    properties.put(org.apache.poi.ss.util.CellUtil.FILL_BACKGROUND_COLOR, IndexedColors.WHITE.index);
    properties.put(org.apache.poi.ss.util.CellUtil.FILL_FOREGROUND_COLOR, IndexedColors.LIGHT_BLUE.getIndex());
    properties.put(org.apache.poi.ss.util.CellUtil.FILL_PATTERN, FillPatternType.SOLID_FOREGROUND);
    org.apache.poi.ss.util.CellUtil.setCellStyleProperties(c, properties);

   }
   wb.write(os);
   os.flush();
  } catch (Exception e) {
   e.printStackTrace();
  }
 }

- Axel Richter

---

你是对的：“但似乎Excel不太喜欢工作簿中有50个完全相同的单元格样式。这些会浪费内存，因为所有50个单元格都共享同一个样式。”。非常感谢。 - Hoang Ngo