java有没有办法知道CellStyle已经存在于使用POI的工作簿中（以便重用），或者只复制Celstyle obj而不是引用

Question

我想将一些记录写入excel，但我知道XSSFWorkbook中的最大单元格样式是64000。但是超过64000的记录，考虑到我想对每个单元格或<强>应用新的^ {{CD2}}，我将用已经存在的单元格样式进行克隆。<强>

即使要克隆，我也需要使用默认的单元格样式^{}，但这超过了64001记录的java.lang.IllegalStateException: The maximum number of cell styles was exceeded.

So is there anyway in POI to know already particular cell style is present and make use of that or when is necessary to clone/create default cellstyle and clone.

克隆的原因是：有时列/行cellstyle和现有引用的excel单元格样式可能不同，因此我采用默认单元格样式并克隆列和列；世界其他地区及；单元格cellstyles到它

即使我尝试向映射添加默认样式map.put("defStyle",workbook.createCellStyle();)，但这无法正确克隆，因为它将在第一次尝试克隆时更改，因为It wont get the Object it will copy the reference即使对象克隆在这里也不可能，因为cellstyle没有实现cloneable interface

Answer 1

不容易。我提前定义了我需要的样式，然后将它们显式地应用于每个单元格，而不仅仅是为每个单元格创建新样式。注意，我在这些预定义的样式中不包括边框，只包括字体、颜色，有时还包括填充。对于borders，我稍后将它们绘制到电子表格中，以简化代码

Answer 2

一般来说，不必创建超过最大可能单元样式数的单元样式。要根据单元格的内容设置单元格格式，可以使用条件格式。还可以使用条件格式来格式化行（例如奇偶行）。也适用于专栏

因此，一般来说，不是每个单元格或大量单元格都应该使用单元格样式进行格式化。相反，应该创建较少的单元格样式，然后将其用作默认单元格样式，或者如果条件格式真的不可能，则在单个情况下使用

在我的示例中，所有单元格都有一个默认单元格样式，第一行有一个单行单元格样式（甚至可以使用条件格式实现）

要在将默认单元格样式应用于所有列后保持其工作状态，必须将其应用于所有新创建的apache poi单元格。为此，我提供了一个方法getPreferredCellStyle(Cell cell)Excel本身将自动将列（或行）单元格样式应用于新填充的单元格

如果仍然需要将单个单元格格式化为不同的格式，则应为此使用CellUtil。这就提供了“处理样式的各种方法允许您根据需要创建单元格样式。当您对单元格应用样式更改时，代码将尝试查看是否已经存在满足您需要的样式。如果不存在，则会创建新样式。这是为了防止创建过多样式。Excel中对可以支持的样式。“参见我的示例中的注释

import java.io.*;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import org.apache.poi.ss.util.CellUtil;

import java.util.Map;
import java.util.HashMap;

public class CarefulCreateCellStyles {

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

 public CarefulCreateCellStyles() throws Exception {

   Workbook workbook = new XSSFWorkbook();

   // at first we are creating needed fonts
   Font defaultFont = workbook.createFont();
   defaultFont.setFontName("Arial");
   defaultFont.setFontHeightInPoints((short)14);

   Font specialfont = workbook.createFont();
   specialfont.setFontName("Courier New");
   specialfont.setFontHeightInPoints((short)18);
   specialfont.setBold(true);

   // now we are creating a default cell style which will then be applied to all cells
   CellStyle defaultCellStyle = workbook.createCellStyle();
   defaultCellStyle.setFont(defaultFont);

   // maybe sone rows need their own default cell style
   CellStyle aRowCellStyle = workbook.createCellStyle();
   aRowCellStyle.cloneStyleFrom(defaultCellStyle);
   aRowCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
   aRowCellStyle.setFillForegroundColor((short)3);


   Sheet sheet = workbook.createSheet("Sheet1");

   // apply default cell style as column style to all columns
   org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCol cTCol = 
      ((XSSFSheet)sheet).getCTWorksheet().getColsArray(0).addNewCol();
   cTCol.setMin(1);
   cTCol.setMax(workbook.getSpreadsheetVersion().getLastColumnIndex());
   cTCol.setWidth(20 + 0.7109375);
   cTCol.setStyle(defaultCellStyle.getIndex());

   // creating cells
   Row row = sheet.createRow(0);
   row.setRowStyle(aRowCellStyle);
   Cell cell = null;
   for (int c = 0; c  < 3; c++) {
    cell = CellUtil.createCell(row, c, "Header " + (c+1));
    // we get the preferred cell style for each cell we are creating
    cell.setCellStyle(getPreferredCellStyle(cell));
   }

   System.out.println(workbook.getNumCellStyles()); // 3 = 0(default) and 2 just created

   row = sheet.createRow(1);
   cell = CellUtil.createCell(row, 0, "centered");
   cell.setCellStyle(getPreferredCellStyle(cell));
   CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);

   System.out.println(workbook.getNumCellStyles()); // 4 = 0 and 3 just created

   cell = CellUtil.createCell(row, 1, "bordered");
   cell.setCellStyle(getPreferredCellStyle(cell));
   Map<String, Object> properties = new HashMap<String, Object>();
   properties.put(CellUtil.BORDER_LEFT, BorderStyle.THICK);
   properties.put(CellUtil.BORDER_RIGHT, BorderStyle.THICK);
   properties.put(CellUtil.BORDER_TOP, BorderStyle.THICK);
   properties.put(CellUtil.BORDER_BOTTOM, BorderStyle.THICK);
   CellUtil.setCellStyleProperties(cell, properties);

   System.out.println(workbook.getNumCellStyles()); // 5 = 0 and 4 just created

   cell = CellUtil.createCell(row, 2, "other font");
   cell.setCellStyle(getPreferredCellStyle(cell));
   CellUtil.setFont(cell, specialfont);

   System.out.println(workbook.getNumCellStyles()); // 6 = 0 and 5 just created

// until now we have always created new cell styles. but from now on CellUtil will use
// already present cell styles if they matching the needed properties.

   row = sheet.createRow(2);
   cell = CellUtil.createCell(row, 0, "bordered");
   cell.setCellStyle(getPreferredCellStyle(cell));
   properties = new HashMap<String, Object>();
   properties.put(CellUtil.BORDER_LEFT, BorderStyle.THICK);
   properties.put(CellUtil.BORDER_RIGHT, BorderStyle.THICK);
   properties.put(CellUtil.BORDER_TOP, BorderStyle.THICK);
   properties.put(CellUtil.BORDER_BOTTOM, BorderStyle.THICK);
   CellUtil.setCellStyleProperties(cell, properties);

   System.out.println(workbook.getNumCellStyles()); // 6 = nothing new created

   cell = CellUtil.createCell(row, 1, "other font");
   cell.setCellStyle(getPreferredCellStyle(cell));
   CellUtil.setFont(cell, specialfont);

   System.out.println(workbook.getNumCellStyles()); // 6 = nothing new created

   cell = CellUtil.createCell(row, 2, "centered");
   cell.setCellStyle(getPreferredCellStyle(cell));
   CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);

   System.out.println(workbook.getNumCellStyles()); // 6 = nothing new created

   FileOutputStream out = new FileOutputStream("CarefulCreateCellStyles.xlsx");
   workbook.write(out);
   out.close();
   workbook.close();  
 }

 public static void main(String[] args) throws Exception {
  CarefulCreateCellStyles carefulCreateCellStyles = new CarefulCreateCellStyles();
 }
}