1. Overview
Apache POI is a popular Java API to manipulate different kinds of Microsoft Office documents such as Word, Excel, and PowerPoint programmatically.
It’s quite often that we need to expand columns in Excel spreadsheets. This is a common requirement when we produce spreadsheets for people to read. This helps readers to visualize the content in the columns better, which can’t be done with the default column size.
In this tutorial, we’ll learn how to use the API to adjust the column width manually and automatically in Excel spreadsheets.
2. Dependencies
First of all, we’ll need the following Apache POI dependencies in our Maven pom.xml:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.2.5</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.5</version>
</dependency>
3. Spreadsheet Preparation
Let’s start by having a quick revision on creating an Excel spreadsheet. We’ll prepare an Excel spreadsheet and populate some data into it for demonstration purposes:
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("NewSheet");
Row headerRow = sheet.createRow(0);
Cell headerCell1 = headerRow.createCell(0);
headerCell1.setCellValue("Full Name");
Cell headerCell2 = headerRow.createCell(1);
headerCell2.setCellValue("Abbreviation");
Row dataRow = sheet.createRow(1);
Cell dataCell1 = dataRow.createCell(0);
dataCell1.setCellValue("Java Virtual Machine");
Cell dataCell2 = dataRow.createCell(1);
dataCell2.setCellValue("JVM");
// More data rows created here...
Now, if we open the generated spreadsheet in Excel, we’ll see that every column has the same default width:
Obviously, the content within the 1st column is too long and truncated due to the limited column width.
4. Width Adjustment
Apache POI offers two different ways to adjust the column width. We can choose either way depending on our own requirements. Let us explore both approaches now.
4.1. Fixed Width Adjustment
We can expand a particular column to a fixed width by calling setColumnWidth() on the target Sheet instance. There are two arguments in this method, which are columnIndex and width, respectively.
It’s complex to manually derive the column width showing all the content, as it depends on various factors such as font type and font size. According to the definition of setColumnWidth() in the API doc, the width argument is in units of 1/256th of a character width.
Given the default font Calibri with font size 11 in Excel, we could use the number of characters in the cell * 256 for the column width as a rough approximation:
String cellValue = row.getCell(0).getStringCellValue();
sheet.setColumnWidth(0, cellValue.length() * 256);
After the adjustment, we’ll see the entire content in the 1st column:
It’s kind of a hassle to derive the column width by ourselves. Especially when we are dealing with spreadsheets containing numerous data rows. We must go through each row to identify the maximum character count. The presence of columns that include different fonts and font sizes adds further complexity to the width calculation.
4.2. Auto Width Adjustment
Fortunately, Apache POI provides a convenient method, autoSizeColumn(), to adjust the column width automatically. This ensures the content of the column can be fully visible to readers.
autoSizeColumn() requires only the column argument, which is a zero-based column index. We can use the following code to auto-adjust the column width on the 1st column:
sheet.autoSizeColumn(0);
We’ll see the following if we apply autoSizeColumn() to every column. All columns’ content is now fully visible to the readers without any truncation:
5. Conclusion
In this article, we’ve explored two different approaches in Apache POI to adjusting the column width of Excel spreadsheets: Fixed width adjustment and auto width adjustment. Adjusting column widths is essential for improving readability and creating reader-friendly Excel spreadsheets.
As usual, the source code for the article is available over on GitHub.
