vue项目中纯前端Excel文件(.xlsx)的导入导出

你的名字 2022-10-06 01:57 276阅读 0赞

#### 目录 ####

> 应用场景  
> Excel文件导入  
> Excel文件导出

##### 应用场景 #####

在需要展示大量数据的表格中，一条条插入显然有些鸡肋。此时我们会想到使用文件的形式插入。Excel表格的插入方式往往是种不错的选择。如果当Excel文件上传时后端无暇解析怎么办。此时我们就需要在前端进行Excel文件的解析，用后端容易接收的形式进行添加。那么如果这个Excel文件的添加面向用户，用户不清楚添加模板怎么搞，此时我们就需要为用户提供一套模板，可以导出Excel文件（模板）。

##### Excel文件的导入/导出 #####

1. 此处封装了导入、导出组件

需要安装的库文件有：`file-saver xlsx`、`script-loader`

//npm 
    npm install -S file-saver xlsx
    npm install -D script-loader

组件是以弹窗的形式展示（如下）

<template>
      <div>
        <a-modal
          v-drag-dialog
          :title="title"
          :visible="visible"
          :maskClosable="maskClosable"
          :confirmLoading="confirmLoading"
          :destroyOnClose="destroyOnClose"
          @ok="onOk"
          @cancel="onCancel"
          @afterClose="afterClose"
        >
          <a-upload-dragger
            accept=".xlsx, .xlc, .xlm, .xls"
            :multiple="false"
            action=""
            :showUploadList="false"
            :beforeUpload="beforeUpload"
          >
            <p class="ant-upload-drag-icon">
              <a-icon type="inbox" />
            </p>
            <p class="ant-upload-text">单击或拖动文件到此区域上载</p>
            <p class="ant-upload-hint">
              支持单个文件上传。 只能上传xlsx文件，且不超过500kb
            </p>
          </a-upload-dragger>
          点击下载导入<a-button type="link" @click="exportExcel">模板</a-button>
          <a-progress :percent="percent" />
          <a-alert
            v-if="percent >= 100"
            message="验证成功"
            description="数据验证成功，请点击确定按钮进行提交."
            type="success"
            show-icon
          />
          <a-alert
            v-if="error"
            message="验证出错"
            :description="errmsg"
            type="error"
            show-icon
          />
        </a-modal>
      </div>
    </template>
    
    <script>
    import {  file2Xce } from '../../../utils/xlsxUtil';
    
    export default { 
      name: 'UploadIssue',
      data() { 
        return { 
          title: '批量添加',
          uploadColumn: { 
            body: {  field: 'body', rule: this.$Rules.notNull('题干验证出错') },
            a: {  field: 'a' },
            b: {  field: 'b' },
            c: {  field: 'c' },
            d: {  field: 'd' },
            answer: {  field: 'answer' },
            note: {  field: 'note' },
          },
          tableDataUpload: [],
          percent: 0,
          error: false,
          errmsg: '数据验证出错',
          tableData: [
            { body:'题干内容',a: "A选项", b: "B选项",c: "C选项",d: "D选项",answer:'正确答案必须是数字（1:A,2:B,3:C,4:D）',note:'答案的解释（选填）'},
          ]
        };
      },
      methods: { 
        // 导出模板的操作
        exportExcel() { 
          require.ensure([], () => { 
            const {  export_json_to_excel } = require('../../../excel/Export2Excel');
            const tHeader = ['body', 'a', 'b', 'c', 'd','answer','note'];
            // 上面设置Excel的表格第一行的标题
            const filterVal = ['body', 'a', 'b', 'c', 'd','answer','note'];
            // 上面的index、nickName、name是tableData里对象的属性
            const list = this.tableData; //把data里的tableData存到list
            const data = this.formatJson(filterVal, list);
            export_json_to_excel(tHeader, data, '批量添加题库模板');
          });
        },
        formatJson(filterVal, jsonData) { 
          return jsonData.map((v) => filterVal.map((j) => v[j]));
        },
        beforeUpload(file, fileList) { 
          const fileSplit = file.name.split('.');
          const types = fileSplit[fileSplit.length - 1];
          const fileType = ['xlsx', 'xlc', 'xlm', 'xls', 'xlt'].some(
            (item) => item === types
          );
          file2Xce(file).then(async (tabJson) => { 
            console.log('解析的数据', tabJson[0]);
            // 初始化
            this.tableDataUpload = [];
            this.percent = 0;
            this.error = false;
            try { 
              if (tabJson && tabJson.length > 0) { 
                if (tabJson[0].sheet) { 
                  // sheet1 数据
                  const dataSheet1 = tabJson[0].sheet;
                  const unit = 99 / dataSheet1.length;
                  // 遍历 sheet1 数据
                  for (let i = 0; i < dataSheet1.length; i++) { 
                    let data = { };
                    for (let key of Object.keys(this.uploadColumn)) { 
                      let item = this.uploadColumn[key];
                      const value = dataSheet1[i][item.field]; // 数据
                      if (item.rule) { 
                        // 需要进行权限验证
                        const validator = await this.$Rules.rule(item.rule, value);
                        // 如果验证不通过则抛出异常
                        if (validator) { 
                          throw '第' + (i + 1) + '行' + validator;
                        }
                      }
                      data[key] = value ? `${ value}` : undefined;
                    }
                    this.tableDataUpload.push(data);
                    this.percent = this.percent + unit;
                  }
                  this.percent = 100;
                  // 结束
                  console.log('tableDataUpload', this.tableDataUpload);
                }
              }
            } catch (error) { 
              this.errmsg = error;
              this.error = true;
              this.$notification.error({  message: error });
            }
          });
          return false;
        },
        async onConfirm() { 
          if (this.tableDataUpload.length > 0) { 
            this.confirmLoading = true;
            try { 
              const result = await this.$api.PostIssueList(this.tableDataUpload);
              if (result.code == 0) { 
                this.$message.success('添加成功！');
                this.$emit('success');
                this.visible = false;
              } else { 
                this.$message.error('添加失败！' + result.msg);
              }
            } catch (error) { 
              this.$message.error('添加异常！' + error.message);
            }
            this.confirmLoading = false;
          }
        },
        show() { 
          this.tableDataUpload = [];
          this.percent = 0;
          this.confirmLoading = false;
          this.error = false;
          this.visible = true;
        },
      },
    };
    </script>

2. xlsxUtil.js文件

import XLSX from "xlsx"
    
    // 文件转xlsx
    export const file2Xce = (file) => { 
            return new Promise(function(resolve, reject) { 
                const reader = new FileReader()
                reader.onload = function(e) { 
                    const data = e.target.result
                    let wb = XLSX.read(data, { 
                        type: 'binary'
                    })
                    const result = []
                    wb.SheetNames.forEach((sheetName) => { 
                        result.push({ 
                            sheetName: sheetName,
                            sheet: XLSX.utils.sheet_to_json(wb.Sheets[sheetName])
                        })
                    })
                    resolve(result)
                }
                reader.readAsBinaryString(file)
            })
        }
        // export default { file2Xce }

***提示：以上主要用于导入***  
3. 在src目录下创建excel文件夹包含文件（Blob.js、Export2Excel.js）  
**Blob.js**

**Export2Excel.js**

require('script-loader!file-saver');
    require('./Blob.js');
    require('script-loader!xlsx/dist/xlsx.core.min');
    
    function generateArray(table) { 
        var out = [];
        var rows = table.querySelectorAll('tr');
        var ranges = [];
        for (var R = 0; R < rows.length; ++R) { 
            var outRow = [];
            var row = rows[R];
            var columns = row.querySelectorAll('td');
            for (var C = 0; C < columns.length; ++C) { 
                var cell = columns[C];
                var colspan = cell.getAttribute('colspan');
                var rowspan = cell.getAttribute('rowspan');
                var cellValue = cell.innerText;
                if (cellValue !== "" && cellValue == +cellValue) cellValue = +cellValue;
                //Skip ranges
                ranges.forEach(function(range) { 
                    if (R >= range.s.r && R <= range.e.r && outRow.length >= range.s.c && outRow.length <= range.e.c) { 
                        for (var i = 0; i <= range.e.c - range.s.c; ++i) outRow.push(null);
                    }
                });
    
                //Handle Row Span
                if (rowspan || colspan) { 
                    rowspan = rowspan || 1;
                    colspan = colspan || 1;
                    ranges.push({  s: {  r: R, c: outRow.length }, e: {  r: R + rowspan - 1, c: outRow.length + colspan - 1 } });
                };
    
                //Handle Value
                outRow.push(cellValue !== "" ? cellValue : null);
    
                //Handle Colspan
                if (colspan)
                    for (var k = 0; k < colspan - 1; ++k) outRow.push(null);
            }
            out.push(outRow);
        }
        return [out, ranges];
    };
    
    function datenum(v, date1904) { 
        if (date1904) v += 1462;
        var epoch = Date.parse(v);
        return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000);
    }
    
    function sheet_from_array_of_arrays(data, opts) { 
        var ws = { };
        var range = {  s: {  c: 10000000, r: 10000000 }, e: {  c: 0, r: 0 } };
        for (var R = 0; R != data.length; ++R) { 
            for (var C = 0; C != data[R].length; ++C) { 
                if (range.s.r > R) range.s.r = R;
                if (range.s.c > C) range.s.c = C;
                if (range.e.r < R) range.e.r = R;
                if (range.e.c < C) range.e.c = C;
                var cell = {  v: data[R][C] };
                if (cell.v == null) continue;
                var cell_ref = XLSX.utils.encode_cell({  c: C, r: R });
    
                if (typeof cell.v === 'number') cell.t = 'n';
                else if (typeof cell.v === 'boolean') cell.t = 'b';
                else if (cell.v instanceof Date) { 
                    cell.t = 'n';
                    cell.z = XLSX.SSF._table[14];
                    cell.v = datenum(cell.v);
                } else cell.t = 's';
    
                ws[cell_ref] = cell;
            }
        }
        if (range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range);
        return ws;
    }
    
    function Workbook() { 
        if (!(this instanceof Workbook)) return new Workbook();
        this.SheetNames = [];
        this.Sheets = { };
    }
    
    function s2ab(s) { 
        var buf = new ArrayBuffer(s.length);
        var view = new Uint8Array(buf);
        for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
        return buf;
    }
    
    export function export_table_to_excel(id) { 
        var theTable = document.getElementById(id);
        console.log('a')
        var oo = generateArray(theTable);
        var ranges = oo[1];
    
        /* original data */
        var data = oo[0];
        var ws_name = "SheetJS";
        console.log(data);
    
        var wb = new Workbook(),
            ws = sheet_from_array_of_arrays(data);
    
        /* add ranges to worksheet */
        // ws['!cols'] = ['apple', 'banan'];
        ws['!merges'] = ranges;
    
        /* add worksheet to workbook */
        wb.SheetNames.push(ws_name);
        wb.Sheets[ws_name] = ws;
    
        var wbout = XLSX.write(wb, {  bookType: 'xlsx', bookSST: false, type: 'binary' });
    
        saveAs(new Blob([s2ab(wbout)], {  type: "application/octet-stream" }), "test.xlsx")
    }
    
    function formatJson(jsonData) { 
        console.log(jsonData)
    }
    export function export_json_to_excel(th, jsonData, defaultTitle) { 
    
        /* original data */
    
        var data = jsonData;
        data.unshift(th);
        var ws_name = "SheetJS";
    
        var wb = new Workbook(),
            ws = sheet_from_array_of_arrays(data);
    
    
        /* add worksheet to workbook */
        wb.SheetNames.push(ws_name);
        wb.Sheets[ws_name] = ws;
    
        var wbout = XLSX.write(wb, {  bookType: 'xlsx', bookSST: false, type: 'binary' });
        var title = defaultTitle || '列表'
        saveAs(new Blob([s2ab(wbout)], {  type: "application/octet-stream" }), title + ".xlsx")
    }

4. 在main.js中引入excel文件夹下的js文件

// 导出方法
    import './excel/Blob'
    import './excel/Export2Excel.js'