實作思路
簡單來說就是用分頁實作，每次從資料庫查詢部分資料，然後交給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;
}
![C經典書籍筆記——C陷阱與缺陷②(文法陷阱之優先級)一、錯誤案列 二、優先級規律[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)