有没有办法知道CellStyle已经存在于工作簿中（重用）使用POI或仅复制Celstyle obj而不是参考

2 回答

• 3

  通常，没有必要创建尽可能多的单元格样式，使它们超过可能的单元格样式的最大数量 . 要根据内容格式化单元格，可以使用条件格式 . 另外，为了格式化行（例如，奇数/偶数行不同），可以使用条件格式 . 也适用于列 .

  因此，通常不应使用单元格样式格式化每个单元格或大量单元格 . 相反，应该创建更少的单元格样式，然后将其用作默认单元格样式，或者在单个情况下，如果实际上无法实现条件格式化 .

  在我的例子中，我有一个所有单元格的默认单元格样式和第一行的单行单元格样式（即使这可以使用条件格式实现） .

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

  如果然后有必要格式化不同的单个单元格，那么应该使用CellUtil . 这提供"various methods that deal with style's allow you to create your CellStyles as you need them. When you apply a style change to a cell, the code will attempt to see if a style already exists that meets your needs. If not, then it will create a new style. This is to prevent creating too many styles. there is an upper limit in Excel on the number of styles that can be supported."请参阅我的示例中的注释 .

  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
  
  
     workbook.write(new FileOutputStream("CarefulCreateCellStyles.xlsx"));
     workbook.close();  
   }
  
   public static void main(String[] args) throws Exception {
    CarefulCreateCellStyles carefulCreateCellStyles = new CarefulCreateCellStyles();
   }
  }
  

  回复于 2024-04-27T19:31:48+08:00
• 1

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

  回复于 2024-04-27T19:31:48+08:00