1. Introduction
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.
2. Dependency
Let’s start with the dependency we need to write and load Excel files, poi:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.2.5</version>
</dependency>
3. Scenario and Helper Methods
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.
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:
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:
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()
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.
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:
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:
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
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:
@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.
6. Conclusion
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.
As always, the source code is available over on GitHub.
