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([

            {

                blocks: [

                    { headers:columns, data:data },

                ], sheetName: "销售数据sheet"

            },

        ], "统计报表")

 

添加一个文件写exportExcel的function

支持同时生成多个sheet 

 

import XLSX from 'xlsx';
import { message } from 'antd';
import { isBlank } from './PubUtils'

//exportExcel  sheets=[blocks:[{headers:[],data:[]}],sheetName:""}]
// fileName =""
function exportExcel(sheets, fileName = 'dowloadFile') {
    if (sheets.length <= 0) {
        message.error("no data download")
        return
    }
 13     const sheetsArr = sheets.map(sheet => {
        let Count = 1
        let colsWidth = []
        let sheetOutPut = {}
         18         sheet.blocks.map(
            block => {
                if (block.headers.length <= 0) {
                    message.error("no data download")
                    return
                }
                const blockHeaders = block.headers.filter(item => !item.excelHidden)
                const _headers = blockHeaders
                    .map((item, i) => Object.assign({}, { key: item.key, title: item.title, position: getPostition(i) + Count }))
                    .reduce((prev, next) => Object.assign({}, prev, { [next.position]: { key: next.key, v: next.title } }), {});
                console.log("_headers==", _headers)
                Count++
                let dataArr = [], _data = {}
                //有数据时进行处理
                if (block.data && block.data.length > 0) {
                    dataArr = block.data
                        .map((item, i) => blockHeaders.map((head, j) => {
                            let content = ""
                            let position = getPostition(j) + (i + Count)
                            if (head.excelRender) {
                                content = head.excelRender(item[head.dataIndex], item, i)
                            } else if (head.render) {
                                content = head.render(item[head.dataIndex], item, i)
                            } else {
                                content = item[head.dataIndex]
                            }
                            content = isBlank(content) ? "" : content
                            // 转换成 worksheet 需要的结构
                            _data[position] = { v: content }
                            return { content, position }
                        }
                        ))
                }
                colsWidth = getColWidth(colsWidth, block.headers, dataArr)
                Count += dataArr.length + 1
                // 合并 headers 和 data
                sheetOutPut = Object.assign({}, sheetOutPut, _headers, _data);
            }
        )
        if (colsWidth.length <= 0) {
            message.error("no data download")
            return Object.assign(
                { sheetName: sheet.sheetName },
            )
        }
        // 计算出范围 ,["A1",..., "H2"]
        const ref = `A1:${getPostition(colsWidth.length - 1) + '' + Count}`;
        return Object.assign(
            { sheetName: sheet.sheetName },
            sheetOutPut,
            {
                '!ref': ref,
                '!cols': colsWidth,
            },
        )
    })
    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) {
        value = value - 1
        result = String.fromCharCode(65 + parseInt(value % 26)) + result
        value = parseInt(value / 26)
    }
    return result
}
function getColWidth(preColWidth, 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
        };
    }))
    if (preColWidth.length < allWch[0].length) {
        preColWidth = preColWidth.concat(allWch[0].slice(preColWidth.length))
    }
    let colWidth = preColWidth
    for (let i = 0; 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;