百万级数据量导出excel
百万级数据量导出excel
sxxf
下面展示一些 内联代码片。
package com.highdatas.mdm.util;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.List;
import java.util.Map;
/**
* 大数据量下载
*/
public class SxxfWorkbookApplicationTests {
private final static Logger logger = LoggerFactory.getLogger(SxxfWorkbookApplicationTests.class);
/**
*
* @param title 中文表头以及一些特殊业务标识
* @param fields 英文表头
* @param data 实际数据
* @param fileName 文件名
* @param response
*/
public static void contextLoads(List<Map<String, Object>> title, List<String> fields, List<Map<String, Object>> data, String fileName, HttpServletResponse response) {
// 导出的excel,全文件名
final String excelExportDestfilepath = fileName;
OutputStream fos = null;
Workbook sxssfWorkbook = null;
try {
/// -> 从数据库中查询出要进行excel导出的数据
/// -> excel到处逻辑
long startTime = System.currentTimeMillis();
// 获取SXSSFWorkbook实例
sxssfWorkbook = new SXSSFWorkbook();
Sheet sheet = sxssfWorkbook.createSheet(fileName);
// 冻结最左边的两列、冻结最上面的一行
// 即：滚动横向滚动条时，左边的第一、二列固定不动;滚动纵向滚动条时，上面的第一行固定不动。
sheet.createFreezePane(2, 1);
// 设置并获取到需要的样式
XSSFCellStyle style = (XSSFCellStyle) sxssfWorkbook.createCellStyle();
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
int hasTitle = 0;
// 创建第一行,作为header表头
if (title != null){
Row header = sheet.createRow(0);
for (int i = 0; i < title.size(); i++) {
Cell cell = header.createCell(i);
cell.setCellValue((String) title.get(i).get("alias"));
if ("true".equals((String) title.get(i).get("mainTable"))){
cell.setCellStyle(style);
}
}
hasTitle += 1;
}
// 遍历创建行,导出数据
if (data != null){
int rowSize = data.size();
Field[] classFields = new Field[0];
for (int rowNum = 0; rowNum < rowSize; rowNum++) {
Row row = sheet.createRow(rowNum + hasTitle);
Object obj = data.get(rowNum);
if (obj instanceof Map){
int fieldsSize = fields.size();
Map<String, Object> map = (Map<String, Object>) obj;
if (map != null){
for (int colCell = 0; colCell < fieldsSize; colCell++) {
Cell cell = row.createCell(colCell);
cell.setCellValue((String)map.get(fields.get(colCell)));
}
}
}else {
//利用反射获取所有字段
if (classFields == null || classFields.length == 0){
classFields = obj.getClass().getDeclaredFields();
}
int colCell = 0;
for(String field : fields){
for(Field classField : classFields){
//设置字段可见性
classField.setAccessible(true);
if(field.equals(classField.getName())){
Cell cell = row.createCell(colCell);
cell.setCellValue((String)classField.get(obj));
colCell ++;
}
}
}
}
}
}
response.setHeader("Content-Disposition", "attachment;filename="+java.net.URLEncoder.encode(fileName, "UTF-8"));
response.setContentType("multipart/form-data");
fos = response.getOutputStream();
sxssfWorkbook.write(fos);
fos.flush();
} catch (Exception e) {
logger.error("发生异常咯！", e);
} finally {
try {
if(sxssfWorkbook != null) {
// dispose of temporary files backing this workbook on disk -> 处
// 理SXSSFWorkbook导出excel时，产生的临时文件
((SXSSFWorkbook) sxssfWorkbook).dispose();
}
if(fos != null) {
fos.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
* 设置sheet
*/
private static void setSheet(Sheet sheet) {
// 设置各列宽度(单位为:字符宽度的1/256)
sheet.setColumnWidth(0, 32 * 256);
sheet.setColumnWidth(1, 32 * 256);
sheet.setColumnWidth(2, 20 * 256);
sheet.setColumnWidth(3, 20 * 256);
sheet.setColumnWidth(4, 20 * 256);
sheet.setColumnWidth(5, 20 * 256);
sheet.setColumnWidth(6, 20 * 256);
sheet.setColumnWidth(7, 20 * 256);
sheet.setColumnWidth(8, 20 * 256);
sheet.setColumnWidth(9, 20 * 256);
sheet.setColumnWidth(10, 32 * 256);
}
/**
* 获取并设置header样式
*/
private static XSSFCellStyle getAndSetXSSFCellStyleHeader(SXSSFWorkbook sxssfWorkbook) {
XSSFCellStyle xssfCellStyle = (XSSFCellStyle) sxssfWorkbook.createCellStyle();
Font font = sxssfWorkbook.createFont();
// 字体大小
font.setFontHeightInPoints((short) 14);
// 字体粗细
font.setBoldweight((short) 20);
// 将字体应用到样式上面
xssfCellStyle.setFont(font);
// 是否自动换行
xssfCellStyle.setWrapText(false);
// 水平居中
xssfCellStyle.setAlignment(HorizontalAlignment.CENTER);
// 垂直居中
xssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
return xssfCellStyle;
}
/**
* 获取并设置样式一
*/
private static XSSFCellStyle getAndSetXSSFCellStyleOne(SXSSFWorkbook sxssfWorkbook) {
XSSFCellStyle xssfCellStyle = (XSSFCellStyle) sxssfWorkbook.createCellStyle();
XSSFDataFormat format = (XSSFDataFormat)sxssfWorkbook.createDataFormat();
// 是否自动换行
xssfCellStyle.setWrapText(false);
// 水平居中
xssfCellStyle.setAlignment(HorizontalAlignment.CENTER);
// 垂直居中
xssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 前景颜色
xssfCellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
xssfCellStyle.setFillForegroundColor(IndexedColors.AQUA.getIndex());
// 边框
xssfCellStyle.setBorderBottom(BorderStyle.THIN);
xssfCellStyle.setBorderRight(BorderStyle.THIN);
xssfCellStyle.setBorderTop(BorderStyle.THIN);
xssfCellStyle.setBorderLeft(BorderStyle.THIN);
xssfCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
xssfCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
xssfCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
xssfCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
// 防止数字过长,excel导出后,显示为科学计数法,如:防止8615192053888被显示为8.61519E+12
xssfCellStyle.setDataFormat(format.getFormat("0"));
return xssfCellStyle;
}
/**
* 获取并设置样式二
*/
private static XSSFCellStyle getAndSetXSSFCellStyleTwo(SXSSFWorkbook sxssfWorkbook) {
XSSFCellStyle xssfCellStyle = (XSSFCellStyle) sxssfWorkbook.createCellStyle();
XSSFDataFormat format = (XSSFDataFormat)sxssfWorkbook.createDataFormat();
// 是否自动换行
xssfCellStyle.setWrapText(false);
// 水平居中
xssfCellStyle.setAlignment(HorizontalAlignment.CENTER);
// 边框
xssfCellStyle.setBorderBottom(BorderStyle.THIN);
xssfCellStyle.setBorderRight(BorderStyle.THIN);
xssfCellStyle.setBorderTop(BorderStyle.THIN);
xssfCellStyle.setBorderLeft(BorderStyle.THIN);
xssfCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
xssfCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
xssfCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
xssfCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
// 垂直居中
xssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 防止数字过长,excel导出后,显示为科学计数法,如:防止8615192053888被显示为8.61519E+12
xssfCellStyle.setDataFormat(format.getFormat("0"));
return xssfCellStyle;
}
}
package com.highdatas.mdm.service.download.impl;
import com.alibaba.fastjson.JSONArray;
import com.highdatas.mdm.mapper.*;
import com.highdatas.mdm.pojo.ResponsePushData;
import com.highdatas.mdm.service.IFlowsService;
import com.highdatas.mdm.service.IMaintainFieldService;
import com.highdatas.mdm.service.IMaintainService;
import com.highdatas.mdm.service.download.MappingDownloadService;
import com.highdatas.mdm.util.DateTimeUtils;
import com.highdatas.mdm.util.ExcelWriteUtil;
import com.highdatas.mdm.util.SxxfWorkbookApplicationTests;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import javax.servlet.http.HttpServletResponse;
import java.util.*;
@Service
public class MappingDownloadServiceImpl implements MappingDownloadService {
@Autowired
IMaintainService maintainService;
@Autowired
SysFieldMapper fieldMapper;
@Autowired
IMaintainFieldService maintainFieldService;
@Autowired
MaintainFieldMapper maintainFieldMapper;
@Autowired
IFlowsService flowsService;
@Autowired
TableInfoMapper tableInfoMapper;
@Autowired
MenuMappingMapper menuMappingMapper;
@Autowired
SysMenuMapper sysMenuMapper;
@Autowired
connectionRelationMappingDOMapper mappingDOMapper;
/**
* mapping未关联下载
*
* @param params
* @return
*/
@Override
public ResponsePushData notAssociatedDownload(Map<String, Object> params, HttpServletResponse response) {
ResponsePushData responsePushData = new ResponsePushData();
List<Map<String, Object>> tableDataList = new ArrayList<>();
Map<String, Object> resultMap = new HashMap<>();
try {
//下载模板的表头
List<Map<String, Object>> fieldOrderList= (List<Map<String, Object>>) params.get("fieldOrderList");
//1，查询本表所有字段名称，id，标识，中文名称等
List<Map<String, Object>> viewHederList = fieldMapper.selectView(params.get("tableName").toString());
System.out.println(viewHederList);
//2，查询mapping关联表的所有字段名称，id，标识，中文名称等
String connectionRelation = menuMappingMapper.selectConnection(params.get("tableName").toString(), params.get("menuId").toString());
System.out.println(connectionRelation);
String topicName=null;
List<Map<String, Object>> newConnectionRelationList = new ArrayList<>();
if (StringUtils.isNotBlank(connectionRelation)) {
List<Map<String, Object>> connectionRelationList = (List<Map<String, Object>>) JSONArray.parse(connectionRelation);
for (Map<String, Object> connectionMap : connectionRelationList) {
Map<String, Object> topicMap = new HashMap<>();
Map<String, Object> tableMap = new HashMap<>();
Map<String, Object> fieldMap = new HashMap<>();
topicName = sysMenuMapper.selectNameById(connectionMap.get("id").toString());
topicMap.put("id", connectionMap.get("id"));
topicMap.put("topicName", topicName);
String name = sysMenuMapper.selectNameById(connectionMap.get("tableNameId").toString());
tableMap.put("tableNameId", connectionMap.get("tableNameId"));
tableMap.put("Name", name);
tableMap.put("tableName", menuMappingMapper.selectBymenuId(connectionMap.get("tableNameId").toString()).get("tableName"));
topicMap.put("children", tableMap);
String alias = fieldMapper.selectAlias(menuMappingMapper.selectBymenuId(connectionMap.get("tableNameId").toString()).get("tableName").toString(), connectionMap.get("field").toString());
String id = fieldMapper.selectId(tableMap.get("tableName").toString(), connectionMap.get("field").toString());
fieldMap.put("alias", alias);
fieldMap.put("id", id);
fieldMap.put("field", connectionMap.get("field"));
tableMap.put("children", fieldMap);
newConnectionRelationList.add(topicMap);
}
}
if (params.get("associateName") != null && params.get("associateName") != "") {
List<Map<String, Object>> assocList = fieldMapper.selectTableName(params.get("associateName").toString());
//关联字段表头
resultMap.put("assocList", assocList);
} else {
resultMap.put("assocList", null);
resultMap.put("associaList", null);
}
String resultSql = "";
String sql = "";
if (params.get("connAllFiled") != null && !params.get("connAllFiled").equals("")) {
List<String> list = (List<String>) JSONArray.parse(params.get("connAllFiled").toString());
if (!params.get("associateName").equals("") && params.get("associateName") != null) {
for (String ac : list) {
String ty = params.get("associateName").toString() + ".";
sql += ty + ac + " as s" + ac + " , ";
}
resultSql = "," + sql.substring(0, sql.length() - 2);
} else {
resultSql = params.get("tableName").toString() + ".*";
}
//3, 查询本表数据 根据 选中字段 和表名
List<Map> fieldList= (List<Map>) params.get("fieldList");
if (params.get("connState").equals("0")) {
tableDataList = menuMappingMapper.selectNotDatas(params.get("tableName").toString(),resultSql, fieldList);
} else {
tableDataList = menuMappingMapper.selectDatas(params.get("tableName").toString(),
resultSql, params.get("associateName").toString(), params.get("ctThisField").toString(),
fieldList, params.get("ctConnrctionField").toString());
}
}
System.out.println(tableDataList);
List<Map<String,Object>> head = new ArrayList<>();
List<String> fields = new ArrayList<>();
for (int i=1;i<=fieldOrderList.size();i++){
for (Map fieldOrder:fieldOrderList){
if ((int)fieldOrder.get("number")==i){
Map map=new HashMap();
map.put("alias",fieldOrder.get("alias"));
map.put("mainTable",fieldOrder.get("mainTable"));
head.add(map);
fields.add((String) fieldOrder.get("field"));
}
}
}
System.out.println(head);
SxxfWorkbookApplicationTests.contextLoads(head,fields,tableDataList,topicName+"未关联"+ DateTimeUtils.dayFormatSimple(new Date())+".xlsx",response);
} catch (Exception e) {
}
return responsePushData;
}
}