最近遇到一个问题,线上生产环境某个功能导出数据到excel文件非常缓慢,几万数据导十多分钟都导不出来,导出慢的原因一是主表A数据量太大,接近2亿,另外里面部分数据来自于另外一张表B,B表也是几千万的数据量,数据库层面能做的优化已经做了,视图、索引这些工具都上了(没有分表是一开始项目设计阶段就没考虑,后面也没有专人维护,是另外一段故事了,这里不展开描述),但是依旧很慢,那就只能改导出代码了。
项目原来使用的是jxl来导出,生成的是xls格式Excel文件,这是旧版本的Excel文件,缺点有两点:一是单sheet页数据量小,只有6万多,二是文件太大,同等数据量下,xlsx格式的比xls格式的文件小4倍。一番搜索后,发现了POI自3.8版本后新加了一个 SXSSFWorkbook
类,可以处理大数据量的导出,并且内存占用不高,下面是一个小demo,写入10万行数据花费11065ms,文件大小14M不到,可以说很高效了。
package exceltest;
import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellUtil;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class SXSSFWorkbookTest {
public static void main(String[] args) {
long start = System.currentTimeMillis();
Excel2007AboveOperate();
long end = System.currentTimeMillis();
System.out.println("花费:"+(end-start));//10万数据花费:11065
}
public static void Excel2007AboveOperate() {
XSSFWorkbook workbook1 = new XSSFWorkbook();
SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(workbook1, 100);
for (int i = 0; i < 10; i++) {
sxssfWorkbook.createSheet();
sxssfWorkbook.setSheetName(i, "第"+i+"页");
Sheet first = sxssfWorkbook.getSheetAt(i);
for (int j = 0; j < 10000; j++) {
Row row = first.createRow(j);
for (int k = 0; k < 11; k++) {
if(j == 0) {
// 首行
row.createCell(k).setCellValue("column" + k);
} else {
// 数据
if (k == 0) {
CellUtil.createCell(row, k, String.valueOf(j));
} else
CellUtil.createCell(row, k, String.valueOf(Math.random()));
}
}
}
}
FileOutputStream out;
try {
out = new FileOutputStream("F:\\workbook666.xlsx");
sxssfWorkbook.write(out);
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
以为故事到这里就结束了,然而事情并没有那么简单!生产环境有个包集成了POI的代码,也可以用 SXSSFWorkbook
这个nb的类,但是会报错 java.lang.RuntimeException: Provider for class javax.xml.stream.XMLEventFactory cannot be created
,查找一番下来,有说缺少依赖包的,有说jdk版本低云云,单独引用最新的POI包,依旧是报错,怀疑是集成包里的poi代码缺少了一些东西,至于是什么无从考量,只能另辟蹊径来解决这个导出问题了,只要是依赖POI包的方法都不行,只有找到不依赖POI包却依然能导出excel文件方法才行!
又是一番寻找,看到了一篇博客,讲的是xls文件的本质其实是一个xml文件,可以通过手动拼接的方式来生成一个符合xls格式的xml文件,原博客文章暂时没找到,如果原作者看到可以留言提醒下。直接上demo代码。
package exceltest;
import java.io.BufferedOutputStream;
import java.io.DataOutputStream;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
public class Xml2ExcelTest {
public static void main(String[] args) {
test2();
}
public static void test2() {
StringBuffer sb = new StringBuffer();
File file = new File("F://testxml2xls666.xls");
try {
DataOutputStream rafs = new DataOutputStream(new BufferedOutputStream(new FileOutputStream(file)));
sb.append("\n");
sb.append("\n");
sb.append("\n");
sb.append(" \n");
sb.append(" \n");
sb.append(" <Alignment ss:Vertical=\"Center\"/>\n");
sb.append(" <Borders/>\n");
sb.append(" <Font ss:FontName=\"宋体\" x:CharSet=\"134\" ss:Size=\"12\"/>\n");
sb.append(" <Interior/>\n");
sb.append(" <NumberFormat/>\n");
sb.append(" <Protection/>\n");
sb.append(" \n");
sb.append(" \n");
int maxRow = 10;
int maxCol = 10;
for (int i = 0; i < 5; i++) {
sb.append("\n");
sb.append("\n");
for (int j = 0; j < maxRow; j++) {
sb.append("\n");
sb.append("").append("还好").append(i).append("\n");
sb.append("").append(String.valueOf(Math.random())).append("\n");
sb.append("").append(String.valueOf(Math.random())).append("\n");
sb.append("").append(String.valueOf(Math.random())).append("\n");
sb.append("").append(String.valueOf(Math.random())).append("\n");
sb.append("").append(String.valueOf(Math.random())).append("\n");
sb.append("").append(String.valueOf(Math.random())).append("\n");
sb.append("").append(String.valueOf(Math.random())).append("\n");
sb.append("").append(String.valueOf(Math.random())).append("\n");
sb.append("").append(String.valueOf(Math.random())).append("\n");
sb.append("\n");
}
sb.append("\n");
sb.append("\n");
sb.append("False\n");
sb.append("False\n");
sb.append("\n");
sb.append("\n");
rafs.write(sb.toString().getBytes());
rafs.flush();
sb = null;
sb = new StringBuffer();
}
sb.append("\n");
rafs.write(sb.toString().getBytes());
rafs.flush();
rafs.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}
demo可行,拿正式数据做测试,10万数据可以正常导出,速度比之前通过jxl导出快,但是有个缺点很明显,就是文件太大了,如果上生产,那文件大小估计得按G来算,这个方法也无法胜任啊!但是却提供了一种思路,既然xls文件能通过xml文件间接得到,那么xlsx文件是否也可行?想到就干!
又是一番查找,找到了一半的答案,喜忧参半吧!喜的是这个思路行得通, xlsx文件其实是一个压缩文件,可以将后缀改为zip解压,里面包含了很多的xml文件,可以用多线程逐个击破,同时解决写入和效率问题,大家可以在自己电脑上试下,你会发现新大陆的。忧的是里面每个单元格值不是明文,是键值对,需要准备一个很大的数据结构来存储。于是将本地新建的xlsx文件和通过代码生成的xlsx文件做个了比较,代码生成的xml文件里是直接放的明文,难道是我本地Office版本太高了?可能是吧,问题暂且放一边,先把demo跑通过才是正事。
先看下解压出来的文件结构:
_rels/.rels
docProps/app.xml
docProps/core.xml
xl/_rels/workbook.xml.rels
xl/worksheets/sheet1.xml
......
xl/worksheets/sheetn.xml
xl/sharedStrings.xml
xl/styles.xml
xl/workbook.xml
[Content_Types].xml
_rels/.rels
文件内容是固定的,可以直接写。
docProps/app.xml
文件内容是固定的,可以直接写。
Apache POI
docProps/core.xml
文件里面需要注意下创建时间,这个时间格式需要特殊处理下。
2021-12-16T12:10:02Z
Apache POI
xl/sharedStrings.xml
文件内容是固定的,可以直接写。
xl/styles.xml
样式文件,需要自定义样式的可以提前通过代码生成。
xl/workbook.xml
sheet页的信息文件,每个sheet页都有一个id,是一一对应的关系。 name
是表名,可以自定义, sheetId
从1开始, r:id
从rId3开始。
xl/_rels/workbook.xml.rels
sheet的依赖文件信息,rId1和rId2都是固定的,从rId3开始对应创建的sheet页文件路径。
[Content_Types].xml
汇总文件,里面包含了xlsx文件依赖的相关xml文件信息
xl/worksheets/sheet1.xml
sheet页数据文件,其中第一个sheet页是默认选中打开的sheet页,会加上 tabSelected="true"
属性,只能在其中一个xml文件里设置,不能给多个sheet页xml文件设置。行号从1开始,列号从大写的英文字母加数字(也是从1开始)组成。 cols
标签里的 col
标签是设置列宽,列号通过 min="1" max="1"
这两个标签定义,也是从1开始。合并单元格使用 <mergecells></mergecells>
标签,跟在 标签后面,比如想要合并A1到D1,可以这样写 <mergecells><mergecell ref="A1:D1"></mergecell></mergecells>
。
column0
column1
column2
column3
column4
column5
column6
column7
column8
column9
column10
......
以上就是一个xlsx文件里包含的xml文件,使用IO流将相关的信息写入到xml文件中去,最后压缩成xlsx文件,就可以得到一个xlsx格式的excel文件了。
但是其中有个点需要注意,压缩方法不能使用 java.util.zip
里的,用 java.util.zip
得到的压缩文件打开会提示文件损坏,得换成 apache
的 compress
方法,因为这是poi相关包源码用到的压缩方法。至于为什么用 java.util.zip
生成的文件打不开,这个原因暂时未知。
下面是用写xml文件生成xlsx文件的代码demo。
package exceltest;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.DataOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.function.Supplier;
import org.apache.commons.compress.archivers.zip.ZipArchiveEntry;
import org.apache.commons.compress.archivers.zip.ZipArchiveOutputStream;
import org.apache.commons.compress.utils.IOUtils;
/**
* 借助xml文件生成xlsx文件
* @author 程序员小川
* @date 2021-12-21
*
*/
public class XML2XlsxFileDemo {
public static void main(String[] args) {
testM();
try {
File file = new File("F:\\test996.xlsx");
FileOutputStream outputStreamExcel = new FileOutputStream(file);
ZipArchiveOutputStream zos = new ZipArchiveOutputStream(outputStreamExcel);
compressDirectoryToZipfile("F:\\xlsxtest222", "F:\\xlsxtest222", zos);
zos.flush();
zos.finish();
zos.close();
} catch (Exception e) {
e.printStackTrace();
}
}
private static void compressDirectoryToZipfile(String rootDir, String sourceDir, ZipArchiveOutputStream out) throws IOException {
try {
File[] files = new File(sourceDir).listFiles();
assert files != null;
for (File file : files) {
if (file.isDirectory()) {
compressDirectoryToZipfile(rootDir, sourceDir + File.separator + file.getName(), out);
} else {
ZipArchiveEntry entry = new ZipArchiveEntry(file.getAbsolutePath().substring(rootDir.length() + 1));
out.putArchiveEntry(entry);
try (InputStream in = new BufferedInputStream(new FileInputStream(sourceDir + File.separator + file.getName()))) {
IOUtils.copy(in, out);
}
out.closeArchiveEntry();
}
}
} catch(Exception e) {
e.printStackTrace();
}
}
public static void testM() {
String filefolder = "F:\\xlsxtest222";
try {
File fo = new File(filefolder + File.separator + "_rels");
if (!fo.exists()) {
fo.mkdirs();
}
fo = new File(filefolder + File.separator + "docProps");
if (!fo.exists()) {
fo.mkdirs();
}
fo = new File(filefolder + File.separator + "xl" + File.separator + "_rels");
if (!fo.exists()) {
fo.mkdirs();
}
fo = new File(filefolder + File.separator + "xl" + File.separator + "worksheets");
if (!fo.exists()) {
fo.mkdirs();
}
File _rels_f = new File(filefolder + File.separator + "_rels" + File.separator + ".rels");
if (!_rels_f.exists()) {
_rels_f.createNewFile();
}
DataOutputStream rafs = new DataOutputStream(new BufferedOutputStream(new FileOutputStream(_rels_f)));
StringBuilder sb = new StringBuilder();
sb.append("\n");
sb.append("\n");
sb.append("\n");
sb.append("\n");
sb.append("\n");
sb.append("");
rafs.write(sb.toString().getBytes());
rafs.flush();
rafs.close();
File docProps_appxml_f = new File(filefolder + File.separator + "docProps" + File.separator + "app.xml");
if (!docProps_appxml_f.exists()) {
docProps_appxml_f.createNewFile();
}
rafs = new DataOutputStream(new BufferedOutputStream(new FileOutputStream(docProps_appxml_f)));
sb = new StringBuilder();
sb.append("\n");
sb.append("Apache POI\n");
rafs.write(sb.toString().getBytes());
rafs.flush();
rafs.close();
File docProps_corexml_f = new File(filefolder + File.separator + "docProps" + File.separator + "core.xml");
if (!docProps_corexml_f.exists()) {
docProps_corexml_f.createNewFile();
}
rafs = new DataOutputStream(new BufferedOutputStream(new FileOutputStream(docProps_corexml_f)));
sb = new StringBuilder();
sb.append("\n");
sb.append("\n");
//格式化创建时间
SimpleDateFormat datestr = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'");
String date = datestr.format(new Date());
sb.append("").append(date).append("\n");
sb.append("Apache POI\n");
sb.append("");
rafs.write(sb.toString().getBytes());
rafs.flush();
rafs.close();
File xl_sharedStrings_f = new File(filefolder + File.separator + "xl" + File.separator + "sharedStrings.xml");
if (!xl_sharedStrings_f.exists()) {
xl_sharedStrings_f.createNewFile();
}
rafs = new DataOutputStream(new BufferedOutputStream(new FileOutputStream(xl_sharedStrings_f)));
sb = new StringBuilder();
sb.append("\n");
sb.append("");
rafs.write(sb.toString().getBytes());
rafs.flush();
rafs.close();
File xl_styles_f = new File(filefolder + File.separator + "xl" + File.separator + "styles.xml");
if (!xl_styles_f.exists()) {
xl_styles_f.createNewFile();
}
rafs = new DataOutputStream(new BufferedOutputStream(new FileOutputStream(xl_styles_f)));
sb = new StringBuilder();
sb.append("\n");
sb.append("");
rafs.write(sb.toString().getBytes());
rafs.flush();
rafs.close();
File xl_workbook_f = new File(filefolder + File.separator + "xl" + File.separator + "workbook.xml");
if (!xl_workbook_f.exists()) {
xl_workbook_f.createNewFile();
}
rafs = new DataOutputStream(new BufferedOutputStream(new FileOutputStream(xl_workbook_f)));
sb = new StringBuilder();
sb.append("\n");
sb.append("\n");
sb.append("\n");
sb.append("\n");
sb.append("\n");
sb.append("\n");
sb.append("\n");
for (int i=0; i\n");
}
sb.append("\n");
sb.append("\n");
rafs.write(sb.toString().getBytes());
rafs.flush();
rafs.close();
ExecutorService completableFutureExecutor = Executors.newCachedThreadPool();
List> futures = new ArrayList<>();
for (int i = 0; i < 10; i++) {
final int fi = i;
CompletableFuture completableFuture = CompletableFuture.supplyAsync(new Supplier() {
@Override
public String get() {
try {
return createNewSheet(fi);
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
}, completableFutureExecutor);
futures.add(completableFuture);
}
//等待全部完成
CompletableFuture.allOf(futures.toArray(new CompletableFuture[0])).join();
File xl__rels_workbookxml_f = new File(filefolder + File.separator + "xl" + File.separator +"_rels"+ File.separator + "workbook.xml.rels");
if (!xl__rels_workbookxml_f.exists()) {
xl__rels_workbookxml_f.createNewFile();
}
rafs = new DataOutputStream(new BufferedOutputStream(new FileOutputStream(xl__rels_workbookxml_f)));
sb = new StringBuilder();
sb.append("\n");
sb.append("\n");
sb.append("\n");
sb.append("\n");
for (int j = 0; j < futures.size(); j++) {
String val = futures.get(j).get();
sb.append("\n");
}
sb.append("");
rafs.write(sb.toString().getBytes());
rafs.flush();
rafs.close();
File Content_Types_f = new File(filefolder + File.separator + "[Content_Types].xml");
if (!Content_Types_f.exists()) {
Content_Types_f.createNewFile();
}
rafs = new DataOutputStream(new BufferedOutputStream(new FileOutputStream(Content_Types_f)));
sb = new StringBuilder();
sb.append("\n");
sb.append("\n");
sb.append("\n");
sb.append("\n");
sb.append("\n");
sb.append("\n");
sb.append("\n");
sb.append("\n");
sb.append("\n");
for (int j = 0; j < futures.size(); j++) {
String val = futures.get(j).get();
sb.append("\n");
}
sb.append("");
rafs.write(sb.toString().getBytes());
rafs.flush();
rafs.close();
completableFutureExecutor.shutdown();
} catch (Exception e) {
e.printStackTrace();
}
}
public static String createNewSheet(int i) {
String filename = "sheet" + (i+1) + ".xml";
try {
String filefolder = "F:\\xlsxtest222";
File _rels_f = new File(filefolder + File.separator + "xl" + File.separator + "worksheets" + File.separator + filename);
if (!_rels_f.exists()) {
_rels_f.createNewFile();
}
DataOutputStream rafs = new DataOutputStream(new BufferedOutputStream(new FileOutputStream(_rels_f)));
StringBuilder sb = new StringBuilder();
sb.append("\n");
if (i == 0) {
sb.append("\n");
} else {
sb.append("\n");
}
for (int j = 0; j < 100; j++) {
if (j == 0) {
sb.append("\n");
sb.append("column0");
sb.append("column1");
sb.append("column2");
sb.append("column3");
sb.append("column4");
sb.append("column5");
sb.append("column6");
sb.append("column7");
sb.append("column8");
sb.append("column9");
sb.append("column10");
sb.append("\n");
} else {
sb.append("\n");
sb.append("").append(j).append("");
sb.append("").append(String.valueOf(Math.random())).append("");
sb.append("").append(String.valueOf(Math.random())).append("");
sb.append("").append(String.valueOf(Math.random())).append("");
sb.append("").append(String.valueOf(Math.random())).append("");
sb.append("").append(String.valueOf(Math.random())).append("");
sb.append("").append(String.valueOf(Math.random())).append("");
sb.append("").append(String.valueOf(Math.random())).append("");
sb.append("").append(String.valueOf(Math.random())).append("");
sb.append("").append(String.valueOf(Math.random())).append("");
sb.append("").append(String.valueOf(Math.random())).append("");
sb.append("\n");
}
}
sb.append("");
rafs.write(sb.toString().getBytes());
rafs.flush();
rafs.close();
return filename;
} catch(Exception e) {
e.printStackTrace();
return null;
}
}
}
需要使用的依赖包 commons-compress
maven坐标如下:
org.apache.commons
commons-compress
1.20
以上就是全部内容,转载请注明出处。
Original: https://www.cnblogs.com/xiaochuan94/p/15716076.html
Author: 程序员小川
Title: 一次大数据量导出优化–借助xml导出xls、xlsx文件
原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/572944/
转载文章受原作者版权保护。转载请注明原作者出处!