POI报表——模板打印 AND 海量数据导出

最新推荐文章于 2022-08-09 19:12:40 发布

Mr小林

最新推荐文章于 2022-08-09 19:12:40 发布

阅读量1.3k

点赞数 2

分类专栏： ------【Java】文章标签：报表 poi 工作

本文链接：https://blog.csdn.net/mrlin6688/article/details/53572000

版权

------【Java】专栏收录该内容

94 篇文章 0 订阅

订阅专栏

准备工作

导入POI的Jar包：

这里写图片描述

maven工程需要在：Pom.xml文件中的xml配置

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

模板打印的9个步骤：

1.制作模板，样式和字体都设置好
2.加载模板文件，得到工作簿。
3.获取工作表。
4.获取行对象。
5.获取单元格对象。
6.读取单元格的内容。
7.读取单元格的样式。
8.保存，关闭流。
9.下载

对象介绍：

HSSFWorkbook对象

Excel2003数据量有限制：65536行，256列
HSSFWorkbook对象只能操作excel2003 xls扩展名。支持使用模板方法
XSSFWorkbook对象
XSSFWorkbook可以支持excel2007及以上版本 xlsx扩展名。支持使用模板方法
Excel2007支持单sheet：1048576行，16384列
工作原理：XSSFWorkbook是可以实现百万级别数据报表的，实质上，会产生问题。原因是在执行POI报表时所产生的行对象，单元格对象，样式对象，字体对象，它们都不会销毁，只有POI报表完成这些对象事才会销毁，这样会造成CPU急剧工作，堆内存中对象大量增加，最终导致堆内存溢出。
SXSSFWorkbook对象
可以解决百万数据的poi，但不支持使用模板方法。要求poi相关的jar在3.0以上。

工作原理：
在初始化SXSSFWorkbook这个对象时，可以指定内存中所产生后的POI导出相关对象的个数（默认100个），一旦内存中的对象个数达到了这个指定值时，就将内存中的这些对象内容写到磁盘中（XML文件格式），就将这些对象从内存中销毁掉，以后只要达到这个值时，都会这样处理。在最终的excel导出完成时，也会将写入在xml文件中的内容一起导出。
缺点：
内存与磁盘交互时，需要占用IO流相关的操作，它本身也是耗时的，如果机器配置比较低时，就会出现磁盘写操作还没有完成，但是内存中又存在了指定数量的对象。

实战应用：

手动制作模板：

手动模板

代码实现：

/**
     * 实现出货表的打印(使用模板)
     */
    public String print() throws Exception {
        //1.加载指定模板文件，获取工作簿
        String path = ServletActionContext.getServletContext().getRealPath("/")+"make/xlsprint/tOUTPRODUCT.xlsx";//获取了模板文件所在位置
        //Workbook wb = new HSSFWorkbook(new FileInputStream(path));

        Workbook wb = new XSSFWorkbook(new FileInputStream(path));//它可以操作excel2007+版本
        //2.获取工作表
        Sheet sheet  = wb.getSheetAt(0);

        //定义一些公共变量
        int rowNo=0,cellNo=1;
        Row nRow=null;
        Cell nCell =null;
        //3.=================================大标题======================
        nRow = sheet.getRow(rowNo++);//获取第一行的行对象   rowNo++就会进入第二行

        //获取单元格对象
        nCell = nRow.getCell(cellNo);

        //设置单元格的内容
        nCell.setCellValue(inputDate.replace("-0", "-").replace("-", "年")+"月份出货表");//2015-01  2015-10


        //4.==================================小标题===========================
        rowNo++;//跳过第二行    

        //5.==================================内容 ==============================
        //获取了第三行的行对象
        nRow = sheet.getRow(rowNo);//rowNo没有进行++运算， rowNo还等于2

        //获取单元格对象的样式和内容
        CellStyle customerCellStyle = nRow.getCell(cellNo++).getCellStyle();//客户

        System.out.println("获取单元格的内容:"+nRow.getCell(cellNo).getStringCellValue());

        CellStyle orderNoCellStyle = nRow.getCell(cellNo++).getCellStyle();//订单号
        CellStyle productNoCellStyle = nRow.getCell(cellNo++).getCellStyle();//货号
        CellStyle cNumberCellStyle = nRow.getCell(cellNo++).getCellStyle();//数量
        CellStyle factoryNameCellStyle = nRow.getCell(cellNo++).getCellStyle();//工厂
        CellStyle deliveryPeriodCellStyle = nRow.getCell(cellNo++).getCellStyle();//工厂交期
        CellStyle shipTimeCellStyle = nRow.getCell(cellNo++).getCellStyle();//船期
        CellStyle tradeTermsCellStyle = nRow.getCell(cellNo++).getCellStyle();//贸易条款



        String hql="from ContractProduct where to_char(contract.shipTime,'yyyy-MM')='"+inputDate+"' ";
        List<ContractProduct> cpList = contractProductService.find(hql, ContractProduct.class, null);//查询符合条件的记录（ 符合船期要求）

        //遍历货物列表
        for(ContractProduct cp :cpList){
            nRow = sheet.createRow(rowNo++);//产生行对象
            nRow.setHeightInPoints(24f);//设置行高

            cellNo=1;

            nCell = nRow.createCell(cellNo++);//产生单元格对象
            nCell.setCellValue(cp.getContract().getCustomName());//单元格的内容 
            nCell.setCellStyle(customerCellStyle);//设置样式

            //订单号
            nCell = nRow.createCell(cellNo++);//产生单元格对象
            nCell.setCellValue(cp.getOrderNo());//单元格的内容 
            nCell.setCellStyle(orderNoCellStyle);//设置样式

            //货号
            nCell = nRow.createCell(cellNo++);//产生单元格对象
            nCell.setCellValue(cp.getProductNo());//单元格的内容 
            nCell.setCellStyle(productNoCellStyle);//设置样式

            //数量
            nCell = nRow.createCell(cellNo++);//产生单元格对象
            nCell.setCellValue(cp.getCnumber());//单元格的内容 
            nCell.setCellStyle(cNumberCellStyle);//设置样式

            //工厂
            nCell = nRow.createCell(cellNo++);//产生单元格对象
            nCell.setCellValue(cp.getFactoryName());//单元格的内容 
            nCell.setCellStyle(factoryNameCellStyle);//设置样式

            //工厂交期
            nCell = nRow.createCell(cellNo++);//产生单元格对象
            nCell.setCellValue(UtilFuns.dateTimeFormat(cp.getContract().getDeliveryPeriod()));//单元格的内容 
            nCell.setCellStyle(deliveryPeriodCellStyle);//设置样式

            //船期
            nCell = nRow.createCell(cellNo++);//产生单元格对象
            nCell.setCellValue(UtilFuns.dateTimeFormat(cp.getContract().getShipTime()));//单元格的内容 
            nCell.setCellStyle(shipTimeCellStyle);//设置样式

            //贸易条款
            nCell = nRow.createCell(cellNo++);//产生单元格对象
            nCell.setCellValue(cp.getContract().getTradeTerms());//单元格的内容 
        nCell.setCellStyle(tradeTermsCellStyle);//设置样式  
        }

        //6.==============================实现文件下载========================
        DownloadUtil downLoadUtil = new DownloadUtil(); 
        ByteArrayOutputStream baos = new ByteArrayOutputStream();//缓存
        wb.write(baos);//将工作簿的内容全部输出到缓存
        baos.close();//刷新后，缓存中的数据是最新的   
        HttpServletResponse response = ServletActionContext.getResponse();//得到response对象
        downLoadUtil.download(baos, response, "出货表.xlsx");//文件下载
        return NONE;
    }

从Excel中的数据导入数据库是很简单的事：
“获取单元格内容”举例： nRow.getCell(cellNo).getStringCellValue();

导出结果：

**出货表**

百万数据高效导出：

规则：

HIbernate数据单表要小于500万
Mybatis/Jdbc数据量大于500万，oracle不要超过一个亿

SXSSFwookbook+oracl+Jdbc+线程高效导出海量数据：

大数据导出：POITest2是对高效导出的例子84万条只需要3s、做好的技术组合到了一起。jdbc循环一次15000条记录，歇1毫秒，会搬移很多对象到磁盘中，防止对内存中对象过多而造成内存溢出，同时防止cpu占用过多，然后继续循环，这样cup可以高效运转，循环往复。。。。。这样的配个解决了，SXSSFwookbook的这个缺点（内存与磁盘交互时，需要占用IO流相关的操作，它本身也是耗时的，如果机器配置比较低时，就会出现磁盘写操作还没有完成，但是内存中又存在了指定数量的对象。）

package poi.test;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.junit.Test;

public class POITest2 {

    public static void main(String[] args) throws Exception{
        String xlsFile = "c:/poiSXSSFDBBigData.xlsx";                   //输出文件
        Workbook wb = new SXSSFWorkbook(100);               //创建excel文件，内存只有100条记录【关键语句】
        Sheet sheet = wb.createSheet("我的第一个工作簿");       //建立新的sheet对象

        Row nRow = null;
        Cell nCell   = null;

        //使用jdbc链接数据库
        Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();     
        String url = "jdbc:oracle:thin:@127.0.0.1:1521:ORCL";
        String user = "itheima";
        String password = "itheima";

        /*Class.forName("com.mysql.jdbc.Driver").newInstance();  
        String url = "jdbc:mysql://localhost:3306/jkmore100?characterEncoding=utf8";
        String user = "root";
        String password = "root";*/

        Connection conn = DriverManager.getConnection(url, user,password);   
        Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);   

        String sql = "SELECT * FROM CONTRACT_PRODUCT_C";    //100万测试数据
        //sql = "select name,age,des from customer";    //100万测试数据
        ResultSet rs = stmt.executeQuery(sql);                          //bug 要分次读取，否则记录过多


        long  startTime = System.currentTimeMillis();   //开始时间
        System.out.println("strat execute time: " + startTime);
        //context
        int rowNo = 0;
        int colNo = 0;
        while(rs.next()) {

            for(int i=0;i<6000;i++){
                colNo = 0;
                nRow = sheet.createRow(rowNo++);

                nCell = nRow.createCell(colNo++);
                nCell.setCellValue(rs.getString(colNo));

                nCell = nRow.createCell(colNo++);
                nCell.setCellValue(rs.getString(colNo));

                if(rowNo%100==0){
                    System.out.println("row no: " + rowNo);
                }
            }

            Thread.sleep(1);            //休息一下，防止对CPU占用
        }

        long finishedTime = System.currentTimeMillis(); //处理完成时间
        System.out.println("finished execute  time: " + (finishedTime - startTime)/1000 + "s");


        FileOutputStream fOut = new FileOutputStream(xlsFile);
        wb.write(fOut);
        fOut.flush();
        fOut.close();

        long stopTime = System.currentTimeMillis();     //写文件时间
        System.out.println("write xlsx file time: " + (stopTime - startTime)/1000 + "m");


        close(rs, stmt, conn);

    }

    //close resource
    private static  void close(ResultSet rs, Statement stmt, Connection conn ) throws SQLException{
        rs.close();   
        stmt.close();   
        conn.close(); 
    }   

}

小结：

以上是我对POI报表模板方法和海量数据导出的总结，如有不足，请您给出指正，大家一起成长。

Mr小林

关注

2
点赞
踩
4

收藏

觉得还不错? 一键收藏
2
评论
POI报表——模板打印 AND 海量数据导出

准备工作导入POI的Jar包：maven工程需要在：Pom.xml文件中的xml配置<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.11</version> </dependenc
复制链接

扫一扫