POI报表及百万数据导入导出方案

1、POI报表的概述

1.1 需求说明

在企业级应用开发中，Excel报表是一种最常见的报表需求。Excel报表开发一般分为两种形式：为了方便操作，基于Excel的报表批量上传数据；通过java代码生成Excel报表。

1.2 Excel的两种形式

目前世面上的Excel分为两个大的版本Excel2003和Excel2007及以上两个版本，两者之间的区别如下：

Excel2003是一个特有的二进制格式，其核心结构是复合文档类型的结构，存储数据量较小；Excel2007 的核心结构是 XML 类型的结构，采用的是基于 XML 的压缩方式，使其占用的空间更小，操作效率更高

1.3 常见excel操作工具

Java中常见的用来操作Excl的方式一般有2种：JXL和POI。
JXL只能对Excel进行操作,属于比较老的框架，它只支持到Excel 95-2000的版本。现在已经停止更新和维护。
Apache POI是Apache软件基金会的开源项目，由Java编写的免费开源的跨平台的 Java API，Apache POI提供API给Java语言操作Microsoft Office的功能。可对微软的Word,Excel,Ppt进行操作,包括office2003和2007,Excl2003和2007。poi现在一直有更新。所以现在主流使用POI。

1.4 POI的应用场景

1. 数据报表生成
2. 数据备份
3. 数据批量上传

2、POI的入门操作

2.1 搭建环境

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

2.2 POI结构说明

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

2.3 API介绍

• Workbook： Excel的文档对象,针对不同的Excel类型分为：HSSFWorkbook（2003）和 XSSFWorkbool（2007）
• Sheet： Excel的表单
• Row： Excel的行
• Cell： Excel的格子单元
• Font： Excel字体
• CellStyle： 格子单元样式

2.4 基本操作

2.4.1 创建Excel

@Test
public void createExcel throws Exception {
   
        //1.创建工作簿  HSSFWorkbook -- 2003
        Workbook wb = new XSSFWorkbook(); //2007版本
        //2.创建表单sheet
        Sheet sheet = wb.createSheet("test");
        //3.文件流
        FileOutputStream pis = new FileOutputStream("E:\\excel\\poi\\test.xlsx");
        //4.写入文件
        wb.write(pis);
        pis.close();
    }

2.4.2 创建单元格

@Test
public void createCell throws Exception {
   
        //创建工作簿  HSSFWorkbook -- 2003
        Workbook wb = new XSSFWorkbook(); //2007版本
        //创建表单sheet
        Sheet sheet = wb.createSheet("test");
        //创建行对象  参数：索引（从0开始）
        Row row = sheet.createRow(2);
        //创建单元格对象  参数：索引（从0开始）
        Cell cell = row.createCell(2);
        //向单元格中写入内容
        cell.setCellValue("君莫笑");
        //文件流
        FileOutputStream pis = new FileOutputStream("E:\\excel\\poi\\test1.xlsx");
        //写入文件
        wb.write(pis);
        pis.close();
    }

2.4.3 设置格式

@Test
     public void setStyle() throws Exception {
   
        //创建工作簿  HSSFWorkbook -- 2003
        Workbook wb = new XSSFWorkbook(); //2007版本
        //创建表单sheet
        Sheet sheet = wb.createSheet("test");
        //创建行对象  参数：索引（从0开始）
        Row row = sheet.createRow(2);
        //创建单元格对象  参数：索引（从0开始）
        Cell cell = row.createCell(2);
        //向单元格中写入内容
        cell.setCellValue("君莫笑");

        //样式处理
        //创建样式对象
        CellStyle style = wb.createCellStyle();
        style.setBorderTop(BorderStyle.THIN);//上边框
        style.setBorderBottom(BorderStyle.THIN);//下边框
        style.setBorderLeft(BorderStyle.THIN);//左边框
        style.setBorderRight(BorderStyle.THIN);//右边框
        //创建字体对象
        Font font = wb.createFont();
        font.setFontName("华文行楷"); //字体
        font.setFontHeightInPoints((short)28);//字号
        style.setFont(font);

        //行高和列宽
        row.setHeightInPoints(50);//行高
        //列宽的宽度  字符宽度
        sheet.setColumnWidth(2,31 * 256);//列宽

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

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

        //文件流
        FileOutputStream pis = new FileOutputStream("E:\\excel\\poi\\test2.xlsx");
        //写入文件
        wb.write(pis);
        pis.close();
    }

2.4.4 插入图形

blic void createPicture() throws Exception {
   
        //创建工作簿  HSSFWorkbook -- 2003
        Workbook wb = new XSSFWorkbook(); //2007版本
        //创建表单sheet
        Sheet sheet = wb.createSheet("test");

        //读取图片流
        FileInputStream stream = new FileInputStream("E:\\excel\\poi\\test.jpg");
        //转化二进制数组
        byte[] bytes = IOUtils.toByteArray(stream);
        stream.read(bytes);
        //向POI内存中添加一张图片，返回图片在图片集合中的索引
        int index = wb.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);//参数一：图片的二进制数据，参数二：图片类型
        //绘制图片工具类
        CreationHelper helper = wb.getCreationHelper();
        //创建一个绘图对象
        Drawing<?> patriarch = sheet.createDrawingPatriarch();
        //创建锚点，设置图片坐标
        ClientAnchor anchor = helper.createClientAnchor();
        anchor.setRow1(0);
        anchor.setCol1(0);
        //绘制图片
        Picture picture = patriarch.createPicture(anchor, index);//图片位置，图片的索引
        picture.resize();//自适应渲染图片

        //文件流
        FileOutputStream pis = new FileOutputStream("E:\\excel\\poi\\test3.xlsx");
        //写入文件
        wb.write(pis);
        pis.close();
    }

2.4.5 加载解析Excel

	/**
	 * 读取excel并解析
	 *      sheet.getLastRowNum() : 最后一行的索引
	 *      row.getLastCellNum() ： 最后一个单元格的号码
	 */
	 @Test
     public void parseExcel() throws Exception {
   
        //1.根据Excel文件创建工作簿
        Workbook wb = new XSSFWorkbook("E:\\excel\\poi\\test.xlsx");
        //2.获取Sheet
        Sheet sheet = wb.getSheetAt(0);//参数：索引
        //3.获取Sheet中的每一行，和每一个单元格
        for (int rowNum = 0; rowNum<= sheet.getLastRowNum() ;rowNum ++) {
   
            Row row = sheet.getRow(rowNum);//根据索引获取每一个行
            StringBuilder sb = new StringBuilder();
            for(int cellNum=2;cellNum< row