1. Introduction
1.导言
In this quick tutorial, we’ll explore effective methods for applying a bold font style to entire rows in Excel sheets using the Apache POI library. Through straightforward examples and valuable insights, we’ll navigate the nuances of each method.
在本快速教程中，我们将探讨使用 Apache POI 库对 Excel 表中的整行应用粗体字体样式的有效方法。通过直观的示例和宝贵的见解，我们将了解每种方法的细微差别。
2. Dependency
2.依赖性
Let’s start with the dependency we need to write and load Excel files, poi:
让我们从编写和加载 Excel 文件所需的依赖关系 poi 开始：
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.2.5</version>
</dependency>3. Scenario and Helper Methods
3.情景和辅助方法
Our scenario involves creating a sheet with a header row and a few data rows. Then, we’ll define a bold style for the font used in the header row. Ultimately, we’ll create a few methods to set this bold style. Most importantly, we’ll see why we need more than one to do this, as the obvious choice (setRowStyle()) doesn’t work as expected.
我们的方案包括创建一个具有标题行和若干数据行的工作表。然后，我们将为标题行中使用的字体定义粗体样式。最后，我们将创建几个方法来设置这种粗体样式。最重要的是，我们将看到为什么我们需要多个方法来完成这项工作，因为显而易见的选择（setRowStyle()）并不能像预期的那样工作。
To facilitate sheet creation, we’ll start with a utils class. Let’s write a couple of methods to create cells and rows with cells:
为了便于创建工作表，我们将从实用工具类开始。让我们编写几个方法来创建单元格和带有单元格的行：
public class PoiUtils {
private static void newCell(Row row, String value) {
short cellNum = row.getLastCellNum();
if (cellNum == -1)
cellNum = 0;
Cell cell = row.createCell(cellNum);
cell.setCellValue(value);
}
public static Row newRow(Sheet sheet, String... rowValues) {
Row row = sheet.createRow(sheet.getLastRowNum() + 1);
for (String value : rowValues) {
newCell(row, value);
}
return row;
}
// ...
}Then, to create a bold font style, we’ll first create a font from our Workbook, then call setBold(true). Secondly, we’ll create a CellStyle that will use our bold font:
然后，为了创建粗体字体样式，我们将首先从 Workbook 中创建一个字体，然后调用 setBold(true). 其次，我们将创建一个 CellStyle 来使用我们的粗体字体：
public static CellStyle boldFontStyle(Workbook workbook) {
Font boldFont = workbook.createFont();
boldFont.setBold(true);
CellStyle boldStyle = workbook.createCellStyle();
boldStyle.setFont(boldFont);
return boldStyle;
}Finally, to write our sheet to a file, we need to call write() on our Workbook:
最后，要将工作表写入文件，我们需要在 Workbook 上调用 write() ：
public static void write(Workbook workbook, Path path)
throws IOException {
try (FileOutputStream fileOut = new FileOutputStream(path.toFile())) {
workbook.write(fileOut);
}
}4. Caveats of Using setRowStyle()
4.使用 setRowStyle() 的注意事项
When looking at the POI API, the most obvious choice for our task is Row.setRowStyle(). Unfortunately, this method doesn’t work reliably, and a bug is currently open. The problem seems to be that the Microsoft Office render ignores the row style and only cares about cell styles.
在查看 POI API 时，最明显的选择是Row.setRowStyle()。遗憾的是，该方法并不能可靠地工作，目前bug尚未解决。 问题似乎在于 Microsoft Office 呈现忽略了行样式，而只关心单元格样式。
On the other hand, it works with OpenOffice, but only if we use the SXSSFWorkbook implementation, which is intended for large files. To test this, we’ll start with a sample sheet method:
另一方面，它在 OpenOffice 中也能工作，但前提是我们使用 SXSSFWorkbook 实现，该实现适用于大文件。为了测试这一点，我们将从一个示例工作表方法开始：
private void writeSampleSheet(Path destination, Workbook workbook)
throws IOException {
Sheet sheet = workbook.createSheet();
CellStyle boldStyle = PoiUtils.boldFontStyle(workbook);
Row header = PoiUtils.newRow(sheet, "Name", "Value", "Details");
header.setRowStyle(boldStyle);
PoiUtils.newRow(sheet, "Albert", "A", "First");
PoiUtils.newRow(sheet, "Jane", "B", "Second");
PoiUtils.write(workbook, destination);
}Then, an assertion method is used to check the styles on the first and second rows. First, we assert the first Row has a bold font style. Then, for each cell in it, we assert the default style isn’t the same as the style we set for the first Row. This asserts our row style has priority. Finally, we assert that the second Row doesn’t contain any style applied:
然后，使用断言方法检查第一行和第二行的样式。首先，我们断言第一行 Row 具有粗体字体样式。然后，对于其中的每个单元格，我们断言默认样式与我们为第一行 Row 设置的样式不相同。最后，我们断言第二个 Row 没有应用任何样式：
private void assertRowStyleAppliedAndDefaultCellStylesDontMatch(Path sheetFile)
throws IOException, InvalidFormatException {
try (Workbook workbook = new XSSFWorkbook(sheetFile.toFile())) {
Sheet sheet = workbook.getSheetAt(0);
Row row0 = sheet.getRow(0);
XSSFCellStyle rowStyle = (XSSFCellStyle) row0.getRowStyle();
assertTrue(rowStyle.getFont().getBold());
row0.forEach(cell -> {
XSSFCellStyle style = (XSSFCellStyle) cell.getCellStyle();
assertNotEquals(rowStyle, style);
});
Row row1 = sheet.getRow(1);
XSSFCellStyle row1Style = (XSSFCellStyle) row1.getRowStyle();
assertNull(row1Style);
Files.delete(sheetFile);
}
}Ultimately, our test consists of writing the sheet to a temporary file and then reading it back. We ensure our style is applied, test the styles in the first and second rows, and then delete the file:
最终，我们的测试包括将工作表写入临时文件，然后读回。我们要确保应用了样式，测试第一行和第二行的样式，然后删除文件：
@Test
void givenXssfWorkbook_whenSetRowStyle1stRow_thenOnly1stRowStyled()
throws IOException, InvalidFormatException {
Path sheetFile = Files.createTempFile("xssf-row-style", ".xlsx");
try (Workbook workbook = new XSSFWorkbook()) {
writeSampleSheet(sheetFile, workbook);
}
assertRowStyleAppliedAndDefaultCellStylesDontMatch(sheetFile);
}When running this test, we can now check that we get our bold style only for the first row, which is what we intended.
运行此测试时，我们现在可以检查我们是否只在第一行获得了粗体样式，这正是我们想要的。
5. Using setCellStyle() Cells in the Row
5.在行中使用 setCellStyle() 单元格
Given the problems with setRowStyle(), we’re left with setCellStyle(). We’ll need it to set the style for every cell in the row where we want to apply our bold style. So, let’s modify our original by iterating through each row in our header and calling setCellStyle() with our bold style instead:
鉴于 setRowStyle() 所存在的问题，我们只能使用 setCellStyle()。我们需要它来为行中每一个要应用粗体样式的单元格设置样式。因此，让我们修改原来的设置，迭代标题中的每一行，并使用粗体样式调用 setCellStyle()：
@Test
void givenXssfWorkbook_whenSetCellStyleForEachRow_thenAllCellsContainStyle()
throws IOException, InvalidFormatException {
Path sheetFile = Files.createTempFile("xssf-cell-style", ".xlsx");
try (Workbook workbook = new XSSFWorkbook()) {
Sheet sheet = workbook.createSheet();
CellStyle boldStyle = PoiUtils.boldFontStyle(workbook);
Row header = PoiUtils.newRow(sheet, "Name", "Value", "Details");
header.forEach(cell -> cell.setCellStyle(boldStyle));
PoiUtils.newRow(sheet, "Albert", "A", "First");
PoiUtils.write(workbook, sheetFile);
}
// ...
}This way, we can guarantee our style is applied consistently across formats and platforms. Let’s finish our test by asserting row styles aren’t set and that every cell in the first row contains a bold font style:
通过这种方式，我们可以保证在不同格式和平台上应用的样式保持一致。最后，让我们断言行样式未设置，并且第一行中的每个单元格都包含粗体字体样式：
try (Workbook workbook = new XSSFWorkbook(sheetFile.toFile())) {
Sheet sheet = workbook.getSheetAt(0);
Row row0 = sheet.getRow(0);
XSSFCellStyle rowStyle = (XSSFCellStyle) row0.getRowStyle();
assertNull(rowStyle);
row0.forEach(cell -> {
XSSFCellStyle style = (XSSFCellStyle) cell.getCellStyle();
assertTrue(style.getFont().getBold());
});
Row row1 = sheet.getRow(1);
rowStyle = (XSSFCellStyle) row1.getRowStyle();
assertNull(rowStyle);
Files.delete(sheetFile);
}Note that we’re only using XSSFWorkbook here for convenience. This method works consistently across all Workbook implementations.
请注意，为了方便起见，我们在这里只使用 XSSFWorkbook 。此方法在所有 Workbook 实现中均可一致使用。
6. Conclusion
6.结论
In this article, we learned that while setRowStyle() may not reliably fulfill our goal, we’ve uncovered a robust alternative using setCellStyle(). We can now confidently format rows in Excel sheets, ensuring consistent and visually impactful results across various platforms.
在本文中，我们了解到虽然 setRowStyle() 可能无法可靠地实现我们的目标，但我们发现了使用 setCellStyle() 的强大替代方法。现在，我们可以自信地格式化 Excel 表单中的行，确保在不同平台上获得一致且具有视觉冲击力的结果。
As always, the source code is available over on GitHub.
与往常一样，源代码可在 GitHub 上获取。