自定义表格可根据自己需要查询需要的数据导出表格。
目前还是初学,有问题多指正,欢迎互相讨论。
const dataObj = {
fields: {},
subLists: [],
};
const createRow = () => {
let str = '';
for (let i = 0, len = dataObj.subLists.length; i < len; i += 1) {
str += '<Row>'
+ '<Cell ss:StyleID="s61"><Data ss:Type="String">' + dataObj.subLists[i].vendor + '</Data></Cell>'
+ '<Cell ss:StyleID="s61"><Data ss:Type="String">' + dataObj.subLists[i].date + '</Data></Cell>'
+ '<Cell ss:StyleID="s61"><Data ss:Type="String">' + dataObj.subLists[i].glNumber + '</Data></Cell>'
+ '<Cell ss:StyleID="s61"><Data ss:Type="String">' + dataObj.subLists[i].remark + '</Data></Cell>'
+ '<Cell ss:StyleID="s61"><Data ss:Type="String">' + dataObj.subLists[i].debitAmount + '</Data></Cell>'
+ '<Cell ss:StyleID="s61"><Data ss:Type="String">' + dataObj.subLists[i].creditAmount + '</Data></Cell>'
+ '<Cell ss:StyleID="s61"><Data ss:Type="String">' + dataObj.subLists[i].balanceAmount + '</Data></Cell>'
+ '</Row>';
}
return str;
};
const createHeadExcelHtml = () => {
let xmlStr = '<?xml version="1.0"?><?mso-application progid="Excel.Sheet"?>';
xmlStr += '<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" ';
xmlStr += 'xmlns:o="urn:schemas-microsoft-com:office:office" ';
xmlStr += 'xmlns:x="urn:schemas-microsoft-com:office:excel" ';
xmlStr += 'xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ';
xmlStr += 'xmlns:html="http://www.w3.org/TR/REC-html40">';
xmlStr += '<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">';
xmlStr += '</ExcelWorkbook>';
xmlStr += '<Styles>';
xmlStr += '<Style ss:ID="Default" ss:Name="Normal">';
xmlStr += '<Alignment ss:Vertical="Center"/>';
xmlStr += ' <Borders/>';
xmlStr += '<Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#000000"/>';
xmlStr += '<Interior/>';
xmlStr += '<NumberFormat/>';
xmlStr += '<Protection/>';
xmlStr += '</Style>';
xmlStr += '<Style ss:ID="s61">';
xmlStr += '<Alignment ss:Horizontal="Left" ss:Vertical="Center" />';
xmlStr += ' <Borders/>';
xmlStr += '<Font ss:FontName="宋体" x:CharSet="134" ss:Size="10" ss:Color="#000000"/>';
xmlStr += '<Interior/>';
xmlStr += '<NumberFormat/>';
xmlStr += '<Protection/>';
xmlStr += '</Style>';
xmlStr += '</Styles>';
xmlStr += '<Worksheet ss:Name="Sheet1">';
xmlStr += '<Table ss:ExpandedColumnCount="22" ss:ExpandedRowCount="8000">';
xmlStr += '<Column ss:Index="1" ss:StyleID="s62" ss:AutoFitWidth="0" ss:Width="95"/>';
xmlStr += '<Column ss:Index="2" ss:StyleID="s62" ss:AutoFitWidth="0" ss:Width="95"/>';
xmlStr += '<Column ss:Index="3" ss:StyleID="s62" ss:AutoFitWidth="0" ss:Width="95"/>';
xmlStr += '<Column ss:Index="4" ss:StyleID="s62" ss:AutoFitWidth="0" ss:Width="95"/>';
xmlStr += '<Column ss:Index="5" ss:StyleID="s62" ss:AutoFitWidth="0" ss:Width="95"/>';
xmlStr += '<Column ss:Index="6" ss:StyleID="s62" ss:AutoFitWidth="0" ss:Width="95"/>';
xmlStr += '<Column ss:Index="7" ss:StyleID="s62" ss:AutoFitWidth="0" ss:Width="95"/>';
xmlStr += '<Row ss:Height="15">'
+ '<Cell ss:StyleID="s61" ss:MergeAcross="0"><Data ss:Type="String">供应商</Data></Cell>'
+ '<Cell ss:StyleID="s61"><Data ss:Type="String">日期</Data></Cell>'
+ '<Cell ss:StyleID="s61"><Data ss:Type="String">凭证号</Data></Cell>'
+ '<Cell ss:StyleID="s61"><Data ss:Type="String">摘要</Data></Cell>'
+ '<Cell ss:StyleID="s61"><Data ss:Type="String">借方金额</Data></Cell>'
+ '<Cell ss:StyleID="s61"><Data ss:Type="String">贷方金额</Data></Cell>'
+ '<Cell ss:StyleID="s61"><Data ss:Type="String">金额余额</Data></Cell>'
+ '</Row>';
xmlStr += createRow();
xmlStr += '</Table></Worksheet></Workbook>';
const strXmlEncoded = encode.convert({
string: xmlStr,
inputEncoding: encode.Encoding.UTF_8,
outputEncoding: encode.Encoding.BASE_64,
});
return file.create({
name: '表格导出.xls',
fileType: file.Type.EXCEL,
contents: strXmlEncoded,
});
};
const objXlsFile = createHeadExcelHtml();
options.response.writeFile({ file: objXlsFile });