业务场景：将列表数据导出成excel
支持大数据
package com.wisedu.emap.fxyqglapp.service.impl;
import java.io.Closeable;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.io.IOUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.springframework.beans.factory.annotation.Autowired;
import self.micromagic.util.annotation.Config;
import com.google.gson.JsonObject;
import com.wisedu.emap.base.util.ListUtil;
import com.wisedu.emap.base.util.StringUtil;
import com.wisedu.emap.dao.DaoParam;
import com.wisedu.emap.dao.DaoService;
import com.wisedu.emap.model2.IEmapItem;
import com.wisedu.emap.model2.IEmapModel;
import com.wisedu.emap.model2.QueryResult;
import com.wisedu.emap.model2.container.BizModelContainer;
import com.wisedu.emap.pedestal.app.IEmapAppContext;
import com.wisedu.emap.stateapp.util.ProcessUtil;
import com.wisedu.emap.swpubapp.dao.SwDaoExcuter;
import com.wisedu.emap.swpubapp.service.PublicToolService;
import com.wisedu.emap.swpubapp.util.ComJsonUtil;
import com.wisedu.emap.swpubapp.util.CommonUtils;
import com.wisedu.emap.swpubapp.util.exception.BusinessException;
import com.wisedu.emap.swpubapp.util.exception.PubExceptionUtil;
public class ExportTest {
@Autowired
private SwDaoExcuter swDaoExcuter;
/** 上下文 */
@Autowired
private IEmapAppContext appContext;
@Autowired
private DaoService daoService;
@Autowired
private PublicToolService publicToolService;
@Autowired
private ProcessUtil process;
/** 文件存放一级目录 */
@Config(name = "attachment.rootStorePath", defaultValue = "", description = "一级路径")
private static String rootStorePath;
/** 文件临时存放地址 **/
private static String fileStorePath = "/emap/store/file";
/** 导出模板表头显示值 map **/
private Map<Integer, String> HEAD_DISPLAY = new HashMap<Integer, String>();
/** 导出模板表头代码值 map **/
private Map<Integer, String> HEAD_VALUE = new HashMap<Integer, String>();
public static void main(String[] args) {
}
public JsonObject exportFxxcInfoFunc(HttpServletRequest request,
HttpServletResponse response) throws Exception {
JsonObject resObj = new JsonObject();
String requestJsonStr = ComJsonUtil.readJSONString(request);
DaoParam daoParam = CommonUtils.buildParams(requestJsonStr, true);
try {
//1.初始化导出模板表头
initHeaderMap();
//1.生成导出报表
String fileName = exportRctbtjDetail(daoParam);
resObj.addProperty("FILENAME", fileName);
}
catch (BusinessException e) {
throw e;
}
catch (Exception e) {
PubExceptionUtil.throwBusinessException("导出明细数据异常", e);
}
return resObj;
}
/**
* 初始化导出模板表头 map
* @return
* @throws Exception
*/
protected void initHeaderMap() {
BizModelContainer bizmodel = appContext.getBizModel("cxfxshdcxcxx_display");
IEmapModel model = bizmodel.getModel();
Iterator<IEmapItem> it = model.iterator();
int index = 0;
while (it.hasNext()){
IEmapItem item = it.next();
if(("true".equals(StringUtil.getString(item.getAttribute("web.hidden")))
&& "true".equals(StringUtil.getString(item.getAttribute("web.grid.fixed")))) ||
"true".equals(StringUtil.getString(item.getAttribute("web.grid.hidden")))){
continue;
}else{
HEAD_DISPLAY.put(index, item.getCaption());
if(!StringUtil.isEmpty(StringUtil.getString(item.getAttribute("dic")))){
HEAD_VALUE.put(index, item.getName()+"_DISPLAY");
}else{
HEAD_VALUE.put(index, item.getName());
}
index ++;
}
}
}
/**
* 生成导出明细
* @param request
* @param response
* @return
* @throws Exception
*/
public JsonObject exportRctbtjDetailFunc(HttpServletRequest request, HttpServletResponse response) throws Exception {
JsonObject resObj = new JsonObject();
String requestJsonStr = ComJsonUtil.readJSONString(request);
DaoParam daoParam = CommonUtils.buildParams(requestJsonStr, true);
try {
//1.初始化导出模板表头
initHeaderMap();
//1.生成导出报表
String fileName = exportRctbtjDetail(daoParam);
resObj.addProperty("FILENAME", fileName);
}
catch (BusinessException e) {
throw e;
}
catch (Exception e) {
PubExceptionUtil.throwBusinessException("导出明细数据异常", e);
}
return resObj;
}
/**
* 生成导出文件
* @param wjdm
* @return
* @throws Exception
*/
public String exportRctbtjDetail(DaoParam daoParam) throws Exception {
String uuid = UUID.randomUUID().toString();
//2.生成导出明细excel
buildExportRctbtjDetail(uuid,daoParam);
return uuid;
}
/**
* 生成导出明细excel
* @param token
* @return
* @throws Exception
*/
private void buildExportRctbtjDetail(String fileName,DaoParam daoParam) throws Exception {
OutputStream out = null;
try {
//1.创建sheet
int rowaccess = 256;//内存中缓存记录行数
SXSSFWorkbook workbook = new SXSSFWorkbook(rowaccess);//创建一个excel
Sheet sheet = workbook.createSheet();//创建一个sheet
CellStyle headerStyle = getCellStyle(workbook, true);
CellStyle contentStyle = getCellStyle(workbook, false);
//2.创建模板头
createExcelHeader(sheet,headerStyle);
//3.设置模板内容
setExcelContext(sheet,contentStyle,daoParam);
//5.生成导出目录文件
String outFilePath = rootStorePath + fileStorePath + File.separator + fileName + ".xlsx";
out = new FileOutputStream(outFilePath);
//6.输出到导出文件
workbook.write(out);
out.flush();
}
catch (BusinessException e) {
throw e;
}
catch (FileNotFoundException e) {
PubExceptionUtil.throwBusinessException("无法创建生成文件目录", e);
}
catch (Exception e) {
PubExceptionUtil.throwBusinessException("生成导出明细异常", e);
}
finally {
IOUtils.closeQuietly(out);
// try {
// if (closeable != null) {
// closeable.close();
// }
// } catch (IOException ioe) {
// // ignore
// }
}
}
/**
*
* [简要描述]：获取单元格样式
* [详细描述]：
*
* @param workbook
* @param isHeader
* @return
*/
public CellStyle getCellStyle(SXSSFWorkbook workbook, boolean isHeader)
{
CellStyle headerStyle = workbook.createCellStyle();
DataFormat format = workbook.createDataFormat();
if (isHeader)
{
Font headerFont = workbook.createFont();
headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerStyle.setFont(headerFont);
headerStyle.setDataFormat(format.getFormat("@"));
return headerStyle;
}else{
CellStyle cellStyle = workbook.createCellStyle();
Font cellFont = workbook.createFont();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
cellStyle.setFont(cellFont);
cellStyle.setDataFormat(format.getFormat("@"));
}
return headerStyle;
}
/**
* 创建模板头
* @param sheet
* @param style
* @throws Exception
*/
private void createExcelHeader(Sheet sheet,CellStyle style) throws Exception {
Row firstRow = sheet.createRow(0);//第一行
firstRow.setHeightInPoints(20);
// Row secondRow = sheet.createRow(1);
// secondRow.setHeightInPoints(20);
//基本信息
for (int i = 0; i < HEAD_DISPLAY.size(); i++)
{
sheet.setColumnWidth(i, 12 * 256);
//第一行
Cell firstRowCell = firstRow.createCell(i);//创建这一行的每一格
firstRowCell.setCellStyle(style);
firstRowCell.setCellType(XSSFCell.CELL_TYPE_STRING);
firstRowCell.setCellValue(HEAD_DISPLAY.get(i));
}
}
/**
* 生成内容
* @param sheet
* @throws Exception
*/
public void setExcelContext(Sheet sheet,CellStyle style,DaoParam daoParam) throws Exception {
List<Map<String, Object>> resultList = getRctbtjExportData(daoParam);
if(ListUtil.isEmpty(resultList)){
return;
}
int rowindex = 1;//表头为0，数据从1开始
Row contentRow = null;
for(Map<String, Object> resultMap : resultList){
if (rowindex % 256 == 0) {//超过256条写入磁盘
((SXSSFSheet) sheet).flushRows();
}
contentRow = sheet.createRow(rowindex);//创建每一行
//设置内容
for(int i=0;i<HEAD_VALUE.size();i++){
Cell rowCell = contentRow.createCell(i);
rowCell.setCellStyle(style);
rowCell.setCellType(XSSFCell.CELL_TYPE_STRING);
rowCell.setCellValue(StringUtil.getString(resultMap.get(HEAD_VALUE.get(i))));
}
rowindex ++;
}
}
/**
* 查询每日填报表格数据
* @return
* @throws Exception
*/
public List<Map<String, Object>> getRctbtjExportData(DaoParam daoParam) throws Exception{
List<Map<String, Object>> resultList = new ArrayList<Map<String,Object>>();
try {
daoParam.setAllRow(true);
QueryResult<Map<String, Object>> queryResult1 = swDaoExcuter.customQuery(
appContext, "cxfxshdcxcxx", daoParam);
resultList = CommonUtils.queryResult2List(queryResult1);
}
catch (Exception e) {
PubExceptionUtil.throwBusinessException("查询行程信息数据异常",e);
}
return resultList;
}
/*************下载*************/
/**
* 下载导出明细
* @param request
* @param response
* @throws Exception
*/
public void downloadFxxcInfoFunc(HttpServletRequest request, HttpServletResponse response) throws Exception {
File file = null;
InputStream in = null;
OutputStream out = null;
try {
//1.获取参数
String fileName = request.getParameter("FILENAME");
//2.获取文件路径
file = new File(rootStorePath + fileStorePath + File.separator + fileName + ".xlsx");
if (!file.exists()) {
PubExceptionUtil.throwBusinessException("文件不存在");
}
in = new FileInputStream(file);
//3.设置返回
response.reset();
response.addHeader("content-disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
response.addHeader("Content-Length", "" + file.length());
response.setContentType("application/octet-stream");
//4.将文件流写到返回流中
out = response.getOutputStream();
IOUtils.copy(in, out);
out.flush();
}
catch (BusinessException e) {
throw e;
}
catch (Exception e) {
PubExceptionUtil.throwBusinessException("下载导出明细异常", e);
}
finally {
IOUtils.closeQuietly(in);
IOUtils.closeQuietly(out);
// 文件导出结束以后，直接删除
if (file != null && file.exists()) {
file.delete();
}
}
}
public JsonObject queryFileIfExists(DaoParam daoParam) throws Exception {
JsonObject result = new JsonObject();
File file = null;
try {
//1.获取参数
String fileName = StringUtil.getString(daoParam.getParams().get("FILENAME"));
//2.获取文件路径
file = new File(rootStorePath + fileStorePath + File.separator + fileName + ".xlsx");
if (!file.exists()) {
result.addProperty("flag", "1");
}else{
result.addProperty("flag", "0");
}
} catch (Exception e) {
PubExceptionUtil.throwBusinessException("校验文件是否存在异常", e);
}
return result;
}
/** 前段js
var callback = function (resultData){
if (resultData.code == "0"){
var questData = XGJSUTIL.querySyncAuto(WIS_CONFIG.ROOT_PATH + "/modules/fxsh/queryFileIfExists.do",
{FILENAME:resultData.data.FILENAME}) || {};
if(questData.flag == '0'){
window.location.href = WIS_CONFIG.ROOT_PATH +"/modules/fxsh/downloadFxxcInfoFunc.do?FILENAME="
+resultData.data.FILENAME;
$.bhTip({content: "导出成功！",state: 'success'});
}
setTimeout(function(){
$('[data-action=导出行程信息]').removeAttr("disabled");
exportFlag = true;
}, 3000);
}else{
COMMON.dialogWarning('提示', resultData);
return false;
}
};
XGJSUTIL.postAsyncCustom(WIS_CONFIG.ROOT_PATH +"/modules/fxsh/exportFxxcInfoFunc.do",param,callback);
*/
}