Poi导入导出百万Excel数据--java编程
程序员文章站
2022-03-27 20:14:15
前言 项目中做报表,导入导出Excel,是常见也是不可或缺的功能,本文讲解导入多 Excel的写法,以及导出百万的优化方案图解代码思路导出多Excel工具类package com.XXX.XXX.utils;import.XXX.ExportExcelBase;import lombok.extern.slf4j.Slf4j;import org.apache.poi.xssf.usermodel.*;import org.springframework.core.env...
前言
项目中做报表,导入导出Excel,是常见也是不可或缺的功能,本文讲解导入多
Excel的写法,以及导出百万的优化方案
图解代码思路
导出多Excel工具类
package com.XXX.XXX.utils;
import.XXX.ExportExcelBase;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.core.env.Environment;
import org.springframework.stereotype.Component;
import swiftsdk.SfOssClient;
import swiftsdk.SwiftConfiguration;
import javax.annotation.PostConstruct;
import javax.annotation.Resource;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
/**
* @Author windwardbird
* @packageName windwardbird exportMulitExcelUtil
* @describeOfFunction: 数据大于50万分Excel
*/
@Slf4j
@Component
public class ExportMultiExcelUtils {
private static Environment environment;
@Resource
private Environment env;
@PostConstruct
public void init() {
environment = env; // 注入bean (自己new 出来的)
}
private final SimpleDateFormat format = new SimpleDateFormat("MM-dd-HH");
private final SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
private final SimpleDateFormat df1 = new SimpleDateFormat("yyyy-MM-dd-HH");
private static final String NEW_EXCEL_SUFFIX = ".xlsx";
private static final String OLD_EXCEL_SUFFIX = ".xls";
private static final Integer CURRENT_MAX_NUMBER = null==environment.getProperty("max.export.number") ?500000:env.getProperty("max.export.number"); //每个默认Excel50万
public void ExportExcel(Map<String, Object> tempMap, Object exportExcelBase, String tableName, String model,String part) throws Exception {
log.info("进入Excel工具类");
List<ExportExcelBase> exportExcelBaseList = (List<ExportExcelBase>) exportExcelBase;
List<List<Object>> excelDataResult = new ArrayList<>(); //Excel 的对应数
List<Object> headExcelValue = new ArrayList<>(); //Excel表头
List<Object> headExcelKey = new ArrayList<>(); //连接key
GetExcelHeadInfo(tempMap, headExcelValue, headExcelKey);//获取Excel 表头以及连接key
//处理数据
ProcessingInputData(exportExcelBaseList, excelDataResult, headExcelKey);
log.info("Excel 数据处理完毕");
//TODO: 数据多余50万处理成多个Excel
Integer excelTotal = excelDataResult.size(); //处理过Excel总条数
if (excelTotal <= CURRENT_MAX_NUMBER) {
List<List<Object>> excelDataResultTemp = new ArrayList<>();
XSSFWorkbook wb = new XSSFWorkbook();//新版的Excel
XSSFSheet sheet = wb.createSheet(tableName);
excelDataResultTemp.add(headExcelValue);
for (int j = 0; j < excelTotal; j++) {
excelDataResultTemp.add(excelDataResult.get(j));
}
InsertDataToExcel(excelDataResultTemp, excelDataResultTemp.size(), sheet);
log.info("查询数据小于50万");
//导出Excel
ExportExcelOfResponseStream(tableName, wb, model,part);
} else {
log.info("查询数据大于50万");
int excelNumber = 0; //分EXCEl导出 默认生成一个Excel
int flagNum = excelTotal % CURRENT_MAX_NUMBER; // 取模看是否需要多分Excel
if (flagNum == 0) {
excelNumber = excelTotal / CURRENT_MAX_NUMBER;
} else {
excelNumber = excelTotal / CURRENT_MAX_NUMBER + 1;
}
for (int i = 1; i <= excelNumber; i++) {
//构造当前Excel数据
List<List<Object>> excelDataResultTemp = new ArrayList<>();
XSSFWorkbook wb = new XSSFWorkbook();//新版的Excel
XSSFSheet sheet = wb.createSheet(tableName);
if (i == 1) {
//ToDo: 取50万整数据,单独加表头
excelDataResultTemp.add(headExcelValue);
for (int j = 0; j < CURRENT_MAX_NUMBER; j++) {
excelDataResultTemp.add(excelDataResult.get(j));
}
} else {
excelDataResultTemp.add(headExcelValue); //添加每个Excel的表头
for (int j = CURRENT_MAX_NUMBER * (i - 1); j < CURRENT_MAX_NUMBER * i; j++) {
if (j > excelTotal) {
break;
} else if (j < excelTotal) {
excelDataResultTemp.add(excelDataResult.get(j));
}
}
}
InsertDataToExcel(excelDataResultTemp, excelDataResultTemp.size(), sheet);
//导出Excel
ExportExcelOfResponseStream(tableName, wb, model,part);
}
}
log.info("数据转成excel处理完毕,进入生成压缩包阶段");
//获取excel,打成zip包,根据当前的时间,放入相应的服务器路径
String zipPath = environment.getProperty("base.path") + File.separator + model + File.separator + part + File.separator + df.format(new Date()) + File.separator;
CompressUtil.generateFile(zipPath,"zip",tableName);
File file = new File(zipPath);
File[] files = file.listFiles();
// System.gc();
//删除excel
for (int i = 0; i < files.length; i++) {
if (!files[i].getName().endsWith(".zip")) {
log.info("文件名称:{}" , files[i].getName());
files[i].delete();
}
}
private void ExportExcelOfResponseStream(String tableName, XSSFWorkbook wb, String model,String part) throws IOException {
// 设置文件名(根据当前时间)
String fileName = "" + tableName + "-" + format.format(new Date()) + "-" + SnowflakeIdUtil.nextId() + NEW_EXCEL_SUFFIX;
//获取文件夹,如果没有则创建
String format = df.format(new Date());
String path = environment.getProperty("base.path") + File.separator + model + File.separator + part + File.separator + format;
File f = new File(path);
if (!f.exists()) {
f.mkdirs();
}
//写入excel
FileOutputStream fileOutputStream = new FileOutputStream(path + File.separator + fileName);
wb.write(fileOutputStream);
fileOutputStream.flush();
fileOutputStream.close();
wb.close();
}
private void InsertDataToExcel(List<List<Object>> excelDataResult, int excelTotal, XSSFSheet
sheet) {
for (int i = 0; i < excelTotal; i++) {
XSSFRow row = sheet.createRow(i);// 创建行数
List<Object> rowData = excelDataResult.get(i);
for (int j = 0; j < rowData.size(); j++) {
Object cellValue = null == rowData.get(j) ? "" : rowData.get(j);
row.createCell(j).setCellValue(String.valueOf(cellValue));
//row.setRowStyle(cellStyle);
}
}
}
// 格式不加走默认(根据需要自己添加)
private XSSFSheet GETExcelTypeAndName(String tableName) {
XSSFWorkbook wb = new XSSFWorkbook();//新版的Excel
XSSFCellStyle cellStyle = wb.createCellStyle(); //创建单元格式
//创建单元格格式
XSSFFont ztFont = (XSSFFont) wb.createFont();
ztFont.setFontHeightInPoints((short) 10); // 将字体大小设置为10px
ztFont.setFontName("Arial"); // 将“华文行楷”字体应用到当前单元格上
ztFont.setBoldweight( XSSFFont.BOLDWEIGHT_BOLD);//字体加粗
cellStyle.setFont(ztFont); // 设置字体格式
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //居中显示
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
XSSFSheet sheet = wb.createSheet(tableName);
return sheet;
}
private void ProcessingInputData(List<ExportExcelBase> exportExcelBase, List<List<Object>> excelDataResult, List<Object> headExcelKey) {
exportExcelBase.forEach(frontPartDetail -> {
try {
List<Object> headExcelData = new ArrayList<>(); //
Map<String, Object> frontPartMap = BeanToMap.javaBeanToMap(frontPartDetail); //当行数超过三千,就不要BeantoMap ,直接在内存根据表头构造好List<Map<String,Object>> 传入 ,效率提升50倍加
for (int i = 0; i < headExcelKey.size(); i++) {
Object tempData = frontPartMap.get(headExcelKey.get(i));
headExcelData.add(tempData);
}
excelDataResult.add(headExcelData);
} catch (Exception e) {
e.printStackTrace();
}
}
);
}
private void GetExcelHeadInfo(Map<String, Object> tempMap, List<Object> headExcelValue, List<Object> headExcelKey) {
for (Map.Entry<String, Object> entry : tempMap.entrySet()) {
String key = entry.getKey(); //取数据
Object value = entry.getValue();//表头
headExcelValue.add(value);
headExcelKey.add(key);
}
}
}
优化
导出3千可以直接用上面的工具类,倘若要导出上万或者百万的Excel?
1.上面的逻辑代码仍可以用(前提是吃硬件,时间慢)
2. 需要做的仅仅是换个接收的工具类
a .阿里的EasyExcel
b.阿帕奇的 SXSSF 替换 (XSSF)/(HSSF)
- pom文件的引用
阿里
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>1.1.2-beta5</version>
</dependency>
阿帕奇
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
生成zip工具可以自己写,也可以参考
https://blog.csdn.net/WindwirdBird/article/details/109273548
javaBean 转换map
https://blog.csdn.net/windwirdbird/article/details/109258678
本文地址:https://blog.csdn.net/WindwirdBird/article/details/109257876