一次大数据量导出优化--借助xml导出xls、xlsx文件

最近遇到一个问题，线上生产环境某个功能导出数据到excel文件非常缓慢，几万数据导十多分钟都导不出来，导出慢的原因一是主表A数据量太大，接近2亿，另外里面部分数据来自于另外一张表B，B表也是几千万的数据量，数据库层面能做的优化已经做了，视图、索引这些工具都上了（没有分表是一开始项目设计阶段就没考虑，后面也没有专人维护，是另外一段故事了，这里不展开描述），但是依旧很慢，那就只能改导出代码了。
【一次大数据量导出优化--借助xml导出xls、xlsx文件】项目原来使用的是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));} elseCellUtil.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("<?xml version=\"1.0\"?>\n");sb.append("<?mso-application progid=\"Excel.Sheet\"?>\n");sb.append("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\n");sb.append("xmlns:o=\"urn:schemas-microsoft-com:office:office\"\n");sb.append(" xmlns:x=\"urn:schemas-microsoft-com:office:excel\"\n");sb.append(" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"\n");sb.append(" xmlns:html=\"http://www.w3.org/TR/REC-html40\">\n");sb.append(" <Styles>\n");sb.append("<Style ss:ID=\"Default\" ss:Name=\"Normal\">\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("</Style>\n");sb.append(" </Styles>\n");int maxRow = 10;int maxCol = 10;for (int i = 0; i < 5; i++) {sb.append("<Worksheet ss:Name=\"第").append(i).append("页\">\n");sb.append("<Table ss:ExpandedColumnCount=\"").append(maxRow).append("\" ss:ExpandedRowCount=\"");sb.append(maxCol).append("\" x:FullColumns=\"1\" x:FullRows=\"1\">\n");for (int j = 0; j < maxRow; j++) {sb.append("<Row>\n");sb.append("<Cell><Data ss:Type=\"String\">").append("还好").append(i).append("</Data></Cell>\n");sb.append("<Cell><Data ss:Type=\"String\">").append(String.valueOf(Math.random())).append("</Data></Cell>\n");sb.append("<Cell><Data ss:Type=\"String\">").append(String.valueOf(Math.random())).append("</Data></Cell>\n");sb.append("<Cell><Data ss:Type=\"String\">").append(String.valueOf(Math.random())).append("</Data></Cell>\n");sb.append("<Cell><Data ss:Type=\"String\">").append(String.valueOf(Math.random())).append("</Data></Cell>\n");sb.append("<Cell><Data ss:Type=\"String\">").append(String.valueOf(Math.random())).append("</Data></Cell>\n");sb.append("<Cell><Data ss:Type=\"String\">").append(String.valueOf(Math.random())).append("</Data></Cell>\n");sb.append("<Cell><Data ss:Type=\"String\">").append(String.valueOf(Math.random())).append("</Data></Cell>\n");sb.append("<Cell><Data ss:Type=\"String\">").append(String.valueOf(Math.random())).append("</Data></Cell>\n");sb.append("<Cell><Data ss:Type=\"String\">").append(String.valueOf(Math.random())).append("</Data></Cell>\n");sb.append("</Row>\n");}sb.append("</Table>\n");sb.append("<WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">\n");sb.append("<ProtectObjects>False</ProtectObjects>\n");sb.append("<ProtectScenarios>False</ProtectScenarios>\n");sb.append("</WorksheetOptions>\n");sb.append("</Worksheet>\n");rafs.write(sb.toString().getBytes());rafs.flush();sb = null;sb = new StringBuffer();}sb.append("</Workbook>\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/.relsdocProps/app.xmldocProps/core.xmlxl/_rels/workbook.xml.relsxl/worksheets/sheet1.xml......xl/worksheets/sheetn.xmlxl/sharedStrings.xmlxl/styles.xmlxl/workbook.xml[Content_Types].xml_rels/.rels文件内容是固定的，可以直接写。
<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"> <Relationship Id="rId1" Target="xl/workbook.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument"/> <Relationship Id="rId2" Target="docProps/app.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties"/> <Relationship Id="rId3" Target="docProps/core.xml" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties"/> </Relationships>docProps/app.xml文件内容是固定的，可以直接写。
<?xml version="1.0" encoding="UTF-8"?> <Properties xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties"><Application>Apache POI</Application></Properties>docProps/core.xml文件里面需要注意下创建时间，这个时间格式需要特殊处理下。
<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <cp:coreProperties xmlns:cp="http://schemas.openxmlformats.org/package/2006/metadata/core-properties" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:dcterms="http://purl.org/dc/terms/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <dcterms:created xsi:type="dcterms:W3CDTF">2021-12-16T12:10:02Z</dcterms:created> <dc:creator>Apache POI</dc:creator> </cp:coreProperties>xl/sharedStrings.xml文件内容是固定的，可以直接写。
<?xml version="1.0" encoding="UTF-8"?> <sst count="0" uniqueCount="0" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"/>xl/styles.xml样式文件，需要自定义样式的可以提前通过代码生成。
<?xml version="1.0" encoding="UTF-8"?> <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><numFmts count="0"/><fonts count="1"><font><sz val="11.0"/><color indexed="8"/><name val="Calibri"/><family val="2"/><scheme val="minor"/></font></fonts><fills count="2"><fill><patternFill patternType="none"/></fill><fill><patternFill patternType="darkGray"/></fill></fills><borders count="1"><border><left/><right/><top/><bottom/><diagonal/></border></borders><cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"/></cellStyleXfs><cellXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/></cellXfs></styleSheet>xl/workbook.xmlsheet页的信息文件，每个sheet页都有一个id，是一一对应的关系。name是表名，可以自定义，sheetId从1开始，r:id从rId3开始。
<?xml version="1.0" encoding="UTF-8"?><workbook xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><workbookPr date1904="false"/><bookViews><workbookView activeTab="0"/></bookViews><sheets><sheet sheetId="1" r:id="rId3" name="第0页"/><sheet sheetId="2" r:id="rId4" name="第1页"/><sheet sheetId="3" r:id="rId5" name="第2页"/><sheet sheetId="4" r:id="rId6" name="第3页"/><sheet sheetId="5" r:id="rId7" name="第4页"/><sheet sheetId="6" r:id="rId8" name="第5页"/><sheet sheetId="7" r:id="rId9" name="第6页"/><sheet sheetId="8" r:id="rId10" name="第7页"/><sheet sheetId="9" r:id="rId11" name="第8页"/><sheet sheetId="10" r:id="rId12" name="第9页"/></sheets></workbook>xl/_rels/workbook.xml.relssheet的依赖文件信息，rId1和rId2都是固定的，从rId3开始对应创建的sheet页文件路径。
<?xml version="1.0" encoding="UTF-8" standalone="yes"?><Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Target="sharedStrings.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings"/><Relationship Id="rId2" Target="styles.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles"/><Relationship Id="rId3" Target="worksheets/sheet1.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet"/><Relationship Id="rId4" Target="worksheets/sheet2.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet"/><Relationship Id="rId5" Target="worksheets/sheet3.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet"/><Relationship Id="rId6" Target="worksheets/sheet4.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet"/><Relationship Id="rId7" Target="worksheets/sheet5.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet"/><Relationship Id="rId8" Target="worksheets/sheet6.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet"/><Relationship Id="rId9" Target="worksheets/sheet7.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet"/><Relationship Id="rId10" Target="worksheets/sheet8.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet"/><Relationship Id="rId11" Target="worksheets/sheet9.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet"/><Relationship Id="rId12" Target="worksheets/sheet10.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet"/></Relationships>[Content_Types].xml汇总文件，里面包含了xlsx文件依赖的相关xml文件信息
<?xml version="1.0" encoding="UTF-8" standalone="yes"?><Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types"><Default ContentType="application/vnd.openxmlformats-package.relationships+xml" Extension="rels"/><Default ContentType="application/xml" Extension="xml"/><Override ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml" PartName="/docProps/app.xml"/><Override ContentType="application/vnd.openxmlformats-package.core-properties+xml" PartName="/docProps/core.xml"/><Override ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml" PartName="/xl/sharedStrings.xml"/><Override ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml" PartName="/xl/styles.xml"/><Override ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml" PartName="/xl/workbook.xml"/><Override ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml" PartName="/xl/worksheets/sheet1.xml"/><Override ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml" PartName="/xl/worksheets/sheet2.xml"/><Override ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml" PartName="/xl/worksheets/sheet3.xml"/><Override ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml" PartName="/xl/worksheets/sheet4.xml"/><Override ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml" PartName="/xl/worksheets/sheet5.xml"/><Override ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml" PartName="/xl/worksheets/sheet6.xml"/><Override ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml" PartName="/xl/worksheets/sheet7.xml"/><Override ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml" PartName="/xl/worksheets/sheet8.xml"/><Override ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml" PartName="/xl/worksheets/sheet9.xml"/><Override ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml" PartName="/xl/worksheets/sheet10.xml"/></Types>xl/worksheets/sheet1.xmlsheet页数据文件，其中第一个sheet页是默认选中打开的sheet页，会加上tabSelected="true"属性，只能在其中一个xml文件里设置，不能给多个sheet页xml文件设置。行号从1开始，列号从大写的英文字母加数字(也是从1开始)组成。cols标签里的col标签是设置列宽，列号通过min="1" max="1"这两个标签定义，也是从1开始。合并单元格使用<mergeCells></mergeCells>标签，跟在</sheetData>标签后面，比如想要合并A1到D1，可以这样写<mergeCells><mergeCell ref="A1:D1"/></mergeCells> 。
<?xml version=\"1.0\" encoding=\"UTF-8\"?><worksheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\"><dimension ref=\"A1\"/><sheetViews><sheetView workbookViewId=\"0\" tabSelected=\"true\"/></sheetViews><sheetFormatPr defaultRowHeight=\"15.0\"/><cols><col min="1" max="1" width="8.71875" customWidth="true"/></cols><sheetData><row r="1"><c r=\"A1\" t=\"inlineStr\"><is><t>column0</t></is></c><c r=\"B1\" t=\"inlineStr\"><is><t>column1</t></is></c><c r=\"C1\" t=\"inlineStr\"><is><t>column2</t></is></c><c r=\"D1\" t=\"inlineStr\"><is><t>column3</t></is></c><c r=\"E1\" t=\"inlineStr\"><is><t>column4</t></is></c><c r=\"F1\" t=\"inlineStr\"><is><t>column5</t></is></c><c r=\"G1\" t=\"inlineStr\"><is><t>column6</t></is></c><c r=\"H1\" t=\"inlineStr\"><is><t>column7</t></is></c><c r=\"I1\" t=\"inlineStr\"><is><t>column8</t></is></c><c r=\"J1\" t=\"inlineStr\"><is><t>column9</t></is></c><c r=\"K1\" t=\"inlineStr\"><is><t>column10</t></is></c>......</sheetData><pageMargins bottom=\"0.75\" footer=\"0.3\" header=\"0.3\" left=\"0.7\" right=\"0.7\" top=\"0.75\"/></worksheet>以上就是一个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("<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>\n");sb.append("<Relationships xmlns=\"http://schemas.openxmlformats.org/package/2006/relationships\">\n");sb.append("<Relationship Id=\"rId1\" Target=\"xl/workbook.xml\" Type=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument\"/>\n");sb.append("<Relationship Id=\"rId2\" Target=\"docProps/app.xml\" Type=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties\"/>\n");sb.append("<Relationship Id=\"rId3\" Target=\"docProps/core.xml\" Type=\"http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties\"/>\n");sb.append("</Relationships>");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("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n");sb.append("<Properties xmlns=\"http://schemas.openxmlformats.org/officeDocument/2006/extended-properties\"><Application>Apache POI</Application></Properties>\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("<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>\n");sb.append("<cp:coreProperties xmlns:cp=\"http://schemas.openxmlformats.org/package/2006/metadata/core-properties\" xmlns:dc=\"http://purl.org/dc/elements/1.1/\" xmlns:dcterms=\"http://purl.org/dc/terms/\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\">\n");//格式化创建时间SimpleDateFormat datestr = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'");String date = datestr.format(new Date());sb.append("<dcterms:created xsi:type=\"dcterms:W3CDTF\">").append(date).append("</dcterms:created>\n");sb.append("<dc:creator>Apache POI</dc:creator>\n");sb.append("</cp:coreProperties>");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("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n");sb.append("<sst count=\"0\" uniqueCount=\"0\" xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\"/>");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("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n");sb.append("<styleSheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\"><numFmts count=\"0\"/><fonts count=\"1\"><font><sz val=\"11.0\"/><color indexed=\"8\"/><name val=\"Calibri\"/><family val=\"2\"/><scheme val=\"minor\"/></font></fonts><fills count=\"2\"><fill><patternFill patternType=\"none\"/></fill><fill><patternFill patternType=\"darkGray\"/></fill></fills><borders count=\"1\"><border><left/><right/><top/><bottom/><diagonal/></border></borders><cellStyleXfs count=\"1\"><xf numFmtId=\"0\" fontId=\"0\" fillId=\"0\" borderId=\"0\"/></cellStyleXfs><cellXfs count=\"1\"><xf numFmtId=\"0\" fontId=\"0\" fillId=\"0\" borderId=\"0\" xfId=\"0\"/></cellXfs></styleSheet>");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("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n");sb.append("<workbook xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\" xmlns:r=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships\">\n");sb.append("<workbookPr date1904=\"false\"/>\n");sb.append("<bookViews>\n");sb.append("<workbookView activeTab=\"0\"/>\n");sb.append("</bookViews>\n");sb.append("<sheets>\n");for (int i=0; i<10;i++) {sb.append("<sheet name=\"第").append(i).append("页\" r:id=\"rId").append(i+3).append("\" sheetId=\"").append(i+1).append("\"/>\n");}sb.append("</sheets>\n");sb.append("</workbook>\n");rafs.write(sb.toString().getBytes());rafs.flush();rafs.close();ExecutorService completableFutureExecutor = Executors.newCachedThreadPool();List<CompletableFuture<String>> futures = new ArrayList<>();for (int i = 0; i < 10; i++) {final int fi = i;CompletableFuture<String> completableFuture = CompletableFuture.supplyAsync(new Supplier<String>() {@Overridepublic 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("<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>\n");sb.append("<Relationships xmlns=\"http://schemas.openxmlformats.org/package/2006/relationships\">\n");sb.append("<Relationship Id=\"rId1\" Target=\"sharedStrings.xml\" Type=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings\"/>\n");sb.append("<Relationship Id=\"rId2\" Target=\"styles.xml\" Type=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles\"/>\n");for (int j = 0; j < futures.size(); j++) {String val = futures.get(j).get();sb.append("<Relationship Id=\"rId").append(j+3).append("\" Target=\"worksheets/").append(val).append("\" Type=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet\"/>\n");}sb.append("</Relationships>");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("<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>\n");sb.append("<Types xmlns=\"http://schemas.openxmlformats.org/package/2006/content-types\">\n");sb.append("<Default ContentType=\"application/vnd.openxmlformats-package.relationships+xml\" Extension=\"rels\"/>\n");sb.append("<Default ContentType=\"application/xml\" Extension=\"xml\"/>\n");sb.append("<Override ContentType=\"application/vnd.openxmlformats-officedocument.extended-properties+xml\" PartName=\"/docProps/app.xml\"/>\n");sb.append("<Override ContentType=\"application/vnd.openxmlformats-package.core-properties+xml\" PartName=\"/docProps/core.xml\"/>\n");sb.append("<Override ContentType=\"application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml\" PartName=\"/xl/sharedStrings.xml\"/>\n");sb.append("<Override ContentType=\"application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml\" PartName=\"/xl/styles.xml\"/>\n");sb.append("<Override ContentType=\"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml\" PartName=\"/xl/workbook.xml\"/>\n");for (int j = 0; j < futures.size(); j++) {String val = futures.get(j).get();sb.append("<Override ContentType=\"application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml\" PartName=\"/xl/worksheets/").append(val).append("\"/>\n");}sb.append("</Types>");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("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n");if (i == 0) {sb.append("<worksheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\"><dimension ref=\"A1\"/><sheetViews><sheetView workbookViewId=\"0\" tabSelected=\"true\"/></sheetViews><sheetFormatPr defaultRowHeight=\"15.0\"/><sheetData>\n");} else {sb.append("<worksheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\"><dimension ref=\"A1\"/><sheetViews><sheetView workbookViewId=\"0\"/></sheetViews><sheetFormatPr defaultRowHeight=\"15.0\"/><sheetData>\n");}for (int j = 0; j < 100; j++) {if (j == 0) {sb.append("<row r=\"").append(j+1).append("\">\n");sb.append("<c r=\"A1\" t=\"inlineStr\"><is><t>column0</t></is></c>");sb.append("<c r=\"B1\" t=\"inlineStr\"><is><t>column1</t></is></c>");sb.append("<c r=\"C1\" t=\"inlineStr\"><is><t>column2</t></is></c>");sb.append("<c r=\"D1\" t=\"inlineStr\"><is><t>column3</t></is></c>");sb.append("<c r=\"E1\" t=\"inlineStr\"><is><t>column4</t></is></c>");sb.append("<c r=\"F1\" t=\"inlineStr\"><is><t>column5</t></is></c>");sb.append("<c r=\"G1\" t=\"inlineStr\"><is><t>column6</t></is></c>");sb.append("<c r=\"H1\" t=\"inlineStr\"><is><t>column7</t></is></c>");sb.append("<c r=\"I1\" t=\"inlineStr\"><is><t>column8</t></is></c>");sb.append("<c r=\"J1\" t=\"inlineStr\"><is><t>column9</t></is></c>");sb.append("<c r=\"K1\" t=\"inlineStr\"><is><t>column10</t></is></c>");sb.append("</row>\n");} else {sb.append("<row r=\"").append(j+1).append("\">\n");sb.append("<c r=\"A").append(j+1).append("\" t=\"inlineStr\"><is><t>").append(j).append("</t></is></c>");sb.append("<c r=\"B").append(j+1).append("\" t=\"inlineStr\"><is><t>").append(String.valueOf(Math.random())).append("</t></is></c>");sb.append("<c r=\"C").append(j+1).append("\" t=\"inlineStr\"><is><t>").append(String.valueOf(Math.random())).append("</t></is></c>");sb.append("<c r=\"D").append(j+1).append("\" t=\"inlineStr\"><is><t>").append(String.valueOf(Math.random())).append("</t></is></c>");sb.append("<c r=\"E").append(j+1).append("\" t=\"inlineStr\"><is><t>").append(String.valueOf(Math.random())).append("</t></is></c>");sb.append("<c r=\"F").append(j+1).append("\" t=\"inlineStr\"><is><t>").append(String.valueOf(Math.random())).append("</t></is></c>");sb.append("<c r=\"G").append(j+1).append("\" t=\"inlineStr\"><is><t>").append(String.valueOf(Math.random())).append("</t></is></c>");sb.append("<c r=\"H").append(j+1).append("\" t=\"inlineStr\"><is><t>").append(String.valueOf(Math.random())).append("</t></is></c>");sb.append("<c r=\"I").append(j+1).append("\" t=\"inlineStr\"><is><t>").append(String.valueOf(Math.random())).append("</t></is></c>");sb.append("<c r=\"J").append(j+1).append("\" t=\"inlineStr\"><is><t>").append(String.valueOf(Math.random())).append("</t></is></c>");sb.append("<c r=\"K").append(j+1).append("\" t=\"inlineStr\"><is><t>").append(String.valueOf(Math.random())).append("</t></is></c>");sb.append("</row>\n");}}sb.append("</sheetData><pageMargins bottom=\"0.75\" footer=\"0.3\" header=\"0.3\" left=\"0.7\" right=\"0.7\" top=\"0.75\"/></worksheet>");rafs.write(sb.toString().getBytes());rafs.flush();rafs.close();return filename;} catch(Exception e) {e.printStackTrace();return null;}	}	}

需要使用的依赖包commons-compressmaven坐标如下：

<dependency><groupId>org.apache.commons</groupId><artifactId>commons-compress</artifactId><version>1.20</version></dependency>

以上就是全部内容，转载请注明出处。