[JavaScript知识库]记一次vue前端导出excel

背景：之前的导出由于没有设置列宽和自动换行，相关人员提出需要优化下。

业务流程：后台只处理数据查询逻辑， 导出交给前端处理

参考文档：纯前端js(或者vue)导出excel实现：合并单元格、设置单元格样式、单元格内换行_李星星爱搬砖的博客-CSDN博客_vue导出excel表格合并单元格

vue引用xlsx-style组件 报错:This relative module was not found: ./cptable in ./node_modules/xlsx-style_jing_mama的博客-CSDN博客

一、安装?xlsx-style

npm install --save xlsx file-saver  // 这是之前安装过的

npm install --save xlsx-style       // 本次安装

运行控制台报错：

This relative module was not found: * ./cptable in ./node_modules/xlsx-style

在?webpack.base.conf.js 中添加：

module.exports = {
    externals: [ {
         './cptable': 'var cptable'
    }]
}

?重新 npm install，运行正常?

二、前端处理

exportExcel2 (list) {
	require.ensure([], () => {
		const { export_json_to_excel_and_autoWidth } = require('../../Excel/Export2Excel.js');
		const tHeader = ['序号', '试卷名称', '学生姓名', '成绩'];
		// 上面设置Excel的表格第一行的标题
		const filterVal = ['sort', 'sjName', 'stuName', 'score'];
		// 上面的sort、sjName、stuName、score是tableData里对象的属性
		// const list = this.tableData;  
        //把data里的tableData存到list
		const data = this.formatJson(filterVal, list);
		let autoWidth = true;
		export_json_to_excel_and_autoWidth(tHeader, data, '学生成绩表', autoWidth);
	})
},

Export2Excel.js

/* eslint-disable */
require('script-loader!file-saver');
require('./Blob');//转二进制用  这边要写你的blob的实际地址
require('script-loader!xlsx/dist/xlsx.core.min');
import XLSXS from "xlsx-style" // 必须要引入才可以，否则报错



function generateArray (table) {
    var out = [];
    var rows = table.querySelectorAll('tr');
    var ranges = [];
    for (var R = 0; R < rows.length; ++R) {
        var outRow = [];
        var row = rows[R];
        var columns = row.querySelectorAll('td');
        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);
        }
        out.push(outRow);
    }
    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) {
    var theTable = document.getElementById(id);
    console.log('a')
    var oo = generateArray(theTable);
    var ranges = oo[1];

    /* original data */
    var data = oo[0];
    var ws_name = "SheetJS";
    console.log(data);

    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"
    }), "test.xlsx")
}

function formatJson (jsonData) {
    console.log(jsonData)
}
export function export_json_to_excel (th, jsonData, defaultTitle) {

    /* original data */

    var data = jsonData;
    data.unshift(th);
    var ws_name = "SheetJS";

    var wb = new Workbook(),
        ws = sheet_from_array_of_arrays(data);


    /* 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'
    });
    var title = defaultTitle || '列表'
    saveAs(new Blob([s2ab(wbout)], {
        type: "application/octet-stream"
    }), title + ".xlsx")
};

export function export_json_to_excel_and_autoWidth (th, jsonData, defaultTitle, autoWidth) {

    /* original data */

    var data = jsonData;
    data.unshift(th);
    var ws_name = "SheetJS";

    var wb = new Workbook(),
        ws = sheet_from_array_of_arrays(data);

    let borderAll = { //单元格外侧框线
        top: {
            style: 'thin', // medium 太粗了
        },
        bottom: {
            style: 'thin'
        },
        left: {
            style: 'thin'
        },
        right: {
            style: 'thin'
        }
    };
        
    for (let key in ws) {
        if (ws[key] instanceof Object) {
            ws[key].s = {
                border: borderAll,
                alignment: {
                    horizontal: 'left', // center 水平居中对齐
                    vertical: 'center',//垂直居中
                    wrapText: 1,//自动换行
                },
                font: {
                    sz: 12,//单元格中字体的样式与颜色设置
                    color: {
                        // rgb: '495060'
                    }
                },
                bold: true,
                numFmt: 0
            }
        }
    }

    if (autoWidth) {
        let colWidths = [];
        // 计算每一列的所有单元格宽度
        // 先遍历行
        data.forEach((row) => {
            // 列序号
            let index = 0
            // 遍历列
            for (const key in row) {
                if (colWidths[index] == null) colWidths[index] = []
                switch (typeof row[key]) {
                    case 'string':
                    case 'number':
                    case 'boolean':                     colWidths[index].push(getCellWidth(row[key]))
                        break
                    case 'object':
                    case 'function':
                        colWidths[index].push(0)
                        break
                }
                index++
            }
        })
        ws['!cols'] = [];
        colWidths.forEach((widths, index) => {
            // 计算列头的宽度
            widths.push(getCellWidth(th[index]))
            // 设置最大值为列宽
            ws['!cols'].push({
                wch: Math.max(...widths)
            })
        })
    }

    /* add worksheet to workbook */
    wb.SheetNames.push(ws_name);
    wb.Sheets[ws_name] = ws;

    var wbout = XLSXS.write(wb, {
        bookType: 'xlsx',
        bookSST: false,
        type: 'binary'
    });
    var title = defaultTitle || '列表'
    saveAs(new Blob([s2ab(wbout)], {
        type: "application/octet-stream"
    }), title + ".xlsx")
};

export function getCellWidth(value) {
    if (value == null) {
        return 10
    } else if (value.toString().charCodeAt(0) > 255 || value.toString().charCodeAt(0) == 40) { // 当第一个字符是 '(', charCodeAt(0) == 40 整列就不会自动换行，这里单独处理。
        // 判断是否包含中文
        let length = value.toString().length * 2
        if (length > 60) {
            length = 40 
            //这里的宽度可以自己设定，在前面设置wrapText: 1可以在单元格内换行
        }
        return length
    } else {
        // return value.toString().length * 1.2
        return value.toString().length
    }
}

总结

value.toString().charCodeAt(0) > 255 当内容为中文设定最大长度，可实现自动换行。

当内容开头不是中文，而是‘(1)’，这个时候??value.toString().charCodeAt(0) = 40 ，也就是‘('的unicode值，这里单独处理?

存在的问题：【临时解决办法：去掉单元格外框线---borderAll】

?