最近遇到一個問題，線上生產環境某個功能匯出資料到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("<?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/.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檔案內容是固定的，可以直接寫，
<?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" custom/>
</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>() {
@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("<?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>
以上就是全部內容，轉載請注明出處，
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/388910.html
標籤：Java
下一篇：Spring-AOP學習筆記