东莞松山湖天气_中国黄页免费版_网络推广有哪些方法_免费推广网站有哪些

Apache POI 学习

- - - 1. 引言
    - 2. 环境搭建
    - - Maven
      - Gradle
    - 3. 基础概念
    - 4. 基本操作
    - - 4.1 创建 Excel 文件
      - 4.2 读取 Excel 文件
    - 5. 进阶操作
    - - 5.1 设置单元格样式
      - 5.2 数据验证
      - 5.3 图表创建
      - 5.4 合并单元格
      - 5.5 居中对齐
      - 5.6 设置边框和字体颜色
    - 6. 性能优化
    - 7. 总结

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 官方文档