java - 使用 Apache POI 将填充颜色和边框应用到 Excel 范围

Question

我正在使用 Apache POI 将 Excel VBA 脚本转换为 Java，但我陷入了困境。在 VBA 中，可以轻松地将填充颜色和边框连续应用到单元格。但在 POI 中，如果不创建数百个 XSSFCellStyles [(使用的每种颜色)x(每种边框组合)x(使用的每种字体)]，这似乎几乎是不可能的。我正在尝试重新创建此电子表格，该电子表格目前是通过 VBA 生成的: Spreadsheet Image

下面的代码旨在通过将顶部两行填充为灰色并添加外部边框来开始格式化。我已将代码分为三类(请原谅任何草率的代码或菜鸟错误。仍在学习 Java 的第二周):

1. 主类:

   public class CreateExcel {
   
   public static void createExcel(String[] args) throws IOException {
   XSSFWorkbook workbook = new XSSFWorkbook();
   XSSFSheet resultsSheet = workbook.createSheet("Results");
   ExcelMethods format = new ExcelMethods();
   ExcelStyles style = new ExcelStyles();
   
   format.formatRange(workbook, resultsSheet, style.fillPMEDarkGray(workbook), 1, 2, 2, 14);
   format.formatRange(workbook, resultsSheet, style.borderLeft(workbook), 1, 2, 2, 1);
   format.formatRange(workbook, resultsSheet, style.borderRight(workbook), 1, 2, 16, 1);
   format.formatRange(workbook, resultsSheet, style.borderTop(workbook), 1, 1, 2, 14);
   format.formatRange(workbook, resultsSheet, style.borderBottom(workbook), 2, 1, 2, 14);
   
   try (FileOutputStream fileOut = new FileOutputStream("C:<file location>/workbook.xlsx")) {
       workbook.write(fileOut);
   
           }
       }
   }
   

2. 具有格式单元格循环的类:

   public class ExcelMethods {
   
   public void formatRange(XSSFWorkbook workbook,
                           XSSFSheet sheet,
                           XSSFCellStyle style,
                           int rowStart,
                           int numRows,
                           int columnStart,
                           int numColumns) {
       for (int i = rowStart; i <= rowStart + numRows; i++) {
           XSSFRow row = sheet.createRow(i);
           for (int j = columnStart; j <= columnStart + numColumns; j++) {
               XSSFCell cell = row.createCell(j);
               cell.setCellStyle(style);
           }
       }
   }
   

3. 定义了样式的类:

   public class ExcelStyles{
   
   public XSSFCellStyle fillPMEDarkGray(XSSFWorkbook workbook) {
       XSSFColor pmeDarkGray = new XSSFColor(new java.awt.Color(128, 128, 128));
       XSSFCellStyle fillCell = workbook.createCellStyle();
       fillCell.setFillForegroundColor(pmeDarkGray);
       fillCell.setFillPattern(SOLID_FOREGROUND);
   
       return fillCell;
   }
   
   public XSSFCellStyle borderLeft(XSSFWorkbook workbook) {
       XSSFCellStyle cellBorder = workbook.createCellStyle();
       cellBorder.setBorderLeft(BorderStyle.THICK);
   
       return cellBorder;
   }
   
   public XSSFCellStyle borderRight(XSSFWorkbook workbook) {
       XSSFCellStyle cellBorder = workbook.createCellStyle();
       cellBorder.setBorderRight(BorderStyle.THICK);
   
       return cellBorder;
   }
   
   public XSSFCellStyle borderTop(XSSFWorkbook workbook) {
       XSSFCellStyle cellBorder = workbook.createCellStyle();
       cellBorder.setBorderTop(BorderStyle.THICK);
   
       return cellBorder;
   }
   
   public XSSFCellStyle borderBottom(XSSFWorkbook workbook) {
       XSSFCellStyle cellBorder = workbook.createCellStyle();
       cellBorder.setBorderBottom(BorderStyle.THICK);
   
       return cellBorder;
       }
   }
   

通过梳理 Stack Overflow、POI API 文档等，我了解到一个单元格只能有一个 XSSFCellStyle，因此尝试向已填充的单元格添加边框会消除填充。因此，我的代码只生成一组填充黑色的单元格。

对我来说，Apache POI 无法轻松完成 VBA 所做的事情，这似乎很奇怪。我知道我一定错过了一些东西/设置了错误的代码/等等。

顺便说一句，我也尝试过按照 Apache POI API 中的描述使用 PropertyTemplate.drawBorders，但我也无法让它工作 - 相同的结果。

我希望有人能想出一个聪明的方法来解决这个问题，因为我已经无计可施了。

感谢您的帮助!

Answer 1

好的，让我们通过一个示例来了解如何使用 apache poi 创建示例表，而无需使用低级对象。

主要方法是使用CellUtil和PropertyTemplate来创建所需的单元格样式。因此，我们不需要手动创建每个所需的单元格样式(例如，有边框范围的每个不同颜色的边缘)。但我们仍然避免创建太多的单元格样式(例如，每个单元格一个单元格样式)。

但正如 Not able to set custom color in XSSFCell Apache POI 中提到的PropertyTemplate 以及 CellUtil 和 RegionUtil 已经仅基于 ss.usermodel 级别，而不是基于 xssf.usermodel级别。但是 org.apache.poi.ss.usermodel.CellStyle 到目前为止还不知道有关 setFillForegroundColor(Color color) 的信息。它只知道setFillForegroundColor(short bg)。因此，到目前为止，ss.usermodel级别根本无法将Color设置为填充前景色。只能使用short(颜色索引)。我们必须牢记这一点，并且只能使用 IndexedColors 设置单元格填充。否则会失败。

示例代码，注释描述其作用:

import java.io.FileOutputStream;

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

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

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

public class CreateExcelCellStyles {

 public static 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 static void main(String[] args) throws Exception {

  //the data
  Object[][] data = new Object[][]{
   {null, "Returns", "Benchmark 1", null, null, null, "Benchmark 2", null, null, null, "Benchmark 3", null, null, null},
   {null, null, "PME Plus", null, null, "Direct", "PME Plus", null, null, "Direct", "PME Plus", null, null, "Direct"}, 
   {null, null, "PME IRR", "IRR", "KS PME", "Alpha", "PME IRR", "IRR", "KS PME", "Alpha", "PME IRR", "IRR", "KS PME", "Alpha"}, 
   {"1 Year", .17, .162, .162, 1.01, .007, .191, .191, .99, -.018, .192, .192, .99, -.018}, 
   {"3 Year", null, null, null, null, null, null, null, null, null, null, null, null, null}, 
   {"5 Year", null, null, null, null, null, null, null, null, null, null, null, null, null}, 
   {"10 Year", null, null, null, null, null, null, null, null, null, null, null, null, null}, 
   {"20 Year", .103, .051, .059, 1.17, .048, .071, .074, 1.11, .03, .062, .066, 1.14, .037}, 
   {"Since Inception", .109, .062, .066, 1.15, .041, .079, .08, 1.10, .027, .073, .074, 1.12, .031}, 
  };

  //we need PropertyTemplate later
  PropertyTemplate propertyTemplate = new PropertyTemplate();

  //we need properties map for cell styles later
  Map<String, Object> properties;

  //creating workbook
  Workbook workbook = new XSSFWorkbook();

  //we need DataFormat later
  DataFormat format = workbook.createDataFormat();

  //creating default font
  Font defaultFont = workbook.createFont();
  defaultFont.setFontName("Calibri");
  defaultFont.setFontHeightInPoints((short)12);

  //we need font in bold and white for headings
  Font defaultFontWhite = workbook.createFont();
  defaultFontWhite.setFontName("Calibri");
  defaultFontWhite.setFontHeightInPoints((short)12);
  defaultFontWhite.setBold(true);
  defaultFontWhite.setColor(IndexedColors.WHITE.getIndex());

  //creating default cell style having default font
  CellStyle defaultCellStyle = workbook.createCellStyle();
  defaultCellStyle.setFont(defaultFont);

  //we need percent style for numbers later
  CellStyle percent = workbook.createCellStyle();
  percent.cloneStyleFrom(defaultCellStyle);
  percent.setDataFormat(format.getFormat("0.0%"));
  percent.setAlignment(HorizontalAlignment.CENTER);

  //we need user defined number style having unit "x" for numbers later
  CellStyle doubleX = workbook.createCellStyle();
  doubleX.cloneStyleFrom(defaultCellStyle);
  doubleX.setDataFormat(format.getFormat("0.00\\x"));
  doubleX.setAlignment(HorizontalAlignment.CENTER);

  //creating sheet
  Sheet sheet = workbook.createSheet();

  //set default column styles
  sheet.setDefaultColumnStyle(0, defaultCellStyle); //first column A = default
  for (int c = 1; c < 14; c++) sheet.setDefaultColumnStyle(c, percent); //columns B to N = percent; some will be overridden later
  sheet.setDefaultColumnStyle(4, doubleX); //column E = user defined number style having unit "x"
  sheet.setDefaultColumnStyle(8, doubleX); //column I = user defined number style having unit "x"
  sheet.setDefaultColumnStyle(12, doubleX); //column M = user defined number style having unit "x"

  //put data in sheet
  int r = 0;
  for (Object[] rowdata : data) {
   Row row = sheet.createRow(r++);
   int c = 0;
   for (Object celldata : rowdata) {
    Cell cell = row.createCell(c++);
    if (celldata instanceof String) cell.setCellValue((String)celldata);
    else if (celldata instanceof Double) cell.setCellValue((Double)celldata);
    cell.setCellStyle(getPreferredCellStyle(cell)); //get preferred cell style from column style
   }
  }

  //add merged regions
  sheet.addMergedRegion(new CellRangeAddress(0, 0, 2, 5));
  sheet.addMergedRegion(new CellRangeAddress(0, 0, 6, 9));
  sheet.addMergedRegion(new CellRangeAddress(0, 0, 10, 13));
  sheet.addMergedRegion(new CellRangeAddress(1, 1, 2, 4));
  sheet.addMergedRegion(new CellRangeAddress(1, 1, 6, 8));
  sheet.addMergedRegion(new CellRangeAddress(1, 1, 10, 12));

  sheet.addMergedRegion(new CellRangeAddress(0, 2, 0, 0));
  CellUtil.setVerticalAlignment(CellUtil.getCell(CellUtil.getRow(0, sheet), 0), VerticalAlignment.CENTER);
  sheet.addMergedRegion(new CellRangeAddress(0, 2, 1, 1));
  CellUtil.setVerticalAlignment(CellUtil.getCell(CellUtil.getRow(0, sheet), 1), VerticalAlignment.CENTER);

  //styling the table headings (rows 1 to 3)
  for (int rw = 0; rw < 3; rw++) {
   Row row = sheet.getRow(rw);
   for (int c = 0; c < 14; c++) {
    properties = new HashMap<String, Object>();
    properties.put(CellUtil.FILL_PATTERN, FillPatternType.SOLID_FOREGROUND);
    properties.put(CellUtil.FILL_FOREGROUND_COLOR, IndexedColors.GREY_50_PERCENT.getIndex()); //do using only IndexedColors for fills
    //properties.put(CellUtil.FONT, defaultFontWhite.getIndex()); //up to apache poi 3.17
    properties.put(CellUtil.FONT, defaultFontWhite.getIndexAsInt()); //since apache poi 4.0.0
    CellUtil.setCellStyleProperties(CellUtil.getCell(row, c), properties); //do using CellUtil for **add** new properties to already applied cell styles
   }
  }
  propertyTemplate.drawBorders(new CellRangeAddress(0, 2, 0, 13), BorderStyle.MEDIUM, BorderExtent.ALL); //since we have merged regions we can simply drawing all borders here

  //styling the table body (rows 4 to 9)
  for (int rw = 3; rw < 9; rw++) {
   Row row = sheet.getRow(rw);

   properties = new HashMap<String, Object>();
   properties.put(CellUtil.FILL_PATTERN, FillPatternType.SOLID_FOREGROUND);
   properties.put(CellUtil.FILL_FOREGROUND_COLOR, IndexedColors.LEMON_CHIFFON.getIndex());
   CellUtil.setCellStyleProperties(CellUtil.getCell(row, 0), properties); //column A

   properties = new HashMap<String, Object>();
   properties.put(CellUtil.FILL_PATTERN, FillPatternType.SOLID_FOREGROUND);
   properties.put(CellUtil.FILL_FOREGROUND_COLOR, IndexedColors.PALE_BLUE.getIndex());
   CellUtil.setCellStyleProperties(CellUtil.getCell(row, 1), properties); //column B

   for (int c = 2; c < 6; c++) {
    properties = new HashMap<String, Object>();
    properties.put(CellUtil.FILL_PATTERN, FillPatternType.SOLID_FOREGROUND);
    properties.put(CellUtil.FILL_FOREGROUND_COLOR, IndexedColors.LIGHT_GREEN.getIndex());
    CellUtil.setCellStyleProperties(CellUtil.getCell(row, c), properties); //columns C:F
   }
   for (int c = 6; c < 10; c++) {
    properties = new HashMap<String, Object>();
    properties.put(CellUtil.FILL_PATTERN, FillPatternType.SOLID_FOREGROUND);
    properties.put(CellUtil.FILL_FOREGROUND_COLOR, IndexedColors.LIGHT_ORANGE.getIndex());
    CellUtil.setCellStyleProperties(CellUtil.getCell(row, c), properties); //columns G:J
   }
   for (int c = 10; c < 14; c++) {
    properties = new HashMap<String, Object>();
    properties.put(CellUtil.FILL_PATTERN, FillPatternType.SOLID_FOREGROUND);
    properties.put(CellUtil.FILL_FOREGROUND_COLOR, IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
    CellUtil.setCellStyleProperties(CellUtil.getCell(row, c), properties); //columns K:N
   }
  }
  propertyTemplate.drawBorders(new CellRangeAddress(3, 8, 0, 0), BorderStyle.MEDIUM, BorderExtent.OUTSIDE); //outside border around A4:A9
  propertyTemplate.drawBorders(new CellRangeAddress(3, 8, 1, 1), BorderStyle.MEDIUM, BorderExtent.OUTSIDE); //outside border around B4:B9
  propertyTemplate.drawBorders(new CellRangeAddress(3, 8, 2, 5), BorderStyle.MEDIUM, BorderExtent.OUTSIDE); //outside border around C4:F9
  propertyTemplate.drawBorders(new CellRangeAddress(3, 8, 6, 9), BorderStyle.MEDIUM, BorderExtent.OUTSIDE); //outside border around G4:J9
  propertyTemplate.drawBorders(new CellRangeAddress(3, 8, 10, 13), BorderStyle.MEDIUM, BorderExtent.OUTSIDE); //outside border around K4:N9

  //apply the PropertyTemplate borders
  propertyTemplate.applyBorders(sheet);

  //resizing the columns A:N
  for (int c = 0; c < 14; c++) sheet.autoSizeColumn(c, true);

  //writing the result
  FileOutputStream out = new FileOutputStream("CreateExcelCellStyles.xlsx");
  workbook.write(out);
  out.close();
  workbook.close();

 }

}

结果: