JAVA使用POI导出海量数据附源码（支持图片）
2016-12-23 本文已影响990人
jackcooper
实现思路
简单来说就是用分页实现，每次从数据库查询部分数据，然后交给poi的SXSSFWorkbook，poi发现数超过自身设置的暂存区数量时会flush到磁盘文件。所以我们需要使用poi的这个特性来实现海量数据下载。
以下3个步骤：
-
从数据库中待导出数据的总行数
-
总行数除以页数得到查询次数
-
循环次数将数据依次查出到SXSSFWorkbook
-
从SXSSFWorkbook中导出Excel文件
附代码：
1、ExcelUtile工具类
package com.br.monitor.utils;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/**
*
* @author: 作者：jack-cooper
* @explain: 释义：excel导出泛型工具类
* @version: 日期：2016-05-31 09:59:26
* 本工具类支持Excel导出，需要设定表头和表头对应的字段
* 通过设定每个sheet的数据行数，可以多sheet导出
* 支持设定时间格式
* 支持图片导出
* @param <T>
*/
public class ExcelUtils<T> {
private static final Logger logger = LoggerFactory.getLogger(ExcelUtils.class);
private static CellStyle titleStyle; // 标题行样式
private static Font titleFont; // 标题行字体
private static CellStyle dateStyle; // 日期行样式
private static Font dateFont; // 日期行字体
private static CellStyle headStyle; // 表头行样式
private static Font headFont; // 表头行字体
private static CellStyle contentStyle; // 内容行样式
private static Font contentFont; // 内容行字体
private static DataFormat format; //内容格式化
private static String pattern = "yyyy-MM-dd HH:mm:ss"; //默认下载日期格式
/**
* 处理中文文件名乱码问题
* @param request
* @param fileNames
* @return
*/
public static String processFileName(HttpServletRequest request, String fileNames) {
String codedfilename = null;
try {
String agent = request.getHeader("USER-AGENT");
if (null != agent && -1 != agent.indexOf("MSIE") || null != agent
&& -1 != agent.indexOf("Trident")) {// ie
String name = java.net.URLEncoder.encode(fileNames, "UTF-8");
codedfilename = name;
} else if (null != agent && -1 != agent.indexOf("Mozilla")) {// 火狐,chrome等
codedfilename = new String(fileNames.getBytes("UTF-8"), "iso-8859-1");
}
} catch (Exception e) {
e.printStackTrace();
}
return codedfilename;
}
/**
* @Description: 自动调整列宽
*/
private static void adjustColumnSize(Sheet sheet, List<String> headList) {
for (int i = 0; i < headList.size(); i++) {
sheet.autoSizeColumn((short)i);
sheet.setColumnWidth(i, headList.get(i).getBytes().length*2*100);
}
}
/**
* @Description: 创建统计行
*/
private static void createCountRow(Sheet sheet, Map<String, Object> map) {
Row countRow = sheet.createRow(0);
Cell countCell = countRow.createCell(0);
countCell.setCellValue("汇总信息：");
countCell.getCellStyle().cloneStyleFrom(titleStyle);
int num = 2;
if(map != null) {
for (String key : map.keySet()) {
countCell = countRow.createCell(num);
countCell.getCellStyle().cloneStyleFrom(titleStyle);
countCell.setCellValue(key);
num++;
countCell = countRow.createCell(num);
countCell.setCellValue(map.get(key).toString());
countCell.getCellStyle().cloneStyleFrom(titleStyle);
num = num + 2;
}
}
}
/**
* @Description: 初始化标题行样式
*/
private static void initTitleCellStyle() {
titleStyle.setAlignment(CellStyle.ALIGN_CENTER);
titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
titleStyle.setFont(titleFont);
titleStyle.setFillBackgroundColor(IndexedColors.BLACK.index);
titleStyle.setDataFormat(format.getFormat("@"));
}
/**
* @Description: 初始化数据行样式
*/
private static void initDateCellStyle() {
dateStyle.setAlignment(CellStyle.ALIGN_CENTER_SELECTION);
dateStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
dateStyle.setFont(dateFont);
// dateStyle.setFillBackgroundColor(IndexedColors.SKY_BLUE.index);
dateStyle.setDataFormat(format.getFormat("@"));
}
/**
* @Description: 初始化表头行样式
*/
private static void initHeadCellStyle() {
headStyle.setAlignment(CellStyle.ALIGN_CENTER);
headStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
headStyle.setFont(headFont);
//是设置单元格填充样式，SOLID_FOREGROUND纯色使用前景颜色填充，接着设置前景颜色
headStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
headStyle.setFillForegroundColor(HSSFColor.LIME.index);
headStyle.setBorderTop(CellStyle.BORDER_MEDIUM);
headStyle.setBorderBottom(CellStyle.BORDER_THIN);
headStyle.setBorderLeft(CellStyle.BORDER_THIN);
headStyle.setBorderRight(CellStyle.BORDER_THIN);
headStyle.setTopBorderColor(IndexedColors.BLACK.index);
headStyle.setBottomBorderColor(IndexedColors.BLACK.index);
headStyle.setLeftBorderColor(IndexedColors.BLACK.index);
headStyle.setRightBorderColor(IndexedColors.BLACK.index);
headStyle.setDataFormat(format.getFormat("@"));
}
//============================================================辅助方法==================================================
/**
* @Description: 初始化内容行样式
*/
private static void initContentCellStyle() {
contentStyle.setAlignment(CellStyle.ALIGN_CENTER);
contentStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
contentStyle.setFont(contentFont);
// contentStyle.setBorderTop(CellStyle.BORDER_THIN);
// contentStyle.setBorderBottom(CellStyle.BORDER_THIN);
// contentStyle.setBorderLeft(CellStyle.BORDER_THIN);
// contentStyle.setBorderRight(CellStyle.BORDER_THIN);
// contentStyle.setTopBorderColor(IndexedColors.BLACK.index);
// contentStyle.setBottomBorderColor(IndexedColors.BLACK.index);
// contentStyle.setLeftBorderColor(IndexedColors.BLACK.index);
// contentStyle.setRightBorderColor(IndexedColors.BLACK.index);
contentStyle.setWrapText(false); // 字段换行
}
/**
* @Description: 初始化标题行字体
*/
private static void initTitleFont() {
titleFont.setFontName("华文楷体");
titleFont.setFontHeightInPoints((short) 10);
titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
titleFont.setCharSet(Font.DEFAULT_CHARSET);
titleFont.setColor(IndexedColors.BLACK.index);
}
/**
* @Description: 初始化日期行字体
*/
private static void initDateFont() {
dateFont.setFontName("隶书");
dateFont.setFontHeightInPoints((short) 10);
dateFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
dateFont.setCharSet(Font.DEFAULT_CHARSET);
dateFont.setColor(IndexedColors.BLACK.index);
}
/**
* @Description: 初始化表头行字体
*/
private static void initHeadFont() {
headFont.setFontName("宋体");
headFont.setFontHeightInPoints((short) 10);
headFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
headFont.setCharSet(Font.DEFAULT_CHARSET);
headFont.setColor(IndexedColors.BLACK.index);
}
/**
* @Description: 初始化内容行字体
*/
private static void initContentFont() {
// contentFont.setFontName("宋体");
// contentFont.setFontHeightInPoints((short) 10);
// contentFont.setBoldweight(Font.BOLDWEIGHT_NORMAL);
// contentFont.setCharSet(Font.DEFAULT_CHARSET);
// contentFont.setColor(IndexedColors.BLACK.index);
}
/**
* @Description: 初始化
*/
public SXSSFWorkbook init() {
SXSSFWorkbook wb = new SXSSFWorkbook(1000);
wb.setCompressTempFiles(true);
titleFont = wb.createFont();
titleStyle = wb.createCellStyle();
dateStyle = wb.createCellStyle();
dateFont = wb.createFont();
headStyle = wb.createCellStyle();
headFont = wb.createFont();
contentStyle = wb.createCellStyle();
contentFont = wb.createFont();
format = wb.createDataFormat();
initTitleCellStyle();
initTitleFont();
initDateCellStyle();
initDateFont();
initHeadCellStyle();
initHeadFont();
initContentCellStyle();
initContentFont();
return wb;
}
/**
* 说明：支持海量数据下载
* @param workbook 工作薄
* @param heads sheet表头【数组】
* @param columns 表头对应的对象属性【数组】
* @param statisticsMap 统计信息
* @param excelName excel名字
* @param result 结果集
* @param sheetNum sheet编号
* @return 工作薄
* @throws Exception
*/
public SXSSFWorkbook installWorkbook(SXSSFWorkbook workbook, String[] heads, String[] columns, Map<String, Object> statisticsMap, String excelName, List<T> result, int sheetNum)
throws Exception {
List<String> headList = new ArrayList<String>();
List<String> columnsList = new ArrayList<String>();
Collections.addAll(headList,heads);
Collections.addAll(columnsList,columns);
return installWorkbook(workbook,headList,columnsList,statisticsMap,excelName,result,sheetNum);
}
public SXSSFWorkbook installWorkbook(SXSSFWorkbook workbook, List<String> headList,List<String> columnsList, Map<String, Object> statisticsMap, String excelName, List<T> result, int sheetNum)
throws Exception {
return installWorkbook(workbook,headList,columnsList,statisticsMap,excelName,result,sheetNum,pattern);
}
/**
* 说明：支持海量数据下载
* @param workbook 工作薄
* @param headList sheet表头
* @param columnsList 表头对应的对象属性
* @param statisticsMap 统计信息
* @param excelName excel名字
* @param result 结果集
* @param sheetNum sheet编号
* @param datePattern 日期类型格式 默认 : yyyy-MM-dd HH:mm:ss
* @return 工作薄
* @throws Exception
*/
public SXSSFWorkbook installWorkbook(SXSSFWorkbook workbook, List<String> headList,List<String> columnsList, Map<String, Object> statisticsMap, String excelName, List<T> result, int sheetNum,String datePattern)
throws Exception {
logger.info(excelName + "下载中，请稍后…………" + sheetNum);
Sheet sheet;
if ((sheetNum - 1) % 5 == 0) { //每5万一个sheet
sheet = workbook.createSheet(excelName + "-" + (sheetNum - 1) / 5);
adjustColumnSize(sheet, headList);//自动列宽
//1、统计信息
// createCountRow(sheet, statisticsMap);
//2、设置表头
Row row = sheet.createRow(0);
for (int a = 0; a < headList.size(); a++) {
Cell cell = row.createCell(a);
cell.setCellValue(headList.get(a));
cell.setCellStyle(headStyle);
}
} else {
sheet = workbook.getSheet(excelName + "-" + (sheetNum - 1) / 5);
}
//2、添加数据
int rowStart = sheet.getLastRowNum();
for (int b = 0; b < (result.size()); b++) {
Row dateRow = sheet.createRow(rowStart + b + 1);
T t = result.get(b);
for (int c = 0; c < headList.size(); c++) {
Cell cell = dateRow.createCell(c);
String fieldName = columnsList.get(c);
String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
Class tCls = t.getClass();
Method getMethod = tCls.getMethod(getMethodName, new Class[]{});
Object value = getMethod.invoke(t, new Class[]{});
String textValue = null;
if (value == null) {
textValue = "";
} else if (value instanceof Date) {
Date date = (Date) value;
SimpleDateFormat sdf = new SimpleDateFormat(datePattern);
textValue = sdf.format(date);
} else {
// 其它数据类型都当作字符串简单处理
textValue = value.toString();
}
if (textValue != null) {
Pattern p = Pattern.compile("^[+-]?(0|([1-9]\\d*))(\\.\\d+)?$");
Matcher matcher = p.matcher(textValue);
if (matcher.matches()) {
if(fieldName.toLowerCase().contains("rate")) {
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
cell.setCellStyle(cellStyle);
}
// 是数字当作double处理
cell.setCellValue(Double.parseDouble(textValue));
} else {
HSSFRichTextString richString = new HSSFRichTextString(
textValue);
// HSSFFont font3 = workbook.createFont();
// font3.setColor(HSSFColor.BLUE.index);
// richString.applyFont(font3);
cell.setCellValue(richString);
}
}
}
}
return workbook;
}
/**
* 说明：支持海量数据下载
* @param workbook 工作薄
* @param headList sheet表头
* @param columnsList 表头对应的对象属性
* @param excelName excel名字
* @param array jsonArray结果集
* @param sheetNum sheet编号
* @param datePattern 日期类型格式 默认 : yyyy-MM-dd HH:mm:ss
* @return 工作薄
* @throws Exception
*/
public SXSSFWorkbook installWorkbookByJsonArray(SXSSFWorkbook workbook, List<String> headList, List<String> columnsList, String excelName, JSONArray array, int sheetNum, String datePattern)
throws Exception {
logger.info(excelName + "下载中，请稍后………………" + sheetNum);
Sheet sheet;
// if ((sheetNum - 1) % 5 == 0) { //每5万一个sheet
sheet = workbook.createSheet(excelName);
adjustColumnSize(sheet, headList);//自动列宽
//2、设置表头
Row row = sheet.createRow(0);
for (int a = 0; a < headList.size(); a++) {
Cell cell = row.createCell(a);
cell.setCellValue(headList.get(a));
cell.setCellStyle(headStyle);
}
// } else {
// sheet = workbook.getSheet(excelName + "-" + (sheetNum - 1) / 5);
// }
//2、添加数据
int rowStart = sheet.getLastRowNum();
for (int b = 0; b < (array.size()); b++) {
Row dateRow = sheet.createRow(rowStart + b + 1);
JSONObject t = array.getJSONObject(b);
for (int c = 0; c < headList.size(); c++) {
Cell cell = dateRow.createCell(c);
String fieldName = columnsList.get(c);
Object value = t.get(fieldName);
String textValue = null;
if (value == null) {
textValue = "";
} else if (value instanceof Date) {
Date date = (Date) value;
SimpleDateFormat sdf = new SimpleDateFormat(datePattern);
textValue = sdf.format(date);
} else {
// 其它数据类型都当作字符串简单处理
textValue = value.toString();
}
if (textValue != null) {
Pattern p = Pattern.compile("^[+-]?(0|([1-9]\\d*))(\\.\\d+)?$");
Matcher matcher = p.matcher(textValue);
if (matcher.matches()) {
if(fieldName.toLowerCase().contains("rate")) {
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
cell.setCellStyle(cellStyle);
}
// 是数字当作double处理
cell.setCellValue(Double.parseDouble(textValue));
} else {
HSSFRichTextString richString = new HSSFRichTextString(
textValue);
// HSSFFont font3 = workbook.createFont();
// font3.setColor(HSSFColor.BLUE.index);
// richString.applyFont(font3);
cell.setCellValue(richString);
}
}
}
}
return workbook;
}
/**
* 导出excel
* @param request
* @param response
* @param wb
* @param excelName 文件名
* @throws Exception
*/
public void export(HttpServletRequest request, HttpServletResponse response, SXSSFWorkbook wb, String excelName)throws Exception {
logger.info("ExcelUtil<T>导出excel开始========>文件名："+excelName);
ServletOutputStream out = null;
try {
response.setHeader("Cache-Control", "private");
response.setHeader("Pragma", "private");
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Type", "application/force-download");
String name = processFileName(request, excelName + ".xlsx");
response.setHeader("Content-disposition", "attachment;filename=" + name);
out = response.getOutputStream();
wb.write(out);
out.flush();
out.close();
} catch (final IOException e) {
throw e;
}
System.gc();
logger.info("导出excel结束");
}
/**
*
* @param out 输入流
* @param wb 输出的工作簿对象
* @throws Exception
*/
public void export(OutputStream out, SXSSFWorkbook wb)throws Exception {
wb.write(out);
out.flush();
out.close();
}
}
2、使用工具类实现海量数据下载（泛型）
//。。。。。。。
int count= studentService.queryCount(studentQuery);;
int size=0;
int pageSize = 10000; //每次查询数量
ExcelUtil<Student> excelUtil= new ExcelUtil<Student>();
SXSSFWorkbook workbook = excelUtil.init();
Map<String,Object> map=new HashMap<String, Object>()；
Page page=new Page();
size=count/pageSize +1;
for(int i=1;i<size+1;i++){
page.setCurrentPage(i);
page.setPageSize(pageSize );
ResultList result = studentService.queryStudent(studentQuery,page);
workbook=excelUtil.installWorkbook(workbook,headList,map,excelName,result.getList(),i);
}
ExcelExport.export(request, response, workbook, excelName);
//。。。。。。。。
3、使用工具类实现海量数据下载（jsonarry）
/**
* 行业申请增长率趋势 -- 表格
* @param dmrReqDvo
* @return
*/
public JSONObject qryFormByIndustryAppGrowthThred(final DmrReqDvo dmrReqDvo) throws InvocationTargetException, IllegalAccessException, NoSuchMethodException, InstantiationException {
JSONObject resultObj = new JSONObject();
DmrReqDvo dd = (DmrReqDvo)BeanUtils.cloneBean(dmrReqDvo);
List<Map<Object, Object>> maps = this.industryAppGrowthThred(dd);
//表头
ArrayList<String> header = new ArrayList<String>() {{
add("客群");
add("统计时间");
add("申请量");
if(StatisticalMethodConstant.FIXED_BASE.equals(dmrReqDvo.getStatisMethod())){
add("定基比增长率");
}else if(StatisticalMethodConstant.MOM.equals(dmrReqDvo.getStatisMethod())) {
add("环比增长率");
}
}};
//jsonField
List<String> keyList = new ArrayList<>();
keyList.add("comp_type");
keyList.add("statis_month");
if (StatisticalDimension.CNT.equals(dmrReqDvo.getStatisDim())) {
keyList.add("req_cnt");
keyList.add("req_cnt_rate");
}else if(StatisticalDimension.USER.equals(dmrReqDvo.getStatisDim())){
keyList.add("req_user");
keyList.add("req_user_rate");
}
//data
JSONArray array = new JSONArray();
for (Map map: maps) {
JSONObject item = new JSONObject();
for (Object key : map.keySet()) {
String keyStr = (String)key;
if(keyList.contains(keyStr)){
item.put(keyStr, map.get(key));
}
}
array.add(item);
}
//array转List 排序
List<JSONObject> list = JSONArray.parseArray(array.toJSONString(), JSONObject.class);
Collections.sort(list, new Comparator<JSONObject>() {
@Override
public int compare(JSONObject o1, JSONObject o2) {
String compType1 = o1.getString("comp_type");
String compType2 = o2.getString("comp_type");
if(compType1.compareTo(compType2)>0){
return 1;
}else if(compType1.compareTo(compType2)< 0){
return -1;
}else{
return 0;
}
}
});
JSONObject jsonObject = this.qryParameters();
JSONObject busType = jsonObject.getJSONObject("busType");
Map<String, String> busyTypeMap = new HashedMap();
busyTypeMap = JSON.parseObject(busType.toJSONString(), Map.class);
for (JSONObject item : list) {
item.put("comp_type", busyTypeMap.get(item.get("comp_type")));
}
array = JSONArray.parseArray(list.toString());
resultObj.put("headers", header);
resultObj.put("keys", keyList);
resultObj.put("arrays", array);
return resultObj;
}
/**
* 行业申请增长率趋势
* @param dmrReqDvo
* @param excelUtil
* @param workbook
* @return
* @throws Exception
*/
private SXSSFWorkbook createIndustryAppGrowthThredData(final DmrReqDvo dmrReqDvo, ExcelUtils<Object> excelUtil, SXSSFWorkbook workbook, int sheetNum) throws Exception {
JSONObject object = this.qryFormByIndustryAppGrowthThred(dmrReqDvo);
List headers = object.getObject("headers", List.class);
List keys = object.getObject("keys", List.class);
JSONArray array = object.getObject("arrays", JSONArray.class);
workbook=excelUtil.installWorkbookByJsonArray(workbook,headers,keys,"行业申请增长率趋势",array,sheetNum,"yyyy-MM-dd");
return workbook;
}
