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( sheetsfileName =  'dowloadFile') {
     const  sheetsArr =  sheets. map( sheet  => {
         const  _headers =  sheet. headers
            . map(( itemi=>  Object. assign({}, {  key:  item. keytitle:  item. titleposition:  getPostition( i) +  1 }))
            . reduce(( prevnext=>  Object. assign({},  prev, {  [next.position]: {  key:  next. keyv:  next. title } }), {});
         const  dataArr =  sheet. data
            . map(( itemi=>  sheet. headers. map(( headj=> {
                 let  content =  ""
                 if ( head. render) {
                     content =  head. render( item[ head. dataIndex],  item)
                }  else {
                     content =  item[ head. dataIndex]
                }
                 return {  contentposition:  getPostition( j) + ( i +  2) }
            }
            ))
         // 对刚才的结果进行降维处理(二维数组变成一维数组)
         const  _data =  dataArr. reduce(( prevnext=>  prev. concat( next))
             // 转换成 worksheet 需要的结构
            . reduce(( prevnext=>  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. headersdataArr),
            },
        )
    })
     const  sheetNames =  sheetsArr. map( sheet  =>  sheet. sheetName)
     const  wbSheets =  sheetsArr. reduce(( prevnext=>
         Object. assign({},  prev, {  [next.sheetName]:  next }), {})
     // 构建 workbook 对象
     const  wb = {
         SheetNames:  sheetNames,
         Sheets:  wbSheets,
    };
     // 导出 Excel
     XLSX. writeFile( wbfileName +  ".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( headersdataArr) {
     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 =  1i <  allWch. lengthi++) {
         for ( let  j =  0j <  allWch[ i]. lengthj++) {
             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

你可能感兴趣的