修改后的单元格字体无法保存(XSSF)

Question

我正在用Apache-POI 5.0编写Excel，但遇到了麻烦。也就是说，我迭代单元格来根据内容的长度()修改它们的字体大小。文件中的默认字体大小是20(. 下面的例子展示了这个bug:(仅限核心代码)

/**
* Export the present seat table through an given BufferedOutputStream
*
* @param out the BufferedOutputStream to be written
* @throws IOException            if <code>out</code> can not be written
* @throws InvalidFormatException NEVER HAPPENS unless you delete <code>templateOfTable.xlsx</code>
*/
void exportTable(BufferedOutputStream out) throws IOException, InvalidFormatException {
InputStream in = MainWindow.class.getResourceAsStream(GlobalVariables.TABLE_TEMPLATE_P);
assert in != null;
OPCPackage pkg = OPCPackage.open(in);
XSSFWorkbook wb = new XSSFWorkbook(pkg);
XSSFSheet table = wb.getSheetAt(0);
for (int row = 0; row < 6; row++) {
XSSFRow r = table.getRow(row);
for (int col = 0; col < 8; col++) {
writeCell(students[row][col],
r.getCell(col, Row.MissingCellPolicy.RETURN_NULL_AND_BLANK),
wb.createCellStyle());
}
}
// TEST POINT 1   iterate over cells
//        for (int row = 0; row < 6; row++) {
//            XSSFRow r = table.getRow(row);
//            for (int col = 0; col < 8; col++) {
//                System.out.println(r.getCell(col).getCellStyle().getFont().getFontHeightInPoints());
//            }
//        }
wb.write(out);
out.close();
pkg.close();
}
/**
* A method to write a cell.
*
* @param stu      the student to be stored
* @param cell     the cell to be written
* @param newStyle should be a new style, through <code>wb.createCellStyle()</code>
*/
private void writeCell(Student stu, XSSFCell cell, XSSFCellStyle newStyle) {
newStyle.cloneStyleFrom(cell.getCellStyle());
cell.setCellValue(stu.getName());

//KEY CODE
newStyle.getFont().setFontHeightInPoints((short) (stu.isLongName() ? 17 : 20));
newStyle.setFillForegroundColor(stu.isBoarding() ?
IndexedColors.PALE_BLUE.getIndex() :
IndexedColors.LIGHT_GREEN.getIndex());
newStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cell.setCellStyle(newStyle);
//TEST POINT 0
//        System.out.println(cell.getCellStyle().getFont().getFontHeightInPoints());
}

在test point 0，输出如下:

20
17
20
20
... (Several "17" and "20")
20

但在test point 1，事情似乎是错误的:

20
20
...
20 (All the lines are "20")

这是最奇怪的事情，因为在输出文件中，每个单元格的颜色和内容都是正确的，但是字体大小(以点为单位的高度)都是20。
我在网上找了很长时间。但是没有用。请帮助或尝试给一些想法如何实现这一目标。提前谢谢。

Answer 1

方法CellStyle.cloneStyleFrom克隆单元格样式，但不克隆字体。因此，您一直使用相同的字体来更改字体大小为17或20。使用最后一个字体大小集。你的价格是20美元。

在Excel存储中，单元格样式和字体都存储在工作簿级别。单元格共享单元格样式，单元格样式共享字体。知道了这一点，CellStyle.cloneStyleFrom实际上并没有多大用处。就像您使用它一样，它为每个单元格创建一个单元格样式，即使这些单元格样式完全相同并且应该共享。此外，如前所述，它不会克隆使用的字体。它也不应该，因为如果不同的单元格样式是相同的字体那么字体应该是共享的。背景是，单元格样式的计数和工作簿中的字体都有限制。

因此，如果需要根据条件改变单元格样式和字体，那么要么你需要在将它们应用到单元格之前创建所有需要的单元格样式和字体，要么你需要一个真正能够改变单元格样式或字体的一些特殊属性的方法。单元格样式有celltil。但是到目前为止，还没有关于字体的内容。

在下面的完整示例中，我提供了一些方法来改变所使用字体的一些特殊属性。此外，我使用CellUtil来改变使用的单元格样式的一些特殊属性。这是完整的，经过测试并适用于XSSF和HSSF。如果它考虑使用共享的单元格样式和字体，以避免达到限制。

import java.io.FileInputStream;
import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellUtil;
import java.util.Map;
import java.util.HashMap;
public class ExcelVaryFonts {

//method for getting current font from cell
private static Font getFont(Cell cell) {
Workbook workbook = cell.getSheet().getWorkbook();
CellStyle style = cell.getCellStyle();
return workbook.getFontAt(style.getFontIndex());
}
private enum FontProperty {
BOLD, COLOR, FONTHEIGHT, FONTNAME, ITALIC, STRIKEOUT, TYPEOFFSET, UNDERLINE
}
//method for getting font having special settings additional to given source font
private static Font getFont(Workbook workbook, Font fontSrc, Map<FontProperty, Object> fontproperties) {
boolean isBold = fontSrc.getBold();
short color = fontSrc.getColor();
short fontHeight = fontSrc.getFontHeight();
String fontName = fontSrc.getFontName();
boolean isItalic = fontSrc.getItalic();
boolean isStrikeout = fontSrc.getStrikeout();
short typeOffset = fontSrc.getTypeOffset();
byte underline = fontSrc.getUnderline();
for (FontProperty property : fontproperties.keySet()) {
switch (property) {
case BOLD:
isBold = (boolean)fontproperties.get(property);
break;
case COLOR:
color = (short)fontproperties.get(property);
break;
case FONTHEIGHT:
fontHeight = (short)fontproperties.get(property);
break;
case FONTNAME:
fontName = (String)fontproperties.get(property);
break;
case ITALIC:
isItalic = (boolean)fontproperties.get(property);
break;
case STRIKEOUT:
isStrikeout = (boolean)fontproperties.get(property);
break;
case TYPEOFFSET:
typeOffset = (short)fontproperties.get(property);
break;
case UNDERLINE:
underline = (byte)fontproperties.get(property);
break;
}
}
Font font = workbook.findFont(isBold, color, fontHeight, fontName, isItalic, isStrikeout, typeOffset, underline);
if (font == null) {
font = workbook.createFont();
font.setBold(isBold);
font.setColor(color);
font.setFontHeight(fontHeight);
font.setFontName(fontName);
font.setItalic(isItalic);
font.setStrikeout(isStrikeout);
font.setTypeOffset(typeOffset);
font.setUnderline(underline);
}
return font;
}

private static void writeCell(Student stu, Cell cell) {
Map<String, Object> styleproperties = null;
Map<FontProperty, Object> fontproperties = null;

Workbook workbook = cell.getSheet().getWorkbook();

cell.setCellValue(stu.getName());

//get or create the needed font 20pt
fontproperties = new HashMap<FontProperty, Object>();
fontproperties.put(FontProperty.FONTHEIGHT, (short)(20*20));
Font font20 = getFont(workbook, getFont(cell), fontproperties);
//get or create the needed font 17pt
fontproperties = new HashMap<FontProperty, Object>();
fontproperties.put(FontProperty.FONTHEIGHT, (short)(17*20));
Font font17 = getFont(workbook, getFont(cell), fontproperties);

//create style propertes for cell
styleproperties = new HashMap<String, Object>();
if (stu.isLongName()) {
styleproperties.put(CellUtil.FONT, font17.getIndex());
} else {
styleproperties.put(CellUtil.FONT, font20.getIndex());
}

if (stu.isBoarding()) {
styleproperties.put(CellUtil.FILL_FOREGROUND_COLOR, IndexedColors.PALE_BLUE.getIndex());
styleproperties.put(CellUtil.FILL_PATTERN, FillPatternType.SOLID_FOREGROUND);
} else {
styleproperties.put(CellUtil.FILL_FOREGROUND_COLOR, IndexedColors.LIGHT_GREEN.getIndex());
styleproperties.put(CellUtil.FILL_PATTERN, FillPatternType.SOLID_FOREGROUND);
}

//set style properties to cell
CellUtil.setCellStyleProperties(cell, styleproperties);  
}

public static void main(String[] args) throws Exception {
String inFilePath = "./ExcelExampleIn.xlsx"; String outFilePath = "./ExcelExampleOut.xlsx";
//String inFilePath = "./ExcelExampleIn.xls"; String outFilePath = "./ExcelExampleOut.xls";

Student[][] students = new Student[][] {
new Student[]{new Student("Jane", true, false), new Student("John has a long name", false, true), new Student("Foo", true, false)},   
new Student[]{new Student("Bar has a very long name", true, true), new Student("Stud", false, false)},   
new Student[]{new Student("Next student having a long name", true, true), new Student("Bar", false, false), new Student("Test", true, false)},   
};
try (Workbook workbook = WorkbookFactory.create(new FileInputStream(inFilePath));
FileOutputStream out = new FileOutputStream(outFilePath ) ) {
Sheet sheet = workbook.getSheetAt(0);
for (int r = 0; r < students.length; r++) {
Row row = CellUtil.getRow(r, sheet);
for (int c = 0; c < students[r].length; c++) {
Cell cell = CellUtil.getCell(row, c);
writeCell(students[r][c], cell);
}
}

workbook.write(out);
}
}
}

使用Student类来完成:

public class Student {
private String name;
private boolean boarding;
private boolean longName;
public Student(String name, boolean boarding, boolean longName) {
this.name = name;
this.boarding = boarding; 
this.longName = longName; 
}
public String getName() {
return this.name;   
}
public boolean isBoarding() {
return this.boarding;   
}
public boolean isLongName() {
return this.longName;   
}
}