发布于 由admin

Java Apache POI操作Excel文件读写与格式控制

引言：Java与Excel的“亲密接触”

大家好，欢迎来到今天的讲座！今天我们要聊的是如何用Java操作Excel文件。没错，就是那个你每天都在用、用来记录数据、做报表、甚至偶尔用来画画的Excel。你可能已经知道，Excel不仅仅是一个简单的表格工具，它还可以用来处理复杂的数据分析和可视化。但你知道吗？通过Java编程语言，我们可以自动化这些操作，让Excel变得更加强大和灵活。

在日常工作中，我们经常需要处理大量的Excel文件，比如读取数据、写入新的内容、格式化单元格、生成图表等等。手动操作不仅耗时，而且容易出错。而Java中的Apache POI库，正是为了解决这些问题而生。它提供了一套强大的API，让我们可以用代码轻松地操作Excel文件，无论是读取、写入，还是格式控制，都可以一气呵成。

那么，什么是Apache POI呢？简单来说，POI是“Poor Obfuscation Implementation”的缩写，但它并不是一个糟糕的实现，恰恰相反，它是一个非常强大且灵活的库。POI最早是由Apache基金会开发的，主要用于操作Microsoft Office文件格式，包括Excel、Word和PowerPoint。今天我们主要关注的是它的Excel部分，即HSSF（用于操作旧版本的.xls文件）和XSSF（用于操作新版本的.xlsx文件）。

在接下来的时间里，我们将深入探讨如何使用Apache POI来读取、写入Excel文件，并进行格式控制。我们会通过具体的代码示例和实际操作，帮助你掌握这些技能。无论你是Java新手，还是有经验的开发者，相信今天的讲座都会让你有所收获。

所以，准备好你的IDE，打开你的代码编辑器，让我们一起开始这段Java与Excel的“亲密接触”之旅吧！

Apache POI简介

在正式进入代码实战之前，我们先来了解一下Apache POI的基本概念和历史背景。Apache POI是一个开源的Java库，专门用于操作Microsoft Office文件格式。它的名字虽然听起来有点奇怪——“Poor Obfuscation Implementation”，但实际上它是一个非常强大且功能丰富的库。POI最初是为了支持Excel文件的操作而开发的，后来逐渐扩展到支持Word、PowerPoint等其他Office文件格式。

HSSF vs XSSF

在Apache POI中，Excel文件的操作主要依赖于两个核心模块：HSSF和XSSF。

  • HSSF (Horrible SpreadSheet Format)：这是POI最早的Excel操作模块，主要用于处理旧版本的Excel文件（.xls格式）。HSSF可以读取和写入97-2003版本的Excel文件，但它也有一些局限性，比如性能较差，尤其是在处理大型文件时。如果你的项目中仍然需要支持老版本的Excel文件，HSSF仍然是一个不错的选择。

  • XSSF (XML SpreadSheet Format)：这是POI的现代Excel操作模块，主要用于处理2007及以后版本的Excel文件（.xlsx格式）。XSSF基于XML格式，因此它可以更好地处理复杂的Excel文件，支持更多的功能，如公式、图表、条件格式等。相比HSSF，XSSF的性能更好，尤其是在处理大型文件时。不过，XSSF的内存占用较大，因此在处理超大数据集时需要注意优化。

选择HSSF还是XSSF？

在实际开发中，我们应该根据具体需求选择合适的模块。如果你的项目只需要处理旧版本的Excel文件，或者对性能要求不高，可以选择HSSF。但如果你需要处理新版本的Excel文件，或者希望利用更多高级功能，XSSF显然是更好的选择。此外，XSSF还支持流式读取和写入，可以在一定程度上缓解内存压力。

其他相关模块

除了HSSF和XSSF，Apache POI还提供了其他一些有用的模块：

  • SXSSF (Streaming XSSF)：这是一个基于XSSF的流式处理模块，适用于处理超大数据集。SXSSF通过将数据写入临时文件的方式，减少了内存占用，从而提高了性能。如果你需要处理几万行甚至几十万行的数据，SXSSF是一个非常好的选择。

  • POI-OOXML：这个模块提供了对Office Open XML格式的支持，主要用于解析和生成Excel、Word和PowerPoint文件的XML内容。它通常与XSSF一起使用，提供了一些额外的功能，如样式、字体、颜色等的定义。

  • POI-Scratchpad：这个模块包含了一些实验性的功能，主要用于处理一些非标准的Office文件格式。虽然它的稳定性不如HSSF和XSSF，但在某些特殊场景下可能会派上用场。

Maven依赖配置

在开始编写代码之前，我们需要确保项目中已经引入了Apache POI的相关依赖。最简单的方式是通过Maven来管理这些依赖。如果你还没有使用Maven，强烈建议你考虑一下，因为它可以大大简化项目的依赖管理和构建过程。

添加Maven依赖

首先，在你的pom.xml文件中添加以下依赖项：

<dependencies>
    <!-- Apache POI for Excel (HSSF and XSSF) -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>5.2.3</version>
    </dependency>

    <!-- Apache POI for OOXML (required for XSSF) -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>5.2.3</version>
    </dependency>

    <!-- Apache POI for SXSSF (optional, for large files) -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml-schemas</artifactId>
        <version>4.1.2</version>
    </dependency>

    <!-- Optional: For better performance with large files -->
    <dependency>
        <groupId>org.apache.commons</groupId>
        <artifactId>commons-collections4</artifactId>
        <version>4.4</version>
    </dependency>
</dependencies>

这里我们引入了三个主要的依赖：

  • poi：这是Apache POI的核心库，包含了HSSF和XSSF的基础功能。
  • poi-ooxml：这是XSSF模块的依赖，用于处理Excel 2007及以后版本的文件。
  • poi-ooxml-schemas：这个依赖是可选的，但它对于处理大型文件非常重要，尤其是当你使用SXSSF时。

此外，我们还引入了commons-collections4，这是一个常用的集合框架，可以帮助我们在处理Excel文件时提高性能。

版本选择

在选择依赖版本时，建议尽量选择最新的稳定版本。Apache POI的更新频率较高，新版本通常会修复一些已知的bug，并增加一些新的功能。你可以通过查阅Apache POI的官方文档或GitHub仓库来获取最新的版本信息。

创建并写入Excel文件

现在我们已经配置好了Maven依赖，接下来就可以开始编写代码了。首先，我们来学习如何创建一个新的Excel文件，并向其中写入数据。这一步看似简单，但却是后续操作的基础。我们将会使用XSSF模块来创建一个.xlsx格式的文件，并向其中添加一些基础数据。

初始化工作簿和表格

在Apache POI中，Excel文件被称为“工作簿”（Workbook），而每个工作簿可以包含多个“表格”（Sheet）。为了创建一个新的Excel文件，我们首先需要创建一个XSSFWorkbook对象，然后在这个工作簿中添加一个或多个表格。

下面是一个简单的代码示例，展示了如何创建一个新的Excel文件，并向其中添加一个表格：

import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFCell;

import java.io.FileOutputStream;
import java.io.IOException;

public class CreateExcelFile {
    public static void main(String[] args) {
        // Step 1: 创建一个新的工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();

        // Step 2: 在工作簿中创建一个新的表格
        XSSFSheet sheet = workbook.createSheet("My First Sheet");

        // Step 3: 创建第一行（第0行）
        XSSFRow row = sheet.createRow(0);

        // Step 4: 在第一行中创建单元格，并设置值
        XSSFCell cell = row.createCell(0);
        cell.setCellValue("Hello, World!");

        // Step 5: 创建第二行（第1行），并添加更多数据
        XSSFRow row2 = sheet.createRow(1);
        XSSFCell cell2 = row2.createCell(0);
        cell2.setCellValue("This is a test.");

        // Step 6: 将工作簿写入文件
        try (FileOutputStream fileOut = new FileOutputStream("example.xlsx")) {
            workbook.write(fileOut);
            System.out.println("Excel file created successfully.");
        } catch (IOException e) {
            e.printStackTrace();
        }

        // Step 7: 关闭工作簿，释放资源
        try {
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

代码解析

  1. 创建工作簿：我们使用XSSFWorkbook类来创建一个新的工作簿。这个对象代表整个Excel文件。

  2. 创建表格：通过调用workbook.createSheet()方法，我们可以在工作簿中创建一个新的表格。你可以为表格指定一个名称，比如“My First Sheet”。

  3. 创建行和单元格：每张表格由多行组成，每一行又包含多个单元格。我们使用sheet.createRow()方法来创建一行，然后使用row.createCell()方法来创建单元格。最后，通过cell.setCellValue()方法为单元格设置值。

  4. 写入文件：当我们完成了所有数据的写入后，需要将工作簿写入一个文件。我们使用FileOutputStream来指定输出文件的路径，并调用workbook.write()方法将数据写入文件。

  5. 关闭工作簿：为了避免资源泄露，我们使用workbook.close()方法来关闭工作簿。这是一个非常重要的步骤，尤其是在处理大型文件时。

运行结果

运行上述代码后，你会在当前目录下看到一个名为example.xlsx的文件。打开这个文件，你会发现它包含两行数据，分别是“Hello, World!”和“This is a test.”。

写入多种类型的数据

在实际应用中，Excel表格中通常会包含多种类型的数据，如字符串、数字、日期、布尔值等。Apache POI提供了丰富的API，可以轻松地处理这些不同类型的数据。下面我们来看几个常见的例子。

写入字符串

我们已经在前面的例子中展示了如何写入字符串。实际上，setCellValue()方法可以直接接受String类型的参数，因此写入字符串非常简单。例如：

XSSFCell cell = row.createCell(0);
cell.setCellValue("Hello, World!");

写入数字

写入数字时，我们可以使用setCellValue()方法的重载版本，接受double类型的参数。这样可以确保数字能够正确地显示在Excel中。例如：

XSSFCell cell = row.createCell(1);
cell.setCellValue(123.45);

如果你想写入整数，也可以直接传递int或long类型的值，POI会自动将其转换为double类型。

写入日期

写入日期稍微复杂一些，因为Excel中的日期是以特殊的格式存储的。为了确保日期能够正确显示，我们需要使用XSSFCellStyle来设置单元格的格式。具体步骤如下：

  1. 创建一个XSSFCellStyle对象。
  2. 使用DataFormat类为单元格设置日期格式。
  3. 将格式应用到单元格中。
  4. 使用setCellValue()方法设置日期值。

以下是一个完整的示例代码：

import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;

import java.util.Date;

// ...

// Step 1: 创建一个单元格样式
XSSFCellStyle dateStyle = workbook.createCellStyle();

// Step 2: 获取数据格式对象，并设置日期格式
DataFormat format = workbook.createDataFormat();
dateStyle.setDataFormat(format.getFormat("yyyy-MM-dd"));

// Step 3: 创建一个包含日期的单元格
XSSFCell dateCell = row.createCell(2);
dateCell.setCellStyle(dateStyle);
dateCell.setCellValue(new Date());

写入布尔值

写入布尔值也非常简单，只需使用setCellValue()方法的布尔版本即可。例如：

XSSFCell cell = row.createCell(3);
cell.setCellValue(true);

写入公式

除了静态数据，Excel还支持动态计算的公式。通过Apache POI，我们可以在单元格中写入公式，并让Excel自动计算结果。例如，假设我们有一个表格，其中A1和B1分别包含两个数字，我们可以在C1中写入一个求和公式：

XSSFCell cellA1 = row.createCell(0);
cellA1.setCellValue(10);

XSSFCell cellB1 = row.createCell(1);
cellB1.setCellValue(20);

XSSFCell cellC1 = row.createCell(2);
cellC1.setCellFormula("A1 + B1");

当用户打开这个Excel文件时，C1单元格会自动显示A1和B1的和，即30。

读取Excel文件

我们已经学会了如何创建和写入Excel文件，接下来我们来看看如何读取现有的Excel文件。读取Excel文件的过程与写入类似，只不过我们需要从文件中读取数据，而不是写入数据。我们仍然使用XSSFWorkbook类来加载文件，然后遍历表格中的行和单元格，提取所需的数据。

读取文件的基本步骤

  1. 加载工作簿：使用FileInputStream从文件中读取数据，并将其加载到XSSFWorkbook对象中。
  2. 获取表格：通过workbook.getSheetAt()或workbook.getSheetByName()方法获取特定的表格。
  3. 遍历行和单元格：使用sheet.getRow()方法获取每一行，然后使用row.getCell()方法获取每个单元格的内容。
  4. 处理不同类型的单元格：根据单元格的类型（字符串、数字、日期等），使用不同的方法来提取数据。

以下是一个完整的代码示例，展示了如何读取一个Excel文件并打印其中的内容：

import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFCell;

import java.io.FileInputStream;
import java.io.IOException;

public class ReadExcelFile {
    public static void main(String[] args) {
        String filePath = "example.xlsx";

        try (FileInputStream fis = new FileInputStream(filePath);
             XSSFWorkbook workbook = new XSSFWorkbook(fis)) {

            // Step 1: 获取第一个表格
            XSSFSheet sheet = workbook.getSheetAt(0);

            // Step 2: 遍历表格中的每一行
            for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                XSSFRow row = sheet.getRow(rowIndex);
                if (row == null) {
                    continue; // 跳过空行
                }

                // Step 3: 遍历每一行中的每一个单元格
                for (int cellIndex = 0; cellIndex < row.getLastCellNum(); cellIndex++) {
                    XSSFCell cell = row.getCell(cellIndex);
                    if (cell == null) {
                        continue; // 跳过空单元格
                    }

                    // Step 4: 根据单元格类型提取数据
                    switch (cell.getCellType()) {
                        case STRING:
                            System.out.print(cell.getStringCellValue() + "t");
                            break;
                        case NUMERIC:
                            if (DateUtil.isCellDateFormatted(cell)) {
                                System.out.print(cell.getDateCellValue() + "t");
                            } else {
                                System.out.print(cell.getNumericCellValue() + "t");
                            }
                            break;
                        case BOOLEAN:
                            System.out.print(cell.getBooleanCellValue() + "t");
                            break;
                        case FORMULA:
                            System.out.print(cell.getCellFormula() + "t");
                            break;
                        default:
                            System.out.print("Unknownt");
                    }
                }
                System.out.println(); // 换行
            }

            System.out.println("Excel file read successfully.");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

代码解析

  1. 加载工作簿：我们使用FileInputStream从文件中读取数据，并将其加载到XSSFWorkbook对象中。注意，FileInputStream需要在try-with-resources语句中声明，以确保文件流在使用完毕后自动关闭。

  2. 获取表格：通过workbook.getSheetAt(0)方法获取工作簿中的第一个表格。你也可以使用workbook.getSheetByName()方法根据表格名称获取特定的表格。

  3. 遍历行和单元格：我们使用sheet.getLastRowNum()方法获取表格中的最后一行索引，然后通过sheet.getRow()方法逐行读取数据。对于每一行，我们使用row.getLastCellNum()方法获取该行中的最后一个单元格索引，然后通过row.getCell()方法逐个读取单元格。

  4. 处理不同类型的单元格：根据单元格的类型（字符串、数字、日期、布尔值、公式等），我们使用不同的方法来提取数据。例如，cell.getStringCellValue()用于提取字符串，cell.getNumericCellValue()用于提取数字，cell.getDateCellValue()用于提取日期，cell.getBooleanCellValue()用于提取布尔值，cell.getCellFormula()用于提取公式。

处理日期格式

在读取日期时，我们使用了DateUtil.isCellDateFormatted()方法来判断单元格是否包含日期格式的数据。如果确实包含日期，则使用cell.getDateCellValue()方法提取日期值。否则，我们将其视为普通的数值。

处理空行和空单元格

在遍历表格时，我们需要注意跳过空行和空单元格。如果某一行或某一单元格为空，则sheet.getRow()或row.getCell()方法将返回null。因此，我们在每次读取行或单元格时，都需要检查其是否为null，以避免出现NullPointerException。

格式控制：让Excel文件更美观

在实际应用中，仅仅写入数据往往是不够的。为了让Excel文件更加美观和易读，我们通常需要对其进行格式控制。Apache POI提供了丰富的API，可以让我们轻松地设置单元格的样式、字体、边框、背景颜色等。接下来，我们将详细介绍如何使用Apache POI来进行格式控制。

设置单元格样式

在Apache POI中，单元格的样式由XSSFCellStyle类表示。我们可以通过创建XSSFCellStyle对象，并将其应用到特定的单元格上来设置样式。以下是一些常见的样式设置方法：

  • 设置字体：我们可以使用XSSFFont类来设置单元格的字体。例如，设置字体大小、加粗、斜体等。
  • 设置背景颜色：通过setFillForegroundColor()和setFillPattern()方法，我们可以为单元格设置背景颜色。
  • 设置边框：通过setBorderTop()、setBorderBottom()、setBorderLeft()和setBorderRight()方法，我们可以为单元格设置边框。
  • 设置对齐方式：通过setAlignment()和setVerticalAlignment()方法，我们可以设置单元格内容的水平和垂直对齐方式。
  • 设置数据格式：通过setDataFormat()方法，我们可以为单元格设置特定的数据格式，例如日期、货币等。

下面是一个完整的代码示例，展示了如何设置单元格的样式：

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import java.io.FileOutputStream;
import java.io.IOException;

public class FormatExcelFile {
    public static void main(String[] args) {
        // Step 1: 创建一个新的工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();

        // Step 2: 创建一个新的表格
        XSSFSheet sheet = workbook.createSheet("Formatted Sheet");

        // Step 3: 创建第一行
        XSSFRow row = sheet.createRow(0);

        // Step 4: 创建一个单元格样式
        XSSFCellStyle style = workbook.createCellStyle();

        // Step 5: 设置字体
        XSSFFont font = workbook.createFont();
        font.setFontHeightInPoints((short) 14);
        font.setBold(true);
        font.setColor(IndexedColors.BLUE.getIndex());
        style.setFont(font);

        // Step 6: 设置背景颜色
        style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        // Step 7: 设置边框
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);

        // Step 8: 设置对齐方式
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        // Step 9: 设置数据格式
        DataFormat format = workbook.createDataFormat();
        style.setDataFormat(format.getFormat("0.00"));

        // Step 10: 创建一个单元格，并应用样式
        XSSFCell cell = row.createCell(0);
        cell.setCellValue(123.45);
        cell.setCellStyle(style);

        // Step 11: 将工作簿写入文件
        try (FileOutputStream fileOut = new FileOutputStream("formatted_example.xlsx")) {
            workbook.write(fileOut);
            System.out.println("Excel file created successfully.");
        } catch (IOException e) {
            e.printStackTrace();
        }

        // Step 12: 关闭工作簿
        try {
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

代码解析

  1. 创建单元格样式：我们使用workbook.createCellStyle()方法创建一个新的XSSFCellStyle对象。这个对象将用于定义单元格的样式。

  2. 设置字体：我们使用workbook.createFont()方法创建一个新的XSSFFont对象，并为其设置字体大小、加粗、颜色等属性。然后，我们将这个字体应用到单元格样式中。

  3. 设置背景颜色：通过style.setFillForegroundColor()方法，我们可以为单元格设置背景颜色。IndexedColors类提供了一些预定义的颜色，例如黄色、蓝色等。我们还可以使用setFillPattern()方法设置填充模式，例如实心填充。

  4. 设置边框：通过setBorderTop()、setBorderBottom()、setBorderLeft()和setBorderRight()方法，我们可以为单元格设置边框。BorderStyle类提供了一些预定义的边框样式，例如细线、粗线等。

  5. 设置对齐方式：通过setAlignment()和setVerticalAlignment()方法，我们可以设置单元格内容的水平和垂直对齐方式。HorizontalAlignment和VerticalAlignment类提供了一些预定义的对齐方式，例如居中、左对齐、右对齐等。

  6. 设置数据格式：通过setDataFormat()方法，我们可以为单元格设置特定的数据格式。DataFormat类提供了一些常用的数据格式，例如日期、货币、百分比等。我们还可以自定义格式，例如保留两位小数。

  7. 应用样式：最后，我们通过cell.setCellStyle()方法将样式应用到特定的单元格中。

设置列宽和行高

除了设置单元格的样式，我们还可以通过setColumnWidth()和setRowHeight()方法来调整表格的列宽和行高。这对于确保表格内容不会被截断或溢出非常有用。

// 设置列宽
sheet.setColumnWidth(0, 5000); // 单位为字符宽度的1/256

// 设置行高
row.setHeight((short) 500); // 单位为1/20点

设置合并单元格

有时我们希望将多个单元格合并为一个单元格，例如在标题行中。Apache POI提供了addMergedRegion()方法来实现这一功能。我们可以通过指定要合并的单元格范围来创建一个合并区域。

// 创建一个合并区域
CellRangeAddress region = new CellRangeAddress(0, 0, 0, 2); // 合并第0行的第0列到第2列
sheet.addMergedRegion(region);

设置条件格式

条件格式是一种根据单元格内容自动应用样式的功能。例如，我们可以设置当某个单元格的值大于100时，将其背景颜色设置为红色。Apache POI提供了XSSFConditionalFormattingRule类来实现条件格式。

// 创建一个条件格式规则
XSSFConditionalFormattingRule rule = sheet.getSheetConditionalFormatting().createConditionalFormattingRule(
    "A1>100"
);

// 设置条件格式的样式
XSSFFont font = workbook.createFont();
font.setColor(IndexedColors.RED.getIndex());
rule.setFont(font);

// 应用条件格式
CellRangeAddress[] regions = {new CellRangeAddress(0, 0, 0, 0)};
sheet.getSheetConditionalFormatting().addConditionalFormatting(regions, rule);

总结与展望

通过今天的讲座，我们详细介绍了如何使用Apache POI库在Java中操作Excel文件。我们从创建和写入Excel文件开始，逐步学习了如何读取现有文件、设置单元格样式、调整列宽和行高、合并单元格以及应用条件格式。这些技能将帮助你在日常工作中更高效地处理Excel文件，自动化繁琐的任务，并生成更加美观和专业的报表。

当然，Apache POI的功能远不止于此。它还支持许多高级功能，如生成图表、处理公式、加密文件等。如果你对这些功能感兴趣，可以进一步深入学习。此外，随着技术的不断发展，Apache POI也在不断更新和完善，未来可能会带来更多新的特性和优化。

最后，希望今天的讲座对你有所帮助。如果你有任何问题或建议，欢迎随时交流。祝你在Java与Excel的世界里玩得开心，编码顺利！

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注