POI导入导出和报表设计和性能比较

---

一、POI简介

1.1、说明

apache POI官网：http://poi.apache.org/index.html

1. POI是Apache软件基金会用Java编写的免费开源的跨平台的 Java API
2. Apache POI提供API给Java程序对Microsoft Office格式档案读和写的功能。
3. POI为“Poor Obfuscation Implementation”的首字母缩写，意为“简洁版的模糊实现”。
4. POI的主要功能是可以用Java操作Microsoft Office的相关文件，但是一般我们都是用来操作Excel相关文件。

1.2、使用场景

1. 将数据库信息导出为Excle表格（导出数据）
2. 将Excel格式的文件导入到数据库里（导入数据）

1.3、结构

1. HSSF － 提供读写Microsoft Excel格式档案的功能。–2003版的xls文件
2. XSSF － 提供读写Microsoft Excel OOXML格式档案的功能。–2007版的xlsx文件
3. HWPF － 提供读写Microsoft Word格式档案的功能。
4. HSLF － 提供读写Microsoft PowerPoint格式档案的功能。
5. HDGF － 提供读写Microsoft Visio格式档案的功能

什么是OOXML格式
简单的说就是：

a、07版（xlsx） 默认基于 XML 的文件格式
b、03版（xls） 遵循二进制文件格式

1.4、xls和xlsx的对比

限制和格式

1. 03版（xls）只能导出 【256列和65536行】
   遵循二进制文件格式
2. 07版（xlsx）的导出能导出 【16384列1048576条】
   默认基于 XML 的文件格式

1.1.1、优缺点

小文件用（HSSF） 03版本:
优点：过程中写入缓存，不操作磁盘，最后一次性写入磁盘，速度快
缺点：超出65536行报错 java.lang.IllegalArgumentException: Invalid row number (65536) outside allowable range (0…65535)

大文件用（XSSF） 07版本:
优点：可以写较大的数据量
缺点：写数据时速度非常慢，非常消耗内存，也会发生内存溢出，如100万条数据

拓展SXSSF：推荐使用
SXSSF 是07版本的加强版 可写入数据更多，速度更快
优点：可以写较大的数据量，写数据速度快，占用更少的内存
注意：
1、过程中会产生临时文件，需要清理临时文件
2、默认有100条记录被保存在内存中，如果超过这个数量，则最前面的数据被写入临时文件
3、如果想自定义内存中数据的数量，可以使用new SXSSFWorkbook（数量）

1.5、常用组件

1.1、 说明：

HSSF是仅支持2003版的.xls文件，不能去操作2007版的.xlsx文件不然会报错
XSSF才是对2007版的支持（如：XSSFWorkbook）
XSSF才是对2007版的支持（如：XSSFWorkbook）
SXSSFk也是对2007版的支持是升级版（如SXSSFWorkbook）

1.2、常见组件：

1. HSSFWorkbook excel的文档对象
2. HSSFSheet excel的表单
3. HSSFRow excel的行
4. HSSFCell excel的格子单元
5. HSSFFont excel字体
6. HSSFCellStyle cell样式

1.3、03版操作流程

1. 用HSSFWorkbook打开或者创建“Excel文件对象”
   2、用HSSFWorkbook对象返回或者创建Sheet对象
   3、用Sheet对象返回行对象，用行对象得到Cell对象
   4、对Cell对象读写。
   5、将生成的HSSFWorkbook放入HttpServletResponse中响应到前端页面

1.6、excel的组成

一个Excel文件对应一个workbook，一个workbook中有多个sheet组成，一个sheet是由多个行(row)和列(cell)组成

---

二、依赖坐标

 <!-- xls03版本 -->
 <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
  <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>4.1.2</version>
  </dependency>

  <!-- xlsx07版本 -->
  <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
  <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>4.1.2</version>
  </dependency>

三、设计报表

效果：

说明:对于数据量不是很大的要求，推荐使用xls更快

示例代码：

	/**
     * 【03版本xls】 测试导出
     *  测试设计报表-数据量小导出快
     * http://127.0.0.1:8081/export03xls
     */
    @GetMapping("/export03xls")
    public void export03xls(HttpServletRequest request, HttpServletResponse response) throws IOException {
        String fname = "【03版本xls】-测试导出";
        fname = new String(fname.getBytes("GBK"), "iso8859-1");
        OutputStream os = response.getOutputStream();
        response.reset();
        response.setHeader("Content-disposition", "attachment; filename=" + fname + ".xls");
        response.setCharacterEncoding("utf-8");
        response.setContentType("application/msexcel");

        //创建一个工作簿
        Workbook workbook = new HSSFWorkbook();
        //创建一个工作表
        Sheet sheet = workbook.createSheet("第一个sheet");

        /**
         * 设置页边距
         * 打印前的页边距设置【设置上下左右】
         * 可以用打印预览查看
         */
        sheet.setMargin(HSSFSheet.BottomMargin, (double) 0.1);
        sheet.setMargin(HSSFSheet.LeftMargin, (double) 0.1);
        sheet.setMargin(HSSFSheet.RightMargin, (double) 0.1);
        sheet.setMargin(HSSFSheet.TopMargin, (double) 0.1);
        sheet.setHorizontallyCenter(true);//是否在页面上水平居中
        sheet.setVerticallyCenter(false);//是否在页面上垂直居中


        /**
         * 设置列宽-列从0开始
         * 设置约为10个英文字符的宽度【10*256】
         */
        sheet.setColumnWidth(0, 15 * 256);
        sheet.setColumnWidth(1, 15 * 256);
        sheet.setColumnWidth(2, 15 * 256);
        sheet.setColumnWidth(3, 15 * 256);

        /**
         * 定义字体
         */
        Font font = workbook.createFont();
        font.setColor(HSSFFont.COLOR_RED);
        font.setFontName("黑体");


        /**
         *  表头单元格的样式
         */
        CellStyle titleStyle = workbook.createCellStyle(); //创建一个样式
        titleStyle.setAlignment(HorizontalAlignment.CENTER);//设置垂直对齐的样式为居中对齐;
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);//设置垂直对齐的样式为居中对齐;
        titleStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex()); //设置背景色-浅蓝色
        titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);//填充颜色的模式-实心
        titleStyle.setFont(font);

        /**
         *  定义其他单元格的样式
         */
        CellStyle cellStyle = workbook.createCellStyle(); //创建一个样式
        cellStyle.setBorderTop(BorderStyle.MEDIUM);//上边框
        cellStyle.setBorderBottom(BorderStyle.MEDIUM); //底部边框-中等边框
        cellStyle.setBorderLeft(BorderStyle.MEDIUM); //左边框
        cellStyle.setBorderRight(BorderStyle.MEDIUM);//右边框

        cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());//底部边框颜色
        cellStyle.setLeftBorderColor(IndexedColors.BLACK.index);//左边框颜色
        cellStyle.setRightBorderColor(IndexedColors.BLACK.index);//右边框颜色

        cellStyle.setAlignment(HorizontalAlignment.CENTER);//设置垂直对齐的样式为居中对齐;
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//设置垂直对齐的样式为居中对齐;

        cellStyle.setFont(font);//使用字体


        /**
         * 创建4行
         * 从0开始算
         * 创建出固定需要的行,用于设计表格格式
         */
        Row row1 = sheet.createRow(0);
        Row row2 = sheet.createRow(1);
        Row row3 = sheet.createRow(2);
        Row row4 = sheet.createRow(3);


        /**
         *  合并单元格
         *  4个参数【起始行，结束行，起始列，结束列】
         *  行和列都是从0开始计数，且起始结束都会合并
         */
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 3));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 1, 2));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 3, 3));


        /**
         * 创建单元格
         * 从0开始算
         */
        Cell cell1 = row1.createCell(0);
        cell1.setCellValue("统计分析");
        cell1.setCellStyle(titleStyle);


        Cell cell2 = row3.createCell(0);
        cell2.setCellValue("幼儿园");
        cell2.setCellStyle(cellStyle);

        Cell cell3 = row3.createCell(1);
        cell3.setCellValue("性别");
        cell3.setCellStyle(cellStyle);

        Cell cell4 = row4.createCell(1);
        cell4.setCellValue("男");
        cell4.setCellStyle(cellStyle);

        Cell cell5 = row4.createCell(2);
        cell5.setCellValue("女");
        cell5.setCellStyle(cellStyle);

        Cell cell6 = row3.createCell(3);
        cell6.setCellValue("总人数");
        cell6.setCellStyle(cellStyle);
        row4.createCell(3).setCellStyle(cellStyle);//把边框补充完整


        //模拟数据
        ArrayList<Student> list = new ArrayList<>();
        list.add(new Student("泉州",100, 1000, 1100));
        list.add(new Student("厦门",200, 2000, 2200));
        list.add(new Student("福州",300, 3000, 3300));

        /**
         *  遍历数据-创建单元格
         *  从第五行，第二列开始
         *  列不变，行增加
         */
        int rowNumber = 4;//第五行
        for (Student student : list) {
            Row sheetRow5 = sheet.createRow(rowNumber);//创建行--创建新行会覆盖旧行
            //创建单元格
            Cell cell5_0 = sheetRow5.createCell(0);//地点
            Cell cell5_1 = sheetRow5.createCell(1);//男
            Cell cell5_2 = sheetRow5.createCell(2);//女
            Cell cell5_3 = sheetRow5.createCell(3);//总数
            //样式
            cell5_0.setCellStyle(cellStyle);
            cell5_1.setCellStyle(cellStyle);
            cell5_2.setCellStyle(cellStyle);
            cell5_3.setCellStyle(cellStyle);
            //赋值
            cell5_0.setCellValue(student.getAddr());
            cell5_1.setCellValue(student.getMaleCount());
            cell5_2.setCellValue(student.getFemaleCount());
            cell5_3.setCellValue(student.getAllCount());
            rowNumber++;//累增行号
        }


        //生成一张表(IO流)  03版本使用xls结尾
        workbook.write(os);
        if (os != null) {
            os.close();
        }
        System.out.println("文件生成完毕！");

    }

四、xlsx和其升级版导出效率比较

结论：升级版SXSSF比原本XSSF强出5倍

【07版本xlxs】 测试导出

 /**
     * 【07版本xlxs】 测试导出
     *  测试性能
     * http://127.0.0.1:8081/export07xlsx
     */
    @GetMapping("/export07xlsx")
    public void export07xlsx(HttpServletRequest request, HttpServletResponse response) throws IOException {
        String fname = "【07版本xlxs】 测试导出";
        fname = new String(fname.getBytes("GBK"), "iso8859-1");
        OutputStream os = response.getOutputStream();
        response.reset();
        response.setHeader("Content-disposition", "attachment; filename=" + fname + ".xlsx");
        response.setCharacterEncoding("utf-8");
        response.setContentType("application/msexcel");

        //记录导出65536行数据多长时间
        long begin = System.currentTimeMillis();
        //创建一个工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        //创建表
        XSSFSheet sheet = workbook.createSheet();
        //写入数据  此时可以写入的数据可以超过65536
        for(int rowNumber = 0;rowNumber<65536;rowNumber++ ){
            XSSFRow row = sheet.createRow(rowNumber);
            for (int cellNum = 0; cellNum < 10; cellNum++) {
                XSSFCell cell = row.createCell(cellNum);
                cell.setCellValue(cellNum);
            }
        }
        workbook.write(os);
        //关闭资源
        os.close();
        long end = System.currentTimeMillis();
        System.out.println("07版本时间(秒):"+((double)(end-begin)/1000)); //11.463秒
    }

【07版本-加强版xlxs】导出测试

  /**
     * 【07版本-加强版xlxs】 测试导出
     *  测试性能-比原07版强出5倍
     * http://127.0.0.1:8081/export07Plusxlsx
     */
    @GetMapping("/export07Plusxlsx")
    public void export07Plusxlsx(HttpServletRequest request, HttpServletResponse response) throws IOException {
        String fname = "【07版本-加强版xlxs】 测试导出";
        fname = new String(fname.getBytes("GBK"), "iso8859-1");
        OutputStream os = response.getOutputStream();
        response.reset();
        response.setHeader("Content-disposition", "attachment; filename=" + fname + ".xlsx");
        response.setCharacterEncoding("utf-8");
        response.setContentType("application/msexcel");

        //记录导出65536行数据多长时间
        long begin = System.currentTimeMillis();
        //创建一个工作簿
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        //创建表
        SXSSFSheet sheet = workbook.createSheet();
        //写入数据
        for(int rowNumber = 0;rowNumber<100000;rowNumber++ ){
            SXSSFRow row = sheet.createRow(rowNumber);
            for (int cellNum = 0; cellNum < 10; cellNum++) {
                SXSSFCell cell = row.createCell(cellNum);
                cell.setCellValue(cellNum);
            }
        }

        workbook.write(os);
        //关闭资源
        os.close();
        //清除临时文件
        workbook.dispose();
        long end = System.currentTimeMillis();
        System.out.println("07加强版时间(秒):"+((double)(end-begin)/1000)); //1.877秒
    }

效果图:

---

五、xls和xlsx导入测试

项目结构：

示例代码：

package sqy.controller;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;
import org.springframework.core.io.ClassPathResource;
import org.springframework.util.ResourceUtils;
import org.springframework.web.bind.annotation.RestController;

import java.io.File;
import java.io.FileInputStream;
import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * @author suqinyi
 * @Date 2022/4/14
 * poi导入
 */
@RestController
public class PoiImportController {

    /**
     *  测试读取 03版xls的excel文件
     *   使用 HSSFWorkbook
     */
    @Test
    public void read03xls() throws Exception {
        //读取resources下的文件
        File file = ResourceUtils.getFile("classpath:excelfile/03版.xls");
        //1.获取文件流
        FileInputStream inputStream = new FileInputStream(file);
        //2.创建工作簿
        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
        //3.得到表
        HSSFSheet sheetAt = workbook.getSheetAt(0);
        //4.得到行
        HSSFRow row = sheetAt.getRow(0);
        //5.得到列
        HSSFCell cell = row.getCell(0);
        //读取值得时候，一定要注意类型
        //cell.getNumericCellValue()获取数字类型
        System.out.println(cell.getStringCellValue()); //获取字符串类型
        //关闭流
        inputStream.close();
    }


    /**
     *  测试读取 07版xlsx的excel文件
     *  使用 XSSFWorkbook
     */
    @Test
    public void read07xlsx() throws Exception {
        //加载资源文件
        ClassPathResource classPathResource = new ClassPathResource("excelfile/" + "07版.xlsx");
        //获取文件
        File file = classPathResource.getFile();

        //1.获取文件流
        FileInputStream inputStream = new FileInputStream(file);
        //2.创建工作簿
        Workbook workbook = new XSSFWorkbook(inputStream);
        //3.得到表
        Sheet sheetAt = workbook.getSheetAt(0);
        //4.得到行
        Row row = sheetAt.getRow(0);
        //5.得到列
        Cell cell = row.getCell(0);
        System.out.println(cell.getStringCellValue());
        //关闭流
        inputStream.close();
    }


    /**
     * 07版xlsx的案例使用
     * @throws Exception
     */
    @Test
    public void read07xlsx_example() throws Exception {
        //加载资源文件
        ClassPathResource classPathResource = new ClassPathResource("excelfile/" + "07版.xlsx");
        //获取文件
        File file = classPathResource.getFile();

        //1.获取文件流
        FileInputStream inputStream = new FileInputStream(file);
        //2.创建一个工作簿
        Workbook workbook = new XSSFWorkbook(inputStream);
        Sheet sheet = workbook.getSheetAt(0);
        //3.获取标题内容
        Row rowTitle = sheet.getRow(0);
        if (rowTitle!=null){
            //rowTitle.getPhysicalNumberOfCells()是获取这一行一共有多少列
            int cellCount = rowTitle.getPhysicalNumberOfCells();
            for (int cellNumber = 0; cellNumber < cellCount; cellNumber++) {
                //遍历获取单元格
                Cell cell = rowTitle.getCell(cellNumber);
                if (cell!=null){
                    // 表头:07版姓名 | 表头:年龄 | 表头:出生日期 | [第2行-第1个]【String】李四
                    System.out.print("列名:"+cell.getStringCellValue()+" | ");
                }
            }
        }

        System.out.println("\n"+"---------------------------");

        //获取表中的内容
        //sheet.getPhysicalNumberOfRows(); 获取这个表中一共有多少行
        int rowCount = sheet.getPhysicalNumberOfRows();
        //因为第一行为标题，所以rowNuber要从第二行开始
        for (int rowNumber = 1; rowNumber < rowCount; rowNumber++) {
            Row rowData = sheet.getRow(rowNumber);
            if (rowData!=null){
                int cellCount = rowTitle.getPhysicalNumberOfCells();//总行数
                for (int cellNumber = 0; cellNumber < cellCount; cellNumber++) {
                    //遍历获取单元格
                    System.out.print("[第"+(rowNumber+1)+"行-第"+(cellNumber+1)+"个]");
                    Cell cell = rowData.getCell(cellNumber);
                    //匹配列的数据类型
                    if (cell!=null){
                        CellType cellType = cell.getCellType();
                        String cellValue = "";
                        switch (cellType){
                            case STRING://字符串类型
                                System.out.print("【String】");
                                cellValue = cell.getStringCellValue();
                                break;
                            case BOOLEAN://布尔类型
                                System.out.print("【Boolen】");
                                cellValue = String.valueOf(cell.getBooleanCellValue()) ;
                                break;
                            case _NONE://空
                                System.out.print("【Blank】");//如果是空直接输出
                                break;
                            case NUMERIC://数字（可能是日期或普通数字）
                                if (HSSFDateUtil.isCellDateFormatted(cell)){ //判断是否是日期类型
                                    System.out.print("【Date】");
                                    Date date = cell.getDateCellValue();
                                    cellValue = new SimpleDateFormat("yyyy-MM-dd").format(date);
                                }else {  //如果不是日期类型就是数字类型
                                    //不是日期格式防止日期过长
                                    System.out.print("【转换为字符串输出】");
                                    cell.setCellType(CellType.STRING);
                                    cellValue = cell.toString();
                                }
                                break;
                            case ERROR://如果是异常类型直接输出
                                System.out.print("【数据类型错误】");
                                break;
                        }
                        System.out.println(cellValue);
                    }
                }
            }
        }
        //关闭输入流
        inputStream.close();
    }


}

效果：