手写OOXML文档——导出xlsx格式表格文档
一、准备工作：
2个js库，另外把样式文件抽离出来
require('file-saver'); import JSZip from 'jszip' import {stylesData,theme1Data} from './xlsxStyles'
转换成OOXML文档：
新建“工作簿1.xlsx”，修改后缀.zip,得到如下目录结构：
内容、属性文档目录：content_Types
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">
<Default Extension="png" ContentType="image/png" />
<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml" />
<Default Extension="xml" ContentType="application/xml" />
<Override PartName="/docProps/app.xml" ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml" />
<Override PartName="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml" />
<Override PartName="/xl/sharedStrings.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml" /> //字符串存储共享文件
<Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml" />
<Override PartName="/xl/theme/theme1.xml" ContentType="application/vnd.openxmlformats-officedocument.theme+xml" />
<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml" />
<Override PartName="/docProps/custom.xml" ContentType="application/vnd.openxmlformats-officedocument.custom-properties+xml" /> //自定义属性文档：如有样式设置
<Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml" /> //遍历：纯数字数据文档或内嵌的字符串
<Override PartName="/xl/worksheets/sheet2.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml" />
<Override PartName="/xl/worksheets/sheet3.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml" />
<Override PartName="/xl/worksheets/sheet4.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml" />
<Override PartName="/xl/worksheets/sheet5.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml" />
<Override PartName="/xl/worksheets/sheet6.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml" />
<Override PartName="/xl/drawings/drawing1.xml" ContentType="application/vnd.openxmlformats-officedocument.drawing+xml" /> //遍历：数据关联文档,如插入图片、表格
<Override PartName="/xl/drawings/drawing2.xml" ContentType="application/vnd.openxmlformats-officedocument.drawing+xml" />
<Override PartName="/xl/drawings/drawing3.xml" ContentType="application/vnd.openxmlformats-officedocument.drawing+xml" />
<Override PartName="/xl/drawings/drawing4.xml" ContentType="application/vnd.openxmlformats-officedocument.drawing+xml" />
<Override PartName="/xl/drawings/drawing5.xml" ContentType="application/vnd.openxmlformats-officedocument.drawing+xml" />
<Override PartName="/xl/drawings/drawing6.xml" ContentType="application/vnd.openxmlformats-officedocument.drawing+xml" />
</Types>
1.1关联文件：
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml" />
<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml" />
<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml" />
</Relationships>
复杂一点的：多个自定义custom.xml文件
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId4" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/custom-properties" Target="docProps/custom.xml" />
...
</Relationships>
1.2.文档属性：
或
多一个自定义xml文档:目测fmtid是随机生成的UUID
<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <Properties xmlns="http://schemas.openxmlformats.org/officeDocument/2006/custom-properties" xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes"> <property fmtid="{D5CDD505-2E9C-101B-9397-08002B2CF9AE}" pid="2" name="KSOProductBuildVer"> <vt:lpwstr>2052-11.1.0.8976</vt:lpwstr> </property> </Properties>
其他2个文件：
app.xml:分页sheet文档,如果是wps生成的信息没有那么全
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Properties xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties" xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes">
<Application>Microsoft Excel</Application>
<DocSecurity>0</DocSecurity>
<ScaleCrop>false</ScaleCrop>
<HeadingPairs>
<vt:vector size="2" baseType="variant">
<vt:variant>
<vt:lpstr>工作表</vt:lpstr>
</vt:variant>
<vt:variant>
<vt:i4>6</vt:i4> //合计总数：6
</vt:variant>
</vt:vector>
</HeadingPairs>
<TitlesOfParts>
<vt:vector size="6" baseType="lpstr"> //合计总数+遍历1-6
<vt:lpstr>Sheet1</vt:lpstr>
<vt:lpstr>Sheet2</vt:lpstr>
<vt:lpstr>Sheet3</vt:lpstr>
<vt:lpstr>Sheet4</vt:lpstr>
<vt:lpstr>Sheet5</vt:lpstr>
<vt:lpstr>Sheet6</vt:lpstr>
</vt:vector>
</TitlesOfParts>
<Company></Company>
<LinksUpToDate>false</LinksUpToDate>
<SharedDoc>false</SharedDoc>
<HyperlinksChanged>false</HyperlinksChanged>
<AppVersion>16.0300</AppVersion>
</Properties>
固定格式的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:dcmitype="http://purl.org/dc/dcmitype/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<dc:creator>zhangsan</dc:creator>
<cp:lastModifiedBy>zhangsan</cp:lastModifiedBy>
<dcterms:created xsi:type="dcterms:W3CDTF">2019-10-21T12:16:00Z</dcterms:created>
<dcterms:modified xsi:type="dcterms:W3CDTF">2019-10-21T12:16:16Z</dcterms:modified>
</cp:coreProperties>
二、核心文件XL：
或
内容不为空多出一个字符串共享文档：
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="841" uniqueCount="217">
<si>
<t>列1</t>
</si>
</sst>
如果是数据不为空，会多出1个drawings文档；
如果有图片，会多出一个media文档；
如果插入表格，会多出一个tables文档。
图形信息使用base64格式存储
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<table xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" id="1" name="表1" displayName="表1" ref="A1:U25"
totalsRowShown="0">
<tableColumns count="21">
<tableColumn id="1" name="列1" dataDxfId="0" />
<tableColumn id="2" name="列2" dataDxfId="1" />
<tableColumn id="3" name="列3" dataDxfId="2" />
</tableColumns>
<tableStyleInfo name="TableStyleMedium2" showFirstColumn="0" showLastColumn="0" showRowStripes="1" showColumnStripes="0" />
</table>
其他：主题文件theme1.xml和外部样式文件styles.xml,数据可以抽离出来单独存放
三、数据输出：存储格式需要区分，并分开存储
/* eslint-disable */ require('file-saver'); import JSZip from 'jszip' import {stylesData,theme1Data} from './xlsxStyles' export function export_txt_to_zip(imgData, chartData ,xlsxName) { // console.log(imgData,"图形"); // console.log(chartData ,"数据"); const chartDataLen = chartData.length || 1 const zip = new JSZip(); const zip_name = xlsxName || 'excel文档'; // 第一层文件夹： const _rels = zip.folder("_rels"); const docProps = zip.folder("docProps");//app.xml core.xml custom.xml const xl = zip.folder("xl"); // sharedStrings.xml styles.xml workbook.xml // 第二层文件夹： const xl_rels = xl.folder("_rels"); const media = xl.folder("media"); //图片2*6=12 const theme = xl.folder("theme");// theme1.xml const drawings = xl.folder("drawings"); // 【1个关系文件夹+数据文件6个】 const worksheets = xl.folder("worksheets"); // 【1个关系文件夹+数据文件6个】 // 第三层文件夹： const drawings_xl_rels = drawings.folder("_rels"); const sheets_xl_rels = worksheets.folder("_rels");//可以不需要关系文档 // 文件内容 //【1】 let xmlData =`<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types"> <Default Extension="png" ContentType="image/png" /> <Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml" /> <Default Extension="xml" ContentType="application/xml" /> <Override PartName="/docProps/app.xml" ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml" /> <Override PartName="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml" /> <Override PartName="/xl/sharedStrings.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml" /> <Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml" /> <Override PartName="/xl/theme/theme1.xml" ContentType="application/vnd.openxmlformats-officedocument.theme+xml" /> <Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml" /> `; //【2】 const relsData = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"> <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml" /> <Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml" /> <Relationship Id="rId3" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml" /> </Relationships> `; //【3】 let appData = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <Properties xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties" xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes"> <Application>表格</Application> <HeadingPairs> <vt:vector size="2" baseType="constiant"> <vt:constiant> <vt:lpstr>工作表</vt:lpstr> </vt:constiant> <vt:constiant> <vt:i4>${chartDataLen}</vt:i4> </vt:constiant> </vt:vector> </HeadingPairs> <TitlesOfParts> <vt:vector size="${chartDataLen}" baseType="lpstr"> `; //【4】 let coreData = `<?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:dcmitype="http://purl.org/dc/dcmitype/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:dcterms="http://purl.org/dc/terms/" xmlns:dcmitype="http://purl.org/dc/dcmitype/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> `; // const userName = JSON.parse(localStorage.getItem("usern")).enname || '创建人' let creatTime = new Date().getFullYear() + '-'; creatTime += (new Date().getMonth() + 1) + '-'; creatTime += new Date().getDate() + 'T'; creatTime += new Date().getHours() + ':'; creatTime += new Date().getMinutes() + ':'; creatTime += new Date().getSeconds() + 'Z'; coreData += ` <dc:creator></dc:creator>`; // 创建人 coreData += ` <cp:lastModifiedBy></cp:lastModifiedBy>`; // 最后修改人 coreData += ` <dcterms:created xsi:type="dcterms:W3CDTF">${creatTime}</dcterms:created>`; // 创建时间 coreData += ` <dcterms:modified xsi:type="dcterms:W3CDTF">${creatTime}</dcterms:modified>`; // 调整时间 coreData += ` </cp:coreProperties>`; //【5】 // const customData = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?> // <Properties xmlns="http://schemas.openxmlformats.org/officeDocument/2006/custom-properties" xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes"> // <property fmtid="{00000000-0001-0000-0${index}00-000000000000}" pid="2" name="KSOProductBuildVer"> // <vt:lpwstr>2052-11.1.0.8976</vt:lpwstr> // </property> // </Properties> // `; //【6】:字符串存储。 let rowNum = 0; let sharedStringsData = ``; //【7】 let xl_relsData = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"> <Relationship Id="rId${chartDataLen+3}" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings" Target="sharedStrings.xml" /> <Relationship Id="rId${chartDataLen+2}" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml" /> <Relationship Id="rId${chartDataLen+1}" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme" Target="theme/theme1.xml" /> `; //【8】 let workbookData = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x15 xr xr6 xr10 xr2" xmlns:x15="http://schemas.microsoft.com/office/spreadsheetml/2010/11/main" xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision" xmlns:xr6="http://schemas.microsoft.com/office/spreadsheetml/2016/revision6" xmlns:xr10="http://schemas.microsoft.com/office/spreadsheetml/2016/revision10" xmlns:xr2="http://schemas.microsoft.com/office/spreadsheetml/2015/revision2"> <fileVersion appName="xl" lastEdited="${chartDataLen+2}" lowestEdited="${chartDataLen+2}" rupBuild="21328" /> <workbookPr /> <bookViews> <workbookView windowWidth="28245" windowHeight="13500" /> </bookViews> <sheets> `; chartData.forEach((item,index)=>{ //【1】 xmlData+=`<Override PartName="/xl/drawings/drawing${index+1}.xml" ContentType="application/vnd.openxmlformats-officedocument.drawing+xml" />`; xmlData+=`<Override PartName="/xl/worksheets/sheet${index+1}.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml" />`; //【3】 appData +=`<vt:lpstr>${item.name}</vt:lpstr>`; //【7】 xl_relsData +=`<Relationship Id="rId${index+1}" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet${index+1}.xml" />`; //【8】 workbookData +=`<sheet name="${item.name}" sheetId="${index+1}" r:id="rId${index+1}" />`; //【9】 let drawing_xml_rels = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?><Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">`; let num = index*2 imgData[index][0].forEach((v,i)=>{ //【9】 num++ drawing_xml_rels += `<Relationship Id="rId${i+1}" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/image" Target="../media/image${num}.png" />`; // 【11】图片文件 media.file(`image${num}.png`, v, {base64: true}); }) drawing_xml_rels += `</Relationships>`; drawings_xl_rels.file(`drawing${index+1}.xml.rels`, drawing_xml_rels); //【12】 const sheet_xml_rels = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"> <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing" Target="../drawings/drawing${index+1}.xml" /> </Relationships> `; sheets_xl_rels.file(`sheet${index+1}.xml.rels`, sheet_xml_rels); const maxLenArr = item.data.map(v=>v.length) const maxLen = Math.max(...maxLenArr) //es6 const dataLen = item.data.length +1 //第一行:+1 const dimension = String.fromCharCode(64 + maxLen) + String(dataLen) //【13】数据：不允许使用特殊符号标记 let drawingData = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <xdr:wsDr xmlns:xdr="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing" xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"> <xdr:twoCellAnchor editAs="oneCell"> <xdr:from> <xdr:col>9</xdr:col> <xdr:colOff>0</xdr:colOff> <xdr:row>0</xdr:row> <xdr:rowOff>161925</xdr:rowOff> </xdr:from> <xdr:to> <xdr:col>15</xdr:col> <xdr:colOff>542925</xdr:colOff> <xdr:row>16</xdr:row> <xdr:rowOff>38100</xdr:rowOff> </xdr:to> <xdr:pic> <xdr:nvPicPr> <xdr:cNvPr id="2" name="图片 1" descr="图1"> <a:extLst> <a:ext uri="{00000000-0001-0000-0${index}00-000000000000}"> <a16:creationId xmlns:a16="http://schemas.microsoft.com/office/drawing/2014/main" id="{00000000-0000-0000-0${index}00-000000000000}" /> </a:ext> </a:extLst> </xdr:cNvPr> <xdr:cNvPicPr> <a:picLocks noChangeAspect="1" /> </xdr:cNvPicPr> </xdr:nvPicPr> <xdr:blipFill> <a:blip xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" r:embed="rId1" /> <a:stretch> <a:fillRect /> </a:stretch> </xdr:blipFill> <xdr:spPr> <a:xfrm> <a:off x="6172200" y="161925" /> <a:ext cx="4657725" cy="2771775" /> </a:xfrm> <a:prstGeom prst="rect"> <a:avLst /> </a:prstGeom> </xdr:spPr> </xdr:pic> <xdr:clientData /> </xdr:twoCellAnchor> <xdr:twoCellAnchor editAs="oneCell"> <xdr:from> <xdr:col>9</xdr:col> <xdr:colOff>9525</xdr:colOff> <xdr:row>18</xdr:row> <xdr:rowOff>9525</xdr:rowOff> </xdr:from> <xdr:to> <xdr:col>15</xdr:col> <xdr:colOff>542925</xdr:colOff> <xdr:row>33</xdr:row> <xdr:rowOff>114300</xdr:rowOff> </xdr:to> <xdr:pic> <xdr:nvPicPr> <xdr:cNvPr id="3" name="图片 2" descr="图2"> <a:extLst> <a:ext uri="{00000000-0001-0000-0${index}00-000000000000}"> <a16:creationId xmlns:a16="http://schemas.microsoft.com/office/drawing/2014/main" id="{00000000-0001-0000-0${index}00-000000000000}" /> </a:ext> </a:extLst> </xdr:cNvPr> <xdr:cNvPicPr> <a:picLocks noChangeAspect="1" /> </xdr:cNvPicPr> </xdr:nvPicPr> <xdr:blipFill> <a:blip xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" r:embed="rId2" /> <a:stretch> <a:fillRect /> </a:stretch> </xdr:blipFill> <xdr:spPr> <a:xfrm> <a:off x="6181725" y="3267075" /> <a:ext cx="4648200" cy="2819400" /> </a:xfrm> <a:prstGeom prst="rect"> <a:avLst /> </a:prstGeom> </xdr:spPr> </xdr:pic> <xdr:clientData /> </xdr:twoCellAnchor> </xdr:wsDr> `; let sheetData = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14ac xr xr2 xr3" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision" xmlns:xr2="http://schemas.microsoft.com/office/spreadsheetml/2015/revision2" xmlns:xr3="http://schemas.microsoft.com/office/spreadsheetml/2016/revision3" xr:uid="{00000000-0001-0000-0${index}00-000000000000}"> <sheetPr /> <dimension ref="A1:${dimension}" /> <sheetViews> <sheetView ${index===0?'tabSelected="1"':''} workbookViewId="0"> <selection activeCell="A1" sqref="A1" /> </sheetView> </sheetViews> <sheetFormatPr defaultColWidth="9" defaultRowHeight="14.25" x14ac:dyDescent="0.2" /> <sheetData>`; let row = 0; item.data.forEach((val)=>{//字符串区分：纯数字与其它 row++ sheetData += `<row r="${row}" spans="1:${maxLen}" x14ac:dyDescent="0.2">`;//第1行开始,每行条数 val.forEach((v,i)=>{ const newVal = String(v).replace(/-/g, '').replace(/</g, '(').replace(/>/g, ')') //去除横杆(减号)+转换尖括号（大于小于号） const abc = String.fromCharCode(65+i)+String(row) var reg = /^[0-9]+.?[0-9]*$/ if (reg.test(v)) { sheetData += `<c r="${abc}" s="1"><v>${v}</v></c>`; }else{ // 非纯数字：需要单独存储 sheetData += `<c r="${abc}" s="2" t="s"><v>${rowNum}</v></c>`; sharedStringsData += `<si><t>${newVal}</t></si>`; rowNum++ } }) sheetData += `</row>`; }) sheetData += `</sheetData> <phoneticPr fontId="1" type="noConversion" /> <pageMargins left="0.75" right="0.75" top="1" bottom="1" header="0.5" footer="0.5" /> <drawing r:id="rId1" /> </worksheet>` worksheets.file(`sheet${index+1}.xml`, sheetData); drawings.file(`drawing${index+1}.xml`, drawingData); }); //【1】 xmlData+=`</Types>`; zip.file(`[Content_Types].xml`, xmlData); //【2】 _rels.file(`.rels`, relsData); //【3】 appData+=`</vt:vector></TitlesOfParts></Properties>`; docProps.file(`app.xml`, appData); //【4】 docProps.file(`core.xml`, coreData); //【5】 // docProps.file(`custom.xml`, customData); //【6】共享字符串 let sharedStrings = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="${rowNum}" uniqueCount="${rowNum}"> `; sharedStrings += sharedStringsData; sharedStrings +=`</sst>`; xl.file(`sharedStrings.xml`, sharedStrings); //【7】 xl_relsData += `</Relationships>`; xl_rels.file(`workbook.xml.rels`, xl_relsData); //【8】 workbookData +=`</sheets><calcPr calcId="144525" /></workbook>`; xl.file(`workbook.xml`, workbookData); // 样式与主题 xl.file(`styles.xml`, stylesData); theme.file(`theme1.xml`, theme1Data); // 导出文件 zip.generateAsync({type:"blob"}).then((blob) => { saveAs(blob, `${zip_name}.xlsx`) }, (err) => { alert('导出失败') }) }
样式和主题抽离：
const stylesData =`<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14ac x16r2 xr" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" xmlns:x16r2="http://schemas.microsoft.com/office/spreadsheetml/2015/02/main" xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision"> <fonts count="2" x14ac:knownFonts="1"> <font> <sz val="11" /> <color theme="1" /> <name val="等线" /> <family val="2" /> <charset val="134" /> <scheme val="minor" /> </font> <font> <sz val="9" /> <name val="等线" /> <family val="2" /> <charset val="134" /> <scheme val="minor" /> </font> </fonts> <fills count="2"> <fill> <patternFill patternType="none" /> </fill> <fill> <patternFill patternType="gray125" /> </fill> </fills> <borders count="2"> <border> <left /> <right /> <top /> <bottom /> <diagonal /> </border> <border> <left style="thin"> <color auto="1" /> </left> <right style="thin"> <color auto="1" /> </right> <top style="thin"> <color auto="1" /> </top> <bottom style="thin"> <color auto="1" /> </bottom> <diagonal /> </border> </borders> <cellStyleXfs count="1"> <xf numFmtId="0" fontId="0" fillId="0" borderId="0"> <alignment vertical="center" /> </xf> </cellStyleXfs> <cellXfs count="3"> <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"> <alignment vertical="center" /> </xf> <xf numFmtId="0" fontId="0" fillId="0" borderId="1" xfId="0" applyBorder="1"> <alignment vertical="center" /> </xf> <xf numFmtId="0" fontId="0" fillId="0" borderId="1" xfId="0" applyFill="1" applyBorder="1" applyAlignment="1"> <alignment vertical="center" /> </xf> </cellXfs> <cellStyles count="1"> <cellStyle name="常规" xfId="0" builtinId="0" /> </cellStyles> <dxfs count="0" /> <tableStyles count="0" defaultTableStyle="TableStyleMedium2" defaultPivotStyle="PivotStyleLight16" /> <extLst> <ext uri="{00000000-0000-0000-0000-000000000000}" xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"> <x14:slicerStyles defaultSlicerStyle="SlicerStyleLight1" /> </ext> <ext uri="{00000000-0001-0000-0000-000000000000}" xmlns:x15="http://schemas.microsoft.com/office/spreadsheetml/2010/11/main"> <x15:timelineStyles defaultTimelineStyle="TimeSlicerStyleLight1" /> </ext> </extLst> </styleSheet> `; const theme1Data = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <a:theme xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" name="Office 主题"> <a:themeElements> <a:clrScheme name="Office"> <a:dk1> <a:sysClr val="windowText" lastClr="000000" /> </a:dk1> <a:lt1> <a:sysClr val="window" lastClr="FFFFFF" /> </a:lt1> <a:dk2> <a:srgbClr val="44546A" /> </a:dk2> <a:lt2> <a:srgbClr val="E7E6E6" /> </a:lt2> <a:accent1> <a:srgbClr val="4472C4" /> </a:accent1> <a:accent2> <a:srgbClr val="ED7D31" /> </a:accent2> <a:accent3> <a:srgbClr val="A5A5A5" /> </a:accent3> <a:accent4> <a:srgbClr val="FFC000" /> </a:accent4> <a:accent5> <a:srgbClr val="5B9BD5" /> </a:accent5> <a:accent6> <a:srgbClr val="70AD47" /> </a:accent6> <a:hlink> <a:srgbClr val="0563C1" /> </a:hlink> <a:folHlink> <a:srgbClr val="954F72" /> </a:folHlink> </a:clrScheme> <a:fontScheme name="Office"> <a:majorFont> <a:latin typeface="Calibri Light" panose="020F0302020204030204" /> <a:ea typeface="" /> <a:cs typeface="" /> <a:font script="Jpan" typeface="游ゴシック Light" /> <a:font script="Hang" typeface="맑은 고딕" /> <a:font script="Hans" typeface="等线 Light" /> <a:font script="Hant" typeface="新細明體" /> <a:font script="Arab" typeface="Times New Roman" /> <a:font script="Hebr" typeface="Times New Roman" /> <a:font script="Thai" typeface="Tahoma" /> <a:font script="Ethi" typeface="Nyala" /> <a:font script="Beng" typeface="Vrinda" /> <a:font script="Gujr" typeface="Shruti" /> <a:font script="Khmr" typeface="MoolBoran" /> <a:font script="Knda" typeface="Tunga" /> <a:font script="Guru" typeface="Raavi" /> <a:font script="Cans" typeface="Euphemia" /> <a:font script="Cher" typeface="Plantagenet Cherokee" /> <a:font script="Yiii" typeface="Microsoft Yi Baiti" /> <a:font script="Tibt" typeface="Microsoft Himalaya" /> <a:font script="Thaa" typeface="MV Boli" /> <a:font script="Deva" typeface="Mangal" /> <a:font script="Telu" typeface="Gautami" /> <a:font script="Taml" typeface="Latha" /> <a:font script="Syrc" typeface="Estrangelo Edessa" /> <a:font script="Orya" typeface="Kalinga" /> <a:font script="Mlym" typeface="Kartika" /> <a:font script="Laoo" typeface="DokChampa" /> <a:font script="Sinh" typeface="Iskoola Pota" /> <a:font script="Mong" typeface="Mongolian Baiti" /> <a:font script="Viet" typeface="Times New Roman" /> <a:font script="Uigh" typeface="Microsoft Uighur" /> <a:font script="Geor" typeface="Sylfaen" /> <a:font script="Armn" typeface="Arial" /> <a:font script="Bugi" typeface="Leelawadee UI" /> <a:font script="Bopo" typeface="Microsoft JhengHei" /> <a:font script="Java" typeface="Javanese Text" /> <a:font script="Lisu" typeface="Segoe UI" /> <a:font script="Mymr" typeface="Myanmar Text" /> <a:font script="Nkoo" typeface="Ebrima" /> <a:font script="Olck" typeface="Nirmala UI" /> <a:font script="Osma" typeface="Ebrima" /> <a:font script="Phag" typeface="Phagspa" /> <a:font script="Syrn" typeface="Estrangelo Edessa" /> <a:font script="Syrj" typeface="Estrangelo Edessa" /> <a:font script="Syre" typeface="Estrangelo Edessa" /> <a:font script="Sora" typeface="Nirmala UI" /> <a:font script="Tale" typeface="Microsoft Tai Le" /> <a:font script="Talu" typeface="Microsoft New Tai Lue" /> <a:font script="Tfng" typeface="Ebrima" /> </a:majorFont> <a:minorFont> <a:latin typeface="Calibri" panose="020F0502020204030204" /> <a:ea typeface="" /> <a:cs typeface="" /> <a:font script="Jpan" typeface="游ゴシック" /> <a:font script="Hang" typeface="맑은 고딕" /> <a:font script="Hans" typeface="等线" /> <a:font script="Hant" typeface="新細明體" /> <a:font script="Arab" typeface="Arial" /> <a:font script="Hebr" typeface="Arial" /> <a:font script="Thai" typeface="Tahoma" /> <a:font script="Ethi" typeface="Nyala" /> <a:font script="Beng" typeface="Vrinda" /> <a:font script="Gujr" typeface="Shruti" /> <a:font script="Khmr" typeface="DaunPenh" /> <a:font script="Knda" typeface="Tunga" /> <a:font script="Guru" typeface="Raavi" /> <a:font script="Cans" typeface="Euphemia" /> <a:font script="Cher" typeface="Plantagenet Cherokee" /> <a:font script="Yiii" typeface="Microsoft Yi Baiti" /> <a:font script="Tibt" typeface="Microsoft Himalaya" /> <a:font script="Thaa" typeface="MV Boli" /> <a:font script="Deva" typeface="Mangal" /> <a:font script="Telu" typeface="Gautami" /> <a:font script="Taml" typeface="Latha" /> <a:font script="Syrc" typeface="Estrangelo Edessa" /> <a:font script="Orya" typeface="Kalinga" /> <a:font script="Mlym" typeface="Kartika" /> <a:font script="Laoo" typeface="DokChampa" /> <a:font script="Sinh" typeface="Iskoola Pota" /> <a:font script="Mong" typeface="Mongolian Baiti" /> <a:font script="Viet" typeface="Arial" /> <a:font script="Uigh" typeface="Microsoft Uighur" /> <a:font script="Geor" typeface="Sylfaen" /> <a:font script="Armn" typeface="Arial" /> <a:font script="Bugi" typeface="Leelawadee UI" /> <a:font script="Bopo" typeface="Microsoft JhengHei" /> <a:font script="Java" typeface="Javanese Text" /> <a:font script="Lisu" typeface="Segoe UI" /> <a:font script="Mymr" typeface="Myanmar Text" /> <a:font script="Nkoo" typeface="Ebrima" /> <a:font script="Olck" typeface="Nirmala UI" /> <a:font script="Osma" typeface="Ebrima" /> <a:font script="Phag" typeface="Phagspa" /> <a:font script="Syrn" typeface="Estrangelo Edessa" /> <a:font script="Syrj" typeface="Estrangelo Edessa" /> <a:font script="Syre" typeface="Estrangelo Edessa" /> <a:font script="Sora" typeface="Nirmala UI" /> <a:font script="Tale" typeface="Microsoft Tai Le" /> <a:font script="Talu" typeface="Microsoft New Tai Lue" /> <a:font script="Tfng" typeface="Ebrima" /> </a:minorFont> </a:fontScheme> <a:fmtScheme name="Office"> <a:fillStyleLst> <a:solidFill> <a:schemeClr val="phClr" /> </a:solidFill> <a:gradFill rotWithShape="1"> <a:gsLst> <a:gs pos="0"> <a:schemeClr val="phClr"> <a:lumMod val="110000" /> <a:satMod val="105000" /> <a:tint val="67000" /> </a:schemeClr> </a:gs> <a:gs pos="50000"> <a:schemeClr val="phClr"> <a:lumMod val="105000" /> <a:satMod val="103000" /> <a:tint val="73000" /> </a:schemeClr> </a:gs> <a:gs pos="100000"> <a:schemeClr val="phClr"> <a:lumMod val="105000" /> <a:satMod val="109000" /> <a:tint val="81000" /> </a:schemeClr> </a:gs> </a:gsLst> <a:lin ang="5400000" scaled="0" /> </a:gradFill> <a:gradFill rotWithShape="1"> <a:gsLst> <a:gs pos="0"> <a:schemeClr val="phClr"> <a:satMod val="103000" /> <a:lumMod val="102000" /> <a:tint val="94000" /> </a:schemeClr> </a:gs> <a:gs pos="50000"> <a:schemeClr val="phClr"> <a:satMod val="110000" /> <a:lumMod val="100000" /> <a:shade val="100000" /> </a:schemeClr> </a:gs> <a:gs pos="100000"> <a:schemeClr val="phClr"> <a:lumMod val="99000" /> <a:satMod val="120000" /> <a:shade val="78000" /> </a:schemeClr> </a:gs> </a:gsLst> <a:lin ang="5400000" scaled="0" /> </a:gradFill> </a:fillStyleLst> <a:lnStyleLst> <a:ln w="6350" cap="flat" cmpd="sng" algn="ctr"> <a:solidFill> <a:schemeClr val="phClr" /> </a:solidFill> <a:prstDash val="solid" /> <a:miter lim="800000" /> </a:ln> <a:ln w="12700" cap="flat" cmpd="sng" algn="ctr"> <a:solidFill> <a:schemeClr val="phClr" /> </a:solidFill> <a:prstDash val="solid" /> <a:miter lim="800000" /> </a:ln> <a:ln w="19050" cap="flat" cmpd="sng" algn="ctr"> <a:solidFill> <a:schemeClr val="phClr" /> </a:solidFill> <a:prstDash val="solid" /> <a:miter lim="800000" /> </a:ln> </a:lnStyleLst> <a:effectStyleLst> <a:effectStyle> <a:effectLst /> </a:effectStyle> <a:effectStyle> <a:effectLst /> </a:effectStyle> <a:effectStyle> <a:effectLst> <a:outerShdw blurRad="57150" dist="19050" dir="5400000" algn="ctr" rotWithShape="0"> <a:srgbClr val="000000"> <a:alpha val="63000" /> </a:srgbClr> </a:outerShdw> </a:effectLst> </a:effectStyle> </a:effectStyleLst> <a:bgFillStyleLst> <a:solidFill> <a:schemeClr val="phClr" /> </a:solidFill> <a:solidFill> <a:schemeClr val="phClr"> <a:tint val="95000" /> <a:satMod val="170000" /> </a:schemeClr> </a:solidFill> <a:gradFill rotWithShape="1"> <a:gsLst> <a:gs pos="0"> <a:schemeClr val="phClr"> <a:tint val="93000" /> <a:satMod val="150000" /> <a:shade val="98000" /> <a:lumMod val="102000" /> </a:schemeClr> </a:gs> <a:gs pos="50000"> <a:schemeClr val="phClr"> <a:tint val="98000" /> <a:satMod val="130000" /> <a:shade val="90000" /> <a:lumMod val="103000" /> </a:schemeClr> </a:gs> <a:gs pos="100000"> <a:schemeClr val="phClr"> <a:shade val="63000" /> <a:satMod val="120000" /> </a:schemeClr> </a:gs> </a:gsLst> <a:lin ang="5400000" scaled="0" /> </a:gradFill> </a:bgFillStyleLst> </a:fmtScheme> </a:themeElements> <a:objectDefaults /> <a:extraClrSchemeLst /> <a:extLst> <a:ext uri="{00000000-0001-0000-0000-000000000000}"> <thm15:themeFamily xmlns:thm15="http://schemas.microsoft.com/office/thememl/2012/main" name="Office Theme" id="{00000000-0000-0000-0000-000000000000}" vid="{00000000-0001-0000-0000-000000000000}" /> </a:ext> </a:extLst> </a:theme> `; export { stylesData, theme1Data }
-end-
