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;

转载于:https://www.cnblogs.com/ITCoNan/p/11599188.html