整理 javascript 表格导出excel文件遇到的多种情况的处理方法

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()
}