Apache POI 学习

1. 引言

Apache POI 是一个用于读写 Microsoft Office 格式文件的 Java API。它支持多种 Office 文件格式，包括 .xls, .xlsx, .ppt, .pptx, .doc, .docx 等。

2. 环境搭建

首先，需要安装 Apache POI 库。可以通过 Maven 或者 Gradle 添加依赖来引入 Apache POI。

Maven

<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>5.2.2</version>
</dependency>
<dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>5.2.2</version>
</dependency>

Gradle

implementation 'org.apache.poi:poi:5.2.2'
implementation 'org.apache.poi:poi-ooxml:5.2.2'

3. 基础概念

基本的概念：

• Workbook：代表一个 Excel 文件。
• Sheet：代表一个 Excel 表单。
• Row：代表一个 Excel 行。
• Cell：代表一个 Excel 单元格。

4. 基本操作

4.1 创建 Excel 文件

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.io.FileOutputStream;
import java.io.IOException;public class CreateExcelExample {public static void main(String[] args) {try (Workbook workbook = new XSSFWorkbook()) {// 创建一个新的工作表Sheet sheet = workbook.createSheet("Sheet1");// 创建一个新的行Row row = sheet.createRow(0);// 创建一个新的单元格Cell cell = row.createCell(0);cell.setCellValue("Hello, Apache POI!");// 将工作簿写入文件try (FileOutputStream fos = new FileOutputStream("example.xlsx")) {workbook.write(fos);}} catch (IOException e) {e.printStackTrace();}}
}

4.2 读取 Excel 文件

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.io.FileInputStream;
import java.io.IOException;public class ReadExcelExample {public static void main(String[] args) {try (FileInputStream fis = new FileInputStream("example.xlsx")) {Workbook workbook = new XSSFWorkbook(fis);Sheet sheet = workbook.getSheetAt(0);Row row = sheet.getRow(0);Cell cell = row.getCell(0);System.out.println(cell.getStringCellValue());} catch (IOException e) {e.printStackTrace();}}
}

5. 进阶操作

5.1 设置单元格样式

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.io.FileOutputStream;
import java.io.IOException;public class SetCellStyleExample {public static void main(String[] args) {try (Workbook workbook = new XSSFWorkbook()) {Sheet sheet = workbook.createSheet("Sheet1");Row row = sheet.createRow(0);Cell cell = row.createCell(0);cell.setCellValue("Hello, Apache POI!");// 创建单元格样式CellStyle style = workbook.createCellStyle();Font font = workbook.createFont();font.setBold(true);style.setFont(font);// 应用样式cell.setCellStyle(style);try (FileOutputStream fos = new FileOutputStream("styled_example.xlsx")) {workbook.write(fos);}} catch (IOException e) {e.printStackTrace();}}
}

5.2 数据验证

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFDataValidationHelper;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Arrays;public class DataValidationExample {public static void main(String[] args) {try (Workbook workbook = new XSSFWorkbook()) {XSSFSheet sheet = (XSSFSheet) workbook.createSheet("Sheet1");Row row = sheet.createRow(0);Cell cell = row.createCell(0);cell.setCellValue("Select an option:");// 设置数据验证XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);DVConstraint constraint = DVConstraint.createExplicitListConstraint(Arrays.asList("Option A", "Option B", "Option C"));CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 1, 1);DataValidation validation = dvHelper.createValidation(constraint, cellRangeAddress);sheet.addValidationData(validation);try (FileOutputStream fos = new FileOutputStream("validated_example.xlsx")) {workbook.write(fos);}} catch (IOException e) {e.printStackTrace();}}
}

5.3 图表创建

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.charts.AxisPosition;
import org.apache.poi.ss.usermodel.charts.ChartAxis;
import org.apache.poi.ss.usermodel.charts.DataSources;
import org.apache.poi.ss.usermodel.charts.ScatterChartData;
import org.apache.poi.ss.usermodel.charts.ScatterChartSeries;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.io.FileOutputStream;
import java.io.IOException;public class ChartCreationExample {public static void main(String[] args) {try (Workbook workbook = new XSSFWorkbook()) {XSSFSheet sheet = (XSSFSheet) workbook.createSheet("Sheet1");// 创建数据行Row row = sheet.createRow(0);row.createCell(0).setCellValue("X Axis");row.createCell(1).setCellValue("Y Axis");for (int i = 1; i <= 10; i++) {row = sheet.createRow(i);row.createCell(0).setCellValue(i);row.createCell(1).setCellValue(i * i);}// 创建图表XSSFDrawing drawing = sheet.createDrawingPatriarch();XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 1, 1, 20, 20);ScatterChartData chartData = sheet.getChartGrid().createScatterChartData();ScatterChartSeries series = chartData.addSeries(DataSources.fromNumericCellRange(sheet, new CellRangeAddress(1, 10, 0, 0)),DataSources.fromNumericCellRange(sheet, new CellRangeAddress(1, 10, 1, 1)));series.setTitle("Sample Series");// 设置图表轴ChartAxis xAxis = chartData.createNumericAxis(AxisPosition.BOTTOM);xAxis.setTitle("X Axis");ChartAxis yAxis = chartData.createNumericAxis(AxisPosition.LEFT);yAxis.setTitle("Y Axis");// 创建图表并添加到工作表drawing.createScatterChart(anchor, chartData);try (FileOutputStream fos = new FileOutputStream("chart_example.xlsx")) {workbook.write(fos);}} catch (IOException e) {e.printStackTrace();}}
}

在使用 Apache POI 处理 Excel 文件时，合并单元格、居中对齐以及其他常见的格式化操作是非常常见的需求。下面我将详细介绍如何使用 Apache POI 来实现这些功能。

5.4 合并单元格

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.io.FileOutputStream;
import java.io.IOException;public class MergeCellsExample {public static void main(String[] args) {try (Workbook workbook = new XSSFWorkbook()) {Sheet sheet = workbook.createSheet("Sheet1");// 创建一个新行Row row = sheet.createRow(0);// 创建一个新单元格Cell cell = row.createCell(0);cell.setCellValue("这是标题行");// 合并单元格sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3)); // 合并第0行第0列到第0行第3列// 创建第二个新行row = sheet.createRow(1);// 填充数据row.createCell(0).setCellValue("列1");row.createCell(1).setCellValue("列2");row.createCell(2).setCellValue("列3");row.createCell(3).setCellValue("列4");// 将工作簿写入文件try (FileOutputStream fos = new FileOutputStream("merged_cells_example.xlsx")) {workbook.write(fos);}} catch (IOException e) {e.printStackTrace();}}
}

5.5 居中对齐

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.io.FileOutputStream;
import java.io.IOException;public class CenterAlignExample {public static void main(String[] args) {try (Workbook workbook = new XSSFWorkbook()) {Sheet sheet = workbook.createSheet("Sheet1");// 创建一个新行Row row = sheet.createRow(0);// 创建一个新单元格Cell cell = row.createCell(0);cell.setCellValue("这是标题行");// 设置单元格样式CellStyle style = workbook.createCellStyle();style.setAlignment(HorizontalAlignment.CENTER); // 水平居中style.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中// 应用样式cell.setCellStyle(style);// 将工作簿写入文件try (FileOutputStream fos = new FileOutputStream("center_aligned_example.xlsx")) {workbook.write(fos);}} catch (IOException e) {e.printStackTrace();}}
}

5.6 设置边框和字体颜色

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.io.FileOutputStream;
import java.io.IOException;public class BorderAndFontColorExample {public static void main(String[] args) {try (Workbook workbook = new XSSFWorkbook()) {Sheet sheet = workbook.createSheet("Sheet1");// 创建一个新行Row row = sheet.createRow(0);// 创建一个新单元格Cell cell = row.createCell(0);cell.setCellValue("这是标题行");// 设置单元格样式CellStyle style = workbook.createCellStyle();style.setAlignment(HorizontalAlignment.CENTER); // 水平居中style.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中// 设置边框style.setBorderBottom(BorderStyle.THIN);style.setBorderLeft(BorderStyle.THIN);style.setBorderRight(BorderStyle.THIN);style.setBorderTop(BorderStyle.THIN);// 设置字体颜色Font font = workbook.createFont();font.setColor(IndexedColors.BLUE.getIndex());style.setFont(font);// 应用样式cell.setCellStyle(style);// 将工作簿写入文件try (FileOutputStream fos = new FileOutputStream("border_and_font_color_example.xlsx")) {workbook.write(fos);}} catch (IOException e) {e.printStackTrace();}}
}

6. 性能优化

在处理大量数据时，性能是一个关键因素。以下是一些优化建议：

• 使用流式处理：对于非常大的文件，可以使用 SXSSFWorkbook 来减少内存占用。
• 批量写入：一次性写入大量数据时，尽量减少磁盘 I/O 操作次数。
• 关闭流：确保在不再使用流之后关闭它们。

7. 总结

Apache POI 是一个功能强大的 Java 库，用于处理 Microsoft Office 文件。
Apache POI 官方文档