使用POI导出EXCEL工具类并解决导出数据量大的问题
POI导出工具类
工作中常常会遇到一些图表需要导出的功能，在这里自己写了一个工具类方便以后使用（使用POI实现）。
项目依赖
<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();
}
}
}
通过这个工具类我们只需要传递对应参数就能够实现简单的EXCEL导出功能。
下面是一个导出的例子
/**
* 导出用户信息
*
* @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));
}
HttpServletResponse 对象可在控制层通过参数传递进来即可。
那么问题来了，对于不是通过客户端浏览器的导出操作，我们就无法用 HttpServletResponse，其实这也是我在做一个统计工具的时候遇到的问题，
这个统计用的是图形界面开发SWT实现。那么我们就无法用这种客户端响应的方式实现导出。那么这个工具类不是就没用了？当时查了网上的资料，其实
只需更改响应方式即可。后来我用输出流修改了这个工具类。改动如下（因为只是个统计小工具也就没用到缓冲流之类的，只是简单做了输出）
fileName可定义具体的输出位置如： 'D:/统计/'+文件名
//通过输出流响应
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;
}
}
}
}
导出数据量大的解决办法
使用poi导出excel的时候如果数据过多，超过65535条会报错，因为excel2003一个sheet表最多导出65535条，excel2007是10万4000多条限制。
Invalid row number (65536) outside allowable range (0..65535)
解决方案：一个sheet最多可以导出65535条，我们可以分成多个sheet导出。
当时我写了一个特别low的方法，但是也算实现的功能。下面分享一下我的办法
当时是这样想的，比如从数据库查询出一百万条数据，我把这数据分成多份，用同一个HSSFWorkbook对象，执行工具类中getHSSFWorkbook方法不就行了
于是就有了以下实现（主要是懒得改工具类-_-）
/**
* 将一个集合拆分成多个
*
* @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;
}
以上是拆分集合的方法，通过这个方法即可实现集合拆分，之后在业务代码这样写就行了
wb为HSSFWorkbook对象，我们在代码里把它在循环外声明就行 HSSFWorkbook wb = new HSSFWorkbook();
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);
}
这样就可以支持大数据量导出了。
