需求:1、需要导出10万行的excel表格数据
解决方案:一开始用同事封装的excel工具类导出发现最多只能也5万多行数据就会报错。后想起上个公司用的阿里的工具类 easyexcel 下面是贴出来的代码。ps: 感谢阿里的程序员贡献那么多开源工具https://github.com/alibaba/easyexcel !
//导入依赖
<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.6</version>
</dependency>
代码实现
//SmsReturnInfoExcel 自己准备导出对象
public void messageReturnExcel(SmsReturnInfo smsReturnInfo, HttpServletRequest request, HttpServletResponse response){
try{
EsPageInfo esPageInfo = service 查询出来的结果;
List<Map> resultList =(List<Map>) esPageInfo.getObj();
List<导出对象> list = new ArrayList<导出对象>();
SmsReturnInfoExcel smsReturnInfoExcel = null;
for(Map map : resultList){
导出对象 = new 导出对象();
导出对象.setApiAccount(String.valueOf(map.get("apiAccounts")));;
导出对象.setCreateTime(String.valueOf(map.get("presentationTime")));
导出对象.setPhone(String.valueOf(map.get("targetAddress")));;
导出对象.setSendNum(String.valueOf(map.get("actualSendNumber")));
导出对象.setReplyContent(String.valueOf(map.get("smsUplinkContent")));
导出对象.setContentNum(String.valueOf(map.get("wordNumber")));
导出对象.setType(String.valueOf(map.get("type")));
导出对象.setGateway(String.valueOf(map.get("gateway")));
导出对象.setProvince(String.valueOf(map.get("localProvince")));
导出对象.setCity(String.valueOf(map.get("localCity")));
list.add(导出对象);
}
// 这里注意 有同学反应使用swagger 会导致各种问题,请直接用浏览器或者用postman
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode("导出", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
List<List<String>> listsHead = getMorningCheckHead(smsReturnInfo.getApiAccount());
EasyExcel.write(response.getOutputStream(), SmsReturnInfoExcel.class).head(listsHead).sheet("模板").doWrite(list);
} catch (Exception e) {
e.printStackTrace();
}
}
private List<List<String>> getMorningCheckHead(String bigTitle){
List<List<String>> head = new ArrayList<List<String>>();
List<String> head0 = new ArrayList<>();
head0.add(bigTitle);
head0.add("api");
List<String> head1 = new ArrayList<>();
head1.add(bigTitle);
head1.add("时间");
List<String> head2 = new ArrayList<>();
head2.add(bigTitle);
head2.add("手机");
List<String> head3 = new ArrayList<>();
head3.add(bigTitle);
head3.add("实际");
List<String> head4 = new ArrayList<>();
head4.add(bigTitle);
head4.add("内容");
List<String> head5 = new ArrayList<>();
head5.add(bigTitle);
head5.add("字数");
List<String> head6 = new ArrayList<>();
head6.add(bigTitle);
head6.add("类型");
List<String> head7 = new ArrayList<>();
head7.add(bigTitle);
head7.add("网");
List<String> head8 = new ArrayList<>();
head8.add(bigTitle);
head8.add("省");
List<String> head9 = new ArrayList<>();
head9.add(bigTitle);
head9.add("市");
head.add(head0);
head.add(head1);
head.add(head2);
head.add(head3);
head.add(head4);
head.add(head5);
head.add(head6);
head.add(head7);
head.add(head8);
head.add(head9);
return head;
}