【笔记】表格处理（一）Apache POI-CSDN博客

本文链接：https://blog.csdn.net/qq_45822726/article/details/139882265

表格处理

Apache POI

表格处理
一、简介
- HSSF 和 XSSF有啥不同？
二、使用步骤

一、简介

Apacche poi文档

Apache POI 是一个强大的 Java 库，用于读写 Microsoft Office 文件。它特别擅长处理 Excel 文件（HSSF 和 XSSF）。

HSSF 和 XSSF有啥不同？

HSSF 和 XSSF 是 Apache POI 中用于处理不同版本的 Excel 文件的两个主要组件。它们之间的主要区别在于支持的 Excel 文件格式和一些实现细节。以下是两者的详细区别：

HSSF

文件格式: HSSF 用于处理 Excel 97-2003 文件，即 .xls 格式。
底层格式: .xls 文件使用的是一种称为Binary Interchange File Format (BIFF) 的二进制格式。
内存使用: HSSF通常在处理大型文件时会占用较多的内存，因为它是基于内存的实现。
导入的包: 主要类位于 org.apache.poi.hssf.usermodel 包中。

XSSF (XML Spreadsheet Format)

文件格式: XSSF 用于处理 Excel 2007 及更高版本的文件，即 .xlsx 格式。
底层格式: .xlsx 文件使用的是基于 XML 的 Office Open XML (OOXML) 格式。
内存使用: XSSF 在处理大型文件时相对更高效，但仍可能占用大量内存。对于非常大的文件，建议使用 SXSSF。
导入的包: 主要类位于 org.apache.poi.xssf.usermodel 包中。

其他区别：

功能支持: XSSF 支持更多的 Excel 2007 及更高版本的功能，如图表、条件格式、数据验证等，而 HSSF 在这方面有一定的局限性。
文件大小: .xlsx 文件通常比 .xls 文件小，因为它们使用了压缩的 XML 格式。
性能: XSSF 在处理复杂和大型工作簿时通常更高效，但在某些情况下可能需要更多的内存。

二、使用步骤

（一）依赖

核心依赖

poi
包含了处理 Microsoft Office 文件的核心功能，包括 Excel、Word、PowerPoint 等。但主要用于处理 .xls 文件（Excel 97-2003）。

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.2.3</version>
</dependency>

poi-ooxml
支持处理基于 OOXML 格式的文件，如 .xlsx（Excel 2007 及更高版本）。

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

poi-ooxml-schemas
包含了处理 OOXML 文件格式所需的额外 XML 模式定义。

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

poi-scratchpad
提供对某些较少用到的文件格式的支持，如 Visio 图表、Publisher 文件等。

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-scratchpad</artifactId>
    <version>5.2.3</version>
</dependency>

其他常见依赖

commons-collections4
Apache Commons Collections 库，提供了一些额外的数据结构和集合操作工具，POI 的某些功能依赖于此库。

<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-collections4</artifactId>
    <version>4.4</version>
</dependency>

xmlbeans
XMLBeans 是一个用于处理 XML 的库，POI 的 OOXML 部分依赖于 XMLBeans 来解析和操作 XML 数据。

<dependency>
    <groupId>org.apache.xmlbeans</groupId>
    <artifactId>xmlbeans</artifactId>
    <version>5.1.1</version>
</dependency>

commons-compress
Apache Commons Compress 库，提供对压缩文件格式的支持，如 ZIP、GZIP 等。POI 在处理 .xlsx 文件时需要解压缩和压缩文件，因此依赖于此库。

<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-compress</artifactId>
    <version>1.21</version>
</dependency>

stax-api
提供对 StAX（Streaming API for XML）标准的支持，POI 在处理大文件时可能会用到流式处理 XML 数据。

<dependency>
    <groupId>javax.xml.stream</groupId>
    <artifactId>stax-api</artifactId>
    <version>1.0-2</version>
</dependency>

（二）基础使用示例

1. 创建一个简单的 Excel 文件

此处创建一个后缀为.xlsx的Excel文件，注释部分即为后缀为xls的文件

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

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

public class CreateExcel {
    public static void main(String[] args) {
    	// XSSF
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Example Sheet");
        // HSSF
        // Workbook workbook = new HSSFWorkbook();
        // Sheet sheet = workbook.createSheet("HSSF Sheet");

        Row row = sheet.createRow(0);
        Cell cell = row.createCell(0);
        cell.setCellValue("Hello, Apache POI!");

        try (FileOutputStream fileOut = new FileOutputStream("example.xlsx")) {
            workbook.write(fileOut);
        } catch (IOException e) {
            e.printStackTrace();
        }
        // try (FileOutputStream fileOut = new FileOutputStream("hssf_example.xls")) {
        //    workbook.write(fileOut);
        // } catch (IOException e) {
        //     e.printStackTrace();
        // }

        try {
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

2. 读取一个 Excel 文件

读取一个后缀为.xlsx的文件，.xls同理。

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

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

public class ReadExcel {
    public static void main(String[] args) {
        try (FileInputStream fileIn = new FileInputStream("example.xlsx");
             Workbook workbook = new XSSFWorkbook(fileIn)) {

            Sheet sheet = workbook.getSheetAt(0);
            for (Row row : sheet) {
                for (Cell cell : row) {
                    switch (cell.getCellType()) {
                        case STRING:
                            System.out.print(cell.getStringCellValue() + "\t");
                            break;
                        case NUMERIC:
                            System.out.print(cell.getNumericCellValue() + "\t");
                            break;
                        case BOOLEAN:
                            System.out.print(cell.getBooleanCellValue() + "\t");
                            break;
                        default:
                            System.out.print("UNKNOWN\t");
                            break;
                    }
                }
                System.out.println();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

3. 设置单元格样式

可以设置单元格的各种样式，如：字体、颜色、边框等。

Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("Styled Sheet");
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellValue("Styled Cell");

CellStyle style = workbook.createCellStyle();
Font font = workbook.createFont();
font.setBold(true);
font.setColor(IndexedColors.RED.getIndex());
style.setFont(font);
style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cell.setCellStyle(style);

try (FileOutputStream fileOut = new FileOutputStream("styled_example.xlsx")) {
    workbook.write(fileOut);
}
workbook.close();

4. 合并单元格

sheet.addMergedRegion:

作用: 该方法用于在 Excel 工作表中合并指定区域的单元格。
参数: 需要一个 CellRangeAddress 对象，表示要合并的单元格区域。

new CellRangeAddress

作用: 创建一个 CellRangeAddress 对象，指定要合并的单元格区域。
参数: 该构造函数接收四个参数，分别是：
- firstRow: 合并区域的起始行索引（从 0 开始）。
- lastRow: 合并区域的结束行索引（从 0 开始）。
- firstCol: 合并区域的起始列索引（从 0 开始）。
- lastCol: 合并区域的结束列索引（从 0 开始）。

Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("Merged Sheet");
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellValue("Merged Cells");
// 从第0行到第1行，从第0列到第3列
sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 3));

try (FileOutputStream fileOut = new FileOutputStream("merged_example.xlsx")) {
    workbook.write(fileOut);
}
workbook.close();

5. 添加图片

获取 CreationHelper 对象: 用于创建和操作 POI 中的各种元素。
获取 Drawing 对象: 用于管理图形对象（如图片）。
创建 ClientAnchor 对象: 用于指定图片的位置和大小。
设置图片的位置: 通过 setCol1 和 setRow1 方法来指定起始列和起始行。
创建图片对象: 使用 createPicture 方法，将图片插入到指定位置。
调整图片大小: 通过 resize 方法，根据单元格的默认大小调整图片的大小。

// 读取图片文件
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("Picture Sheet");

InputStream inputStream = new FileInputStream("image.jpg");
byte[] bytes = IOUtils.toByteArray(inputStream);
int pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
inputStream.close();

// 创建帮助类
CreationHelper helper = workbook.getCreationHelper();
// 创建绘图容器
Drawing<?> drawing = sheet.createDrawingPatriarch();
// 创建锚点
ClientAnchor anchor = helper.createClientAnchor();
// 设置图片位置
anchor.setCol1(0);// 起始行
anchor.setRow1(1);// 起始列
// 创建图片
Picture pict = drawing.createPicture(anchor, pictureIdx);
// 调整图片大小
pict.resize();
// 写入Excel文件
try (FileOutputStream fileOut = new FileOutputStream("picture_example.xlsx")) {
    workbook.write(fileOut);
}
workbook.close();

6. 数据有效性和下拉列表

数据有效性（Data Validation）
- 作用
  数据有效性用于限制单元格中可以输入的数据类型或范围。例如，您可以限制单元格中只能输入数字、特定日期范围内的日期或满足特定条件的文本。
- 主要步骤
  1. 创建数据有效性约束：定义有效性规则，例如整数、日期、列表等。
  2. 创建数据有效性对象：将约束应用到特定的单元格区域。
  3. 将数据有效性对象添加到工作表：将定义好的数据有效性对象添加到指定的工作表中。
下拉列表（Drop-down List）
- 作用
  下拉列表是一种特殊的有效性约束，允许用户从预定义的列表中选择一个值，从而减少输入错误和提高输入效率。
- 主要步骤
  - 定义下拉列表的选项：可以是静态选项列表，也可以是动态引用的单元格区域。
  - 创建数据有效性约束：使用 DataValidationConstraint 创建一个约束，类型为 DVConstraint.ValidationType.LIST。
  - 创建数据有效性对象：将约束应用到特定的单元格区域。
  - 将数据有效性对象添加到工作表：将定义好的数据有效性对象添加到指定的工作表中。
- 注意：除了下拉点选还有下拉列表等各种不同的下拉列表。

Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("Data Validation Sheet");

// 数据有效性约束：整数范围
DataValidationHelper validationHelper = sheet.getDataValidationHelper();
DataValidationConstraint integerConstraint = validationHelper.createIntegerConstraint(
DataValidationConstraint.OperatorType.BETWEEN, "1", "100");
// 定义应用数据有效性的单元格区域
CellRangeAddressList integerAddressList = new CellRangeAddressList(0, 0, 0, 0);
// 创建数据有效性对象
DataValidation integerValidation = validationHelper.createValidation(integerConstraint, integerAddressList);
integerValidation.setShowErrorBox(true);
// 将数据有效性对象添加到工作表
sheet.addValidationData(integerValidation);

// 下拉列表选项
String[] dropdownOptions = {"Option 1", "Option 2", "Option 3"};
// 创建下拉列表约束
DataValidationConstraint dropdownConstraint = validationHelper.createExplicitListConstraint(dropdownOptions);
// 定义应用下拉列表的单元格区域
CellRangeAddressList dropdownAddressList = new CellRangeAddressList(1, 1, 0, 0);
// 创建数据有效性对象
DataValidation dropdownValidation = validationHelper.createValidation(dropdownConstraint, dropdownAddressList);
dropdownValidation.setShowErrorBox(true);
// 将数据有效性对象添加到工作表
sheet.addValidationData(dropdownValidation);

try (FileOutputStream fileOut = new FileOutputStream("data_validation_example.xlsx")) {
            workbook.write(fileOut);
} catch (IOException e) {
      e.printStackTrace();
} finally {
   try {
        workbook.close();
      } catch (IOException e) {
          e.printStackTrace();
    }
}

7. 自动调整列宽

Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("Auto Size Sheet");

Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellValue("This is a very long text that should be auto-sized.");
// 调整第一个列（索引为 0）的宽度，以适应其内容。
sheet.autoSizeColumn(0);

try (FileOutputStream fileOut = new FileOutputStream("auto_size_example.xlsx")) {
    workbook.write(fileOut);
}
workbook.close();

8. 公式计算

Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("Formula Sheet");

Row row = sheet.createRow(0);
Cell cell1 = row.createCell(0);
Cell cell2 = row.createCell(1);
Cell cell3 = row.createCell(2);

cell1.setCellValue(5);
cell2.setCellValue(10);
cell3.setCellFormula("A1+B1");
// 创建一个公式评估器，用于评估单元格中的公式
// FormulaEvaluator 是用于评估单元格公式的接口
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
// 评估第三个单元格的公式，并更新其值
evaluator.evaluateFormulaCell(cell3);

try (FileOutputStream fileOut = new FileOutputStream("formula_example.xlsx")) {
    workbook.write(fileOut);
}
workbook.close();

9. 日期和时间格式

Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("Date Sheet");

Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellValue(new Date());

CellStyle style = workbook.createCellStyle();
CreationHelper createHelper = workbook.getCreationHelper();
style.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm"));
cell.setCellStyle(style);

try (FileOutputStream fileOut = new FileOutputStream("date_example.xlsx")) {
    workbook.write(fileOut);
}
workbook.close();

10. 条件格式

为单元格设置条件格式，例如高亮显示某些条件下的单元格。

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

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

public class ConditionalFormattingExample {
    public static void main(String[] args) {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Conditional Formatting Sheet");

        // 填充一些数据
        for (int i = 0; i < 10; i++) {
            Row row = sheet.createRow(i);
            for (int j = 0; j < 5; j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(i * j);
            }
        }

        // 创建条件格式规则
        SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

        // 条件格式1：单元格值大于20时背景色变为红色
        ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(ComparisonOperator.GT, "20");
        PatternFormatting fill1 = rule1.createPatternFormatting();
        fill1.setFillBackgroundColor(IndexedColors.RED.index);
        fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

        // 条件格式2：单元格值等于0时字体颜色变为蓝色
        ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule(ComparisonOperator.EQUAL, "0");
        FontFormatting fontFmt = rule2.createFontFormatting();
        fontFmt.setFontColorIndex(IndexedColors.BLUE.index);

        // 应用条件格式到一个单元格区域
        CellRangeAddress[] regions = { CellRangeAddress.valueOf("A1:E10") };

        // 添加条件格式规则
        sheetCF.addConditionalFormatting(regions, rule1, rule2);

        // 写入文件
        try (FileOutputStream fileOut = new FileOutputStream("conditional_formatting_example.xlsx")) {
            workbook.write(fileOut);
        } catch (IOException e) {
            e.printStackTrace();
        }

        try {
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

11. 处理大型 Excel 文件

使用SXSSF处理大型Excel文件
SXSSF将数据写入临时文件而不是内存，可以显著减少内存使用

SXSSFWorkbook workbook = new SXSSFWorkbook();
Sheet sheet = workbook.createSheet("Large Sheet");

for (int rownum = 0; rownum < 10000; rownum++) {
    Row row = sheet.createRow(rownum);
    for (int cellnum = 0; cellnum < 10; cellnum++) {
        Cell cell = row.createCell(cellnum);
        cell.setCellValue("Row " + rownum + ", Cell " + cellnum);
    }
}

try (FileOutputStream fileOut = new FileOutputStream("large_example.xlsx")) {
    workbook.write(fileOut);
}
// 关闭工作簿并释放资源
workbook.dispose();

流式处理
对于读取大型Excel文件，可以使用Apache POI的 XSSFEventBasedExcelExtractor 类，它基于SAX (Simple API for XML) 解析器，能够逐行解析Excel文件，减少内存占用。

什么是共享字符串表
- 共享字符串表（Shared Strings Table）是 Excel 文件（特别是 .xlsx 格式的文件）的一种优化机制，用于存储工作簿中的所有唯一字符串。这种机制旨在减少文件的大小和内存使用，并提高字符串查找和处理的效率。
- 工作原理
  在 Excel 中，如果单元格中包含字符串值，这些字符串并不是直接存储在单元格中，而是存储在一个共享字符串表中。单元格中只存储对共享字符串表中相应字符串的引用索引。这样可以避免在文件中重复存储相同的字符串。
- 共享字符串表的优点
  - 减少文件大小：相同的字符串只存储一次，避免了重复存储，显著减少了文件的大小。
  - 提高性能：字符串查找和比较操作变得更快，因为只需要比较索引值而不是字符串内容。
  - 统一管理：字符串的统一管理和存储，使得字符串处理更加高效和一致。

import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;

import java.io.InputStream;
import java.util.Iterator;

public class LargeExcelFileReader {

    public static void main(String[] args) throws Exception {
        // 指定要读取的Excel文件路径
        String filePath = "path/to/large_excel_file.xlsx";
        
        // 打开Excel文件
        OPCPackage pkg = OPCPackage.open(filePath);
        XSSFReader reader = new XSSFReader(pkg);
        
        // 获取共享字符串表
        ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(pkg);
        
        // 获取样式表
        StylesTable styles = reader.getStylesTable();
        
        // 获取工作表的输入流
        XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) reader.getSheetsData();
        
        // 遍历每一个工作表
        while (iter.hasNext()) {
            InputStream stream = iter.next();
            String sheetName = iter.getSheetName();
            System.out.println("Reading sheet: " + sheetName);
            
            // 创建SAX解析器
            XMLReader parser = XMLReaderFactory.createXMLReader();
            ContentHandler handler = new ContentHandler(styles, strings);
            parser.setContentHandler(handler);
            
            // 解析工作表
            InputSource sheetSource = new InputSource(stream);
            parser.parse(sheetSource);
            stream.close();
        }
        pkg.close();
    }
    
    // 自定义的SAX事件处理器，用于处理Excel单元格内容
    private static class ContentHandler extends DefaultHandler {
    	// 用于处理单元格样式
        private final StylesTable stylesTable;
        // 用于处理共享字符串表,共享字符串表在Excel文件中存储了所有的字符串，以便在单元格中引用。
        private final ReadOnlySharedStringsTable sharedStringsTable;
        // 用于格式化单元格数据
        private final DataFormatter formatter;
        // 当前处理的单元格引用
        private String cellReference;
        // 当前处理的单元格值
        private String cellValue;
        // 标识当前处理的内容是否是单元格的值
        private boolean isValue;
        
        public ContentHandler(StylesTable styles, ReadOnlySharedStringsTable strings) {
            this.stylesTable = styles;
            this.sharedStringsTable = strings;
            this.formatter = new DataFormatter();
            this.cellReference = null;
            this.cellValue = null;
            this.isValue = false;
        }
        
        // 当解析器遇到一个新的元素开始时会调用这个方法
        @Override
        public void startElement(String uri, String localName, String qName, Attributes attributes) throws SAXException {
            if ("c".equals(qName)) { // c=>cell
                // 读取单元格引用
                cellReference = attributes.getValue("r");
                // 读取单元格类型
                String cellType = attributes.getValue("t");
                isValue = "s".equals(cellType); // shared string
            }
            if ("v".equals(qName) || "inlineStr".equals(qName)) { // v=>value
                isValue = true;
                cellValue = "";
            }
        }
        
        // 当解析器遇到文本节点时会调用这个方法
        // 如果 isValue 为 true，则将文本内容追加到 cellValue 中
        @Override
        public void characters(char[] ch, int start, int length) throws SAXException {
            if (isValue) {
                cellValue += new String(ch, start, length);
            }
        }
        
        // 当解析器遇到一个元素结束时会调用这个方法
        @Override
        public void endElement(String uri, String localName, String qName) throws SAXException {
            if ("v".equals(qName) || "inlineStr".equals(qName)) {
                if (isValue) {
                    // 处理shared string
                    if (isValue) {
                        int idx = Integer.parseInt(cellValue);
                        cellValue = new XSSFRichTextString(sharedStringsTable.getEntryAt(idx)).toString();
                    }
                    System.out.println(cellReference + " : " + cellValue);
                }
                isValue = false;
            }
        }
    }
}

12. 读取和写入不同版本的 Excel 文件

POI 可以处理不同版本的 Excel 文件，如 .xls 和 .xlsx。

// 读取 .xls 文件
try (FileInputStream fileIn = new FileInputStream("example.xls")) {
    HSSFWorkbook workbook = new HSSFWorkbook(fileIn);
    // 处理工作簿内容
}

// 读取 .xlsx 文件
try (FileInputStream fileIn = new FileInputStream("example.xlsx")) {
    XSSFWorkbook workbook = new XSSFWorkbook(fileIn);
    // 处理工作簿内容
}