我用的这个poi版本
<!-- poi依赖 -->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>3.2.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>3.2.0</version>
</dependency>
先来个导出的基本例子:
public static void main(String[] args) {
try {
List<ExcelExportEntity> entity = new ArrayList<ExcelExportEntity>();
//构造对象等同于@Excel
entity.add(new ExcelExportEntity("姓名", "name"));
entity.add(new ExcelExportEntity("性别", "sex"));
ExcelExportEntity r = new ExcelExportEntity("入学时间", "date");
r.setFormat("yyyy-MM-dd");
entity.add(r);
ExcelExportEntity r2 = new ExcelExportEntity("入学交钱", "money");
entity.add(r2);
r2.setNumFormat("###,###,###.##");
entity.add(new ExcelExportEntity("sku", "sku"));
Workbook workbook = null;
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
for (int i = 0; i < 3000003; i++) {
Map<String, Object> map = new HashMap<String, Object>();
map.put("name", "tanlei" + i);
map.put("sex", i % 2 == 0 ? 1 : 0);
map.put("date", new Date());
map.put("money", 10000000.122 + i);
map.put("sku", "1111111111111111");
list.add(map);
if (list.size() == 10000) {
workbook = ExcelExportUtil.exportBigExcel(new ExportParams(null, "Sheet1"), entity, list);
list.clear();
}
}
ExcelExportUtil.closeExportBigExcel();
FileOutputStream fos = new FileOutputStream("D:/t.xlsx");
workbook.write(fos);
fos.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
几百万行还是很轻松的。
从数据库导出要依赖于jdbc的resultset,resultset的rs.next操作是不会一次性将数据库数据全部取出来的,不会造成java内存泄漏。这里可以参考我的另一篇文章:https://blog.csdn.net/u012572955/article/details/88534950
我这里写了个简单的表头配置,将excel表头信息配置到数据库中。数据库excel_column_info类似如下:
[{
"columnIndex": 1,
"dbFieldEnName": "column1",
"excelColumnName": "商户编码",
"type": "String"
},
{
"columnIndex": 2,
"dbFieldEnName": "column2",
"excelColumnName": "订单日期",
"format": "yyyy-MM-dd",
"type": "Date"
},
{
"columnIndex": 3,
"dbFieldEnName": "column3",
"excelColumnName": "清算日期",
"format": "yyyy-MM-dd",
"type": "Date"
},
{
"columnIndex": 4,
"dbFieldEnName": "column4",
"excelColumnName": "订单编号",
"type": "String"
},
{
"columnIndex": 5,
"dbFieldEnName": "column5",
"excelColumnName": "订单行号",
"type": "String"
},
{
"columnIndex": 6,
"dbFieldEnName": "column6",
"excelColumnName": "交易类型",
"type": "String"
},
{
"columnIndex": 7,
"dbFieldEnName": "column7",
"excelColumnName": "订单金额",
"format": "###,###,###.##",
"type": "Double"
},
{
"columnIndex": 8,
"dbFieldEnName": "column8",
"excelColumnName": "运费",
"format": "###,###,###.##",
"type": "Double"
},
{
"columnIndex": 9,
"dbFieldEnName": "column9",
"excelColumnName": "佣金比例",
"format": "###,###,###.##",
"type": "Double"
},
{
"columnIndex": 10,
"dbFieldEnName": "column10",
"excelColumnName": "佣金金额",
"format": "###,###,###.##",
"type": "Double"
},
{
"columnIndex": 11,
"dbFieldEnName": "column11",
"excelColumnName": "平台红包金额",
"format": "###,###,###.##",
"type": "Double"
},
{
"columnIndex": 12,
"dbFieldEnName": "column12",
"excelColumnName": "平台券金额",
"format": "###,###,###.##",
"type": "Double"
},
{
"columnIndex": 13,
"dbFieldEnName": "column13",
"excelColumnName": "平台礼品卡金额",
"format": "###,###,###.##",
"type": "Double"
},
{
"columnIndex": 14,
"dbFieldEnName": "column14",
"excelColumnName": "平台积分金额",
"format": "###,###,###.##",
"type": "Double"
},
{
"columnIndex": 15,
"dbFieldEnName": "column15",
"excelColumnName": "平台满减金额",
"format": "###,###,###.##",
"type": "Double"
},
{
"columnIndex": 16,
"dbFieldEnName": "column16",
"excelColumnName": "平台其它优惠金额",
"format": "###,###,###.##",
"type": "Double"
},
{
"columnIndex": 17,
"dbFieldEnName": "column17",
"excelColumnName": "苏宁优惠金额",
"format": "###,###,###.##",
"type": "Double"
},
{
"columnIndex": 18,
"dbFieldEnName": "column18",
"excelColumnName": "清算金额",
"format": "###,###,###.##",
"type": "Double"
},
{
"columnIndex": 19,
"dbFieldEnName": "column19",
"excelColumnName": "备注",
"type": "String"
}
]
columnIndex代表数据查询sql是第几个字段,用于排序的,也代表是excel的第几个字段,dbFieldEname代表对应的sql别名,excelColumnName代表excel中文字段名,type代表excel数据类型,format代表格式化操作,像double为了避免科学记数法,date避免英文时间。
对应数据库查询sql如下:
select
oit.cmmdy_id AS "column1",
oit.date_updated AS "column2",
oit.date_updated AS "column3",
oi.order_id AS "column4",
oit.order_item_id AS "column5",
case when oit.status = '36' then '退货'
else '交易' end AS "column6",
round((oit.cmmdy_price * oit.cmmdy_num),2) AS "column7",
oit.shipping_fee AS "column8",
COALESCE(cast(gci.rate as numeric),0) as "column9",
round((oit.cmmdy_price*oit.cmmdy_num)*COALESCE(cast(gci.rate as numeric),0),2) as "column10",
0 as "column11",
oit.share_coupon_reduce AS "column12",
0 as "column13",
0 as "column14",
0 as "column15",
0 as "column16",
0 as "column17",
round((oit.cmmdy_price * oit.cmmdy_num) + oit.shipping_fee-0 + (oit.cmmdy_price*oit.cmmdy_num)*COALESCE(cast(gci.rate as numeric),0),2) as "column18",
'' as "column19"
from mall_order.order_info oi
left join mall_order.order_item oit
on oi.order_id = oit.order_id
left join ims.G_ITEM_SKU gsku
on oit.cmmdy_id = gsku.sku_code
left join ims.g_commision_info gci
on gsku.catalog_id = gci.catalog_id
where oit.status in ('17','18','19','20','21','23','25','27','31','33','35','36','37')
具体封装后的代码:
FileUtil:
public class FileUtil {
private static final Logger logger = LogManager.getLogger(FileUtil.class);
public static final int ZIP_APPEND = 0;
public static final int ZIP_REFRESH = 1;
/**
* 写文件
*
* @param paths
* @param fileName
* @param content
* @return
*/
public static boolean writeFile(String paths, String fileName, String content) {
//默认追加方式
return writeFile(paths,fileName,content,StandardOpenOption.APPEND);
}
public static boolean writeFile(String paths, String fileName, String content,StandardOpenOption openOption) {
try {
if (!Files.exists(Paths.get(paths))) {
Files.createDirectory(Paths.get(paths));
}
if (!Files.exists(Paths.get(paths + File.separator + fileName))) {
Files.createFile(Paths.get(paths + File.separator + fileName));
}
Files.write(Paths.get(paths + File.separator + fileName), content.getBytes(), openOption);
} catch (IOException e) {
logger.error("写文件异常,路径:{}。文件名字:{}。", paths, fileName, e);
return false;
}
return true;
}
/**
* 删除文件
*
* @param dir
* @param fileName
* @return
*/
public static boolean delFile(String dir, String fileName) {
try {
Files.deleteIfExists(Paths.get(dir + File.separator + fileName));
} catch (IOException e) {
logger.error("删除文件异常,路径:{},文件名字:{}。", dir, fileName, e);
return false;
}
return true;
}
/**
* 清空路径(不管是文件还是文件夹)
*
* @param path 要删除的path
* @return 删除成功返回 true,否则返回 false。
*/
public static boolean deletePath(String path) {
File file = new File(path);
// 判断目录或文件是否存在
if (!file.exists()) { // 不存在返回 false
return false;
} else {
// 判断是否为文件
if (file.isFile()) { // 为文件时调用删除文件方法
return deleteFile(path);
} else { // 为目录时调用删除目录方法
return deleteDirectory(path);
}
}
}
/**
* 删除单个文件
*
* @param path 被删除文件path
* @return 删除成功返回true,否则返回false
*/
public static boolean deleteFile(String path) {
try {
return Files.deleteIfExists(Paths.get(path));
} catch (IOException e) {
logger.error("删除文件异常,路径:{}", path, e);
return false;
}
}
/**
* 删除目录以及目录下的文件
*
* @param path 被删除目录的路径
* @return 目录删除成功返回true,否则返回false
*/
public static boolean deleteDirectory(String path) {
//如果path不以文件分隔符结尾,自动添加文件分隔符
if (!path.endsWith(File.separator)) {
path = path + File.separator;
}
File dirFile = new File(path);
//如果dir对应的文件不存在,或者不是一个目录,则退出
if (!dirFile.exists() || !dirFile.isDirectory()) {
return false;
}
boolean flag = true;
//删除文件夹下的所有文件(包括子目录)
File[] files = dirFile.listFiles();
for (int i = 0; i < files.length; i++) {
if (files[i].isFile()) {
//删除子文件
flag = deleteFile(files[i].getAbsolutePath());
if (!flag) break;
} else {
//删除子目录
flag = deleteDirectory(files[i].getAbsolutePath());
if (!flag) break;
}
}
if (!flag) return false;
//删除当前目录
if (dirFile.delete()) {
return true;
} else {
return false;
}
}
/**
* @param srcPath
* @param password
* @param dstPath
* @param type
* @return
*/
//可选覆盖或追加文件
public static boolean encryptZip(String srcPath, String password, String dstPath, int type) {
if (type == ZIP_APPEND) {
return encryptZip(srcPath, password, dstPath);
} else {
deleteFile(dstPath);
return encryptZip(srcPath, password, dstPath);
}
}
private static boolean createDir(String dir) throws Exception{
File dirFile = new File(dir);
if(!dirFile.exists()){
createDir(dirFile.getParent());
dirFile.mkdir();
}
return true;
}
public static boolean encryptZip(String srcPath, String password, String dstPath) {
try {
if (!new File(srcPath).exists()) {
return false;
}
//创建目标文件目录
File desFile = new File(dstPath);
createDir(desFile.getParent());
ZipParameters parameters = new ZipParameters();
parameters.setEncryptFiles(true);
parameters.setEncryptionMethod(Zip4jConstants.ENC_METHOD_AES);
parameters.setAesKeyStrength(Zip4jConstants.AES_STRENGTH_256);
parameters.setPassword(password.toCharArray());
parameters.setCompressionLevel(Zip4jConstants.DEFLATE_LEVEL_NORMAL);//压缩级别7
File srcFile = new File(srcPath);
ZipFile destFile = new ZipFile(dstPath);
if (srcFile.isDirectory()) {
destFile.addFolder(srcFile, parameters);
} else {
destFile.addFile(srcFile, parameters);
}
return true;
} catch (Exception e) {
logger.error("压缩文件异常,原路径:{}。目标路径:{}。", srcPath, dstPath, e);
return false;
}
}
/**
* 根据需求,直接调用静态方法start来执行操作
* 参数:
* rows 为多少行一个文件 int 类型
* sourceFilePath 为源文件路径 String 类型
* targetDirectoryPath 为文件分割后存放的目标目录 String 类型
* ---分割后的文件名为索引号(从0开始)加'_'加源文件名,例如源文件名为test.txt,则分割后文件名为0_test.txt,以此类推
*/
public static Integer split(String sourceFilePath, int rows) throws Exception {
int idx = sourceFilePath.lastIndexOf(".");
String targetDirectoryPath = sourceFilePath.substring(0, idx);
return FileUtil.split(sourceFilePath, rows, targetDirectoryPath);
}
public static Integer split(String sourceFilePath, int rows, String targetDirectoryPath) throws Exception {
File sourceFile = new File(sourceFilePath);
File targetFile = new File(targetDirectoryPath);
String bom = new String(new byte[]{(byte) 0xEF, (byte) 0xBB, (byte) 0xBF});
if (!sourceFile.exists() || rows <= 0 || sourceFile.isDirectory()) {
throw new Exception("源文件不存在或者输入了错误的行数");
}
if (targetFile.exists()) {
if (!targetFile.isDirectory()) {
throw new Exception("目标文件夹错误,不是一个文件夹");
}
} else {
targetFile.mkdirs();
}
String fileName = sourceFile.getName();
String suffix = fileName.substring(fileName.lastIndexOf(".") + 1);//获取文件后缀
InputStreamReader inStream = new InputStreamReader(new FileInputStream(sourceFile), "UTF8");
BufferedReader br = new BufferedReader(inStream);
BufferedWriter bw = null;
StringBuffer sb = new StringBuffer("");
String tableHead = "";
if("csv".equals(suffix)){
tableHead = br.readLine();//默认第一行就是表头,暂存以便给第二张之后的表用
if(StringUtils.isEmpty(tableHead)){
throw new Exception("源文件内容为空,无法切割");
}
tableHead += "\r\n";
}
String tempData = br.readLine();
int i = 1, s = 0;
while (tempData != null) {
sb.append(tempData + "\r\n");
if (i % rows == 0) {
bw = new BufferedWriter(new FileWriter(new File(targetFile.getAbsolutePath() + "/" + s + "_" + sourceFile.getName())));
if ("csv".equals(suffix)) {
//csv文件头部加上bom,避免中文乱码问题(第一个文件已经存在bom,因此不需要再加)
bw.write(bom + tableHead+sb.toString());
} else {
bw.write(sb.toString());
}
bw.close();
sb.setLength(0);
s += 1;
}
i++;
tempData = br.readLine();
}
//读完最后一行且最后一行不在文件尾部,把未写入文件的sb写入最后一个文件
if ((i - 1) % rows != 0||i==1) {
bw = new BufferedWriter(new FileWriter(new File(targetFile.getAbsolutePath() + "/" + s + "_" + sourceFile.getName())));
if ("csv".equals(suffix)) {
//csv文件头部加上bom,避免中文乱码问题
bw.write(bom + tableHead+sb.toString());
} else {
bw.write(sb.toString());
}
bw.close();
br.close();
s += 1;
}
logger.info("文件分割结束,共分割成了{}个文件",s);
return s;
}
}
入参实体:
public class CfgSqlInput {
private static final long serialVersionUID = 8831167730162732878L;
/**
* 需要执行的sql
*/
private String sql;
/**
* 文件路径
*/
private String filePath;
/**
* 文件名字
*/
private String fileName;
/**
* 数据中心
*/
private String dataSource;
/**
* 是否需要表头
*/
private String header;
/**
* 分割线
*/
private String lineSeparator;
/**
* 行尾分割线
*/
private String lineEnd;
/**
* 文件类型
*/
private String suffix;
/**
* excel
* @return
*/
private String excelColumnInfo;
public String getSql() {
return sql;
}
public void setSql(String sql) {
this.sql = sql;
}
public String getFilePath() {
return filePath;
}
public void setFilePath(String filePath) {
this.filePath = filePath;
}
public String getFileName() {
return fileName;
}
public void setFileName(String fileName) {
this.fileName = fileName;
}
public String getDataSource() {
return dataSource;
}
public void setDataSource(String dataSource) {
this.dataSource = dataSource;
}
public String getHeader() {
return header;
}
public void setHeader(String header) {
this.header = header;
}
public String getLineSeparator() {
return lineSeparator;
}
public void setLineSeparator(String lineSeparator) {
this.lineSeparator = lineSeparator;
}
public String getLineEnd() {
return lineEnd;
}
public void setLineEnd(String lineEnd) {
this.lineEnd = lineEnd;
}
public String getSuffix() {
return suffix;
}
public void setSuffix(String suffix) {
this.suffix = suffix;
}
public String getExcelColumnInfo() {
return excelColumnInfo;
}
public void setExcelColumnInfo(String excelColumnInfo) {
this.excelColumnInfo = excelColumnInfo;
}
}
jdbc操作我用了spring的jdbctemplate,也可以直接用java。
@Service
public class ReportRunSqlServiceImpl implements ReportRunSqlService {
private static final Logger logger = LogManager.getLogger(ReportRunSqlServiceImpl.class);
/**
* excel表最大行数对应一个workbook
*/
private static final int DEFAULT_MAX_ROW = 30000;
@Override
public CfgSqlOutput runSqlToXlsx(CfgSqlInput input) {
logger.info("runSql 开始执行报表生成xlsx input:{}", input);
JdbcTemplate jdbcTemplate = null;
CfgSqlOutput output = new CfgSqlOutput();
output.setFlag(true);
try {
jdbcTemplate = (JdbcTemplate) SpringContextHolder.getBean(input.getDataSource());
//先删除旧文件
FileUtil.delFile(input.getFilePath(), input.getFileName());
List<ExcelColumnInfo> excelColumnInfos = generateExcelColumnInfoList(input.getExcelColumnInfo());
if (CollectionUtils.isEmpty(excelColumnInfos)) {
output.setFlag(false);
logger.error("执行excel报表sql的时候发生了异常, 未正确生成excel表头配置信息:{}", input.getExcelColumnInfo());
return output;
}
List<ExcelExportEntity> entitys = generateExcelExportEntity(excelColumnInfos);
if (CollectionUtils.isEmpty(entitys)) {
output.setFlag(false);
logger.error("执行excel报表sql的时候发生了异常, 未正确生成excel表头配置信息:{}", input.getExcelColumnInfo());
return output;
}
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>(); //rs遍历数据集合
List<Integer> rsultNumList = new ArrayList<Integer>();
jdbcTemplate.query(input.getSql(), new RowCallbackHandler() {
@Override
public void processRow(ResultSet resultSet) throws SQLException {
Workbook workbook = null;
ResultSetMetaData resultMetaData = resultSet.getMetaData();
int cols = resultMetaData.getColumnCount();//获取列数量
int workBookNum = 0;
int resultNum = 0; //结果集count
boolean firstRow = false;
if (resultSet.isFirst()) {
firstRow = true;
}
while (firstRow || resultSet.next()) {
resultNum++;
firstRow = false;
//记录结果行数
rsultNumList.clear();
rsultNumList.add(resultNum);
Map<String, Object> map = new HashMap<String, Object>();
for (int i = 1; i <= cols; i++) { //数据放入map
map.put(excelColumnInfos.get(i - 1).getDbFieldEnName(), resultSet.getObject(i));
}
list.add(map);
if (list.size() == DEFAULT_MAX_ROW) {//每30w条创建一个工作簿
workBookNum++;
workbook = ExcelExportUtil.exportBigExcel(new ExportParams(null, null), entitys, list);//创建工作簿
list.clear();
}
}
if (workBookNum * DEFAULT_MAX_ROW < resultNum) {
workbook = ExcelExportUtil.exportBigExcel(new ExportParams(null, null), entitys, list);//创建工作簿
}
uploadXlsx(workbook, input);
}
});
if (rsultNumList.size() < 1) {
logger.info("执行excel报表sql查询数据为空,sql is:{} ", input.getSql());
//生成空文件
Workbook workbook = ExcelExportUtil.exportBigExcel(new ExportParams(null, null), entitys, list);//创建工作簿
uploadXlsx(workbook, input);
} else {
logger.info("执行excel报表sql查询导出数据行数为:{}, sql is:{}", rsultNumList.get(0), input.getSql());
}
} catch (DataAccessException e) {
output.setFlag(false);
logger.error("执行报表sql的时候发生了异常, {}", e);
}
return output;
}
/**
* 生成excel操作
*
* @param workbook
*/
private void uploadXlsx(Workbook workbook, CfgSqlInput input) {
ExcelExportUtil.closeExportBigExcel();
try {
if (!Files.exists(Paths.get(input.getFilePath()))) {
Files.createDirectory(Paths.get(input.getFilePath()));
}
FileOutputStream fos = new FileOutputStream(input.getFilePath() + File.separator + input.getFileName());
workbook.write(fos);
fos.close();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* excel表头配置信息生成和数据库sql字段对应的有序list
*/
private List<ExcelColumnInfo> generateExcelColumnInfoList(String excelColumnInfoJsonStr) {
List<ExcelColumnInfo> list = null;
if (StringUtils.isEmpty(excelColumnInfoJsonStr)) {
return null;
}
list = JSONObject.parseArray(excelColumnInfoJsonStr, ExcelColumnInfo.class);
//根据columnIndex排序
list = list.stream().sorted((a, b) -> a.getColumnIndex() - b.getColumnIndex()).collect(Collectors.toList());
return list;
}
/**
* 生成excel表头和各列数据类型信息
*
* @return
*/
private List<ExcelExportEntity> generateExcelExportEntity(List<ExcelColumnInfo> excelColumnInfoList) {
if (CollectionUtils.isEmpty(excelColumnInfoList)) {
return null;
}
List<ExcelExportEntity> entitys = new ArrayList<ExcelExportEntity>();
for (ExcelColumnInfo excelColumnInfo : excelColumnInfoList) {
ExcelExportEntity entity = new ExcelExportEntity(excelColumnInfo.getExcelColumnName(), excelColumnInfo.getDbFieldEnName()); //new ExcelExportEntity("入学时间", "date");
if (StringUtils.equalsIgnoreCase("Date", excelColumnInfo.getType())) {//如果是date类型,设置format
if (StringUtils.isEmpty(excelColumnInfo.getFormat())) {
entity.setFormat("yyyy-MM-dd");//设置默认format
} else {
entity.setFormat(excelColumnInfo.getFormat());
}
} else if (StringUtils.equalsIgnoreCase("Double", excelColumnInfo.getType())) {
if (StringUtils.isEmpty(excelColumnInfo.getFormat())) {
entity.setNumFormat("###,###,###.##");//设置默认double类型format
} else {
entity.setNumFormat(excelColumnInfo.getFormat());
}
}
//end,其他数据类型无需设置默认数据类型
entitys.add(entity);
}
return entitys;
}
}
调用runSqlToXlsx方法即可。
测试代码如下,自己封装CfgSqlInput参数即可
@Test
public void test() {
ReportCfg reportCfg = reportConfigService.getByReportId("0001");
CfgSqlInput input = new CfgSqlInput();
input.setFileName("1.xlsx");
input.setFilePath("D:/");
input.setSql(reportCfg.getReportSql());
input.setDataSource(reportCfg.getDataSource());//这个是数据源,用户自己可以定义jdbctemplate
input.setExcelColumnInfo(reportCfg.getExcelColumnInfo());
//input.setHeader(reportCfg.getHeader());
//input.setLineEnd(reportCfg.getLineEnd());
//input.setLineSeparator(reportCfg.getLineSeparator());
//input.setSuffix(reportCfg.getSuffix());
reportRunSqlService.runSqlToXlsx(input);
}