React ant table 用 XLSX 导出excel文件
阅读原文时间:2023年07月09日阅读:2

近期做了一个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

1 import XLSX from 'xlsx';
2 import { message } from 'antd';
3 import { isBlank } from './PubUtils'
4
5 //exportExcel sheets=[blocks:[{headers:[],data:[]}],sheetName:""}]
6 // fileName =""
7 function exportExcel(sheets, fileName = 'dowloadFile') {
8 if (sheets.length <= 0) { 9 message.error("no data download") 10 return 11 } 12 13 const sheetsArr = sheets.map(sheet => {
14 let Count = 1
15 let colsWidth = []
16 let sheetOutPut = {}
17 18 sheet.blocks.map(
19 block => {
20 if (block.headers.length <= 0) { 21 message.error("no data download") 22 return 23 } 24 const blockHeaders = block.headers.filter(item => !item.excelHidden)
25 const _headers = blockHeaders
26 .map((item, i) => Object.assign({}, { key: item.key, title: item.title, position: getPostition(i) + Count }))
27 .reduce((prev, next) => Object.assign({}, prev, { [next.position]: { key: next.key, v: next.title } }), {});
28 console.log("_headers==", _headers)
29 Count++
30 let dataArr = [], _data = {}
31 //有数据时进行处理
32 if (block.data && block.data.length > 0) {
33 dataArr = block.data
34 .map((item, i) => blockHeaders.map((head, j) => {
35 let content = ""
36 let position = getPostition(j) + (i + Count)
37 if (head.excelRender) {
38 content = head.excelRender(item[head.dataIndex], item, i)
39 } else if (head.render) {
40 content = head.render(item[head.dataIndex], item, i)
41 } else {
42 content = item[head.dataIndex]
43 }
44 content = isBlank(content) ? "" : content
45 // 转换成 worksheet 需要的结构
46 _data[position] = { v: content }
47 return { content, position }
48 }
49 ))
50 }
51 colsWidth = getColWidth(colsWidth, block.headers, dataArr)
52 Count += dataArr.length + 1
53 // 合并 headers 和 data
54 sheetOutPut = Object.assign({}, sheetOutPut, _headers, _data);
55 }
56 )
57 if (colsWidth.length <= 0) { 58 message.error("no data download") 59 return Object.assign( 60 { sheetName: sheet.sheetName }, 61 ) 62 } 63 // 计算出范围 ,["A1",…, "H2"] 64 const ref = `A1:${getPostition(colsWidth.length - 1) + '' + Count}`; 65 return Object.assign( 66 { sheetName: sheet.sheetName }, 67 sheetOutPut, 68 { 69 '!ref': ref, 70 '!cols': colsWidth, 71 }, 72 ) 73 }) 74 const sheetNames = sheetsArr.map(sheet => sheet.sheetName)
75 const wbSheets = sheetsArr.reduce((prev, next) =>
76 Object.assign({}, prev, { [next.sheetName]: next }), {})
77 // 构建 workbook 对象
78 const wb = {
79 SheetNames: sheetNames,
80 Sheets: wbSheets,
81 };
82 // 导出 Excel
83 XLSX.writeFile(wb, fileName + ".xlsx");
84 }
85
86 function getPostition(index) {
87 let result = String.fromCharCode(65 + parseInt(index % 26))
88 let value = index / 26
89 while (value >= 1) {
90 value = value - 1
91 result = String.fromCharCode(65 + parseInt(value % 26)) + result
92 value = parseInt(value / 26)
93 }
94 return result
95 }
96 function getColWidth(preColWidth, headers, dataArr) {
97 const allWch = [headers,].concat(dataArr).map(item => item.map(val => {
98 let value = val.title || val.content || ""
99 let length = 10
100 /*先判断是否为null/undefined*/
101 if (value) {
102 /*再判断是否为中文*/
103 if (value.toString().charCodeAt(0) > 255) {
104 length = value.toString().length * 2
105 } else {
106 length = value.toString().length
107 }
108 }
109 return {
110 'wch': length < 40 ? length : 40
111 };
112 }))
113 if (preColWidth.length < allWch[0].length) {
114 preColWidth = preColWidth.concat(allWch[0].slice(preColWidth.length))
115 }
116 let colWidth = preColWidth
117 for (let i = 0; i < allWch.length; i++) {
118 for (let j = 0; j < allWch[i].length; j++) {
119 if (colWidth[j]['wch'] < allWch[i][j]['wch']) {
120 colWidth[j]['wch'] = allWch[i][j]['wch'];
121 }
122 }
123 }
124 return colWidth
125 }
126 export default exportExcel;

手机扫一扫

移动阅读更方便

阿里云服务器
腾讯云服务器
七牛云服务器

你可能感兴趣的文章