React ant table 用 XLSX 导出excel文件

近期做了一个react ant design 的table转换成excel 的功能总结下

首先我们会自己定义下 antdesign 的table的columns其中有可能有多语言或者是render方法的转换显示（比如说加特殊符号或者属性的code转换成对应的显示名称）都可以应用上

比如

   const columns = [{
                title: 'Qty',
                dataIndex: 'quantity',
                key: 'quantity'
            }, {
                title: intl.get("totaldiscount").d('Total Discount Price'),
                dataIndex: 'price',
                key: 'price',
                render: text => {
                    var msg = text
                    switch (text) {
                        case "":
                            ''
                            break;
                        default:
                            msg => "￥" + msg
                            break;
                    }
                    return msg
                }
            }]
const data=[{qty:1,price；10}，{qty:2,price；20}]

exportExcel([headers:columns,data:data,sheetName:"销售数据sheet" ],"统计报表")

添加一个文件写exportExcel的function

支持同时生成多个sheet

 1 import XLSX from 'xlsx';
 2 //exportExcel  sheets=[{headers:[],data:[],sheetName:""}] fileName =""
 3 function exportExcel(sheets, fileName = 'dowloadFile') {
 4     const sheetsArr = sheets.map(sheet => {
 5         const _headers = sheet.headers
 6             .map((item, i) => Object.assign({}, { key: item.key, title: item.title, position: getPostition(i) + 1 }))
 7             .reduce((prev, next) => Object.assign({}, prev, { [next.position]: { key: next.key, v: next.title } }), {});
 8         const dataArr = sheet.data
 9             .map((item, i) => sheet.headers.map((head, j) => {
10                 let content = ""
11                 if (head.render) {
12                     content = head.render(item[head.dataIndex], item)
13                 } else {
14                     content = item[head.dataIndex]
15                 }
16                 return { content, position: getPostition(j) + (i + 2) }
17             }
18             ))
19         // 对刚才的结果进行降维处理（二维数组变成一维数组）
20         const _data = dataArr.reduce((prev, next) => prev.concat(next))
21             // 转换成 worksheet 需要的结构
22             .reduce((prev, next) => Object.assign({}, prev, { [next.position]: { v: next.content } }), {});
23 
24         // 合并 headers 和 data
25         const output = Object.assign({}, _headers, _data);
26         // 获取所有单元格的位置
27         const outputPos = Object.keys(output);
28         // 计算出范围 ,["A1",..., "H2"]
29         const ref = `${outputPos[0]}:${outputPos[outputPos.length - 1]}`;
30         return Object.assign(
31             { sheetName: sheet.sheetName },
32             output,
33             {
34                 '!ref': ref,
35                 '!cols': getColWidth(sheet.headers, dataArr),
36             },
37         )
38     })
39     const sheetNames = sheetsArr.map(sheet => sheet.sheetName)
40     const wbSheets = sheetsArr.reduce((prev, next) =>
41         Object.assign({}, prev, { [next.sheetName]: next }), {})
42     // 构建 workbook 对象
43     const wb = {
44         SheetNames: sheetNames,
45         Sheets: wbSheets,
46     };
47     // 导出 Excel
48     XLSX.writeFile(wb, fileName + ".xlsx");
49 }
50 
51 function getPostition(index) {
52     let result = String.fromCharCode(65 + parseInt(index % 26))
53     let value = index / 26
54     while (value >= 1) {
55         result = String.fromCharCode(65 + parseInt(value % 26 - 1)) + result
56         value = parseInt(value / 26)
57     }
58     return result
59 }
60 function getColWidth(headers, dataArr) {
61     const allWch = [headers,].concat(dataArr).map(item => item.map(val => {
62         let value = val.title || val.content || ""
63         let length = 10
64         /*先判断是否为null/undefined*/
65         if (value) {
66             /*再判断是否为中文*/
67             if (value.toString().charCodeAt(0) > 255) {
68                 length = value.toString().length * 2
69             } else {
70                 length = value.toString().length
71             }
72         }
73         return {
74             'wch': length < 40 ? length : 40
75         };
76     }))
77     /*以第一行为初始值*/
78     let colWidth = allWch[0];
79     for (let i = 1; i < allWch.length; i++) {
80         for (let j = 0; j < allWch[i].length; j++) {
81             if (colWidth[j]['wch'] < allWch[i][j]['wch']) {
82                 colWidth[j]['wch'] = allWch[i][j]['wch'];
83             }
84         }
85     }
86     return colWidth
87 }
88 export default exportExcel;

import XLSX from 'xlsx';
//exportExcel  sheets=[{headers:[],data:[],sheetName:""}] fileName =""
function exportExcel(sheets, fileName = 'dowloadFile') {
    const sheetsArr = sheets.map(sheet => {
        const _headers = sheet.headers
            .map((item, i) => Object.assign({}, { key: item.key, title: item.title, position: getPostition(i) + 1 }))
            .reduce((prev, next) => Object.assign({}, prev, { [next.position]: { key: next.key, v: next.title } }), {});
        const dataArr = sheet.data
            .map((item, i) => sheet.headers.map((head, j) => {
                let content = ""
                if (head.render) {
                    content = head.render(item[head.dataIndex], item)
                } else {
                    content = item[head.dataIndex]
                }
                return { content, position: getPostition(j) + (i + 2) }
            }
            ))
        // 对刚才的结果进行降维处理（二维数组变成一维数组）
        const _data = dataArr.reduce((prev, next) => prev.concat(next))
            // 转换成 worksheet 需要的结构
            .reduce((prev, next) => Object.assign({}, prev, { [next.position]: { v: next.content } }), {});

        // 合并 headers 和 data
        const output = Object.assign({}, _headers, _data);
        // 获取所有单元格的位置
        const outputPos = Object.keys(output);
        // 计算出范围 ,["A1",..., "H2"]
        const ref = `${outputPos[0]}:${outputPos[outputPos.length - 1]}`;
        return Object.assign(
            { sheetName: sheet.sheetName },
            output,
            {
                '!ref': ref,
                '!cols': getColWidth(sheet.headers, dataArr),
            },
        )
    })
    const sheetNames = sheetsArr.map(sheet => sheet.sheetName)
    const wbSheets = sheetsArr.reduce((prev, next) =>
        Object.assign({}, prev, { [next.sheetName]: next }), {})
    // 构建 workbook 对象
    const wb = {
        SheetNames: sheetNames,
        Sheets: wbSheets,
    };
    // 导出 Excel
    XLSX.writeFile(wb, fileName + ".xlsx");
}

function getPostition(index) {
    let result = String.fromCharCode(65 + parseInt(index % 26))
    let value = index / 26
    while (value >= 1) {
        result = String.fromCharCode(65 + parseInt(value % 26 - 1)) + result
        value = parseInt(value / 26)
    }
    return result
}
function getColWidth(headers, dataArr) {
    const allWch = [headers,].concat(dataArr).map(item => item.map(val => {
        let value = val.title || val.content || ""
        let length = 10
        /*先判断是否为null/undefined*/
        if (value) {
            /*再判断是否为中文*/
            if (value.toString().charCodeAt(0) > 255) {
                length = value.toString().length * 2
            } else {
                length = value.toString().length
            }
        }
        return {
            'wch': length < 40 ? length : 40
        };
    }))
    /*以第一行为初始值*/
    let colWidth = allWch[0];
    for (let i = 1; i < allWch.length; i++) {
        for (let j = 0; j < allWch[i].length; j++) {
            if (colWidth[j]['wch'] < allWch[i][j]['wch']) {
                colWidth[j]['wch'] = allWch[i][j]['wch'];
            }
        }
    }
    return colWidth
}
export default exportExcel;