JAVA：探索Apache POI 处理利器

秒速五厘米 2024-04-20 12:15 119阅读 0赞

请关注微信公众号：**拾荒的小海螺**

#### 1、简述 ####

Apache POI是Apache软件基金会的顶级项目之一，它允许Java开发人员读取和写入Microsoft Office格式的文档，包括Excel、Word和PowerPoint文件。通过POI，开发人员可以创建、修改和读取Excel电子表格，从而实现各种各样的用例，例如数据导入、报表生成和数据分析等。

![在这里插入图片描述][0a299f9f56e1451a882fecdbd43de61c.png]

#### 2、应用 ####

Apache POI是一个强大的Java库，用于处理Microsoft Office格式文件，特别是Excel电子表格。在本文中，我们将深入探讨Apache POI库，并提供详细的应用实例，以展示其在处理Excel文件时的强大功能。

首先，确保在Maven中引入POI的依赖：

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

##### 2.1 创建和读取 #####

创建一个简单的Excel文件：

import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.apache.poi.ss.usermodel.*;
    
    import java.io.FileOutputStream;
    import java.io.IOException;
    
    public class ExcelCreationExample {
    
        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!");
    
                FileOutputStream fileOut = new FileOutputStream("example.xlsx");
                workbook.write(fileOut);
                fileOut.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

读取和修改现有的Excel文件：

import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.apache.poi.ss.usermodel.*;
    
    import java.io.FileInputStream;
    import java.io.FileOutputStream;
    import java.io.IOException;
    
    public class ExcelModificationExample {
    
        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);
                String cellValue = cell.getStringCellValue();
                System.out.println("Original Cell Value: " + cellValue);
    
                cell.setCellValue("Modified by Apache POI!");
    
                FileOutputStream fos = new FileOutputStream("example.xlsx");
                workbook.write(fos);
                fos.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

##### 2.2 图片读取和插入 #####

使用Apache POI库，你可以方便地插入图片到Excel文件中，并读取已经插入的图片。下面是插入图片和读取图片的示例代码：

插入图片到Excel文件中：

import org.apache.poi.ss.usermodel.*;
    
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    import java.io.FileOutputStream;
    import java.io.FileInputStream;
    import java.io.IOException;
    
    public class InsertImageExample {
    
        public static void main(String[] args) {
            try (Workbook workbook = new XSSFWorkbook()) {
                Sheet sheet = workbook.createSheet("Sheet1");
    
                // 读取图片文件
                FileInputStream fis = new FileInputStream("image.jpg");
                byte[] imageData = fis.readAllBytes();
                fis.close();
    
                // 将图片插入到Excel文件中
                int pictureIdx = workbook.addPicture(imageData, Workbook.PICTURE_TYPE_JPEG);
                CreationHelper helper = workbook.getCreationHelper();
                Drawing drawing = sheet.createDrawingPatriarch();
                ClientAnchor anchor = helper.createClientAnchor();
                anchor.setCol1(1); // 图片起始列
                anchor.setRow1(1); // 图片起始行
                Picture picture = drawing.createPicture(anchor, pictureIdx);
    
                FileOutputStream fos = new FileOutputStream("output.xlsx");
                workbook.write(fos);
                fos.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

从Excel文件中读取图片：

import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    import javax.imageio.ImageIO;
    import java.awt.image.BufferedImage;
    import java.io.FileInputStream;
    import java.io.FileOutputStream;
    import java.io.IOException;
    
    public class ReadImageExample {
    
        public static void main(String[] args) {
            try (Workbook workbook = new XSSFWorkbook(new FileInputStream("output.xlsx"))) {
                Sheet sheet = workbook.getSheetAt(0);
                Drawing drawing = sheet.getDrawingPatriarch();
    
                // 读取图片
                for (Shape shape : drawing) {
                    if (shape instanceof Picture) {
                        Picture picture = (Picture) shape;
                        byte[] imageData = picture.getData();
                        BufferedImage bufferedImage = ImageIO.read(new ByteArrayInputStream(imageData));
    
                        // 将图片保存到文件
                        String filename = "image_from_excel.jpg";
                        FileOutputStream fos = new FileOutputStream(filename);
                        ImageIO.write(bufferedImage, "jpg", fos);
                        fos.close();
    
                        System.out.println("Image saved to: " + filename);
                    }
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

##### 2.3 条件格式 #####

添加条件格式规则：

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) {
            try (Workbook workbook = new XSSFWorkbook()) {
                Sheet sheet = workbook.createSheet("Sheet1");
    
                XSSFSheetConditionalFormatting cf = (XSSFSheetConditionalFormatting) sheet.getSheetConditionalFormatting();
                ConditionalFormattingRule rule = cf.createConditionalFormattingRule(ComparisonOperator.GT, "0");
                FontFormatting fontFmt = rule.createFontFormatting();
                fontFmt.setFontStyle(true, false);
                fontFmt.setFontColorIndex(IndexedColors.RED.index);
    
                CellRangeAddress[] regions = {CellRangeAddress.valueOf("A1:A10")};
                cf.addConditionalFormatting(regions, rule);
    
                FileOutputStream fos = new FileOutputStream("conditional_formatting_example.xlsx");
                workbook.write(fos);
                fos.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

读取当前单元格是否满足条件格式设定的规则：

/**
     * 获取当前单元格是否满足校验规则，并返回满足的规则对象
     * @Author shdxhl
     * @Method  getMatchingConditionalFormattingForCell
     * @Date 17:10 2023-10-14
     * @Param [cell]
     * @return List<EvaluationConditionalFormatRule>
     **/
    private List<EvaluationConditionalFormatRule> getMatchingConditionalFormattingForCell(XSSFCell cell) {
    	Sheet sheet = cell.getSheet();
    	Workbook workbook = sheet.getWorkbook();
    	WorkbookEvaluatorProvider workbookEvaluatorProvider =
    			(WorkbookEvaluatorProvider)workbook.getCreationHelper().createFormulaEvaluator();
    	ConditionalFormattingEvaluator conditionalFormattingEvaluator =
    			new ConditionalFormattingEvaluator(workbook, workbookEvaluatorProvider);
    	List<EvaluationConditionalFormatRule> matchingCFRulesForCell =
    			conditionalFormattingEvaluator.getConditionalFormattingForCell(cell);
    	return matchingCFRulesForCell;
    }
    
    /**
     * 获取当前单元格匹配规则的颜色
     * @Author shdxhl
     * @Method  getMatchingConditionalColor
     * @Date 17:10 2023-10-14
     * @Param [cell]
     * @return XSSFColor
     **/
    private XSSFColor getMatchingConditionalColor(XSSFCell cell){
    	List<EvaluationConditionalFormatRule> formatRuleList = getMatchingConditionalFormattingForCell(cell);
    	if(Objects.nonNull(formatRuleList) && formatRuleList.size() > 0){
    		ConditionalFormattingRule cFRule = formatRuleList.get(0).getRule();
    		XSSFColor fontColor = (XSSFColor) cFRule.getFontFormatting().getFontColor();
    		if (fontColor != null) {
    			return fontColor;
    		}
    	}
    	return null;
    }

#### 3、优化 ####

优化Apache POI代码可以提高处理Excel文件时的性能和效率。以下是一些优化技巧以及对应的实例：

##### 3.1 批量操作 #####

避免逐个单元格进行读写操作，而是批量处理单元格数据。这可以通过缓存或预加载数据来实现。

// 逐行读取数据并存储在内存中
    List<List<String>> data = new ArrayList<>();
    for (Row row : sheet) {
        List<String> rowData = new ArrayList<>();
        for (Cell cell : row) {
            rowData.add(cell.getStringCellValue());
        }
        data.add(rowData);
    }

##### 3.2 使用缓存 #####

合理使用POI提供的缓存机制，例如使用CellStyleCache来避免重复创建CellStyle对象。

CellStyle style = workbook.createCellStyle();
    style.setBorderBottom(BorderStyle.THIN);
    style.setBorderTop(BorderStyle.THIN);
    style.setBorderLeft(BorderStyle.THIN);
    style.setBorderRight(BorderStyle.THIN);
    
    // 使用缓存
    CellStyle cachedStyle = CellStyleCache.getOrAdd(style);

##### 3.3 使用SXSSFWorkbook进行大数据量处理 #####

当处理大量数据时，可以使用SXSSFWorkbook来避免内存溢出问题，因为它支持将数据写入临时文件而不是全部加载到内存中。

try (SXSSFWorkbook workbook = new SXSSFWorkbook()) {
        SXSSFSheet sheet = workbook.createSheet("Sheet1");
        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("Data" + cellnum);
            }
        }
        try (FileOutputStream fos = new FileOutputStream("large_data.xlsx")) {
            workbook.write(fos);
        }
    }

##### 3.4 避免频繁的文件IO操作 #####

尽量减少对文件系统的访问次数，可以通过缓存或批量写入操作来减少文件IO操作。

try (FileOutputStream fos = new FileOutputStream("output.xlsx");
         BufferedOutputStream bos = new BufferedOutputStream(fos);
         Workbook workbook = new XSSFWorkbook()) {
        Sheet sheet = workbook.createSheet("Sheet1");
        // 批量写入数据
        // ...
        workbook.write(bos);
    }

##### 3.5 使用事件模型 #####

如果只需要读取Excel文件的数据而不需要修改，可以使用事件驱动的模型（如SAX）来逐行读取数据，以减少内存消耗。

try (OPCPackage pkg = OPCPackage.open(new File("input.xlsx"))) {
        XSSFReader reader = new XSSFReader(pkg);
        StylesTable styles = reader.getStylesTable();
        ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(pkg);
        XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) reader.getSheetsData();
        while (iter.hasNext()) {
            try (InputStream stream = iter.next()) {
                processSheet(styles, strings, stream);
            }
        }
    }

通过以上优化方法，可以提高Apache POI代码在处理Excel文件时的性能和效率。

#### 4、结论 ####

Apache POI是处理Excel文件的强大工具，可以满足各种各样的需求，从简单的数据导入到复杂的报表生成。通过本文提供的应用实例，你可以开始探索Apache POI，并利用其丰富的功能来处理Excel文件。

[0a299f9f56e1451a882fecdbd43de61c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/1b02cdf1622742f29d4536d61e8fa5aa.png