POI数据导出

最新推荐文章于 2024-09-03 20:52:56 发布

guofeng6869

最新推荐文章于 2024-09-03 20:52:56 发布

阅读量427

点赞数

分类专栏： java 文章标签： poi

java 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

java导出excel 轻松导出百万条记录
置顶 2017年09月30日 03:51:24 wllllllllllllllll 阅读数：2075
网上查了很多博客，发现很多导出excel 都是存一个集合然后利用poi 的api 导出，这样在数据量比较小的时候没什么问题，但是在数据量稍微大一点就会造成堆栈溢出了。所以写了一个每次导出一条记录的工具类，分享给大家！完美导出超大数据集合。不多说，直接贴代码

首先是一个注解类
1
package com.cmi.jego.activity.annotation;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**

Created by wl
*/
@Target({ElementType.FIELD,ElementType.METHOD,ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface ExportAnnotation {

int order() default 100;//排序（属性对应excel的列的列数）
String method() default “”;//属性对应的get方法
//导出title
String columnTitle() default “”;//属性对应的excel 的title
//title数组长度
int length() default 20;//列的个数

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
导出的实体对象 javaBean(例子)

package com.cmi.jego.activity.vo;

import com.cmi.jego.activity.annotation.ExportAnnotation;

/**

Created by wl
*/
@ExportAnnotation(length = 9)
public class RedeemCodeExportVO {

@ExportAnnotation(order = 1,method = “getCouponId”,columnTitle = “优惠券id”)
private String couponId;
@ExportAnnotation(order = 2,method = “getCouponName”,columnTitle = “优惠券名称”)
private String couponName;

@ExportAnnotation(order = 3,method = “getExchangeCode”,columnTitle = “兑换码”)
private String exchangeCode;

@ExportAnnotation(order = 4,method = “getCommodityName”,columnTitle = “商品名称”)
private String commodityName;

@ExportAnnotation(order = 5,method = “getCommodityIds”,columnTitle = “商品id”)
private String commodityIds;

@ExportAnnotation(order = 6,method = “getCreateDate”,columnTitle = “生成日期”)
private String createDate;

@ExportAnnotation(order = 7,method = “getNoEffectDate”,columnTitle = “失效日期”)
private String noEffectDate;

@ExportAnnotation(order = 8,method = “getStatus”,columnTitle = “兑换状态”)
private String status;

@ExportAnnotation(order = 9,method = “getExchangeMobile”,columnTitle = “兑换手机号”)
private String exchangeMobile;

public String getCouponId() {
return couponId;
}

public void setCouponId(String couponId) {
this.couponId = couponId;
}

public String getCouponName() {
return couponName;
}

public void setCouponName(String couponName) {
this.couponName = couponName;
}

public String getExchangeCode() {
return exchangeCode;
}

public void setExchangeCode(String exchangeCode) {
this.exchangeCode = exchangeCode;
}

public String getCommodityName() {
return commodityName;
}

public void setCommodityName(String commodityName) {
this.commodityName = commodityName;
}

public String getCommodityIds() {
return commodityIds;
}

public void setCommodityIds(String commodityIds) {
this.commodityIds = commodityIds;
}

public String getCreateDate() {
return createDate;
}

public void setCreateDate(String createDate) {
this.createDate = createDate;
}

public String getNoEffectDate() {
return noEffectDate;
}

public void setNoEffectDate(String noEffectDate) {
this.noEffectDate = noEffectDate;
}

public String getStatus() {
return status;
}

public void setStatus(String status) {
this.status = status;
}

public String getExchangeMobile() {
return exchangeMobile;
}

public void setExchangeMobile(String exchangeMobile) {
this.exchangeMobile = exchangeMobile;
}
}
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
order 是从1开始的因为项目的需求第一列都是序号

真正的工具类

package com.cmi.jego.activity.utils;

import com.cmi.jego.activity.annotation.ExportAnnotation;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;

import java.text.SimpleDateFormat;
import java.util.*;

/**

Created by wl
*/
public class ExportExcelUtil{

private static final Logger logger = LoggerFactory.getLogger(ExportExcel.class);
//200rows flush
private final SXSSFWorkbook wbk = new SXSSFWorkbook(200);
//工作簿当前sheet
private SXSSFSheet sheet;
//当前sheet的当前row
private SXSSFRow row; //成员变量接收不用每次都在栈中声明
//当前cell
private SXSSFCell cell;
//标题样式初始化分配一个堆内存空间
private final XSSFCellStyle titleStyle = (XSSFCellStyle) this.wbk.createCellStyle();
//cell样式
private final XSSFCellStyle cellStyle = (XSSFCellStyle) this.wbk.createCellStyle();
//row 样式
// private final XSSFCellStyle rowStyle = (XSSFCellStyle) this.wbk.createCellStyle();
//cell font
// private final Font font = this.wbk.createFont();
使用太耗内存
// private XSSFRichTextString xssfRichTextString;

//接收cellValue 当前cellValue
private Object cellValue;

//标题[0][] 方法名[1][]
private final String[][] titlesAndMethods = new String[2][];

//导出对象的 Class 对象使用泛型获取导出对象的类型只能获取父类的泛型的类型这里通过构造器初始化
//Class.forName(((ParameterizedType)this.getClass().getGenericSuperclass()).getActualTypeArguments()[0].getTypeName());
private final Class<?> exportClass;

public ExportExcelUtil(Class<?> clazz){
this.exportClass = clazz;
if(clazz == null)
throw new NullPointerException(“clazz 不能为空”);
this.initTitleAndMethod(this.exportClass);

}

//初始化样式
{
this.initStyle(this.titleStyle,false);
this.initStyle(this.cellStyle,true);
//this.initStyle(rowStyle,true);
}

private int count = 1;

public void exportRowSet(Object rowData) {
if(this.sheet ==null){
throw new NullPointerException(“sheet 为空，先initSheet”);
}
```
 if(this.count % 10000 == 0){
     logger.info("=========="+Runtime.getRuntime().totalMemory()/1024/1024+"M");
     //System.runFinalization();
 }
 this.row = this.sheet.createRow(count);
 //第一个cell 是序号
 {
     this.cell = this.row.createCell(0);
     this.cell.setCellStyle(cellStyle);
     this.cell.setCellValue(count);
 }

 for(int i=1;i<titlesAndMethods[0].length && titlesAndMethods[1][i] != null;i++) {
     try {
         this.cellValue = this.exportClass.getMethod(this.titlesAndMethods[1][i]).invoke(rowData);
         this.cell = this.row.createCell(i);
         this.createCellValue(i);

     } catch (NoSuchMethodException | IllegalAccessException | InvocationTargetException e) {
         //logger.error("导出统计数据 反射获取值 失败,column:{}",i,e);
     }

 }
 this.count++;
```
}

public void writeWbk(OutputStream os) {
try {
this.wbk.write(os);
} catch (IOException e) {
//
}finally {
try {
this.wbk.close();
} catch (IOException e) {
//
}
}
}

public void initSheet(String sheetName){
this.sheet = this.wbk.createSheet(sheetName);
this.sheet.setDefaultColumnWidth((short) 16);
this.row = this.sheet.createRow(0);
this.createRowTitle();
this.count = 1;
}

private void initTitleAndMethod(Class<?> clazz) {
ExportAnnotation exportAnnotation = clazz.getAnnotation(ExportAnnotation.class);
Field[] fields = clazz.getDeclaredFields();
//初始化标题[0][0]
{
//第一个title 序号
int length = exportAnnotation.length();
this.titlesAndMethods[0] = new String[length+1];
this.titlesAndMethods[1] = new String[length+1];
this.titlesAndMethods[0][0] = “序号”;
}
int index;
for(Field field :fields){
exportAnnotation = field.getAnnotation(ExportAnnotation.class);
if(exportAnnotation != null) {
index = exportAnnotation.order();
//标题
this.titlesAndMethods[0][index] = exportAnnotation.columnTitle();
this.titlesAndMethods[1][index] = exportAnnotation.method();
}
}
}

/**
- 创建标题行
  */
  private void createRowTitle() {
  String[] titles = titlesAndMethods[0];
  for(int i=0;i<titles.length;i++){
  this.cell = this.row.createCell(i);
  this.cell.setCellStyle(this.titleStyle);
  //每次都要实例化一个如果为final cellValue 全部相同太耗内存
  // this.xssfRichTextString = new XSSFRichTextString();
  // this.xssfRichTextString.setString(titles[i]);
  this.cell.setCellValue(titles[i]);
  }
  }
private void initStyle(XSSFCellStyle style,boolean isBold) {
// 设置这些样式SOLID_FOREGROUND
style.setFillForegroundColor(HSSFColor.WHITE.index);
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
style.setAlignment(HorizontalAlignment.CENTER);
// 生成一个字体
XSSFFont font = (XSSFFont) this.wbk.createFont();
font.setColor(HSSFColor.BLACK.index);
font.setFontHeightInPoints((short) 12);
font.setBold(isBold);
style.setFont(font);

}

/**
- 创建 cell
  */
  private void createCellValue(int columnIndex){
  
  this.cell.setCellStyle(this.cellStyle);
  // 判断值的类型后进行强制类型转换
  String textValue = null;
  if (this.cellValue instanceof Date) {
  Date date = (Date) this.cellValue;
  SimpleDateFormat sdf = new SimpleDateFormat(“yyyy-MM-dd HH:mm:ss”);
  textValue = sdf.format(date);
  }
  else if (this.cellValue instanceof byte[]) {
  Drawing patriarch = this.sheet.createDrawingPatriarch();
  // 有图片时，设置行高为60px;
  this.row.setHeightInPoints(60);
  // 设置图片所在列宽度为80px,注意这里单位的一个换算
  this.sheet.setColumnWidth(columnIndex, (short) (35.7 * 80));
  // sheet.autoSizeColumn(i);
  byte[] bsValue = (byte[]) this.cellValue;
  ClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) 6, columnIndex, (short) 6, columnIndex);
  anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_DONT_RESIZE);
  patriarch.createPicture(anchor, this.wbk.addPicture(bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));
  }
  else {
  // 其它数据类型都当作字符串简单处理
  if (this.cellValue != null) textValue = this.cellValue.toString();
  }
  // 如果不是图片数据，就判断textValue是否全部由数字组成
  if (textValue != null) {
  if (this.cellValue instanceof Integer) {
  //double 强制转换会有小数点
  this.cell.setCellValue((int) this.cellValue);
  }else if(this.cellValue instanceof Number){
  this.cell.setCellValue((double) this.cellValue);
  }
  else {
  // 数据量大每次实例化太耗内存
  // this.xssfRichTextString = new XSSFRichTextString();
  // this.xssfRichTextString.setString(textValue);
  // this.font.setColor(new XSSFColor().getIndexed());
  // this.xssfRichTextString.applyFont(font);
  this.cell.setCellValue(textValue);
  }
  }
  }

}
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
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
工具类中的titleAndMethods[][] 是一个二维数组titleAndMethods[0]用来存放title titleAndMethods[1]用来存放get方法两个长度是一样的。在ExportUtil初始化的时候通过反射填充
注意：这里我的excel 导出的第一列固定是“序号”如果你的第一列不是序号请在导出的javaBean　属性中添加一个 order=0的属性并增加title 和method 注解，并且将工具类做如下修改
注释掉下面的代码

this.titlesAndMethods[0][0] = “序号”;

//第一个cell 是序号
{
this.cell = this.row.createCell(0);
this.cell.setCellStyle(cellStyle);
this.cell.setCellValue(count);
}
1
2
3
4
5
6
7
8
循环从0开始

for(int i=0;i<titlesAndMethods[0].length && titlesAndMethods[1][i] != null;i++) {
try {
this.cellValue = this.exportClass.getMethod(this.titlesAndMethods[1][i]).invoke(rowData);
this.cell = this.row.createCell(i);
this.createCellValue(i);

        } catch (NoSuchMethodException | IllegalAccessException | InvocationTargetException e) {
            //logger.error("导出统计数据 反射获取值 失败,column:{}",i,e);
        }

    }

1
2
3
4
5
6
7
8
9
10
11
使用该工具类共分为四部

实例化ExportUtil 注意构造器中的Class 参数必须要有ExportAnnotation注解且注解正确 ExportUtil exportUtil = new ExportUtil(RedeemCodeExportVO.class)
初始化sheet exportUtil.initSheet(“sheetName”);
导出一个结果集 exportUtil.exportRowSet(redeemCodeExportVO) 导出对象可能需要封装
写 exportUtil.writeWbk(os);
下面是一个导出数据库数据的例子使用的是jdbc 查询每次导出一个结果集避免全部查询保存在导出的高内存消耗
导出流程大概是

创建文件
sql查询（封装jdbc 每次导出一个封装好的结果集）
上传文件到oss服务器
将oss下载key返回浏览器

创建一个模板代码如下（写在内部类中）

//导出模板 =============================================
//导出步骤
//1.实例化ExportUtil ExportUtil exportUtil = new ExportUtil(clazz);
//2.调用导出一个结果集方法（封装ResultSet）模板回调
//3.写出输出流 exportUtil.write(os) 模板回调
//4.上传到oss 模板回调
//5.返回oss key到前端
//6.前端通过 oss key 请求后端下载文件
private static class ExportTemplate {
//excel 写入的文件
private final File file;
//导出结果返回的对象
ExportStatisticResp exportStatisticResp;
//实例化 file
private ExportTemplate(String filePath, String fileName,ExportStatisticResp exportStatisticResp){
File pathFile = new File(filePath);
if (!mkdirs(pathFile)) {
logger.info(“创建临时目录失败，使用当前目录”);
file = new File(fileName);
} else {
file = new File(filePath, fileName);
}
this.exportStatisticResp = exportStatisticResp;
}
private ExportStatisticResp executeExport(ExportCallBackHandler exportCallBackHandler) {
OutputStream os = null;
try {
if (file == null)
throw new NullPointerException(“file 不能为空”);
//doSomething
os = new BufferedOutputStream(new FileOutputStream(file));
//回调
return exportCallBackHandler.processExport(os,file,exportStatisticResp);
//doSomething
} catch (ParseException | IOException | RuntimeException e) {
logger.error(e.getMessage(),e);

        } finally {
            if(file != null) {
                boolean isDeleted = file.delete();
                if (!isDeleted)
                    logger.info("删除临时文件失败");
            }
            if(os != null){
                try {
                    os.close();
                } catch (IOException e) {
                   logger.info("导出 输出流关闭异常",e);
                }
            }
        }
        //失败
        exportStatisticResp.setStatus(ExportStatisticResp.StatusEnum.Failed.status);
        exportStatisticResp.setMsg("导出失败");
        return exportStatisticResp;
    }

    private boolean mkdirs(File file) {
        return file.exists() || file.mkdirs();
    }

    //回调接口
    private interface ExportCallBackHandler {
        // os  输出流 到file             file   oss上传的文件     返回值 导出对象
        ExportStatisticResp processExport(OutputStream os,File file,ExportStatisticResp exportStatisticResp) throws ParseException, IOException;
    }
}

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
ExportStatisticResp 是我定义的一个返回对象大家可以自己定义

JDBC 使用的是spring 的JdbcTemplate

@Autowired
private JdbcTemplate jdbcTemplate;
1
2
封装查询sql代码如下

/**
* 通过 MyRowCallBackHandler 处理结果集防止查出所有数据内存溢出
*/
private void exportRedeemResultSet(RedeemCodeQueryVO queryVO, Map<String, ActivityInfoGiftVO> map, ExportExcelUtil exportExcelUtil) {
//拼装sql
String sql = ObjectUtil.getRedeemCodeQuerySql(queryVO);
logger.info(“sql:{}”, sql);
//每次查10000个结果集
jdbcTemplate.setFetchSize(10000);
jdbcTemplate.query(sql, new MyRowCallBackHandler(map, exportExcelUtil));
}

/**
 * 导出一个结果集
 */
private static class MyRowCallBackHandler implements RowCallbackHandler {
    private final Map<String, ActivityInfoGiftVO> map;
    private final ExportExcelUtil exportExcelUtil;
    private final RedeemCodeExportVO rowData = new RedeemCodeExportVO();

    private MyRowCallBackHandler(Map<String, ActivityInfoGiftVO> map, ExportExcelUtil exportExcelUtil) {
        this.map = map;
        this.exportExcelUtil = exportExcelUtil;
        exportExcelUtil.initSheet("yyh");
    }

    @Override
    public void processRow(ResultSet rs) throws SQLException {
        //封装 RedeemCodeExportVO
        ObjectUtil.fillRedeemCodeExportVO(map, rs, rowData);
        exportExcelUtil.exportRowSet(rowData);
    }
}

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
自定义一个 MyRowCallBackHandler 实现 JDBC 的RowCallbackHandler 接口在回掉方法public void processRow(ResultSet rs)中封装导出的对象并且调用exportExcelUtil.exportRowSet(rowData);

在自定义MyRowCallBackHandler 类中还有一些成员变量因为sql查询出来的字段可能不能完全满足导出的所有字段要求，还需要外部的一些数据进行封装，另外就是ExportExcelUtil对象由外层调用方法传递过来（注意要保证一个请求只实例化一个ExportExcelUtil对象）在构造器中添加引用
private final RedeemCodeExportVO rowData = new RedeemCodeExportVO(); 是一个final 的实例是因为导出的是rowData的数据不用每次都实例化，只需要在里面填充数据。

下面是接收请求的方法代码如下

@RequestMapping("/exportCode")
public ResMesg exportCode(RedeemCodeQueryVO queryVO, HttpServletRequest request) {
ExportStatisticResp exportStatisticResp = new ExportStatisticResp();

    if (queryVO.getActivityId() != null) {

        List<ActivityInfoGiftVO> giftVOs = iActivityMicroService.findActivityInfoGiftListByActivityInfoId(queryVO.getActivityId());
        //需要到processRow中封装的数据（sql查询出来的数据不满足导出要求  联合查询会降低效率）
        Map<String, ActivityInfoGiftVO> map = new HashMap<>();
        convertList2Map(giftVOs, map);
        //文件名
        String fileName = "fileName";
        //文件路径
        String filePath = request.getServletContext().getRealPath("/") + "/temp/";
        //上传的oss  key
        String exportOssKey = "activity/exportRedeem/" + fileName;
        //模板模式
        return new ExportTemplate(filePath, fileName, exportStatisticResp).executeExport(new ExportTemplate.ExportCallBackHandler() {
            @Override
            public ExportStatisticResp processExport(OutputStream os, File file, ExportStatisticResp exportStatisticResp) throws ParseException, IOException {

//回调接口
//实例化导出工具
ExportExcelUtil exportExcelUtil = new ExportExcelUtil(RedeemCodeExportVO.class);
//执行RowCallBackHandler 回掉接口 processRow
exportRedeemResultSet(queryVO, map, exportExcelUtil);
exportExcelUtil.writeWbk(os);
//上传文件到oss
ossFileService.putObject(“xlsx”, exportOssKey, file.length(), new BufferedInputStream(new FileInputStream(file)));

               //封装返回对象 exportStatisticResp.setStatus(ExportStatisticResp.StatusEnum.Success.status);
                exportStatisticResp.setMsg("导出成功");
                exportStatisticResp.setOsskey(exportOssKey);
                exportStatisticResp.setFileName(fileName);
                logger.info("导出地址=======>>>" + accessService.oss + exportOssKey);
                return exportStatisticResp;

            }
        });
    }

    exportStatisticResp.setStatus(ExportStatisticResp.StatusEnum.SuccessWithoutData.status);
    exportStatisticResp.setMsg("没有查询到相应数据");
    return exportStatisticResp;
}

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
前端接收到oss的key在请求后端下载文件

每一个导出请求都可以按照上面的模式进行处理

下面贴一下直接导出一个excel文件的测试代码(每一万行打印使用的内存测试10万 50万 100万 200万)
代码如下（数据量自己设置）

public static void main(String[] args) throws Exception{
System.out.println("=>>>>>" + (Runtime.getRuntime().totalMemory() / 1024 / 1024) + “M”);
long start = System.currentTimeMillis();
ExportExcelUtil exportExcelUtil = new ExportExcelUtil(RedeemCodeExportVO.class);
exportExcelUtil.initSheet(“abcd”);
RedeemCodeExportVO redeemCodeExportVO;
OutputStream os = new BufferedOutputStream(new FileOutputStream(new File(“1234.xlsx”)));
for(int i=0;i<100000;i++){
redeemCodeExportVO = new RedeemCodeExportVO();
redeemCodeExportVO.setExchangeMobile(i+"");
redeemCodeExportVO.setStatus(“a” + i);
redeemCodeExportVO.setNoEffectDate(“b”+i);
redeemCodeExportVO.setCreateDate(“c” + i);
redeemCodeExportVO.setCommodityName(“d”+i);
redeemCodeExportVO.setCommodityIds(“e” +i);
redeemCodeExportVO.setExchangeCode(“f”+i);
redeemCodeExportVO.setCouponName(“h”+i);
redeemCodeExportVO.setCouponId(“g”+i);
exportExcelUtil.exportRowSet(redeemCodeExportVO);
}
exportExcelUtil.writeWbk(os);
long end = System.currentTimeMillis();
System.out.println("========>>" +(end - start) +“ms”);

}
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
十万

=========================>>>>>121M
03:16:29.555 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========121M
03:16:29.714 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========153M
03:16:29.857 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========154M
03:16:29.963 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========218M
03:16:30.075 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========218M
03:16:30.175 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========219M
03:16:30.345 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========296M
03:16:30.485 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========296M
03:16:30.641 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========296M
03:16:30.779 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========296M
================================>>7363ms
1
2
3
4
5
6
7
8
9
10
11
12
50万

=========================>>>>>121M
03:20:39.931 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========153M
03:20:40.185 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========153M
03:20:40.311 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========218M
03:20:40.420 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========218M
03:20:40.528 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========218M
03:20:40.622 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========218M
03:20:40.750 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========346M
03:20:40.892 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========346M
03:20:41.004 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========346M
03:20:41.113 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========347M
03:20:41.222 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========347M
03:20:41.315 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========347M
03:20:41.404 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========347M
03:20:41.509 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========501M
03:20:41.633 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========501M
03:20:41.721 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========501M
03:20:41.810 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========501M
03:20:41.913 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========501M
03:20:42.027 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========501M
03:20:42.129 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========501M
03:20:42.230 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========501M
03:20:42.352 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========501M
03:20:42.456 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========501M
03:20:42.551 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========501M
03:20:42.666 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========501M
03:20:42.761 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========475M
03:20:42.849 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========475M
03:20:42.946 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========475M
03:20:43.030 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========475M
03:20:43.117 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========475M
03:20:43.215 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========475M
03:20:43.310 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========464M
03:20:43.395 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========464M
03:20:43.488 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========464M
03:20:43.584 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========464M
03:20:43.677 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========464M
03:20:43.776 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========464M
03:20:43.864 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========440M
03:20:43.949 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========440M
03:20:44.049 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========440M
03:20:44.142 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========440M
03:20:44.241 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========440M
03:20:44.332 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========440M
03:20:44.421 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========431M
03:20:44.508 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========431M
03:20:44.597 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========431M
03:20:44.684 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========431M
03:20:44.768 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========431M
03:20:44.863 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========408M
03:20:44.948 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========408M
================================>>16648ms
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
100万没10万条数据打印内存使用情况

=========================>>>>>121M
03:22:29.305 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========347M
03:22:30.392 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========501M
03:22:31.448 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========475M
03:22:32.444 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========440M
03:22:33.335 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========408M
03:22:34.293 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========380M
03:22:35.309 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========354M
03:22:36.189 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========326M
03:22:37.179 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========292M
03:22:38.110 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========270M
================================>>30557ms
1
2
3
4
5
6
7
8
9
10
11
12
200万

=========================>>>>>121M
03:23:56.683 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========346M
03:23:57.733 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========501M
03:23:58.607 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========475M
03:23:59.471 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========440M
03:24:00.351 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========431M
03:24:01.337 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========401M
03:24:02.273 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========373M
03:24:03.154 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========331M
03:24:04.015 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========311M
03:24:04.867 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========287M
Exception in thread “main” java.lang.IllegalArgumentException: Invalid row number (1048576) outside allowable range (0…1048575)
at org.apache.poi.xssf.streaming.SXSSFSheet.createRow(SXSSFSheet.java:120)
at com.cmi.jego.activity.utils.ExportExcelUtil.exportRowSet(ExportExcelUtil.java:86)
at ApplicationTests.main(ApplicationTests.java:61)

Process finished with exit code 1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
报错了超过行数了可以优化一下行数是1048575的倍数就新建一张sheet 代码如下

public void exportRowSet(Object rowData) {
if(this.sheet null){
throw new NullPointerException(“sheet 为空，先initSheet”);
}
//如果行数超过 1048575 新建一张sheet
if(this.count % 1048576 == 0){
String newSheetName = this.sheet.getSheetName()+ (this.count / 1048576) ;
this.initSheet(newSheetName);
}
if(this.count % 100000 == 0){
logger.info("========"+Runtime.getRuntime().totalMemory()/1024/1024+“M”);
//System.runFinalization();
}
this.row = this.sheet.createRow(count);
//第一个cell 是序号
{
this.cell = this.row.createCell(0);
this.cell.setCellStyle(cellStyle);
this.cell.setCellValue(count);
}

    for(int i=1;i<titlesAndMethods[0].length && titlesAndMethods[1][i] != null;i++) {
        try {
            this.cellValue = this.exportClass.getMethod(this.titlesAndMethods[1][i]).invoke(rowData);
            this.cell = this.row.createCell(i);
            this.createCellValue(i);

        } catch (NoSuchMethodException | IllegalAccessException | InvocationTargetException e) {
            //logger.error("导出统计数据 反射获取值 失败,column:{}",i,e);
        }

    }
    this.count++;
}

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
再次执行

=========================>>>>>121M
03:34:19.796 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========347M
03:34:20.765 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========501M
03:34:21.807 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========746M
03:34:22.822 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========707M
03:34:23.798 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========678M
03:34:24.807 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========657M
03:34:25.712 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========623M
03:34:26.642 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========574M
03:34:27.655 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========558M
03:34:28.552 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========530M
03:34:29.998 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========472M
03:34:30.899 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========454M
03:34:31.728 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========421M
03:34:32.641 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========392M
03:34:33.514 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========365M
03:34:34.330 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========333M
03:34:35.161 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========300M
03:34:36.144 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========276M
03:34:37.009 [main] INFO com.cmi.jego.activity.utils.ExportExcel - ==========246M
================================>>55813ms

Process finished with exit code 0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
成功
附上：200万条数据的excel下载链接（总共89M）我的百度网盘链接

ps:一直想写博客但是好久都没有写了，一写就停不下来转眼就凌晨4点钟了。可能我写的注解不够清楚，但是代码认真看应该是看得懂，希望这篇博客能够对大家由所帮助。另外我对poi 的api 并不了解，如何设置excel的各种样式等问题请查阅相关文档和api

写这篇博客的原因主要是因为在网上找的一些例子全部是导出一个集合，如果集合庞大本身保存一个大的集合就容易内存溢出，在加上导出的消耗可想而知能够导出的数据量是非常有限的。因此想提供一个每次导出一条记录的思路和例子！

添加点东西
实际上可以搞个整数超过这个整数就新建一个sheet

譬如：声明一个成员变量 private static final int maxRowNumber = 100000 + 1;
public void exportRowSet(Object rowData) {
if(this.sheet null){
throw new NullPointerException(“sheet 为空，先initSheet”);
}
//如果行数超过 maxRowNumber 新建一张sheet
if(this.count % maxRowNumber == 0){
String newSheetName = this.sheet.getSheetName()+ (this.count / maxRowNumber) ;
this.initSheet(newSheetName);
}
if(this.count % 100000 == 0){
logger.info("========"+Runtime.getRuntime().totalMemory()/1024/1024+“M”);
//System.runFinalization();
}
this.row = this.sheet.createRow(count);
//第一个cell 是序号
{
this.cell = this.row.createCell(0);
this.cell.setCellStyle(cellStyle);
this.cell.setCellValue(count);
}

    for(int i=1;i<titlesAndMethods[0].length && titlesAndMethods[1][i] != null;i++) {
        try {
            this.cellValue = this.exportClass.getMethod(this.titlesAndMethods[1][i]).invoke(rowData);
            this.cell = this.row.createCell(i);
            this.createCellValue(i);
        } catch (NoSuchMethodException | IllegalAccessException | InvocationTargetException e) {
            //logger.error("导出统计数据 反射获取值 失败,column:{}",i,e);
        }

    }
    this.count++;
}