1npm install xlsx file-saver
1import { saveAs } from 'file-saver'2import XLSX from 'xlsx'3
4function datenum(v, date1904) {5 if (date1904) v += 14626 const epoch = Date.parse(v)7
8 return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000)9}10
153 collapsed lines
11function sheet_from_array_of_arrays(data) {12 const ws = {}13 const range = {14 s: {15 c: 10000000,16 r: 1000000017 },18 e: {19 c: 0,20 r: 021 }22 }23
24 for (let R = 0; R !== data.length; ++R) {25 for (let C = 0; C !== data[R].length; ++C) {26 if (range.s.r > R) range.s.r = R27 if (range.s.c > C) range.s.c = C28 if (range.e.r < R) range.e.r = R29 if (range.e.c < C) range.e.c = C30 const cell = {31 v: data[R][C]32 }33
34 if (cell.v === null) continue35 const cell_ref = XLSX.utils.encode_cell({36 c: C,37 r: R38 })39
40 if (typeof cell.v === 'number') cell.t = 'n'41 else if (typeof cell.v === 'boolean') cell.t = 'b'42 else if (cell.v instanceof Date) {43 cell.t = 'n'44 cell.z = XLSX.SSF._table[14]45 cell.v = datenum(cell.v)46 } else cell.t = 's'47
48 ws[cell_ref] = cell49 }50 }51 if (range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range)52
53 return ws54}55
56function Workbook() {57 if (!(this instanceof Workbook)) return new Workbook()58 this.SheetNames = []59 this.Sheets = {}60}61
62function s2ab(s) {63 const buf = new ArrayBuffer(s.length)64 const view = new Uint8Array(buf)65
66 for (let i = 0; i !== s.length; ++i) view[i] = s.charCodeAt(i) & 0xff67
68 return buf69}70
71export function export_json_to_excel({72 autoWidth = true,73 bookType = 'xlsx',74 data,75 filename,76 header,77 merges = [],78 multiHeader = []79} = {}) {80 filename = filename || 'excel-list'81 data = [...data]82 data.unshift(header)83
84 for (let i = multiHeader.length - 1; i > -1; i--) {85 data.unshift(multiHeader[i])86 }87
88 const ws_name = 'SheetJS'89 const wb = new Workbook()90 const ws = sheet_from_array_of_arrays(data)91
92 if (merges.length > 0) {93 if (!ws['!merges']) ws['!merges'] = []94 merges.forEach((item) => {95 ws['!merges'].push(XLSX.utils.decode_range(item))96 })97 }98
99 if (autoWidth) {100 /* 设置worksheet每列的最大宽度*/101 const colWidth = data.map((row) =>102 row.map((val) => {103 /* 先判断是否为null/undefined*/104 if (val === null) {105 return {106 wch: 12107 }108 } else if (val.toString().charCodeAt(0) > 255) {109 /* 再判断是否为中文*/110 return {111 wch: val.toString().length * 3112 }113 } else {114 return {115 wch: val.toString().length + 10116 }117 }118 })119 )120 /* 以第一行为初始值*/121 const result = colWidth[0]122
123 for (let i = 1; i < colWidth.length; i++) {124 for (let j = 0; j < colWidth[i].length; j++) {125 if (result[j].wch < colWidth[i][j].wch) {126 result[j].wch = colWidth[i][j].wch127 }128 }129 }130 ws['!cols'] = result131 }132
133 /* add worksheet to workbook */134 wb.SheetNames.push(ws_name)135 wb.Sheets[ws_name] = ws136
137 const wbout = XLSX.write(wb, {138 bookType,139 bookSST: false,140 type: 'binary'141 })142
143 saveAs(144 new Blob([s2ab(wbout)], {145 type: 'application/octet-stream'146 }),147 `${filename}.${bookType}`148 )149}150
151/**152 * 格式化JSON数据153 * @param filterVal154 * @param jsonData155 * @returns {*}156 */157function formatJson(filterVal, jsonData) {158 return jsonData.map((v) =>159 filterVal.map((j) => {160 return v[j] ? v[j] : ''161 })162 )163}
导出单个 sheet
1/**2 * JSON数据导出excel3 * @param { name, header, headerKeys, data } 参数集合4 */5export function exportJsonToExcel({ data, filename, header, headerKeys }) {6 filename = filename || `tr-export-${new Date().getTime()}`7 const resultData = formatJson(headerKeys, data)8
9 export_json_to_excel({10 header,4 collapsed lines
11 data: resultData,12 filename13 })14}
调用
1const data = [{name: 'Bob', age: 18}, {name: 'Joe', age: 18}]2const header = ['姓名', '年龄']3const headerKeys = ['name', 'age']4
5exportJsonToExcel({ data, header, headerKeys })
导出多个 sheet
1export function json2excelMuti(tableJson, filenames) {2 const tHeader = []3 const dataArr = []4 const sheetnames = []5 const multiHeader = []6 const merges = []7
8 for (const i in tableJson) {9 tHeader.push(tableJson[i].tHeader)10 dataArr.push(formatJson(tableJson[i].filterVal, tableJson[i].tableDatas))14 collapsed lines
11 sheetnames.push(tableJson[i].sheetName)12 multiHeader.push(tableJson[i].multiHeader || [])13 merges.push(tableJson[i].merges || [])14 }15
16 export_json_to_excel({17 multiHeader,18 merges,19 header: tHeader,20 data: dataArr,21 sheetname: sheetnames,22 filename: filenames23 })24}
调用
1const data = [2 { data: [{name: 'Bob', age: 18}, {name: 'Joe', age: 18}], tHeader: ['姓名', '年龄'], filterVal: ['name', 'age'], sheetName: 'sheet1' },3 { data: [{name: 'Bob', age: 18}, {name: 'Joe', age: 18}], tHeader: ['姓名', '年龄'], filterVal: ['name', 'age'], sheetName: 'sheet2' }4]5
6json2excelMuti(data, '导出文件名')