2019-10-22 React 使用 js-xlsx 处理导入导出

本来打算网上抄抄算了，可是虽然有可以用的，但不支持多sheet， 就找了一个随便改造了一下：

    getOutput = (headers, data) => {
        const _headers = headers
            .map((item, i) => Object.assign({}, { key: item.key, title: item.title, position: String.fromCharCode(65 + i) + 1 }))
            .reduce((prev, next) => Object.assign({}, prev, { [next.position]: { key: next.key, v: next.title } }), {});

        const _data = data
            .map((item, i) => headers.map((key, j) => Object.assign({}, { content: item[key.key], position: String.fromCharCode(65 + j) + (i + 2) })))
            // 对刚才的结果进行降维处理（二维数组变成一维数组）
            .reduce((prev, next) => prev.concat(next))
            // 转换成 worksheet 需要的结构
            .reduce((prev, next) => Object.assign({}, prev, { [next.position]: { v: next.content } }), {});
        // const output = Object.assign({}, initColumn, attendanceInfoList);
        // 合并 headers 和 data
        // console.log(_data);
        const output = Object.assign({}, _headers, _data);
        return output;
    };

    getRef = (output) => {
        const outputPos = Object.keys(output);
        // 计算出范围 ,["A1",..., "H2"]
        const ref = `${outputPos[0]}:${outputPos[outputPos.length - 1]}`;
        return ref;
    };


    exportExcel = (headers, data, sheetNames, fileName) => {
        let outputArray = [];
        for(let i = 0; i < headers.length; i++){
            outputArray.push(this.getOutput(headers[i], data[i]));
        }
 
        let refArray = [];
        for(let i = 0; i < outputArray.length; i++) {
            refArray.push(this.getRef(outputArray[i]));
        }

        let sheetDict = {};
        // let sheetNames = ["haha"];
        for(let i = 0; i < outputArray.length; i++) {
            // sheetNames.push[i.toString()];
            sheetDict[sheetNames[i]] = Object.assign(
                    {},
                    outputArray[i],
                    {
                        '!ref': refArray[i],
                    },
                )
        }

        // console.log(sheetNames, sheetDict);

        // 构建 workbook 对象
        const wb = {
            SheetNames: sheetNames,
            Sheets: sheetDict,
        };
        // 导出 Excel
        XLSX.writeFile(wb, fileName);
    };

excel 的标题栏：

        const reagentColumn = [
            {
                title: 'id',
                dataIndex: 'id',
                key: 'id',
            },
            {
                title: '试剂盒名称',
                dataIndex: 'name',
                key: 'name',
            },
            {
                title: '货号',
                dataIndex: 'no',
                key: 'no',
            },
            {
                title: '试剂厂家',
                dataIndex: 'company',
                key: 'company',
            },
            {
                title: '批号',
                dataIndex: 'batch_number',
                key: 'batch_number',
            },
            {
                title: '用量',
                dataIndex: 'usage',
                key: 'usage',
            },
            {
                title: '余量',
                dataIndex: 'surplus',
                key: 'surplus',
            },
            {
                title: '备注',
                dataIndex: 'remark',
                key: 'remark',
            },
        ];

        const splitColumn = [
            {
                title: '试剂名称',
                dataIndex: 'name',
                key: 'name',
            },
            {
                title: '试剂量',
                dataIndex: 'usage',
                key: 'usage',
            },
            {
                title: '分装量',
                dataIndex: 'split',
                key: 'split',
            },
            {
                title: '剩余试剂量',
                dataIndex: 'surplus',
                key: 'surplus',
            },
            {
                title: '备注',
                dataIndex: 'remark',
                key: 'remark',
            },
        ];

        const outputColumns = [
            {
                title: 'id',
                dataIndex: 'id',
                key: 'id',
            },
            {
                title: '样本内部编号',
                dataIndex: 'internal_barcode',
                key: 'internal_barcode',
            },
            {
                title: '产物名称',
                dataIndex: 'outcome',
                key: 'outcome',
            },
            {
                title: '产物量',
                dataIndex: 'number',
                key: 'number',
            },
            {
                title: '单位',
                dataIndex: 'unit',
                key: 'unit',
            }];

数据部分：

template.reagent_suit = {
company: "武汉海吉力生物科技有限公司"
id: 2
name: "人类 KRAS 基因突变检测试剂盒"
no: "HGN-kr01"
}

template.outcome = { 
id: 1
name: "产物1"
unit: "ug"
}

调用：

onClick={() => {this.exportExcel([reagentColumn, splitColumn, outputColumns], [template.reagent_suit, [{}], template.outcome], ["试剂配置", "试剂分装", "产物"], "产物模板.xlsx")