1. Overview

1.概述

Apache POI is a popular Java API to manipulate different kinds of Microsoft Office documents such as Word, Excel, and PowerPoint programmatically.

Apache POI 是一种流行的 Java API，用于以编程方式操作不同类型的 Microsoft Office 文档，如 Word、Excel 和 PowerPoint。

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.

我们经常需要扩展 Excel 电子表格中的列。这有助于读者更好地直观查看列中的内容，而默认列大小无法做到这一点。

In this tutorial, we’ll learn how to use the API to adjust the column width manually and automatically in Excel spreadsheets.

在本教程中，我们将学习如何使用 API 在 Excel 电子表格中手动和自动调整列宽。

2. Dependencies

2.依赖关系

First of all, we’ll need the following Apache POI dependencies in our Maven pom.xml:

首先，我们需要在 Maven pom.xml 中加入以下 Apache POI 依赖项：

<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

3.电子表格编制

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:

首先，让我们快速复习一下 Excel 电子表格的创建。我们将准备一个 Excel 电子表格，并在其中输入一些数据以进行演示：

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

4.宽度调整

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.

Apache POI 提供两种不同的列宽调整方式。我们可以根据自己的需求选择其中一种。现在就让我们来探讨一下这两种方法。

4.1. Fixed Width Adjustment

4.1.固定宽度调整

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.

我们可以通过在目标 Sheet 实例上调用 setColumnWidth() 将特定列扩展为固定宽度。此方法有两个参数，分别是 columnIndex 和 width 。

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.

手动推导显示所有内容的列宽非常复杂，因为它取决于各种因素，例如字体类型和字体大小。根据 API doc 中 setColumnWidth() 的定义，width 参数的单位是字符宽度的 1/256 分之一。

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:

鉴于 Excel 的默认字体为 Calibri，字体大小为 11，我们可以使用单元格中的字符数 * 256 作为列宽的粗略近似值：

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

4.2.自动宽度调整

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.

幸运的是，Apache POI 提供了一个方便的方法autoSizeColumn() ，可自动调整列宽。这确保了读者可以完全看到列的内容。

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:

autoSizeColumn()只需要 column 参数，即基于零的列索引。我们可以使用以下代码自动调整第 1 列的列宽：

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:

如果我们对每一列应用autoSizeColumn()，我们将看到以下效果。现在所有列的内容对读者来说都是完全可见的，没有任何截断：
</p

5. Conclusion

5.结论

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.

在本文中，我们探讨了 Apache POI 中调整 Excel 电子表格列宽的两种不同方法：固定宽度调整和自动宽度调整。调整列宽对于提高可读性和创建便于阅读的 Excel 电子表格至关重要。

As usual, the source code for the article is available over on GitHub.

与往常一样，本文的源代码可在 GitHub 上获取。