java Apache POI：为包含不同样式的列设置边框的优雅方式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37155979/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Apache POI: Elegant way to set borders to a column which contains different styles
提问by samjaf
I'm dynamically creating a new xlsx file using apache-poi. Any column can contain different value types (Numbers, Strings, Booleans, ...). While inserting data into the poi document I set CellStyles depending on the type of the data:
我正在使用 apache-poi 动态创建一个新的 xlsx 文件。任何列都可以包含不同的值类型（数字、字符串、布尔值等）。在将数据插入 poi 文档时，我根据数据类型设置了 CellStyles：
public final XSSFCellStyle cellStyleString;
public final XSSFCellStyle cellStyleNumber;
public final XSSFCellStyle cellStyleDate;
public final XSSFCellStyle cellStyleHeader;
This is how my header rows look like:
这是我的标题行的样子：
| | | | Shared Header |
| H1| H2| H3|SH1|SH2|SH3|SH4|
There are "simple" headers and "shared headers" which contain "sub headers". Shared headers reside in merged cells.
有“简单”标题和包含“子标题”的“共享标题”。共享标题驻留在合并的单元格中。
No I'd like to have a left border at column SH1and a right border at column SH4to emphasize the grouping. But as any column could contain a mix of all cellstyles, it seems like I have to create CellStyles like
不，我想在列上有一个左边框，在列上SH1有一个右边框SH4来强调分组。但是由于任何列都可能包含所有单元格样式的混合，因此我似乎必须像创建单元格样式一样
public final XSSFCellStyle cellStyleString;
public final XSSFCellStyle cellStyleStringBorderLeft;
public final XSSFCellStyle cellStyleStringBorderRight;
//and so on for the other styles...
Furthermore there could be nested shared header which I'd like to distinguish by different border sizes. So I'd need something like
此外，我想通过不同的边框大小来区分嵌套的共享标头。所以我需要类似的东西
public final XSSFCellStyle cellStyleString;
public final XSSFCellStyle cellStyleStringBorderLeftThickLine;
public final XSSFCellStyle cellStyleStringBorderRightThickLine;
public final XSSFCellStyle cellStyleStringBorderLeftThinLine;
public final XSSFCellStyle cellStyleStringBorderRightThinLine;
//and so on for the other styles...
Is there a more elegant way to set the borders of the column regardless of the already existing style?
无论已经存在的样式如何，是否有更优雅的方法来设置列的边框？
Edit
编辑
Although I prefer a clean and simple approach and that for to minimize the number of created styles, I stumpled upon the HSSFOptimiserwhich removes duplicate cellstyles. I didn't know about that class. Even though I prefer avoiding this utility it fits the problem and deserves to be mentioned here.
虽然我更喜欢干净简单的方法，并且为了尽量减少创建的样式数量，我还是偶然发现了HSSFOptimiser，它删除了重复的单元样式。我不知道那门课。尽管我更喜欢避免使用此实用程序，但它符合问题并且值得在此提及。
回答by jmarkmurphy
I am nearing the end of an enhancement to POI that will let you fill out the values with their specific styles, then draw borders around them without having to manually create all the necessary styles for that. In the mean-time, there is a way to do it using CellUtil.setCellStyleProperties(). This lets you add a set of properties to the CellStylethat already exists for a cell.
我即将结束对 POI 的增强，它可以让您使用特定样式填充值，然后在它们周围绘制边框，而无需为此手动创建所有必要的样式。同时，有一种方法可以使用CellUtil.setCellStyleProperties(). 这使您可以将一组属性添加到CellStyle已存在的单元格中。
From the POI Quick Guide for HSSF/XSSF:
来自 HSSF/XSSF 的 POI 快速指南：
Workbook workbook = new XSSFWorkbook(); // OR new HSSFWorkbook()
Sheet sheet = workbook.createSheet("Sheet1");
Map<String, Object> properties = new HashMap<String, Object>();
// create your spreadsheet without borders
...
// create property set for vertical borders
properties.put(CellUtil.BORDER_LEFT, CellStyle.BORDER_MEDIUM);
properties.put(CellUtil.BORDER_RIGHT, CellStyle.BORDER_MEDIUM);
// Apply the borders to a 3x3 region starting at D4
for (int ix=3; ix <= 5; ix++) {
row = sheet.createRow(ix);
for (int iy = 3; iy <= 5; iy++) {
cell = row.createCell(iy);
CellUtil.setCellStyleProperties(cell, properties);
}
}
This allows you to basically fill in your spreadsheet, then draw the borders one cell at a time. Note if all your borders are similar (all THIN) then this will work for your entire range. But, if you wanted to draw MEDIUM borders around the outside of the table, you would have to create some additional property sets. Note, you don't have to use createRow()and createCell()for rows and cells that are already in your spreadsheet. This will work around merged cells.
这允许您基本上填写电子表格，然后一次绘制一个单元格的边框。请注意，如果您的所有边框都相似（全部为 THIN），那么这将适用于您的整个范围。但是，如果您想在表格外部绘制 MEDIUM 边框，则必须创建一些额外的属性集。请注意，您不必对电子表格中已有的行和单元格使用createRow()和createCell()。这将解决合并的单元格。
Note: CellUtil.setCellStyleProperties()appeared in POI 3.14 and allows you to add multiple cell properties in a single shot which avoids the creation of multiple unused styles. The older CellUtil.setCellStyleProperty()sets a single property at a time and, as an unintended consequence, creates intermediate CellStyleobjects in the spreadsheet which turn out never being used. This can be a problem in larger sheets.
注意：CellUtil.setCellStyleProperties()出现在 POI 3.14 中，允许您在一次拍摄中添加多个单元格属性，从而避免创建多个未使用的样式。旧的一次CellUtil.setCellStyleProperty()设置一个属性，作为一个意想不到的结果，CellStyle在电子表格中创建了中间对象，这些对象从未被使用过。这在较大的纸张中可能是一个问题。
Edit:
PropertyTemplateis a new object added in POI 3.15 which will allow you to define a group of borders for cells and stamp it onto any sheet you want to apply it to. This object is like creating a preprinted form to overlay the data. See the POI Spreadsheet quick guide for more information on how to use PropertyTemplate.
编辑：
PropertyTemplate是在 POI 3.15 中添加的一个新对象，它允许您为单元格定义一组边框并将其标记到您想要应用的任何工作表上。这个对象就像创建一个预印的表格来覆盖数据。有关如何使用的更多信息，请参阅 POI 电子表格快速指南PropertyTemplate。
回答by Thomas Raffelsieper
EDIT:
编辑：
So how about utilizing the hash of the POI objects for caching and keeping track of decorated objects. The other created CellStyles that are not being used will be thrown away by the garbage collection.
那么如何利用 POI 对象的散列来缓存和跟踪装饰对象。其他未使用的创建的 CellStyles 将被垃圾收集丢弃。
Here is our cache:
这是我们的缓存：
private Map<Integer, MyCellStyle> styleCache = new HashMap<>();
And our own CellStyle class
还有我们自己的 CellStyle 类
final class MyCellStyle implements Cloneable {
private XSSFCellStyle xssfCellStyle;
public MyCellStyle(XSSFCellStyle xssfCellStyle) {
this.xssfCellStyle = xssfCellStyle;
}
@Override
public MyCellStyle clone() {
MyCellStyle clone = new MyCellStyle(xssfCellStyle);
return clone;
}
public final MyCellStyle borderLeftMedium() {
MyCellStyle result = clone();
result.xssfCellStyle.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM);
return result;
}
... further decorations
public XSSFCellStyle getXSSFCellStyle() {
return xssfCellStyle;
}
}
now to avoid creating new objects we write a small function
现在为了避免创建新对象，我们编写了一个小函数
private MyCellStyle getCellStyle(MyCellStyle targetStyle) {
int targetHash = targetStyle.hashCode();
if (styleCache.keySet().contains(targetHash)) {
return styleCache.get(targetHash);
} else {
return styleCache.put(targetHash, targetStyle);
}
}
then we can create the cells themselves like this:
然后我们可以像这样创建单元格本身：
public void createCells() {
Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet();
Row row = sheet.createRow(1);
Cell cell = row.createCell(1);
MyCellStyle baseStyle = new MyCellStyle(
(XSSFCellStyle) wb.createCellStyle());
MyCellStyle decoratedStyle = getCellStyle(baseStyle.borderLeftMedium());
cell.setCellStyle(decoratedStyle.getXSSFCellStyle());
}
If the hashCode is not unique for the same properties of the MyCellStyle object we might have to override the hashCode function:
如果对于 MyCellStyle 对象的相同属性， hashCode 不是唯一的，我们可能必须覆盖 hashCode 函数：
@Override
public int hashCode() {
return hashValue;
}
and add the styles value within each of our decoration functions:
并在我们的每个装饰函数中添加样式值：
public final MyCellStyle borderLeftMedium() {
MyCellStyle result = clone();
result.xssfCellStyle.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM);
hashValue += XSSFCellStyle.BORDER_MEDIUM; // simplified hash
return result;
}
=======================
========================
ORIGINAL:
原来的：
I like to create decorating methods that add a certain aspect of a cell to a cell style. So at first you create your base style
我喜欢创建将单元格的某个方面添加到单元格样式的装饰方法。所以首先你创建你的基本样式
public final XSSFCellStyle cellStyleStringBase = wb.createCellStyle();
and create decorator methods to create a certain style
并创建装饰器方法来创建某种风格
public XSSFCellStyle addBorderLeft(XSSFCellStyle style) {
XSSFCellStyle result = style.clone();
result.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM);
return result;
}
Now, if you want to avoid to create new objects, you will still have to keep the cellStyles in own variables, you will not be able to avoid that, but from my experience the performance is sufficient if you simply decorate your cells like this
现在，如果您想避免创建新对象，您仍然必须将 cellStyles 保留在自己的变量中，您将无法避免这种情况，但是根据我的经验，如果您像这样简单地装饰您的单元格，性能就足够了
cell1.setCellStyle(addBorderLeft(cellStyleStringBase);
cell2.setCellStyle(addBorderRight(addBorderRight(cellStyleStringBase));
...
If you decorate with a lot of styles it makes sense to create your own CellStyle Class
如果您使用多种样式进行装饰，则创建自己的 CellStyle 类是有意义的
public final MyCellStyle implements Cloneable {
private XSSFCellStyle xssfCellStyle;
public MyCellStyle(XSSFCellStyle xssfCellStyle) {
this.xssfCellStyle = xssfCellStyle;
}
@Override
public MyCellStyle clone() {
MyCellStyle clone = new MyCellStyle(this.xssfCellStyle);
return clone;
}
public final MyCellStyle borderLeftMedium() {
return this.clone().setBorderLeft(XSSFCellStyle.BORDER_MEDIUM);
}
public final MyCellStyle borderRightThick() {
...
}
you can then build your style in a better readable manner:
然后，您可以以更好的可读方式构建您的风格：
MyCellStyle base = new MyCellStyle(cellStyleStringBase);
cell1.setCellStyle(base
.addBorderLeftMedium()
.addBorderRightThick()
.addBorderBottomThin());
untested, but I hope it helps.
未经测试，但我希望它有所帮助。
回答by Christoph-Tobias Schenke
As you already mentioned it is not good to create thousands and thousands of similiar cell-style-objects. in my project i created a simple "style-helper" class which has a map in it, which is aware of all existing style-instances
正如您已经提到的那样，创建成千上万个类似的单元格样式对象并不好。在我的项目中，我创建了一个简单的“样式助手”类，其中有一个地图，它知道所有现有的样式实例
private Workbook workbook;
private HashMap<String, CellStyle> styleMap = new HashMap<>();
public CellStyle getStyle(Font font, ExcelCellAlign hAlign, ExcelCellAlign vAlign, boolean wrapText, ExcelCellBorder border, Color color, ExcelCellFormat cellFormat) {
//build unique which represents the style
String styleKey = ((font != null) ? font.toString() : "") + "_" + hAlign + "_" + vAlign + (wrapText ? "_wrapText" : "") + ((border != null) ? "_" + border.toString() : "") + "_"
+ styleKeyColor + (cellFormat != null ? "_" + cellFormat.toString() : "");
if (styleMap.containsKey(styleKey)) {
//return existing instance from map
return styleMap.get(styleKey);
} else {
//create new style from workbook
CellStyle cellStyle = workbook.createCellStyle();
// set all formattings to new cellStyle object
if (font != null) {
cellStyle.setFont(font);
}
// alignment
if (vAlign != null) {
cellStyle.setVerticalAlignment(vAlign.getAlign());
}
//... snip ...
//border
if (border != null) {
if (border.getTop() > BorderFormatting.BORDER_NONE) {
cellStyle.setBorderTop(border.getTop());
cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
}
//... snip ...
}
if (color != null) {
XSSFColor xssfColor = new XSSFColor(color);
((XSSFCellStyle)cellStyle).setFillForegroundColor(xssfColor);
}
}
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
styleMap.put(styleKey, cellStyle);
return cellStyle;
}
}
The parameter ExcelCellAlign is a simple enum which encapsulates the values for CellStyle.ALIGN_LEFT, CellStyle.ALIGN_RIGHT, ... ExcelCellBorder is similiar to the Align. Just hide the values :-) ExcelCellFormat is a enum which holds default-patterns for fortmatting the value.
参数 ExcelCellAlign 是一个简单的枚举，它封装了 CellStyle.ALIGN_LEFT、CellStyle.ALIGN_RIGHT、... ExcelCellBorder 的值，类似于 Align。只需隐藏值:-) ExcelCellFormat 是一个枚举，它包含用于格式化值的默认模式。
I hope this is a good start for your own implementation. Feel free to ask if something is not clear
我希望这是您自己实施的良好开端。如果有什么不清楚的，请随时询问
