vue2前端导出Excel文件

目录

1、安装依赖

​2、demo

2.1 demo1导出效果

​2.2 demo2导出效果

2.3 demo3导出效果

3、源码

3.1 demo1

3.1 demo2

3.1 demo3​​​​​​​

---

1、安装依赖

对于简单的导出，前端处理相对后端在开发速度上，个人认为要快一些，项目中使用的第三方库为 xlsx 和 file-saver 

npm install xlsx file-saver 或 cnpm install xlsx file-saver

安装完成后，package.json文件生成如下：

2、demo

2.1 demo1导出效果

 2.2 demo2导出效果

 2.3 demo3导出效果

3、源码

<template>
  <div style="margin-left: 200px;margin-top: 50px">
    <el-button type="primary" @click="exportExcel1">导出Demo1</el-button>
    <el-button type="primary" @click="exportExcel2">导出Demo2</el-button>
    <el-button type="primary" @click="exportExcel3">导出Demo3</el-button>
  </div>
</template>

<script>
  import * as XLSX from 'xlsx';
  import { saveAs } from 'file-saver';
  import {export_json_to_excel2} from "@/views/pxf-demo/test2/Export2Excel.js";
  export default {
    data() {
      return {
        tableData: [{
          date: '2016-05-02',
          name: '王小虎',
          address: '上海市普陀区金沙江路 1518 弄'
        }, {
          date: '2016-05-04',
          name: '王小虎',
          address: '上海市普陀区金沙江路 1517 弄'
        }, {
          date: '2016-05-01',
          name: '王小虎',
          address: '上海市普陀区金沙江路 1519 弄'
        }, {
          date: '2016-05-03',
          name: '王小虎',
          address: '上海市普陀区金沙江路 1516 弄'
        }]
      }
    },
    methods: {
      // 格式转换，直接复制即可
      formatJson (filterVal, jsonData) {
        return jsonData.map(v => filterVal.map(j => v[j]));
      },
      exportExcel1() {
        const data = [                                    // 假设数据已经组装好
          ["日期", "姓名", "地址"],
          ["2016-05-01", "王小虎1", "上海市普陀区金沙江路 1516 弄"],
          ["2016-05-02", "王小虎2", "上海市普陀区金沙江路 1516 弄"],
          ["2016-05-03", "王小虎3", "上海市普陀区金沙江路 1516 弄"]
        ];
        const worksheet = XLSX.utils.aoa_to_sheet(data);   // 将数据转换为工作表
        const workbook = XLSX.utils.book_new();             // 创建工作簿并添加工作表
        XLSX.utils.book_append_sheet(workbook, worksheet, 'Sheet1');
        const excelBuffer = XLSX.write(workbook, { bookType: 'xlsx', type: 'array' }); // 生成Excel文件
        // 使用blob和FileReader创建一个URL然后下载
        const dataBlob = new Blob([excelBuffer], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8' });
        saveAs(dataBlob, 'demo1.xlsx');
      },
      exportExcel2() {
        const filterVal = ["date","name","address"]               //需要导出数据的key
        const tHeader = ["日期", "姓名", "地址"]                  //表头
        const data = this.formatJson(filterVal, this.tableData);  //tableData为调用后端接口返回的数据List
        data.unshift(tHeader)                                     //插入表头
        const worksheet = XLSX.utils.aoa_to_sheet(data);   // 将数据转换为工作表
        const workbook = XLSX.utils.book_new();             // 创建工作簿并添加工作表
        XLSX.utils.book_append_sheet(workbook, worksheet, 'Sheet1');
        const excelBuffer = XLSX.write(workbook, { bookType: 'xlsx', type: 'array' }); // 生成Excel文件
        // 使用blob和FileReader创建一个URL然后下载
        const dataBlob = new Blob([excelBuffer], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8' });
        saveAs(dataBlob, 'demo2.xlsx');
      },
      exportExcel3() {
        const filterVal = ["date","name","address"]               //需要导出数据的key
        const tHeader = ["日期", "姓名", "地址"]                  //表头
        const data = this.formatJson(filterVal, this.tableData);  //tableData为调用后端接口返回的数据List
        export_json_to_excel2(tHeader, data, 'demo3')
      }
    }
  };
</script>

3.1 demo1

源码中 【导出Demo1】是默认组装好的数据。

3.1 demo2

源码中 【导出Demo2】是调用后端接口返回的数据List，自己组装数据。

3.1 demo3

从上面效果可看出，对于数据过长时，列宽显示有瑕疵，【导出Demo3】是调用后端接口返回的数据List，自己组装数据，同时封装了列宽，可直接拿去使用。

 import {export_json_to_excel2} from "@/views/pxf-demo/test2/Export2Excel.js";

此文件源码：

require('script-loader!file-saver');
import XLSX from 'xlsx'

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_json_to_excel2(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);

  /*设置worksheet每列的最大宽度*/
  const colWidth = data.map(row => row.map(val => {
    /*先判断是否为null/undefined*/
    if (val == null) {
      return {
        'wch': 10
      };
    }
    /*再判断是否为中文*/
    else if (val.toString().charCodeAt(0) > 255) {
      return {
        'wch': val.toString().length * 2
      };
    } else {
      return {
        'wch': val.toString().length
      };
    }
  }))
  /*以第一行为初始值*/
  let result = colWidth[0];
  for (let i = 1; i < colWidth.length; i++) {
    for (let j = 0; j < colWidth[i].length; j++) {
      if (result[j]['wch'] < colWidth[i][j]['wch']) {
        result[j]['wch'] = colWidth[i][j]['wch'];
      }
    }
  }
  ws['!cols'] = result;

  /* 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")
}