java poi 海量数据_JAVA使用POI导出海量数据附源码（支持图片）

实现思路

简单来说就是用分页实现，每次从数据库查询部分数据，然后交给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

*/

public class ExcelUtils {

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 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 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 statisticsMap, String excelName, List result, int sheetNum)

throws Exception {

List headList = new ArrayList();

List columnsList = new ArrayList();

Collections.addAll(headList,heads);

Collections.addAll(columnsList,columns);

return installWorkbook(workbook,headList,columnsList,statisticsMap,excelName,result,sheetNum);

}

public SXSSFWorkbook installWorkbook(SXSSFWorkbook workbook, List headList,List columnsList, Map statisticsMap, String excelName, List 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 headList,List columnsList, Map statisticsMap, String excelName, List 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 headList, List 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导出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 excelUtil= new ExcelUtil();

SXSSFWorkbook workbook = excelUtil.init();

Map map=new HashMap()；

Page page=new Page();

size=count/pageSize +1;

for(int i=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> maps = this.industryAppGrowthThred(dd);

//表头

ArrayList header = new ArrayList() {{

add("客群");

add("统计时间");

add("申请量");

if(StatisticalMethodConstant.FIXED_BASE.equals(dmrReqDvo.getStatisMethod())){

add("定基比增长率");

}else if(StatisticalMethodConstant.MOM.equals(dmrReqDvo.getStatisMethod())) {

add("环比增长率");

}

}};

//jsonField

List 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 list = JSONArray.parseArray(array.toJSONString(), JSONObject.class);

Collections.sort(list, new Comparator() {

@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 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 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;

}