javascript 处理多种表格的Excel导出方式
在这里请注意一点,这里交流的是一种处理问题的方法,而不是最终研究出的处理方式结果。
以导出Excel为例
/* eslint-disable */
require('file-saver');
require('@/common/Blob');
// require('script-loader!xlsx/dist/xlsx.core.min');
import XLSX from 'xlsx'
function generateArray(table) {
var out = [];
var rows = table.querySelectorAll('tr');
var ranges = [];
var headers = [];
for (var R = 0; R < rows.length; ++R) {
var outRow = [];
var row = rows[R];
var headerArr = []
var columns = row.querySelectorAll('td');
var header = row.querySelectorAll('th');
for (var i = 0; i < header.length; i++) {
headerArr.push(header[i].innerText)
}
headerArr.length > 0 && headers.push(headerArr)
for (var C = 0; C < columns.length; ++C) {
var cell = columns[C];
var colspan = cell.getAttribute('colspan');
var rowspan = cell.getAttribute('rowspan');
var cellValue = cell.innerText;
if (cellValue !== "" && cellValue == +cellValue) cellValue = +cellValue;
//Skip ranges
ranges.forEach(function(range) {
if (R >= range.s.r && R <= range.e.r && outRow.length >= range.s.c && outRow.length <= range.e.c) {
for (var i = 0; i <= range.e.c - range.s.c; ++i) outRow.push(null);
}
});
//Handle Row Span
if (rowspan || colspan) {
rowspan = rowspan || 1;
colspan = colspan || 1;
ranges.push({ s: { r: R, c: outRow.length }, e: { r: R + rowspan - 1, c: outRow.length + colspan - 1 } });
};
//Handle Value
outRow.push(cellValue !== "" ? cellValue : null);
//Handle Colspan
if (colspan)
for (var k = 0; k < colspan - 1; ++k) outRow.push(null);
}
if (outRow.length == 0) continue
out.push(outRow);
}
headers.map(o => {
out.unshift(o)
})
return [out, ranges];
};
function datenum(v, date1904) {
if (date1904) v += 1462;
var epoch = Date.parse(v);
return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000);
}
function sheet_from_array_of_arrays(data, opts) {
var ws = {};
var range = { s: { c: 10000000, r: 10000000 }, e: { c: 0, r: 0 } };
for (var R = 0; R != data.length; ++R) {
for (var C = 0; C != data[R].length; ++C) {
if (range.s.r > R) range.s.r = R;
if (range.s.c > C) range.s.c = C;
if (range.e.r < R) range.e.r = R;
if (range.e.c < C) range.e.c = C;
var cell = { v: data[R][C] };
if (cell.v == null) continue;
var cell_ref = XLSX.utils.encode_cell({ c: C, r: R });
if (typeof cell.v === 'number') cell.t = 'n';
else if (typeof cell.v === 'boolean') cell.t = 'b';
else if (cell.v instanceof Date) {
cell.t = 'n';
cell.z = XLSX.SSF._table[14];
cell.v = datenum(cell.v);
} else cell.t = 's';
ws[cell_ref] = cell;
}
}
if (range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range);
return ws;
}
function Workbook() {
if (!(this instanceof Workbook)) return new Workbook();
this.SheetNames = [];
this.Sheets = {};
}
function s2ab(s) {
var buf = new ArrayBuffer(s.length);
var view = new Uint8Array(buf);
for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
return buf;
}
// 简单表头表格单个导出
export function export_table_to_excel(id, filename = 'test') {
var theTable = document.getElementById(id);
var oo = generateArray(theTable);
var ranges = oo[1];
/* original data */
var data = oo[0];
var ws_name = "SheetJS";
var wb = new Workbook(),
ws = sheet_from_array_of_arrays(data);
/* add ranges to worksheet */
// ws['!cols'] = ['apple', 'banan'];
ws['!merges'] = ranges;
/* add worksheet to workbook */
wb.SheetNames.push(ws_name);
wb.Sheets[ws_name] = ws;
var wbout = XLSX.write(wb, { bookType: 'xlsx', bookSST: false, type: 'binary' });
saveAs(new Blob([s2ab(wbout)], { type: "application/octet-stream" }), filename + parseInt(Math.random() * 1000) + ".xlsx")
}
// 复杂表头单个导出
export function export_customizedHeader_to_excel(id, filename = 'test') {
if (typeof id == 'string') {
var worksheet = 'Sheet1'
var worksheet2 = 'abc'
var uri = 'data:application/vnd.ms-excel;base64,';
var mod = document.getElementById(id)
var exportTemplate =
`<html xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<!--[if gte mso 9]>
<xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name>${worksheet}</x:Name>
<x:WorksheetOptions>
<x:DisplayGridlines/>
</x:WorksheetOptions>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
</x:ExcelWorkbook>
<x:ExcelWorkbook>
</x:ExcelWorkbook>
</xml>
<![endif]-->
</head>
<body>
${mod.innerHTML}
</body>
</html>`;
var x = document.createElement('a');
x.href = uri + window.btoa(unescape(encodeURIComponent(exportTemplate)));
x.download = filename + '_' + parseInt(Math.random() * 1000); //这里是关键所在,当点击之后,设置a标签的属性,这样就可以更改标签的标题了
document.body.appendChild(x)
x.click();
x.remove()
} else {
}
}
let Excel = function(tablesl, wsnames, wbname) {
this.uri = 'data:application/vnd.ms-excel;base64,'
this.tmplWorksheetXML = '<Worksheet ss:Name="{nameWS}"><Table x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="80" ss:DefaultRowHeight="16">{rows}</Table></Worksheet>'
this.Cell = '<Cell ss:Index="{index}" ss:MergeDown="{rowspan}" ss:MergeAcross="{colspan}"><Data ss:Type="String">{data}</Data></Cell>'
this.base64 = function(s) { return window.btoa(unescape(encodeURIComponent(s))) }
this.bodyerXml = ''
this.allSheet = ''
this.tablesl = tablesl
this.wsnames = wsnames
this.wbname = wbname
this.workbookXML = `<Row ss:AutoFitHeight="0">{cell}</Row>`
}
Excel.prototype = {
// BuildAllXml: function (styles, workSheet) {
BuildAllXml(workSheet) {
var xmlInfo = '<?xml version="1.0" encoding="utf-8"?> ' +
'<?mso-application progid="Excel.Sheet"?> ' +
'<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" ' +
'xmlns:o="urn:schemas-microsoft-com:office:office" ' +
'xmlns:x="urn:schemas-microsoft-com:office:excel" ' +
'xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ' +
'xmlns:html="http://www.w3.org/TR/REC-html40"> ' +
'<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"> ' +
'</DocumentProperties> ' +
'<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office"> ' +
'<RemovePersonalInformation/> ' +
'</OfficeDocumentSettings> ' +
'<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel"> ' +
'</ExcelWorkbook> ' +
// '</ExcelWorkbook><Styles> ' + styles + '</Styles> ' +
workSheet + ' </Workbook>';
return xmlInfo;
},
init() {
this.Assembling()
},
format(s, c) { return s.replace(/{(\w+)}/g, function(m, p) { return c[p]; }) },
productHeaderChild(objHe, hebin, CellIndex, idx) {
let onnerCellIndex = CellIndex
let cellChild = ''
for (let j = 0; j < objHe.length; j++) {
let objCel = objHe[j]
let x = objCel.getAttribute('colspan'),
y = objCel.getAttribute('rowspan')
if (x == null && y == null) continue
let colspan = x - 1
let rowspan = y - 1
if (x == null) colspan = 0
if (y == null) rowspan = 0
if (hebin.findIndex(e => e.idx == j && e.num >= idx) >= 0) {
CellIndex++
}
if (j > 0) {
onnerCellIndex = onnerCellIndex + parseInt(objHe[j - 1].getAttribute('colspan'))
} else {
onnerCellIndex = CellIndex + 1
}
if (rowspan > 0) {
hebin.push({ idx: j, num: rowspan })
}
let params = {
index: onnerCellIndex,
rowspan: rowspan,
colspan: colspan,
data: objCel.innerText
}
cellChild += this.format(this.Cell, params);
}
return cellChild
},
productBodyChild(objHe) {
let cellChild = ''
let RowIndex = 3
for (let j = 0; j < objHe.length; j++) {
let params = {
index: RowIndex + j,
rowspan: 0,
colspan: 0,
data: objHe[j].innerText
}
cellChild += this.format(this.Cell, params);
}
return cellChild
},
Assembling() {
let { wsnames, tablesl, allSheet, getRow, tmplWorksheetXML, format } = this;
tablesl.map((obj, idx) => {
let RowIndex = 2,
CellIndex = 2;
let xmls = ''
if (typeof obj == 'string') {
let tab = document.getElementById(obj);
let tables = tab.querySelectorAll('table')
let header = tables[0].rows
let bodyer = tables[1].rows
let hebin = []
// header
for (let i = 0; i < header.length; i++) {
RowIndex++;
let cellChild = this.productHeaderChild(header[i].cells, hebin, CellIndex, i)
xmls += format(getRow(RowIndex), { cell: cellChild });
}
// bodyer
for (let i = 0; i < bodyer.length; i++) {
let cellChild = this.productBodyChild(bodyer[i].cells)
xmls += format(this.workbookXML, { cell: cellChild });
}
} else {
let { header, data } = obj
let cellChild = ''
let maps = new Map()
RowIndex++;
header.map((obj, idx) => {
let params = {
index: RowIndex + idx,
rowspan: 0,
colspan: 0,
data: obj.title
}
cellChild += this.format(this.Cell, params);
maps.set(obj.field, idx)
})
xmls += format(getRow(RowIndex), { cell: cellChild });
data.map((obj, idx) => {
let cellChild = ''
let arr = Object.keys(obj).filter(e => maps.has(e))
arr.sort((a, b) => maps.get(a) - maps.get(b))
arr.map((ob, index) => {
let params = {
index: RowIndex + index,
rowspan: 0,
colspan: 0,
data: obj[ob]
}
cellChild += this.format(this.Cell, params);
})
xmls += format(this.workbookXML, { cell: cellChild });
})
}
let hv = { nameWS: wsnames[idx], rows: xmls }
allSheet += format(tmplWorksheetXML, hv)
})
this.generate(allSheet)
},
generate(allSheet) {
this.bodyerXml = this.BuildAllXml(allSheet)
var link = document.createElement("A");
link.href = this.uri + this.base64(this.bodyerXml);
link.download = this.wbname || 'Workbook.xls';
link.target = '_blank';
document.body.appendChild(link);
link.click();
document.body.removeChild(link);
},
getRow(Index) {
return `<Row ss:Index="${Index}" ss:AutoFitHeight="0">{cell}</Row>`
}
}
function getRow(Index) {
return `<Row ss:Index="${Index}" ss:AutoFitHeight="0">{cell}</Row>`
}
// 复杂表头表格全部导出
export function tablesToExcel(tablesl, wsnames, wbname) {
let tool = new Excel(tablesl, wsnames, wbname)
tool.init()
}