概述
主方法
/**
* <一句话功能简述> 导出大量数据到Excel
* <功能详细描述>
* author: zhanggw
* @param lineHeadArray 列头信息
* @param exportPath 导出路径
* @param writeExcelDataDelegated 向excel写数据委托类，根据业务实现
*/
public static void exportExcelBigData(String[] lineHeadArray, String exportPath, WriteExcelDataDelegated writeExcelDataDelegated) throws Exception {
logger.info("开始导出数据到excel,导出路径:{}", exportPath);
// 初始化EXCEL
SXSSFWorkbook wb = initExcelSimple(lineHeadArray);
// 调用委托类分批写入数据
SXSSFSheet sheet = wb.getSheetAt(0);
// 每次从mysql读取后写入到excel的最大数据量
int pageSize = PER_WRITE_ROW_COUNT;
for (int currentWriteNum = 1; currentWriteNum <= PER_SHEET_WRITE_COUNT; currentWriteNum++) { // 最多分PER_SHEET_WRITE_COUNT批写入
if(!writeExcelDataDelegated.isHasMore()){ // 没有更多数据,停止获取写入
break;
}
// excel写数据的起始位置
int startRowCount = (currentWriteNum - 1) * pageSize + 1;
// excel写数据的结束位置
int endRowCount = startRowCount + pageSize - 1;
logger.debug("startRowCount:{},endRowCount:{},getWriteRowNum:{}", startRowCount, endRowCount, writeExcelDataDelegated.getWriteRowNum());
writeExcelDataDelegated.writeExcelData(wb, sheet, startRowCount, endRowCount, currentWriteNum, pageSize);
}
// 保存EXCEL到本地
downLoadExcelToLocalPath(wb, exportPath);
logger.info("导出完成,导出总记录数:{},导出路径:{}", writeExcelDataDelegated.getWriteRowNum(), exportPath);
}初始化excel方法
/**
* <一句话功能简述> 初始化excel
* author: zhanggw
* 创建时间: 2022/10/8
* @param lineHeadArray 每列头信息，如["学号","姓名"]
*/
private static SXSSFWorkbook initExcelSimple(String[] lineHeadArray) {
// 在内存当中保留100行,超过的数据放到硬盘中
SXSSFWorkbook wb = new SXSSFWorkbook(100);
// excel样式
CellStyle headerCellStyle = wb.createCellStyle();
headerCellStyle.setAlignment(HorizontalAlignment.CENTER);// 居中
headerCellStyle.setBorderBottom(BorderStyle.THIN); //下边框
headerCellStyle.setBorderLeft(BorderStyle.THIN); //左边框
headerCellStyle.setBorderTop(BorderStyle.THIN); //上边框
headerCellStyle.setBorderRight(BorderStyle.THIN); //右边框
// 填充第一行每列头信息
SXSSFSheet sheet = wb.createSheet("sheet1");
SXSSFRow headRow = sheet.createRow(0);
for (int k = 0; k < lineHeadArray.length; k++) {
SXSSFCell headRowCell = headRow.createCell(k);
headRowCell.setCellStyle(headerCellStyle);
headRowCell.setCellValue(lineHeadArray[k]);
}
return wb;
}导出excel写数据委托类
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFSheet;
/**
* <一句话功能简述> 导出excel写数据委托类
* <功能详细描述> 跟POIUtil的exportExcelBigData方法一起使用
* author: zhanggw
* 创建时间: 2022/10/8
*/
public abstract class WriteExcelDataDelegated {
protected Long writeRowNum = 0L; // 已写入excel表的行数,在writeExcelData方法中设置
protected boolean hasMore = true; // 是否还有更多数据待写入excel，在writeExcelData方法中设置
/**
* <一句话功能简述> excel写数据委托类，针对不同的情况自行实现
* <功能详细描述>
* author: zhanggw
* 创建时间: 2019/06/18 14:33
* @param currentSheet 当前写入excel的sheet页
* @param startRowCount 当前写入excel的开始行
* @param endRowCount 当前写入excel的结束行
* @param currentPage 分批查询起始页码
* @param pageSize 分批查询每次查询数据量
*/
public abstract void writeExcelData(Workbook workbook, SXSSFSheet currentSheet, Integer startRowCount, Integer endRowCount, Integer currentPage, Integer pageSize) throws Exception;
public Long getWriteRowNum(){
return writeRowNum;
}
public void setWriteRowNum(Long writeRowNum) {
this.writeRowNum = writeRowNum;
}
public boolean isHasMore() {
return hasMore;
}
public void setHasMore(boolean hasMore) {
this.hasMore = hasMore;
}
}保存excel到本地
/**
* <一句话功能简述> 保存excel到本地
* author: zhanggw
* 创建时间: 2019/06/18 14:39
* @param wb excel对象
* @param exportPath 保存路径
*/
private static void downLoadExcelToLocalPath(SXSSFWorkbook wb, String exportPath) {
FileOutputStream fops = null;
BufferedOutputStream bos = null;
try {
fops = new FileOutputStream(exportPath);
bos = new BufferedOutputStream(fops);
wb.write(bos);
} catch (Exception e) {
logger.error(e.getMessage());
} finally {
if (null != wb) {
try {
wb.dispose();
} catch (Exception e) {
logger.error(e.getMessage());
}
}
if (null != bos) {
try {
bos.close();
} catch (Exception e) {
logger.error(e.getMessage());
}
}
if (null != fops) {
try {
fops.close();
} catch (Exception e) {
logger.error(e.getMessage());
}
}
}
}使用方式如下
public JSONObject exportStatisticsData(Date now) {
JSONObject retJson = new JSONObject();
try{
// 统计商品信息
Date startDate = DateUtils.getPastDateZero(now, 90);
Date endDate = DateUtils.getPastDateZero(now, 0);
Date offStartDate = DateUtils.getPastDateZero(now, 14);
unMapper.deleteTmp();
int saleNum = unMapper.insertSaleProduct(startDate, endDate);
int offNum = unMapper.insertOffShelveProduct(offStartDate, endDate);
logger.debug("插入在售商品数量:{},下架商品数量:{}", saleNum, offNum);
unMapper.updateTmpSupplierName();
unMapper.updateTmpThreeClass();
unMapper.updateClassOneName();
unMapper.updateClassTwoName();
unMapper.updateClassThreeName();
// 导出商品信息到excel
WriteExcelDataDelegated writeExcelDataDelegated = new WriteExcelDataDelegated() {
@Override
public void writeExcelData(Workbook workbook, SXSSFSheet eachSheet, Integer startRowCount, Integer endRowCount, Integer currentPage, Integer pageSize) throws Exception {
try{
PageHelper.startPage(currentPage, pageSize); // 分页获取数据
List<Map<String, Object>> dataList = unMapper.getExportProductInfo();
if (dataList != null && dataList.size()>0) {
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);// 居中
cellStyle.setBorderTop(BorderStyle.THIN); //上边框
cellStyle.setBorderRight(BorderStyle.THIN); //右边框
cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
for (int i = startRowCount; i <= endRowCount; i++) {
SXSSFRow eachDataRow = eachSheet.createRow(i);
int dataArrayStartIndex = i - startRowCount;
if (dataArrayStartIndex < dataList.size()) {
Map<String, Object> dataMap = dataList.get(dataArrayStartIndex);
// 商品信息
POIUtil.setShipOrderCell(eachDataRow, 0, dataMap.get("item_name"), cellStyle);
POIUtil.setShipOrderCell(eachDataRow, 1, dataMap.get("class_one_name"), cellStyle);
POIUtil.setShipOrderCell(eachDataRow, 2, dataMap.get("class_two_name"), cellStyle);
POIUtil.setShipOrderCell(eachDataRow, 3, dataMap.get("class_three_name"), cellStyle);
POIUtil.setShipOrderCell(eachDataRow, 4, dataMap.get("show_price"), cellStyle);
POIUtil.setShipOrderCell(eachDataRow, 5, dataMap.get("supplier_name"), cellStyle);
POIUtil.setShipOrderCell(eachDataRow, 6, dataMap.get("themeName"), cellStyle);
POIUtil.setShipOrderCell(eachDataRow, 7, dataMap.get("saleState"), cellStyle);
POIUtil.setShipOrderCell(eachDataRow, 8, dataMap.get("productTime"), cellStyle);
POIUtil.setShipOrderCell(eachDataRow, 9, dataMap.get("groupNum"), cellStyle);
// 记录当前写入数据
writeRowNum++;
}
}
}else{
hasMore = false;
}
}catch (Exception e) {
logger.error("商品信息导出EXCEL异常!", e);
}
}
};
// 文件本地保存路径
String filePath = getSaveLocalPath("statistics") + DateUtils.getNowDateStr("yyyy-MM-dd_HHmmss")+ ".xlsx";
// excel每列头名称
String[] lineHeadArray = {"商品名称","一级品类","二级品类","三级品类","价格","供应商","专题","在售情况","时间","组号"};
// 执行导出
POIUtil.exportExcelBigData(lineHeadArray, filePath, writeExcelDataDelegated);
retJson.put("filePath", filePath);
}catch (Exception e){
logger.error("exportStatisticsData异常", e);
}
return retJson;
}最后
以上就是震动花生为你收集整理的POI异步分批导出大量数据到excel的全部内容，希望文章能够帮你解决POI异步分批导出大量数据到excel所遇到的程序开发问题。
如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。
本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
发表评论 取消回复