方案二: 纯前端导出Excel

数据没有分页的情况下, 用纯前端导出即可

• Excel 的导入导出都是依赖于js-xlsx来实现的。
• 在 js-xlsx的基础上又封装了Export2Excel.js来方便导出数据。

• 方案二: 纯前端导出Excel
  • 效果图
  • 使用
  • 注意
  • 参数
  • 项目实战
  • 新建一个exportExcel.vue模板用于导出Excel表格,使用代码如下

效果图

使用

由于 Export2Excel不仅依赖js-xlsx还依赖file-saver和script-loader。

所以你先需要安装如下命令：

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

由于js-xlsx体积还是很大的，导出功能也不是一个非常常用的功能，所以使用的时候建议使用懒加载。使用方法如下：

import('@/vendor/Export2Excel').then(excel => {
  excel.export_json_to_excel({
    header: tHeader, //表头 必填
    data, //具体数据 必填
    filename: 'excel-list', //非必填, 导出文件的名字
    autoWidth: true, //非必填, 导出文件的排列方式
    bookType: 'xlsx' //非必填, 导出文件的格式
  })
})

注意

在v3.9.1+以后的版本中移除了对 Bolb 的兼容性代码，如果你还需要兼容很低版本的浏览器可以手动引入blob-polyfill进行兼容。

参数

参数	说明	类型	可选值	默认值
header	导出数据的表头	Array	/	[]
data	导出的具体数据	Array	/	[]
filename	导出文件名	String	/	excel-list
autoWidth	单元格是否要自适应宽度	Boolean	true / false	true
bookType	导出文件类型	String	xlsx, csv, txt, more	xlsx

项目实战

使用脚手架搭建出基本项目雏形,这时候在src目录下新建一个vendor(文件名自己定义)文件夹,新建一个Export2Excel.js文件,这个文件里面在js-xlsx的基础上又封装了Export2Excel.js来方便导出数据。

• 目录如下

• Export2Excel.js代码如下

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

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);
  var oo = generateArray(theTable);
  var ranges = oo[1];

  /* original data */
  var data = oo[0];
  var ws_name = "SheetJS";

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

export function export_json_to_excel({
  multiHeader = [],
  header,
  data,
  filename,
  merges = [],
  autoWidth = true,
  bookType = 'xlsx'
} = {}) {
  /* original data */
  filename = filename || 'excel-list'
  data = [...data]
  data.unshift(header);

  for (let i = multiHeader.length - 1; i > -1; i--) {
    data.unshift(multiHeader[i])
  }

  var ws_name = "SheetJS";
  var wb = new Workbook(),
    ws = sheet_from_array_of_arrays(data);

  if (merges.length > 0) {
    if (!ws['!merges']) ws['!merges'] = [];
    merges.forEach(item => {
      ws['!merges'].push(XLSX.utils.decode_range(item))
    })
  }

  if (autoWidth) {
    /*设置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: bookType,
    bookSST: false,
    type: 'binary'
  });
  saveAs(new Blob([s2ab(wbout)], {
    type: "application/octet-stream"
  }), `${filename}.${bookType}`);
}

新建一个exportExcel.vue模板用于导出Excel表格,使用代码如下

<template>
    <div class="salebill">
        <div class="salebill-first">
            <div class="salebill-search">
                <el-row>
                    <el-col :span="7">
                        <!--导出文件名称-->
                        <div class="filename">
                            <label class="radio-label"
                                   style="padding-left:0;">导出文件名:</label>
                            <el-input v-model="filename"
                                      placeholder="请输入导出文件名"
                                      style="width:200px;marginLeft:20px"
                                      prefix-icon="el-icon-document" />
                        </div>

                    </el-col>
                    <el-col :span="7">
                        <!--设置表格导出的宽度是否自动-->
                        <div class="autoWidth">
                            <label class="radio-label">表格宽度是否自动:</label>
                            <el-radio-group v-model="autoWidth"
                                            style="marginLeft:20px">
                                <el-radio :label="true"
                                          border>True</el-radio>
                                <el-radio :label="false"
                                          border>False</el-radio>
                            </el-radio-group>
                        </div>
                    </el-col>
                    <el-col :span="6">
                        <!--导出文件后缀类型-->
                        <div class="bookType">
                            <label class="radio-label">文件类型:</label>
                            <el-select v-model="bookType"
                                       style="width:120px;marginLeft:20px">
                                <el-option v-for="item in options"
                                           :key="item"
                                           :label="item"
                                           :value="item" />
                            </el-select>
                        </div>
                    </el-col>
                    <el-col :span="4">
                        <div class="grid-content bg-purple-light fr">
                            <el-button type="primary"
                                       @click="handleDownload">导出Excel</el-button>
                        </div>
                    </el-col>
                </el-row>
            </div>
            <el-table :data="list"
                      fixed="right"
                      border
                      fit
                      highlight-current-row
                      align="center"
                      v-loading="loading"
                      style="width: 100%"
                      class="notshop-table"
                      show-summary
                      :header-cell-style="{
            background: '#eef1f6',
            color: '#606266'
          }">
                <el-table-column v-for="item in rightHeader"
                                 :key="item.key"
                                 :property="item.key"
                                 :label="item.label">
                    <template slot-scope="scope">
                        <span>{{scope.row[scope.column.property]}}</span>
                    </template>
                </el-table-column>
            </el-table>
        </div>
    </div>
</template>

<script>
export default {
    name: "salebill",
    components: {},
    data () {
        return {
            saleForm: {
            },
            // loding窗口状态
            loading: false,
            rightHeader: [
                {
                    label: '销售商',
                    key: 'name'
                },
                {
                    label: '日期',
                    key: 'date'
                },
                {
                    label: '数量',
                    key: 'count'
                },
                {
                    label: '销售总价',
                    key: 'saleTotalPrice'
                },
                {
                    label: '支付金额',
                    key: 'payMoney'
                },
                {
                    label: '采购金额',
                    key: 'purseMoney'
                },
                {
                    label: '服务费',
                    key: 'servePrice'
                },
                {
                    label: '手续费',
                    key: 'procedurePrice'
                },
                {
                    label: '利润',
                    key: 'profit'
                },
            ],
            // ***********************  导出Execl相关参数配置  ****************************
            // 列表数据
            list: [
                {
                    id: "12987122",
                    name: "星城哈哈",
                    date: "2020-12-12",
                    count: 100,
                    saleTotalPrice: 100,
                    payMoney: 80,
                    purseMoney: 30,
                    servePrice: 3,
                    procedurePrice: 0.5,
                    profit: 1.5
                },
                {
                    id: "12987122",
                    name: "星城哈哈",
                    date: "2020-12-12",
                    count: 100,
                    saleTotalPrice: 100,
                    payMoney: 80,
                    purseMoney: 30,
                    servePrice: 3,
                    procedurePrice: 0.5,
                    profit: 1.5
                },
                {
                    id: "12987122",
                    name: "星城哈哈",
                    date: "2020-12-12",
                    count: 100,
                    saleTotalPrice: 100,
                    payMoney: 80,
                    purseMoney: 30,
                    servePrice: 3,
                    procedurePrice: 0.5,
                    profit: 1.5
                }
            ],
            // 导出文件名称
            filename: "星城哈哈",
            // 导出表格宽度是否auto
            autoWidth: true,
            // 导出文件格式
            bookType: "xlsx",
            // 默认导出文件后缀类型
            options: ["xlsx", "csv", "txt"]
        };
    },
    mounted () {
    },
    methods: {
        // 导出Excel表格
        handleDownload () {
            this.loading = true;
            // 懒加载该用法
            import("@/vendor/Export2Excel").then(excel => {
                // 设置导出表格的头部
                const tHeader = ["序号","销售商", "日期", "数量", "销售总价", "支付金额", "采购金额", "服务费", "手续费", "利润"];
                // 设置要导出的属性
                const filterVal = [
                    "id",
                    "name",
                    "date",
                    "count",
                    "saleTotalPrice",
                    "payMoney",
                    "purseMoney",
                    "servePrice",
                    "procedurePrice",
                    "profit"
                ];
                // 获取当前展示的表格数据
                const list = this.list;
                // 将要导出的数据进行一个过滤
                const data = this.formatJson(filterVal, list);
                // 调用我们封装好的方法进行导出Excel
                excel.export_json_to_excel({
                    // 导出的头部
                    header: tHeader,
                    // 导出的内容
                    data,
                    // 导出的文件名称
                    filename: this.filename,
                    // 导出的表格宽度是否自动
                    autoWidth: this.autoWidth,
                    // 导出文件的后缀类型
                    bookType: this.bookType
                });
                this.loading = false;
            });
        },
        // 对要导出的内容进行过滤
        formatJson (filterVal, jsonData) {
            return jsonData.map(v =>
                filterVal.map(j => {
                    if (j === "timestamp") {
                        return this.parseTime(v[j]);
                    } else {
                        return v[j];
                    }
                })
            );
        },

    },
};
</script>

<style lang="scss" scoped>
.salebill {
    padding: 10px;
    .salebill-first {
        .salebill-search {
            white-space: nowrap;
            height: 80px;
            line-height: 80px;
            padding: 0 26px;
            background: #fff;
            box-shadow: 0px 1px 16px 0px rgba(209, 209, 209, 0.54);
            margin-bottom: 20px;
            .sale-left {
                margin-right: 15px;
            }
        }
    }
    .salebill-detailed {
        display: flex;
        .sale-item {
            padding: 10px;
            border-radius: 5px;
            color: #409eff;
            cursor: pointer;
            &:first-child {
                margin-left: 15px;
                margin-right: 10px;
            }
        }
        .active-item {
            background-color: #409eff;
            color: #fff;
        }
    }
}
</style>