JAVA使用POI如何导出百万级别数据

转自:JAVA使用POI如何导出百万级别数据
略作改动整理,完美运行

用过POI的人都知道,在POI以前的版本中并不支持大数据量的处理,如果数据量过多还会常报OOM错误,这时候调整JVM的配置参数也不是一个好对策(注:jdk在32位系统中支持的内存不能超过2个G,而在64位中没有限制,但是在64位的系统中,性能并不是太好),好在POI3.8版本新出来了一个SXSSFWorkbook对象,它就是用来解决大数据量以及超大数据量的导入导出操作的,但是SXSSFWorkbook只支持.xlsx格式,不支持.xls格式的Excel文件。
这里普及一下,在POI中使用HSSF对象时,excel 2003最多只允许存储65536条数据,一般用来处理较少的数据量,这时对于百万级别数据,Excel肯定容纳不了,而且在计算机性能稍低的机器上测试,就很容易导致堆溢出。而当我升级到XSSF对象时,它可以直接支持excel2007以上版本,因为它采用ooxml格式。这时excel可以支持1048576条数据,单个sheet表就支持近104万条数据了,虽然这时导出100万数据能满足要求,但使用XSSF测试后发现偶尔还是会发生堆溢出,所以也不适合百万数据的导出。

现在我们知道excel2007及以上版本可以轻松实现存储百万级别的数据,但是系统中的大量数据是如何能够快速准确的导入到excel中这好像是个难题,对于一般的web系统,我们为了解决成本,基本都是使用的入门级web服务器tomcat,既然我们不推荐调整JVM的大小,那我们就要针对我们的代码来解决我们要解决的问题。在POI3.8之后新增加了一个类,SXSSFWorkbook,采用当数据加工时不是类似前面版本的对象,它可以控制excel数据占用的内存,他通过控制在内存中的行数来实现资源管理,即当创建对象超过了设定的行数,它会自动刷新内存,将数据写入文件,这样导致打印时,占用的CPU,和内存很少。但有人会说了,我用过这个类啊,他好像并不能完全解决,当数据量超过一定量后还是会内存溢出的,而且时间还很长。对你只是用了这个类,但是你并没有针对你的需求进行相应的设计,仅仅是用了,所以接下来我要说的问题就是,如何通过SXSSFWorkbook以及相应的写入设计来实现百万级别的数据快速写入。

我先举个例子,以前我们数据库中存在大量的数据,我们要查询,怎么办?我们在没有经过设计的时候是这样来处理的,先写一个集合,然后执行jdbc,将返回的结果赋值给list,然后再返回到页面上,但是当数据量大的时候,就会出现数据无法返回,内存溢出的情况,于是我们在有限的时间和空间下,通过分页将数据一页一页的显示出来,这样可以避免了大数据量数据对内存的占用,也提高了用户的体验,在我们要导出的百万数据也是一个道理,内存突发性占用,我们可以限制导出数据所占用的内存,这里我先建立一个list容器,list中开辟10000行的存储空间,每次存储10000行,用完了将内容清空,然后重复利用,这样就可以有效控制内存,所以我们的设计思路就基本形成了,所以分页数据导出共有以下3个步骤:

1、求数据库中待导出数据的行数

2、根据行数求数据提取次数

3、按次数将数据写入文件

通过以上步骤在效率和用户体验性上都有了很高的提高,接下来上代码

控制器代码

1
2
3
4
5
6
7
8
9
10
11
@GetMapping(value = "exportExcel")
public void exportExcel(HttpServletResponse response) {
    long start = System.currentTimeMillis();
    //获取数据
    List<Stock> objs = testDao.exportExcelBigStock();
    ExcelUtilBigData excelUtilBigData = new ExcelUtilBigData();
    //工具类
    excelUtilBigData.exportExcel(response, objs, PARAM.ETST_COLUMS.getParams(), PARAM.TEST_FIELDS.getParams());
    long end = System.currentTimeMillis();
    System.out.println("用时:"+(end-start));
}

工具类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
package com.owinfo.service.util;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

/**
 * Created by HHYang on 2018/4/16.
 */

public class ExcelUtilBigData {

    private static CellStyle titleStyle; // 标题行样式

    public <T extends Object> void exportExcel(HttpServletResponse response, List<T> objs, String[] colums, String[] fields) {
        try{
            String fileName = "库存表";
            String thizTime = new SimpleDateFormat("yyyy-MM-dd").format(new Date());
            response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode(fileName + "-" + thizTime + ".xlsx", "UTF-8"));
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            // 最重要的就是使用SXSSFWorkbook,表示流的方式进行操作
            // 在内存中保持100行,超过100行将被刷新到磁盘
            SXSSFWorkbook wb = new SXSSFWorkbook(100);
            // -----------获取数据----------------
            //List<Test> objs = this.DataHadle(testDao.exportExcel());
            //List<Test> objs = this.DataHadle(testDao.exportExcelBig());
            System.out.println("总行数:"+objs.size());
            // 数据库中存储的数据行-数据过大时分批次写入文件。
            int page_size = 10000;
            // sheet行数
            int sheet_count = 100000;
            List<T> objsCut;
            List<T> objsSheet;
            Sheet sh;
            Row row;
            for(int sheetNo = 1; sheetNo <= (objs.size()/sheet_count+1); sheetNo++){
                objsSheet = this.PagerBYXX(objs, sheetNo, sheet_count);
                sh = wb.createSheet("sheet"+sheetNo); // 建立新的sheet对象
                row = sh.createRow(0);   // 创建第一行对象
                for(int i = 0; i<colums.length;i++){
                    Cell cel0 = row.createCell(i);//定义表头
                    cel0.setCellValue(colums[i]);
                    cel0.setCellStyle(titleStyle);
                }
                // 求该sheet页待导出数据的行数
                int list_count = objsSheet.size();
                // 根据行数求数据提取次数
                int export_times = list_count % page_size > 0 ? list_count / page_size
                        + 1 : list_count / page_size;
                // 按次数将数据写入文件
                for (int j = 0; j < export_times; j++) {
                    objsCut = this.PagerBYXX(objsSheet, j+1, page_size);
                    int len = objsCut.size() < page_size ? objsCut.size() : page_size;
                    for (int i = 0; i < len; i++) {
                        Row row_value = sh.createRow(j * page_size + i + 1);
                        for(int field = 0; field<fields.length;field++){
                            Cell cel0_value = row_value.createCell(field);
                            String callValue = this.getClassValue(objsCut.get(i), fields[field]);
                            cel0_value.setCellValue(callValue);
                            //cel0_value.setCellValue(objsCut.get(i).getCARDID());
                        }
                    }
                    objsCut = null; // 每次存储len行,用完了将内容清空,以便内存可重复利用
                }
                objsSheet = null;
            }
            OutputStream os = response.getOutputStream();
            wb.write(os);
            os.close();
            wb.dispose();
        } catch (Exception e){
            e.printStackTrace();
        }
    }

    /*手动分页*/
    public static <T extends Object> List<T> PagerBYXX(List<T> lists, int page, int pageSize){
        List<T> listRes;
        if(null == lists || lists.size() == 0){
            return null;
        }
        page = page<=0 ? 1:page;
        pageSize = pageSize<=0 ? 10:pageSize;
        int fromIndex = (page-1)*pageSize;
        int toIndex = (page-1)*pageSize+pageSize;
        if(page == 1 && lists.size() <= page*pageSize){
            return lists;
        }else if(page*pageSize > lists.size()){
            int remain = lists.size()%pageSize;
            listRes = lists.subList(lists.size()-remain, lists.size());
            return listRes;
        }else {
            listRes = lists.subList(fromIndex, toIndex);
            return listRes;
        }
    }

    public String getClassValue(Object obj, String filed) throws InvocationTargetException, IllegalAccessException {
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Class<?> temClass = obj.getClass();
        Field[] fields = temClass.getDeclaredFields();
        Object invoke = null;
        for (Field field : fields) {
            String userFieldName = field.getName();
            Object value = field.getType();
            if(userFieldName.equals(filed)){
                //首字母大写
                char[] cs = userFieldName.toCharArray();
                cs[0] -= 32;
                //System.out.println(String.valueOf(cs));
                //首字母大写
                Method method = null;
                try {
                    //转大小写
                    method = temClass.getMethod("get" + String.valueOf(cs));
                    //不转大小写
                    //method = temClass.getMethod("get" + String.valueOf(userFieldName));
                    if (String.valueOf(value).equals("class java.util.Date")){
                        Object getDate = method.invoke(obj);
                        if(null == getDate){
                            return "";
                        }
                        invoke = simpleDateFormat.format(getDate);
                    }else{
                        invoke = method.invoke(obj);
                        if(null == invoke){
                            return "";
                        }
                    }
                } catch (NoSuchMethodException e) {
                    e.printStackTrace();
                    continue;
                }
                return String.valueOf(invoke);
            }
        }
        return String.valueOf(invoke);
    }

}
ˆ Back To Top