Matlab中cell(javaobj),java – 有没有办法知道CellStyle已经存在于Workbook中(重用)使用POI或仅复制Celstyle obj而不是引用...

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

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

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

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

如果然后有必要将不同的单个单元格格式化,那么应该使用CellUtil.这提供了“处理样式的各种方法,允许您根据需要创建CellStyles.当您将样式更改应用于单元格时,代码将尝试查看是否已存在满足您需求的样式.如果没有,则它将创建一个新的样式.这是为了防止创建太多的样式.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 properties = new HashMap();

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();

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();

}

}