使用 Apache POI 将填充颜色和边框应用于 Excel 范围 [英] Apply Fill Colors and Borders to Excel Range Using Apache POI
问题描述
我正在使用 Apache POI 将 Excel VBA 脚本转换为 Java，但我卡住了.在 VBA 中，可以轻松地将填充颜色和边框连续应用于单元格.但是在 POI 中，如果不创建数百个 XSSFCellStyles [(使用的每种颜色)x(使用的每种边框组合)x(使用的每种字体)]，这似乎几乎是不可能的.我正在尝试重新创建当前通过 VBA 生成的电子表格:
I am working on converting an Excel VBA Script to Java using Apache POI and I am stuck. In VBA it is easy to apply both Fill Colors and Borders to cells in succession. But in POI it seems almost impossible without creating hundreds of XSSFCellStyles [(Every Color Used) x (Every Border Combination) x (Every Font Used)]. I am trying to recreate this spreadsheet, which is currently produced via VBA:
Spreadsheet Image
The code below is intended to begin formatting by filling the top two rows gray and adding the outside borders. I've broken the code up into three classes (please excuse any sloppy code or noob mistakes. Still on week 2 of learning Java):
Main Class:
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); } } }A Class with the Format Cells Loop:
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); } } }A Class with the Styles Defined:
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; } }
Through combing Stack Overflow, POI API Documents, etc, I've come to understand that a cell can only have a single XSSFCellStyle, so trying to add a border to an already-filled cell gets rid of the fill. Thus, my code just produces a set of cells filled black.
It seems strange to me that Apache POI can't do what VBA does so easily. I know I must be missing something / setting up the code wrong / etc.
As an aside, I have also tried using PropertyTemplate.drawBorders as described in the Apache POI API and I couldn't get that to work either - same result.
I'm hoping someone has come up with a clever way around this as I am at my wit's end.
Thanks for your help!
OK, let's have an example of how to create your sample table using apache poi without monkeying around using the low level objects.
The main approach is using CellUtil and PropertyTemplate for creating the needed cell styles. So we do not need creating each single needed cell style manually (each different colored edge of an bordered range for example). But we are nevertheless save not creating too much cell styles (one cell style for each cell for example).
But as mentioned in Not able to set custom color in XSSFCell Apache POI already, PropertyTemplate as well as CellUtil and RegionUtil are be based on ss.usermodel level only and not on xssf.usermodel level. But org.apache.poi.ss.usermodel.CellStyle does not know something about a setFillForegroundColor(Color color) until now. It only knows setFillForegroundColor(short bg). So ss.usermodel level simply cannot set a Color as fill foreground color until now. Only a short (a color index) is possible. This we must have in mind and only set cell fills using IndexedColors. Else it fails.
Example code, commented to describing what it does:
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();
}
}
Result:
这篇关于使用 Apache POI 将填充颜色和边框应用于 Excel 范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
