1
import { saveAs } from 'file-saver'
2
import XLSX from 'xlsx'
3

4
function datenum(v, date1904) {
5
  if (date1904) v += 1462
6
  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
11
function sheet_from_array_of_arrays(data) {
12
  const ws = {}
13
  const range = {
14
    s: {
15
      c: 10000000,
16
      r: 10000000
17
    },
18
    e: {
19
      c: 0,
20
      r: 0
21
    }
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 = R
27
      if (range.s.c > C) range.s.c = C
28
      if (range.e.r < R) range.e.r = R
29
      if (range.e.c < C) range.e.c = C
30
      const cell = {
31
        v: data[R][C]
32
      }
33

34
      if (cell.v === null) continue
35
      const cell_ref = XLSX.utils.encode_cell({
36
        c: C,
37
        r: R
38
      })
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] = cell
49
    }
50
  }
51
  if (range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range)
52

53
  return ws
54
}
55

56
function Workbook() {
57
  if (!(this instanceof Workbook)) return new Workbook()
58
  this.SheetNames = []
59
  this.Sheets = {}
60
}
61

62
function 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) & 0xff
67

68
  return buf
69
}
70

71
export 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: 12
107
          }
108
        } else if (val.toString().charCodeAt(0) > 255) {
109
          /* 再判断是否为中文*/
110
          return {
111
            wch: val.toString().length * 3
112
          }
113
        } else {
114
          return {
115
            wch: val.toString().length + 10
116
          }
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].wch
127
        }
128
      }
129
    }
130
    ws['!cols'] = result
131
  }
132

133
  /* add worksheet to workbook */
134
  wb.SheetNames.push(ws_name)
135
  wb.Sheets[ws_name] = ws
136

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 filterVal
154
 * @param jsonData
155
 * @returns {*}
156
 */
157
function formatJson(filterVal, jsonData) {
158
  return jsonData.map((v) =>
159
    filterVal.map((j) => {
160
      return v[j] ? v[j] : ''
161
    })
162
  )
163
}

1
/**
2
 * JSON数据导出excel
3
 * @param { name, header, headerKeys, data } 参数集合
4
 */
5
export 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
    filename
13
  })
14
}

1
const data = [{name: 'Bob', age: 18}, {name: 'Joe', age: 18}]
2
const header = ['姓名', '年龄']
3
const headerKeys = ['name', 'age']
4

5
exportJsonToExcel({ data, header, headerKeys })

1
export 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: filenames
23
  })
24
}

1
const 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

6
json2excelMuti(data, '导出文件名')