POI报表

一、POI概述

1. POI概述

POI报表通常指基于Apache POI（Poor Obfuscation Implementation）库生成的电子表格或文档报表。Apache POI是一个开源的Java API，用于操作Microsoft Office格式文件（如Excel、Word）。在业务场景中，POI报表常用于数据导出、批量打印或动态生成结构化文档。

应用场景：

1. 数据报表生成。即将数据以Excel的形式导出。

2. 数据备份。

3. 数据批量上传。

2. Excel的两种形式

目前市面上的Excel分为两个大的版本，分别是Excel2003和Excel2007两个版本，之所以分为这两个版本是因为Excel底层的实现不一样。当然，区分这两个版本可以用后缀判断。

3. 常见的Excel操作工具

常见的操作工具有两种，分别是JXL和POI。

• JXL: 只能对Excel进行操作，属于比较老的框架，它只支持到Excel95-2000的版本，现在已经停止更新和维护了。
• POI: 是apache的项目，对Word、Excel、PPT进行操作，包括office2003和2007， Excel 2003和 Excel2007。POI现在一直在维护，所以一般使用POI。

4. POI API 介绍

5. POI 结构说明

HSSF提供读写Microsoft Excel XLS格式档案的功能。
XSSF提供读写Microsoft Excel OOXMLXLSX格式档案的功能。
HWPF提供读写Microsoft Word DOC格式档案的功能。
HSLF提供读写Microsoft PowerPoint格式档案的功能。
HDGF提供读Microsoft Visio格式档案的功能。
HPBF提供读Microsoft Publisher格式档案的功能。
HSMF提供读Microsoft Outlook格式档案的功能。

 二、POI入门案例

1. 将数据导出到Excel

1. 生成Excel表

1. 引入meven依赖

<!-- POI，对Excel操作-->
<dependency>
     <groupId>org.apache.poi</groupId>
     <artifactId>poi</artifactId>
     <version>5.2.2</version>
</dependency>
<!-- 由于Excel内核有2003和2007版本，这里导入的2007，基于xml格式的依赖-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.2</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>4.1.1</version>
</dependency>

2. 在测试类中编写如下代码，运行后可以在指定的路径找到生成的excel表。

 /*POI测试*/
    @Test
    public void testPOI() throws IOException {
        // 1. 创建工作簿, HSSFWorkbook 是2003版本; XSSFWorkbook 2007版本
        XSSFWorkbook wb = new XSSFWorkbook();
        //2. 创建表sheet
        XSSFSheet sheet = wb.createSheet("测试");
        //3. 文件流
        FileOutputStream fis = new FileOutputStream("Z:\\excel\\员工导出.xlsx");
        //4. 写入文件
        wb.write(fis);
        //5. 关闭流
        fis.close();
    }

 2. 写入数据

当然，上面只是生成了一个Excel文档，但并没有在表格里写入数据。下面就演示如何写入内容。 

/*POI测试*/
    @Test
    public void testPOI() throws IOException {
        // 1. 创建工作簿, HSSFWorkbook 是2003版本; XSSFWorkbook 2007版本
        XSSFWorkbook wb = new XSSFWorkbook();
        //2. 创建表sheet
        XSSFSheet sheet = wb.createSheet("测试");
        // 2.1 创建行对象, 索引从0开始，表示在第一行创建
        Row row = sheet.createRow(0);
        // 2.2 创建单元格对象, 索引从0开始
        Cell cell = row.createCell(0);
        //2.3 向单元格写入内容
        cell.setCellValue("员工张三");
        //3. 文件流
        FileOutputStream fis = new FileOutputStream("Z:\\excel\\员工导出.xlsx");
        //4. 写入文件
        wb.write(fis);
        //5. 关闭流
        fis.close();
    }

 3. 设置样式

/*POI测试*/
    @Test
    public void testPOIStyle() throws IOException {
        // 1. 创建工作簿, HSSFWorkbook 是2003版本; XSSFWorkbook 2007版本
        XSSFWorkbook wb = new XSSFWorkbook();
        //2. 创建表sheet
        XSSFSheet sheet = wb.createSheet("测试");
        // 2.1 创建行对象, 索引从0开始，表示在第一行创建
        Row row = sheet.createRow(1);
        // 2.2 创建单元格对象, 索引从0开始
        Cell cell = row.createCell(1);
        //2.3 向单元格写入内容
        cell.setCellValue("员工张三");

        // 样式处理
        // 创建样式对象
        XSSFCellStyle cellStyle = wb.createCellStyle();
        // 设置边框
        cellStyle.setBorderTop(BorderStyle.THIN);  // 上边框
        cellStyle.setBorderBottom(BorderStyle.THIN);  // 下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);  // 左边框
        cellStyle.setBorderRight(BorderStyle.THIN);  // 右边框

        // 创建字体对象
        XSSFFont font = wb.createFont();
        font.setFontName("华文楷体");  // 字体
        font.setFontHeightInPoints((short)28);  // 字号
        cellStyle.setFont(font);

        // 行高和列宽
        row.setHeightInPoints(50);  // 行高
        // 列宽的宽度，字符宽度，这里设置了31个字符的宽度，之所以要乘256，是因为源码将得到的宽度除以了256
        sheet.setColumnWidth(2, 31 * 256);  // 列宽

        // 居中显示
        cellStyle.setAlignment(HorizontalAlignment.CENTER); // 水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);  // 垂直居中

        cell.setCellStyle(cellStyle); // 向单元格设置样式

        //3. 文件流
        FileOutputStream fis = new FileOutputStream("Z:\\excel\\员工导出1.xlsx");
        //4. 写入文件
        wb.write(fis);
        //5. 关闭流
        fis.close();
    }

sheet.setColumnWidth(2, 31 * 256);  设置列宽时，有两个参数，分别是：列宽的宽度、字符宽度。这里设置了31个字符的宽度，之所以要乘256，是因为源码将得到的宽度除以了256。

4. 插入图片

 /*POI测试*/
    @Test
    public void testPOIInsertPicture() throws IOException {
        // 1. 创建工作簿, HSSFWorkbook 是2003版本; XSSFWorkbook 2007版本
        XSSFWorkbook wb = new XSSFWorkbook();
        //2. 创建表sheet
        XSSFSheet sheet = wb.createSheet("测试");
        
        //读取图片流
        FileInputStream stream = new FileInputStream("C:\\Users\\Administrator\\Desktop\\background.jpeg");
        // 转化二进制数组
        byte[] bytes = IOUtils.toByteArray(stream);
        // 向POI内存中添加一张图片，返回图片在图片集合中的索引
        int index = wb.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);  //参数1：图片的二进制数据；参数2：图片类型
        // 创建绘图工具类
        XSSFCreationHelper helper = wb.getCreationHelper();
        // 创建绘图对象
        Drawing<?> patriarch = sheet.createDrawingPatriarch();
        // 创建锚点，设置图片坐标
        ClientAnchor anchor = helper.createClientAnchor();
        // 下面两行表示坐标，第1行第1个单元格开始插入图片
        anchor.setRow1(0);
        anchor.setCol1(0);
        // 绘制图片
        Picture picture = patriarch.createPicture(anchor, index); // 参数1：图片锚点位置，图片索引
        picture.resize();  // 图片自适应
        
        //3. 文件流
        FileOutputStream fis = new FileOutputStream("Z:\\excel\\员工导出.xlsx");
        //4. 写入文件
        wb.write(fis);
        //5. 关闭流
        fis.close();
    }

2. 读取Excel数据到程序

// 读取Excel并解析
    @Test
    public void readExcelData() throws IOException {
        //1. 根据Excel文件创建工作簿
        XSSFWorkbook wb = new XSSFWorkbook("Z:\\测试模板\\oa用户导入模板.xlsx");
        //2. 获取Sheet表
        XSSFSheet sheet = wb.getSheetAt(0);  // 表表示获取Excel的第几张表
        //3.获取Sheet中的每一行，每一个单元格
        // sheet.getLastRowNum() 得到最后一行的索引
        for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
            XSSFRow row = sheet.getRow(rowNum);  // 根据索引获取每一行
            StringBuilder sb = new StringBuilder();
            // row.getLastCellNum() 得到最后一个单元格的索引
            for (int cellNum = 0; cellNum <= row.getLastCellNum(); cellNum++) {
                // 根据索引获取每一个单元格
                XSSFCell cell = row.getCell(cellNum);
                // 获取每个单元格的内容
                Object val = getCellValue(cell);
                sb.append(val).append("-");
            }
            System.out.println(sb.toString());
        }
    }

    // 获取单元格内容的方法
    public Object getCellValue(Cell cell) {
        // 1. 获取单元格的属性类型
        CellType cellType = cell.getCellType();
        // 2. 根据单元格数据类型获取数据
        Object value = null;
        switch (cellType) {
            case STRING:  // 字符串
                value = cell.getStringCellValue();
                break;
            case BOOLEAN:  // 布尔
                value = cell.getBooleanCellValue();
                break;
            case NUMERIC:   // 数字
                if (DateUtil.isCellDateFormatted(cell)) {
                    // 日期格式
                    value = cell.getDateCellValue();
                } else {
                    // 数字
                    value = cell.getNumericCellValue();
                }
                break;
            case FORMULA: // 公式
                value = cell.getCellFormula();
                break;
            default:
                break;
        }
        return value;
    }

 三、模板打印

由于自己在java中设置Excel单元格的样式太麻烦了，我们可以提前手动制作一份带有格式的Excel模板（包含表头样式、数据内容的样式），将模板放在resources目录下，然后读取Excel模板的样式存在数组中，数据写入到Excel时，再将数组中的样式提取出来设置在单元格上。这就是模板打印的核心思想。但 sxssf 不支持模板打印。

基本操作步骤：

• 制作模板文件（模板文件的路径）
• 导入模板文件，从而得到一个工作簿
• 读取工作表
• 读取行
• 读取单元格
• 读取单元格样式
• 设置单元格内容
• 设置单元格样式

将制作好的样式模板放在src/main/resources 目录下。核心代码如下

// 加载模板

Resource resource = new ClassPathResource("excel-template/demo.xlsx"); 

FileInputStream  fils = new FileInputStream(resource.getFile());

// 根据模板创建工作簿

Workbook wb = new XssFWorkbook(fis);

// 读取工作簿

Sheet sheet = wb.getSheetAt(0);

// 抽取公共样式

Row row = sheet.getRow(2);  // 读取模板的数据行

CellStyle styles [] = new CellStyle[row.getLastCellNum()];  // 存放样式的数组

// 遍历单元格

for (int i = 0; i < row.getLastCellNum; i++) {

    Cell cell = row.getCell(i)    ;

     styles[i] = cell.getCellStyle();

}

// 构造数据单元格

int rowNum = 2;

Cell cell = null;

for (Employee e : empList) {

    row = sheet.createRow(rowIndex++);

    cell = row.createCell(0);

    cell.setCellValue(e.getuserId);

    cell.setCellStyle(styles[0]);   // 将读取到的样式设置在内容单元格上

}

四、百万数据报表

1. 概述

Excel 2003:在POI中使用HSSF对象时，excel2003最多只允许存储65536条数据，一般用来处理较少的数据量。这时对于百万级别数据，Excel肯定容纳不了。
Excel 2007 :当POI升级到XSSF对象时，它可以直接支持excel2007以上版本，因为它采用ooxml格式。这时excel可以支持1048576条数据，单个sheet表就支持近百万条数据。但实际运行时还可能存在问题，原因是执行POI报表所产生的行对象，单元格对象，字体对象，他们都不会销毁，这就导致OOM的风险。

对于百万数据量的Excel导入导出，只讨论基于Excel2007的解决方法。在ApachePoi官方提供了对操作大数据量的导入导出的工具和解决办法，操作ExEel2007使用XSSF对象，可以分为三种模式:分别是用户模式，事件模式，SXSSF对象。值得一提的是，SCSSF不支持模板打印。

用户模式:用户模式有许多封装好的方法操作简单，但创建太多的对象，非常耗内存(之前使用的方法)
事件模式:基于SAX方式解析XML，SAX全称Simple API forXML，它是一个接口，也是一个软件包。它是一种XML解析的替代方法，不同于DOM解析XML文档时把所有内容一次性加载到内存中的方式，它逐行扫描文档，一边扫描，一边解析。
SXSSF对象:是用来生成海量excel数据文件，主要原理是借助临时存储空间生成excel，即将对象写入到本地磁盘。

查看性能当然需要使用监测工具，jdk自带有性能监测工具，通常在jdk的安装目录下的bin文件夹中，在bin目录下找到jvisualvm.exe 程序，这就是监控工具。只需要双击打开就可运行。

双击后进入如下页面。 

双击项目的启动程序类名，在点击头部工具栏的“监视”菜单，就会看到如下图的画面。记录了cpu的使用情况等信息。

2.  思路分析

基于XSSFWork导出Excel报表，是通过将所有单元格对象保存到内存中，当所有的Excel单元格全部创建完成之后一次性写入到Excel并导出。当百万数据级别的Excel导出时，随着表格的不断创建，内存中对象越来越多，直至内存溢出。Apache Poi提供了SXSSFWork对象，专门用于处理大数据量Excel报表导出。

3. 原理分析

在实例化SXSSFWork这个对象时，可以指定在内存中所产生的POI导出相关对象的数量(默认100)，一旦内存中的对象的个数达到这个指定值时，就将内存中的这些对象的内容写入到磁盘中(XML的文件格式)，就可以将这些对象从内存中销毁，以后只要达到这个值，就会以类似的处理方式处理，直至Excel导出完成。在SXSSFWork中有个构造方法，可以规定在内存中存在多少个对象，它就是 new SXSSFWorkbook(200); 表示可以在内存中最多可以有200个对象，超过这个数量就会将临时写入到磁盘，然后清除内存中的对象。但需要注意的是：我们创建的对象是直接放在内存中的，速度非常快，但我们对磁盘的读写速度是比不上内存上的读写速度的。举个简单的例子，我们在内存中1秒创建了100个对象，而在磁盘中1秒写入了1个对象到磁盘，随着创建数量的增加，内存还是会溢出，所以这个方法不是万能的，唯一能做的就是减少对象的创建。可以不设置样式和字体，这样可以减少对象的生成。

 4. 导出数据到本地案例

将数据库查询的百万数据写入到Excel中。

@Test
    public void readExcelBigData() throws IOException {
        //1. 获取到数据
        List<User> dataList = queryAll();
        // 2. 创建工作簿，SXSSFWorkbook：百万数据报表专用
        // SXSSFWorkbook(200) 这个构造方法表示阈值，内存中的对象数量的最大数量值，这里表示内存中最多存在200个对象，超过这个数量就会将临时写入到磁盘，然后清除内存中的对象。
        SXSSFWorkbook wb = new SXSSFWorkbook(200);
        // 构造sheet
        Sheet sheet = wb.createSheet();
        // 处理标题
        String[] titles = "编号，姓名，工号".split("，");
        // 创建行
        Row row = sheet.createRow(0);
        int titleIndex = 0;  // 标题索引
        // 将标题写入行
        Cell cell = null;
        for (String title : titles) {
            cell = row.createCell(titleIndex++);
            cell.setCellValue(title);
        }
        // 将查询的数据写入到excel中
        OutputStream out = new FileOutputStream(new File("Z:\\百万数据导出.xlsx"));
        for (int i = 1; i <= dataList.size(); i++) {
            User u = dataList.get(i - 1);
            HSSFRow r = sheet.createRow(i);
            HSSFCell cell = r.createCell(0);
            cell.setCellValue(u.getId());

            cell = r.createCell(1);
            cell.setCellValue(u.getUsername());

            cell = r.createCell(1);
            cell.setCellValue(u.getWoekNo());

        }
        
        wb.write(out);

        out.close();
        wb.close();
    }

5. 导入数据到数据库案例

存在问题：对于大数据量的读取操作，java会一次性将所有的Excel数据对象存入到内存中，可能导致内存溢出。

解决：逐行读取并使用。这样读取完一条数据，使用后就会将对象销毁。

实现步骤：

（1）设置POI的事件模式

• 根据Excel 获取文件流
• 根据文件流创建OPCPage
• 创建XSSFReader对象

（2）Sax 解析

• 自定义Sheet处理器
• 创建Sax的XmlReader对象
• 设置Sheet的事件处理器
• 逐行读取

原理分析：

对于Excel2007的实质是XML格式存储数据，可以使用基于SAX的方式解析XML，完成Excel的读取，SAX提供了一种XML文档中读取数据的机制，他逐行扫描文档，一边扫描一边解析。由于应用程序只在读取数据时检查数据，因此不需要将数据存储在内存中，这对于大型文档的解析是个巨大的优势。

 1. 自定义事件处理器，处理每一行的数据读取

/**
     * 自定义事件处理器，处理每一行的数据读取
     * */
    public class SheetHandler implements XSSFSheetXMLHandler.SheetContentsHandler {
        private User user;  // 实体对象
        /**
         * 开始解析某一行时触发
         * */
        @Override
        public void startRow(int i) {
            // 实例化对象
            if (i > 0) {
                user = new User();
            }
        }
        /**
         * 结束解析某一行时触发
         * */
        @Override
        public void endRow(int i) {
            // 适用对象进行业务操作
            System.out.println(user);
        }
        /**
         * 对每一行的每个表格进行处理
         * cellReference: 单元格名称
         * value： 数据
         * xssfComment： 批注
         * */
        @Override
        public void cell(String s, String s1, XSSFComment xssfComment) {
            // 为对象赋值
            if (user != null) {
                String pix = CellReference.substring(0, 1);  // 获取到单元格名称
                switch (pix) {
                    case "A":
                        user.setId(value);
                        break;
                    case "B":
                        user.setUsername(value);
                        break;
                    case "C":
                        user.setWorkNo(value);
                        break;
                    default:
                        break;
                }
                
            }
        }
    }

2. 测试

@Test
    void PoiTest() throws Exception {
        String path = "Z:\\百万数据报表.xlsx";  // 报表路径
        // 1. 根据excel报表获取 OPCPackage
        OPCPackage open = OPCPackage.open(path, PackageAccess.READ);// 以只读的形式打开文件
//2. 创建 XSSFReader
        XSSFReader reader = new XSSFReader(open);
//        3. 获取SharedStringTable对象
        SharedStrings table = reader.getSharedStringsTable();
//        4.获取styleTable对象
        StylesTable stylesTable = reader.getStylesTable();
//        5.创建Sax的xmlReader对象
        XMLReader xmlReader = XMLReaderFactory.createXMLReader();
//        6.注册事件处理器
        XSSFSheetXMLHandler xmlHandler = new XSSFSheetXMLHandler(stylesTable, table, new SheetHandler(), false);
        xmlReader.setContentHandler(xmlHandler);
//        7. 逐行读取
         XSSFReader.SheetIterator sheetIterator= (XSSFReader.SheetIterator) reader.getSheetsData();
         while (sheetIterator.hasNext()) {
             InputStream stream = sheetIterator.next();  // 每一个sheet的流数据
             InputSource is = new InputSource(stream);
             xmlReader.parse(is);
         }
    }