方案

x-data-spreadsheet + xlsx

优点：
    支持excel的在线编辑、导入、导出、数据存储及回显
    存储数据体积较小
缺点：
    导出excel无法携带样式

luckysheet + luckyexcel + exceljs + file-saver

优点：
    支持excel的在线编辑、导入、导出、数据存储及回显
    导出excel可以携带样式
缺点：
    存储数据体积较大
    只支持script引入，无npm包

效果

x-data-spreadsheet + xlsx

luckysheet + luckyexcel + exceljs + file-saver

实现

x-data-spreadsheet + xlsx

1.npm安装依赖

npm i x-data-spreadsheet xlsx
1

2.组件实现

<template>
  <div>
    <div class="mb-md">
      <input type="file" @change="chageFile" ref="inputFile" />
      <button @click="exportExcel">导出xlsx</button>
    </div>
    <!--web spreadsheet组件-->
    <div id="x-spreadsheet-demo"></div>
  </div>
</template>

<script>
//引入依赖包
import zhCN from 'x-data-spreadsheet/src/locale/zh-cn'
import Spreadsheet from 'x-data-spreadsheet'
import * as XLSX from 'xlsx'
Spreadsheet.locale('zh-cn', zhCN)
//本地存储，demo数据存储、回显使用
import local from '@/utils/local-storage'
export default {
  name: 'XspreadsheetDemo',
  data() {
    return {
      xs: null
    }
  },
  mounted() {
    this.init()
  },
  methods: {
    init() {
      this.xs = new Spreadsheet('#x-spreadsheet-demo', {
        row: {
          len: 10000,
          height: 25
        }
      })
      let excelData = local.getItem('excel')
      if (excelData) {
        this.xs.loadData(excelData) // load data
      }

      this.xs.change(data => {
        local.setItem('excel', data)
      })

      // data validation
      this.xs.validate()
    },
    exportExcel() {
      var new_wb = this.xtos(this.xs.getData())
      XLSX.writeFile(new_wb, 'SheetJS.xlsx')
    },
    xtos(sdata) {
      console.log(sdata)
      var out = XLSX.utils.book_new()
      sdata.forEach(function (xws) {
        var aoa = [[]]
        var rowobj = xws.rows
        for (var ri = 0; ri < rowobj.len; ++ri) {
          var row = rowobj[ri]
          if (!row) continue
          aoa[ri] = []
          Object.keys(row.cells).forEach(function (k) {
            var idx = +k
            if (isNaN(idx)) return
            aoa[ri][idx] = row.cells[k].text
          })
        }
        var ws = XLSX.utils.aoa_to_sheet(aoa)

        /** 读取在线中的合并单元格，并写入导出的数据中
                 * merges: Array(19)
                    0: "A16:P16"
                    1: "A17:P17"
                    2: "O2:P2"
                    3: "F2:G2"
                 */
        ws['!merges'] = []
        xws.merges.forEach(merge => {
          ws['!merges'].push(XLSX.utils.decode_range(merge))
        })

        XLSX.utils.book_append_sheet(out, ws, xws.name)
      })
      return out
    },
    chageFile() {
      this.importExcel(this.$refs.inputFile.files[0])
    },
    importExcel(file) {
      let reader = new FileReader()
      reader.onload = e => {
        let data = e.target.result
        let fixedData = this.fixData(data)
        let workbook = XLSX.read(btoa(fixedData), { type: 'base64' })
        console.log(workbook)
        console.log(this.stox(workbook))
        this.xs.loadData(this.stox(workbook))
      }
      reader.readAsArrayBuffer(file)
      // return workbook
    },
    fixData(data) {
      var o = '',
        l = 0,
        w = 10240
      for (; l < data.byteLength / w; ++l) o += String.fromCharCode.apply(null, new Uint8Array(data.slice(l * w, l * w + w)))
      o += String.fromCharCode.apply(null, new Uint8Array(data.slice(l * w)))
      return o
    },
    stox(wb) {
      var out = []
      wb.SheetNames.forEach(function (name) {
        var o = { name: name, rows: {}, merges: [] }
        var ws = wb.Sheets[name]
        var aoa = XLSX.utils.sheet_to_json(ws, { raw: false, header: 1 })
        aoa.forEach(function (r, i) {
          var cells = {}
          r.forEach(function (c, j) {
            cells[j] = { text: c }
          })
          o.rows[i] = { cells: cells }
        })
        // 设置合并单元格
        ws['!merges'].forEach(merge => {
          /** merge = {
           *  s: {c: 0, r: 15}
           *  e: {c: 15, r: 15}
           * }
           */
          // 修改 cell 中 merge [合并行数,合并列数]
          let cell = o.rows[merge.s.r].cells[merge.s.c]

          //无内容单元格处理
          if (!cell) {
            cell = { text: '' }
          }
          cell.merge = [merge.e.r - merge.s.r, merge.e.c - merge.s.c]
          o.rows[merge.s.r].cells[merge.s.c] = cell

          // 修改 merges
          o.merges.push(XLSX.utils.encode_range(merge))
        })
        out.push(o)
      })
      return out
    }
  }
}
</script>
<style scoped>
</style>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153

luckysheet + luckyexcel + exceljs + file-saver

1.npm安装依赖

npm i exceljs file-saver luckyexcel
1

2.index.html中引入

<link rel='stylesheet' href='https://cdn.jsdelivr.net/npm/luckysheet/dist/plugins/css/pluginsCss.css' />
<link rel='stylesheet' href='https://cdn.jsdelivr.net/npm/luckysheet/dist/plugins/plugins.css' />
<link rel='stylesheet' href='https://cdn.jsdelivr.net/npm/luckysheet/dist/css/luckysheet.css' />
<link rel='stylesheet' href='https://cdn.jsdelivr.net/npm/luckysheet/dist/assets/iconfont/iconfont.css' />
<script src="https://cdn.jsdelivr.net/npm/luckysheet/dist/plugins/js/plugin.js">script>
<script src="https://cdn.jsdelivr.net/npm/luckysheet/dist/luckysheet.umd.js">script>
1
2
3
4
5
6

3.组件实现

<template>
  <div class="test2">
    <div class="mb-md">
      <input type="file" @change="chageFile" ref="inputFile" />
      <button @click="exportExcelBtn">导出xlsx</button>
      <button @click="save">保存</button>
    </div>
    <!--web spreadsheet组件-->
    <div class="excel">
      <div id="luckysheetDom" style="margin: 0px; padding: 0px; width: 100%; height: 100%"></div>
    </div>
  </div>
</template>

<script>
//引入依赖包
import LuckyExcel from 'luckyexcel'
const luckysheet = window.luckysheet
//代码见下
import { exportExcel } from './export'
//本地存储，demo数据存储、回显使用
import local from '@/utils/local-storage'
export default {
  name: 'XspreadsheetDemo',
  data() {
    return {
      xs: null
    }
  },
  mounted() {
    this.init()
  },
  methods: {
    init() {
      let options = local.getItem('excel2')
      if (!options) {
        options = {
          container: 'luckysheetDom',
          title: '',
          lang: 'zh',
          showinfobar: false
        }
      }
      // 可开启只读模式
      // options.allowEdit = false
      luckysheet.create(options)
    },
    save() {
      let data = luckysheet.toJson()
      console.log(JSON.stringify(data))
      local.setItem('excel2', data)
    },
    exportExcelBtn() {
      // console.log(luckysheet.getluckysheetfile())
      exportExcel(luckysheet.getluckysheetfile(), '下载')
    },
    chageFile() {
      this.importExcel(this.$refs.inputFile.files[0])
    },
    importExcel(file) {
      let name = file.name
      //获取文件后缀
      let suffixArr = name.split('.'),
        suffix = suffixArr[suffixArr.length - 1]
      if (suffix !== 'xlsx') {
        alert('目前只能导入xlsx类型的文件')
        return
      }
      LuckyExcel.transformExcelToLucky(file, this.fileCb, this.errorCb)
    },
    fileCb(exportJson, luckysheetfile) {
      // 转换后获取工作表数据
      if (exportJson.sheets === null || exportJson.sheets.length === 0) {
        alert('无法读取excel文件的内容，当前不支持xls文件!')
        return
      }
      // console.log('exportJson', exportJson)
      // console.log('luckysheetfile', luckysheetfile)

      luckysheet.destroy()

      luckysheet.create({
        container: 'luckysheetDom', //luckysheet is the container id
        showinfobar: false,
        data: exportJson.sheets,
        title: exportJson.info.name,
        userInfo: exportJson.info.name.creator
      })
    },
    errorCb(error) {
      console.log(error)
    }
  }
}
</script>
<style scoped lang="less">
.test2 {
  width: 100%;
  height: 100%;
  display: flex;
  flex-direction: column;
  .excel {
    flex: 1;
  }
}
</style>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106

4.export.js

// import { createCellPos } from './translateNumToLetter'
import Excel from 'exceljs'

import FileSaver from 'file-saver'

const exportExcel = function (luckysheet, value) {
  // 参数为luckysheet.getluckysheetfile()获取的对象
  // 1.创建工作簿，可以为工作簿添加属性
  const workbook = new Excel.Workbook()
  // 2.创建表格，第二个参数可以配置创建什么样的工作表
  if (Object.prototype.toString.call(luckysheet) === '[object Object]') {
    luckysheet = [luckysheet]
  }
  luckysheet.forEach(function (table) {
    if (table.data.length === 0) {
      return true
    }
    // ws.getCell('B2').fill = fills.
    const worksheet = workbook.addWorksheet(table.name)
    const merge = (table.config && table.config.merge) || {}
    const borderInfo = (table.config && table.config.borderInfo) || {}
    // 3.设置单元格合并,设置单元格边框,设置单元格样式,设置值
    setStyleAndValue(table.data, worksheet)
    setMerge(merge, worksheet)
    setBorder(borderInfo, worksheet)
    return true
  })

  // return
  // 4.写入 buffer
  const buffer = workbook.xlsx.writeBuffer().then(data => {
    // console.log('data', data)
    const blob = new Blob([data], {
      type: 'application/vnd.ms-excel;charset=utf-8'
    })
    console.log('导出成功！')
    FileSaver.saveAs(blob, `${value}.xlsx`)
  })
  return buffer
}

var setMerge = function (luckyMerge = {}, worksheet) {
  const mergearr = Object.values(luckyMerge)
  mergearr.forEach(function (elem) {
    // elem格式：{r: 0, c: 0, rs: 1, cs: 2}
    // 按开始行，开始列，结束行，结束列合并（相当于 K10:M12）
    worksheet.mergeCells(elem.r + 1, elem.c + 1, elem.r + elem.rs, elem.c + elem.cs)
  })
}

var setBorder = function (luckyBorderInfo, worksheet) {
  if (!Array.isArray(luckyBorderInfo)) {
    return
  }
  // console.log('luckyBorderInfo', luckyBorderInfo)
  luckyBorderInfo.forEach(function (elem) {
    // 现在只兼容到borderType 为range的情况
    // console.log('ele', elem)
    if (elem.rangeType === 'range') {
      let border = borderConvert(elem.borderType, elem.style, elem.color)
      let rang = elem.range[0]
      // console.log('range', rang)
      let row = rang.row
      let column = rang.column
      for (let i = row[0] + 1; i < row[1] + 2; i++) {
        for (let y = column[0] + 1; y < column[1] + 2; y++) {
          worksheet.getCell(i, y).border = border
        }
      }
    }
    if (elem.rangeType === 'cell') {
      // col_index: 2
      // row_index: 1
      // b: {
      //   color: '#d0d4e3'
      //   style: 1
      // }
      const { col_index, row_index } = elem.value
      const borderData = Object.assign({}, elem.value)
      delete borderData.col_index
      delete borderData.row_index
      let border = addborderToCell(borderData, row_index, col_index)
      // console.log('bordre', border, borderData)
      worksheet.getCell(row_index + 1, col_index + 1).border = border
    }
    // console.log(rang.column_focus + 1, rang.row_focus + 1)
    // worksheet.getCell(rang.row_focus + 1, rang.column_focus + 1).border = border
  })
}
var setStyleAndValue = function (cellArr, worksheet) {
  if (!Array.isArray(cellArr)) {
    return
  }
  cellArr.forEach(function (row, rowid) {
    row.every(function (cell, columnid) {
      if (!cell) {
        return true
      }
      let fill = fillConvert(cell.bg)

      let font = fontConvert(cell.ff, cell.fc, cell.bl, cell.it, cell.fs, cell.cl, cell.ul)
      let alignment = alignmentConvert(cell.vt, cell.ht, cell.tb, cell.tr)
      let value = ''

      if (cell.f) {
        value = { formula: cell.f, result: cell.v }
      } else if (!cell.v && cell.ct && cell.ct.s) {
        // xls转为xlsx之后，内部存在不同的格式，都会进到富文本里，即值不存在与cell.v，而是存在于cell.ct.s之后
        // value = cell.ct.s[0].v
        cell.ct.s.forEach(arr => {
          value += arr.v
        })
      } else {
        value = cell.v
      }
      //  style 填入到_value中可以实现填充色
      let letter = createCellPos(columnid)
      let target = worksheet.getCell(letter + (rowid + 1))
      // console.log('1233', letter + (rowid + 1))
      for (const key in fill) {
        target.fill = fill
        break
      }
      target.font = font
      target.alignment = alignment
      target.value = value

      return true
    })
  })
}

var fillConvert = function (bg) {
  if (!bg) {
    return {}
  }
  // const bgc = bg.replace('#', '')
  let fill = {
    type: 'pattern',
    pattern: 'solid',
    fgColor: { argb: bg.replace('#', '') }
  }
  return fill
}

var fontConvert = function (ff = 0, fc = '#000000', bl = 0, it = 0, fs = 10, cl = 0, ul = 0) {
  // luckysheet：ff(样式), fc(颜色), bl(粗体), it(斜体), fs(大小), cl(删除线), ul(下划线)
  const luckyToExcel = {
    0: '微软雅黑',
    1: '宋体（Song）',
    2: '黑体（ST Heiti）',
    3: '楷体（ST Kaiti）',
    4: '仿宋（ST FangSong）',
    5: '新宋体（ST Song）',
    6: '华文新魏',
    7: '华文行楷',
    8: '华文隶书',
    9: 'Arial',
    10: 'Times New Roman ',
    11: 'Tahoma ',
    12: 'Verdana',
    num2bl: function (num) {
      return num !== 0
    }
  }
  // 出现Bug，导入的时候ff为luckyToExcel的val

  let font = {
    name: typeof ff === 'number' ? luckyToExcel[ff] : ff,
    family: 1,
    size: fs,
    color: { argb: fc.replace('#', '') },
    bold: luckyToExcel.num2bl(bl),
    italic: luckyToExcel.num2bl(it),
    underline: luckyToExcel.num2bl(ul),
    strike: luckyToExcel.num2bl(cl)
  }

  return font
}

var alignmentConvert = function (vt = 'default', ht = 'default', tb = 'default', tr = 'default') {
  // luckysheet:vt(垂直), ht(水平), tb(换行), tr(旋转)
  const luckyToExcel = {
    vertical: {
      0: 'middle',
      1: 'top',
      2: 'bottom',
      default: 'top'
    },
    horizontal: {
      0: 'center',
      1: 'left',
      2: 'right',
      default: 'left'
    },
    wrapText: {
      0: false,
      1: false,
      2: true,
      default: false
    },
    textRotation: {
      0: 0,
      1: 45,
      2: -45,
      3: 'vertical',
      4: 90,
      5: -90,
      default: 0
    }
  }

  let alignment = {
    vertical: luckyToExcel.vertical[vt],
    horizontal: luckyToExcel.horizontal[ht],
    wrapText: luckyToExcel.wrapText[tb],
    textRotation: luckyToExcel.textRotation[tr]
  }
  return alignment
}

var borderConvert = function (borderType, style = 1, color = '#000') {
  // 对应luckysheet的config中borderinfo的的参数
  if (!borderType) {
    return {}
  }
  const luckyToExcel = {
    type: {
      'border-all': 'all',
      'border-top': 'top',
      'border-right': 'right',
      'border-bottom': 'bottom',
      'border-left': 'left'
    },
    style: {
      0: 'none',
      1: 'thin',
      2: 'hair',
      3: 'dotted',
      4: 'dashDot', // 'Dashed',
      5: 'dashDot',
      6: 'dashDotDot',
      7: 'double',
      8: 'medium',
      9: 'mediumDashed',
      10: 'mediumDashDot',
      11: 'mediumDashDotDot',
      12: 'slantDashDot',
      13: 'thick'
    }
  }
  let template = {
    style: luckyToExcel.style[style],
    color: { argb: color.replace('#', '') }
  }
  let border = {}
  if (luckyToExcel.type[borderType] === 'all') {
    border['top'] = template
    border['right'] = template
    border['bottom'] = template
    border['left'] = template
  } else {
    border[luckyToExcel.type[borderType]] = template
  }
  // console.log('border', border)
  return border
}

function addborderToCell(borders, row_index, col_index) {
  let border = {}
  const luckyExcel = {
    type: {
      l: 'left',
      r: 'right',
      b: 'bottom',
      t: 'top'
    },
    style: {
      0: 'none',
      1: 'thin',
      2: 'hair',
      3: 'dotted',
      4: 'dashDot', // 'Dashed',
      5: 'dashDot',
      6: 'dashDotDot',
      7: 'double',
      8: 'medium',
      9: 'mediumDashed',
      10: 'mediumDashDot',
      11: 'mediumDashDotDot',
      12: 'slantDashDot',
      13: 'thick'
    }
  }
  // console.log('borders', borders)
  for (const bor in borders) {
    // console.log(bor)
    if (borders[bor].color.indexOf('rgb') === -1) {
      border[luckyExcel.type[bor]] = {
        style: luckyExcel.style[borders[bor].style],
        color: { argb: borders[bor].color.replace('#', '') }
      }
    } else {
      border[luckyExcel.type[bor]] = {
        style: luckyExcel.style[borders[bor].style],
        color: { argb: borders[bor].color }
      }
    }
  }

  return border
}

function createCellPos(n) {
  let ordA = 'A'.charCodeAt(0)

  let ordZ = 'Z'.charCodeAt(0)
  let len = ordZ - ordA + 1
  let s = ''
  while (n >= 0) {
    s = String.fromCharCode((n % len) + ordA) + s

    n = Math.floor(n / len) - 1
  }
  return s
}

export { exportExcel }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329