使用POI导出EXCEL工具类并解决导出数据量大的问题

<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.6</version>
</dependency>

package com.adcc.eoss.util;import org.apache.poi.hssf.usermodel.*;import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;/**
* Created by LMQ on 2019/3/18.
*/
public class ExcelUtil { public static HSSFWorkbook getHSSFWorkbook(String sheetName, String[] title, String[][] values) {
return getHSSFWorkbook(sheetName, title, values, null);
} /**
* 导出Excel
*
* @param sheetName sheet名称
* @param title 标题
* @param values 内容
* @param wb HSSFWorkbook对象
* @return
*/
public static HSSFWorkbook getHSSFWorkbook(String sheetName, String[] title, String[][] values, HSSFWorkbook wb) { // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
if (wb == null) {
wb = new HSSFWorkbook();
} // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet(sheetName); // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
HSSFRow row = sheet.createRow(0); // 第四步，创建单元格，并设置值表头 设置表头居中
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 创建一个垂直居中格式 //声明列对象
HSSFCell cell = null; //创建标题
for (int i = 0; i < title.length; i++) {
cell = row.createCell(i);
cell.getCellStyle().setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cell.getCellStyle().setAlignment(HSSFCellStyle.ALIGN_CENTER);
cell.setCellValue(title[i]);
cell.setCellStyle(style);
} //创建内容
for (int i = 0; i < values.length; i++) {
row = sheet.createRow(i + 1);
for (int j = 0; j < values[i].length; j++) {
//将内容按顺序赋给对应的列对象
row.createCell(j).setCellValue(values[i][j]);
}
}
return wb;
} //响应到客户端
public static void returnClient(HttpServletResponse response, String fileName, HSSFWorkbook wb) {
try {
setResponseHeader(response, fileName);
OutputStream os = response.getOutputStream();
wb.write(os);
os.flush();
os.close();
} catch (Exception e) {
e.printStackTrace();
}
} //发送响应流方法
public static void setResponseHeader(HttpServletResponse response, String fileName) {
try {
try {
fileName = new String(fileName.getBytes(), "ISO8859-1");
} catch (UnsupportedEncodingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
response.setContentType("application/octet-stream;charset=ISO8859-1");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
} catch (Exception ex) {
ex.printStackTrace();
}
}
}

/**
* 导出用户信息
*
* @param map
* @param response
*/
public void exportToExcel(Map<String, Object> map, HttpServletResponse response) throws Exception {
// 查询得到用户数据
List<UserVO> vos = findByConditionReturnExcel(map);
// 定义表格头信息
String[] titles = new String[]{"用户名称", "状态", "创建时间", "创建人", "最后登录时间", "所在公司"};
// 为表格内容赋值
String[][] values = new String[vos.size()][titles.length];
if (vos != null && vos.size() > 0) {
for (int i = 0; i < vos.size(); i++) {
values[i][0] = vos.get(i).getUserName();
values[i][1] = vos.get(i).getUserState();
values[i][2] = vos.get(i).getCreateDate();
values[i][3] = vos.get(i).getCreateUser();
values[i][4] = vos.get(i).getLastLoginTime();
values[i][5] = vos.get(i).getSourceCompanyName();
}
}
// 导出
ExcelUtil.returnClient(response, "user.xls", ExcelUtil.getHSSFWorkbook("用户管理", titles, values));
}

//通过输出流响应
public static void returnClient(String fileName, HSSFWorkbook wb) throws Exception {
FileOutputStream os = null;
try {
File file = new File(fileName);
os = new FileOutputStream(file);
wb.write(os);
os.flush();
os.close();
} catch (Exception e) {
throw e;
} finally {
if (os != null) {
try {
// 关闭流
os.close();
} catch (IOException e) {
throw e;
}
}
}
}

/**
* 将一个集合拆分成多个
*
* @param list 需要拆分的集合
* @param num 每个集合的数量
* @return
*/
public Map<String, List<CDM>> splitList(List<CDM> list, Integer num) { // list 长度
int listSize = list.size();
// 用map来存放集合
HashMap<String, List<CDM>> listHashMap = new HashMap<String, List<CDM>>();
// 单个集合对象
List<CDM> childList = new ArrayList<>();
// 遍历要拆分集合按定义的num数量存放到childList
for (int i = 0; i < listSize; i++) {
childList.add(list.get(i));
if (((i + 1) % num == 0) || (i + 1 == listSize)) {
// 存入map
listHashMap.put(String.valueOf(i), childList);
childList = new ArrayList<>();
}
}
return listHashMap;
}

if (list.size() > 65535) {
// 每个小集合放60000万个，个数可以自己定义
Map<String, List<CDM>> childListMap = splitList(list, 60000);
// 循环map,循环为wb添加sheet wb也就是我们的HSSFWorkbook对象
for (Map.Entry<String, List<CDM>> entry : childListMap.entrySet()) {
String[][] values = new String[entry.getValue().size()][titles.length];
for (int i = 0; i < entry.getValue().size(); i++) {
values[i][0] = entry.getValue().get(i).getId();
values[i][1] = entry.getValue().get(i).getName();
//....
}
wb = ExcelUtil.getHSSFWorkbook("统计" + entry.getKey(), titles, values, wb);
}