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