likes
comments
collection
share

纯前端导出Excel详细过程(包含按列导出+表格带样式+表格合并+表格嵌套表格+中英文两个sheet)

作者站长头像
站长
· 阅读数 30

一、前言

vue项目纯前端导出Excel,数据结构数组中包含数组,实现按所需的列导出Excel,运用到xlsx-populate插件,导出的Excel文件:带边框、首列合并、表格嵌套表格、分别生成chinese和english两个工作表。导出表格实现效果如下:

纯前端导出Excel详细过程(包含按列导出+表格带样式+表格合并+表格嵌套表格+中英文两个sheet)

二、准备工作

1.安装依赖

npm install file-saver -S                //使用版本:^2.0.5
npm install script-loader -S             //使用版本:^0.7.2
npm install xlsx -S                      //使用版本:^0.17.3
//xlsx-populate使excel带样式导出
npm install xlsx-populate -S             //使用版本:^1.21.0

2.引入js文件工具库

在src文件夹下新建excel文件夹,添加export.js文件。

有个需求是生成一个chinese和一个english工作表。如果只需要生成一个工作表的可以只创建一个工作表。

//导入 xlsx 模块
import XLSX from "xlsx";
//导入 xlsxPopulate 模块
import XlsxPopulate from "xlsx-populate";
// 用于统一设置报表的样式  “A" "B"  EXCEL 的列
const alphabetList = ["A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z",];
function workbook2blob(workbook) {
  // 生成excel的配置项
  const wopts = {
    // 要生成的文件类型
    bookType: "xlsx",
    // 是否生成Shared String Table,官方解释是,如果开启生成速度会下降,但在低版本IOS设备上有更好的兼容性
    bookSST: false,
    type: "binary",
  };
  const wbout = XLSX.write(workbook, wopts);
  // 将字符串转ArrayBuffer
  function s2ab(s) {
    const buf = new ArrayBuffer(s.length);
    const view = new Uint8Array(buf);
    for (let i = 0; i !== s.length; ++i) view[i] = s.charCodeAt(i) & 0xff;
    return buf;
  }
  const blob = new Blob([s2ab(wbout)], {
    type: "application/octet-stream",
  });
  return blob;
}
//导出 handleExport --> 只创建一个工作表时,只传tableZh,titleZh,dataInfo就可。
export function handleExport(tableZh,tableEn,titleZh,titleEn,dataInfo) {
  //创建一个空工作簿
  const wb = XLSX.utils.book_new();
  //表格数据——中英文版
  const finalDataZh = [...titleZh, ...tableZh];
  const finalDataEn = [...titleEn, ...tableEn];
  //将json数据转为sheet
  const sheetZh = XLSX.utils.json_to_sheet(finalDataZh, { skipHeader: true,});
  const sheetEn = XLSX.utils.json_to_sheet(finalDataEn, { skipHeader: true,});
  //创建工作表,第三个参数为生成excel的sheet名称
  XLSX.utils.book_append_sheet(wb, sheetZh, "Chinese");
  XLSX.utils.book_append_sheet(wb, sheetEn, "English");
  const workbookBlob = workbook2blob(wb);
  //dataInfo为接受的样式和合并参数
  return addStyle(workbookBlob, dataInfo);
}
//添加样式的方法
function addStyle(workbookBlob, dataInfo) {
  return XlsxPopulate.fromDataAsync(workbookBlob).then((workbook) => {
    // 循环所有的表改变样式
    for (let index = 0; index < workbook._maxSheetId; index++) {
      // 设置行高 sheet(’sheet号’).row(行号).height('行高')
      workbook.sheet(index).row(1).height(25);
      // 取消垂直居中
      // workbook.sheet(index).printOptions("verticalCentered", undefined);
      //打印页边距预模板
      workbook.sheet(index).pageMarginsPreset("narrow");
      //可以给指定的格子添加内容并合并单元格
      // workbook.sheet(index).range("M43:P43").value('要写入的内容:').merged(true)
    }
    workbook.sheets().forEach((sheet) => {
      // 所有cell垂直居中,修改字体
      sheet.usedRange().style({
        fontFamily: "Arial",
        verticalAlignment: "center",
      });
      // 去除所有边框 (网格线)
      // sheet.gridLinesVisible(false);
      // 设置单元格宽度
      alphabetList.forEach((item) => {
        sheet.column(item).width(15);
      });
      // 合并单元格
      if(dataInfo.mergesRange){
        for(let i=0;i<dataInfo.mergesRange.length;i++){
          sheet.range(dataInfo.mergesRange[i]).merged(true).style({
          //水平居中
          horizontalAlignment: "center",
          //垂直居中
          verticalAlignment: "center",
          });
        }
      }
      // .style 是添加样式 --> title加粗合并及居中
      sheet.range(dataInfo.titleRange).merged(true).style({
        //加粗
        bold: true,
        //水平居中
        horizontalAlignment: "center",
        //垂直居中
        verticalAlignment: "center",
        //字号
        fontSize: 14,
      });
      sheet.range(dataInfo.tbodyRange).style({
        horizontalAlignment: "center",
        //内容放不下时候允许换行
        wrapText: true,
        fontSize: 10,
      });
      if(dataInfo.keystyle){
        for(let i=0;i<dataInfo.keystyle.length;i++){
          sheet.column(dataInfo.keystyle[i]).style({
            wrapText: true,
            horizontalAlignment: "left",
          });
          sheet.column(dataInfo.keystyle[i]).width(60)
        }
      }
      // 表头加粗及背景色
      sheet.range(dataInfo.theadRange).style({
        wrapText: true,
        fill: "C9C7C7",
        bold: true,
        horizontalAlignment: "center",
        fontSize: 10,
      });
      // 表格黑色细边框
      sheet.range(dataInfo.tableRange).style({
        border: {
          style: "thin",
          color: "000000",
          direction: "both",
        },
      });
    });
    return workbook.outputAsync().then(
      (workbookBlob) => URL.createObjectURL(workbookBlob) // 创建blob地址
    );
  });
}

三、代码实现

1、代码文件位置

纯前端导出Excel详细过程(包含按列导出+表格带样式+表格合并+表格嵌套表格+中英文两个sheet)

纯前端导出Excel详细过程(包含按列导出+表格带样式+表格合并+表格嵌套表格+中英文两个sheet)

2、表格数据结构

做项目时是从后台数据库获取的表格数据,这里为了直接定义静态数据,数组包含对象数组,结构复杂,格式为:

纯前端导出Excel详细过程(包含按列导出+表格带样式+表格合并+表格嵌套表格+中英文两个sheet)

3、组件代码实现

3.1 Demos父组件代码

<template>
    <div>
        <el-tabs v-model="activeName" type="border-card" style="height: calc(100vh - 62px);">
            <el-tab-pane label="按需导出Excel" name="first">
                <ExportExcel :totaltableList="totaltableList"></ExportExcel>
                <el-table :data="totaltableList" border>
                    <el-table-column label="考核名称" prop="kpi_name"></el-table-column>
                    <el-table-column label="归属用户" prop="user_name"></el-table-column>
                    <el-table-column label="开始日期" prop="start_time"></el-table-column>
                    <el-table-column label="操作" width="130px">
                        <el-button type="primary" icon="el-icon-edit" size="mini"></el-button>&nbsp;
                        <el-button type="danger" icon="el-icon-delete" size="mini"></el-button>
                    </el-table-column>
                </el-table>
            </el-tab-pane>
            <el-tab-pane label="表格拖拽" name="second">表格列表拖拽</el-tab-pane>
            <el-tab-pane label="下拉框嵌入表格" name="third">下拉框嵌入表格</el-tab-pane>
        </el-tabs>
    </div>
</template>
<script>
import ExportExcel from './ExportExcel';
export default {
    name:"Demos",
    components:{
        ExportExcel,
    },
    data() {
        return {
            activeName: 'first',
            totaltableList:[
                {
                    "id": 1,
                    "kpi_name":'2022年4月月度考核表',
                    "kpi_name_en":'2022-04-monthly-check-form',
                    "user_name":"张三",
                    "start_time":"2022-04-01",
                    "goal_list": [
                        {
                            "goal": "出勤率",
                            "goal_en": "attendance rate",
                            "kpi_method": "出勤率达到100%,迟到一次扣10元。",
                            "kpi_method_en": "If the attendance rate reaches 100%, 10 yuan will be deducted once being late.",
                            "weight": 50,
                            "end_score": 99,
                        },
                        {
                            "goal": "工作态度",
                            "goal_en": "working attitude",
                            "kpi_method": "积极主动,态度端正。",
                            "kpi_method_en": "Initiative and good attitude.",
                            "weight": 50,
                            "end_score": 98,
                        },
                    ]
                },
                {
                    "id": 2,
                    "kpi_name":'2022年5月月度考核表',
                    "kpi_name_en":'2022-05-monthly-check-form',
                    "user_name":"李四",
                    "start_time":"2022-05-01",
                    "goal_list": [
                        {
                            "goal": "出勤率",
                            "goal_en": "attendance rate",
                            "kpi_method": "出勤率达到100%,迟到一次扣10元。",
                            "kpi_method_en": "If the attendance rate reaches 100%, 10 yuan will be deducted once being late.",
                            "weight": 50,
                            "end_score": 100,
                        },
                        {
                            "goal": "工作态度",
                            "goal_en": "working attitude",
                            "kpi_method": "积极主动,态度端正。",
                            "kpi_method_en": "Initiative and good attitude.",
                            "weight": 50,
                            "end_score": 100,
                        },
                    ]
                },
            ],
        }
    },
}
</script>
<style scoped>
::v-deep .el-table .cell{
    text-align: center;
}
</style>

3.2 ExportExcel子组件代码

纯前端导出Excel详细过程(包含按列导出+表格带样式+表格合并+表格嵌套表格+中英文两个sheet)

<template>
    <div>
        <div><el-button class="exportBtn" size="small" @click="selectcloumnDrawer = true">导出Excel</el-button></div>
        <!-- 按需导出列 -->
        <el-dialog :visible.sync="selectcloumnDrawer" title="选择列" @close="closeDialog()">
         <ExportColumn :columnList="columnListModel" @exportExcel="exportExcel"></ExportColumn>
        </el-dialog>
    </div>
</template>
<script>
// 引入选择按列导出弹窗组件
import ExportColumn from './ExportColumn'
import {handleExport} from '../../../../excel/export.js'

export default {
  components: {
    ExportColumn,
  },
  props:{
      totaltableList:{
          type:Array,
          default:()=>[]
      }
  },
  data(){
    return {
      selectcloumnDrawer:false,//控制导出列弹窗显示
      columnList:[],//选中的列
      columnListModel:[//全部列
        { name:"考核名称",
          name_en:"Check Name",
          field_code:"kpi_name", },
        { name:"归属用户",
          name_en:"belong user",
          field_code:"user_name", },
        { name:"开始日期",
          name_en:"start time",
          field_code:"start_time", },
        {  name:"目标详情",
          name_en:"goal detail",
          field_code:"goal_list", },
      ],
    }
  },
  methods:{
    //导出excel
    exportExcel(checkedColumn) {
      this.columnList = checkedColumn
      this.exportExcelfn()
    },
    exportExcelfn(){
      const alphabetList = ["A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"]
      //主表格数据
      const tHeaderZh = this.columnList.map((p)=>{return p.name})//选择导出列的中文表头
      const tHeaderEn = this.columnList.map((p)=>{return p.name_en})//选择导出列的英文表头
      const filterVal = this.columnList.map((p)=>{return p.field_code})//选择导出列的字段key
      //获取导出表格的原始数据
      var list = JSON.parse(JSON.stringify(this.totaltableList))//totaltableList是表格json数据
      const isHasChildExcel = filterVal.includes('goal_list')//判断是否导出目标详情子表格
      //根据goal_list长度新增行,导出Excel包含子表格时执行
      if(isHasChildExcel){
        //子表格数据
        const cHeaderZh = ["考核项目","考核办法","比重(%)","最后得分"]
        const cHeaderEn = ["Check Item","Check Target","weight(%)","Check Method","score"]
        const cfilterVal = ["goal","kpi_method","weight","end_score"]
        const endData = { goalValue:cHeaderZh, goalEnValue:cHeaderEn, keyValue:cfilterVal, }
        var cHeaderLen = cHeaderZh.length
        //根据goal_list长度新增行
        list.forEach((p,index) => {
          var ret = []
          if(p.goal_list.length === 0){
            ret.push(p)
          }else{
            p.goal_list.unshift(endData)
            for(var i = 0;i < p.goal_list.length;i++){
              ret.push(p)
            }
          }
          list.splice(index,1,ret)
        })
        list = [].concat(...list)
        //包含嵌套表格数据时导出的表头数据
        for(var i = 1;i < cHeaderZh.length;i++){
          tHeaderZh.push('目标详情')
          tHeaderEn.push('goal detail')
        }
        filterVal.splice(-1,1,...cfilterVal)
      }
      //将数组转为二维数组
      const dataZh = this.formatJson(filterVal, list);
      const dataEn = this.formatJson(filterVal, list,"language");
      //将数组中的对象转为{A:'',B:''}格式
      const tableZh = this.changecolmuntokey(tHeaderZh,dataZh,alphabetList)
      const tableEn = this.changecolmuntokey(tHeaderEn,dataEn,alphabetList)
      //设置合并单元格行
      if(isHasChildExcel){
        var rowSpanList = []
        let rowSpan = {}
        const goallength = this.totaltableList.map(p=>p.goal_list.length)
        const columnlen = isHasChildExcel?tHeaderZh.length-cHeaderLen:tHeaderZh.length
        for(let j=0;j<columnlen;j++){
          let index = 0
          let i = 0
          for(; i < goallength.length;){
            for(; index < dataZh.length;){
              rowSpan = [[j, index+3],[j,index+3+goallength[i]]] //+3:从第三行开始才需要行合并
              rowSpanList.push(rowSpan)
              index = index+goallength[i]+1
              i++
            }
          }
        }
        //单元格行合并
        var mergesRange = rowSpanList.map((p)=>{
          var mergeslist = []
          for(let index=0;index<columnlen;index++){
            var data = p.map((q)=>{
              const ret = alphabetList[index].toString()+q[1].toString()
              return ret
            })
            data = data.join(':')
            mergeslist.push(data)
          }
          return mergeslist
        })
        //单元格列合并
        mergesRange.unshift(`${alphabetList[columnlen]}2:${alphabetList[tHeaderZh.length-1]}2`)
      }
      //指定Excel样式和合并范围等属性
      const dataInfo = {
        titleCell: "A1",
        titleRange: `A1:${alphabetList[tHeaderZh.length-1]}1`,
        theadRange: `A2:${alphabetList[tHeaderZh.length-1]}2`,
        tbodyRange: `A3:${alphabetList[tHeaderZh.length-1]}${dataZh.length + 2}`,
        tableRange: `A2:${alphabetList[tHeaderZh.length-1]}${dataZh.length + 2}`,
      };
      //有子表格时才有合并单元格
      if(isHasChildExcel) this.$set(dataInfo,'mergesRange',mergesRange.flat())
      //表格第一行标题
      const titleZh = [{ A: "员工考核表" }];
      const titleEn = [{ A: "Employee Evaluation Table" }];
      //传入得到的数据,导出引用handleExport方法导出
      handleExport(tableZh,tableEn,titleZh,titleEn,dataInfo).then(url => {
        const downloadAnchorNode = document.createElement("a");
        downloadAnchorNode.setAttribute("href", url);
        downloadAnchorNode.setAttribute(
          "download",
          "ExportList-Kpis.xlsx" //自定义导出文件的名称
        );
        downloadAnchorNode.click();
        downloadAnchorNode.remove();
      });
      setTimeout(()=>{this.closeDialog('exportexcel')})
    },
    //将数组中的对象转为{A:'',B:''}格式
    changecolmuntokey(tHeaderZh,dataZh,alphabetList){
      const table = []
      const list1 = []
      //表头转为{A:'',B:''}格式
      tHeaderZh.map((p,i)=>{
        this.$set(list1,alphabetList[i],p)
      })
      table.push(list1)
      //表身转为{A:'',B:''}格式
      dataZh.map((p) => {
        const list = {}
        for(let i=0;i<p.length;i++){
          this.$set(list,alphabetList[i],p[i])
        }
        table.push(list)
      })
      return table
    },
    //自定义二维数组数据格式
    formatJson(filterVal, jsonData,language) {
      var result1 = jsonData.map((v,vIndex) => {
        var result = filterVal.map(j => {
          if(j === "kpi_name"){
            if(language){
              return v[j+'_en']
            }else{
              return v[j]
            }
          }else if(j === "goal" || j === "kpi_method" || j === "weight" || j === "end_score"){ //有子表格数据时根据索引显示
            var goalIndex = 0
            if(vIndex !== 0){
              for(var i=1;i<=v.goal_list.length;i++){
                if(vIndex >= i){
                  if(jsonData[vIndex].id === jsonData[vIndex-i].id){
                    goalIndex = i
                  }
                }
              }
            }
            if(v.goal_list[goalIndex]){
              if('keyValue' in v.goal_list[goalIndex]){
                for(var i in v.goal_list[goalIndex].keyValue){
                  if(v.goal_list[goalIndex].keyValue[i] === j){
                    if(language){
                      return v.goal_list[goalIndex].goalEnValue[i]
                    }else{
                      return v.goal_list[goalIndex].goalValue[i]
                    }
                  }
                }
              }
              if(language && (j === "goal" || j === "kpi_method" || j === "goal_str")){
                return v.goal_list[goalIndex][j]?v.goal_list[goalIndex][j+'_en']:''
              }else{
                return v.goal_list[goalIndex][j]?v.goal_list[goalIndex][j]:''
              }
            }else{
              return ''
            }
          }else{
            return v[j]
          }
        })
        return result
      })
      return result1
    },
    closeDialog(){
      this.selectcloumnDrawer = false
      this.$bus.$emit('refershcolumn')
    },
  }
}
</script>
<style scoped>
.exportBtn{
    float: right;
    margin: 4px auto;
}
::v-deep .el-dialog__header {
    background-color: #ecf1f6;
    margin-bottom: 4px;
}
::v-deep .el-dialog__body {
    padding: 2px 20px 20px;
}
</style>

3.3 ExportColumn子组件按列导出的代码

<template>
    <div>
        <div class="dialog_body">
            <el-checkbox :indeterminate="isIndeterminate" v-model="checkAll" @change="handleCheckAllChange" style="float:left">全选</el-checkbox>
            <div style="margin: 15px 0;"></div>
            <el-checkbox-group v-model="checkedColumn" @change="handleCheckedColumnChange" class="flexcolumn">
                <el-checkbox style="display:flex;white-space:normal;width:142px;margin-right:10px;word-break:break-word;height:30px;" v-for="itemKey in columnList" :label="itemKey" :key="itemKey.name">{{itemKey.name}}</el-checkbox>
            </el-checkbox-group>
        </div>
        <div class="right_sub_btn">
            <el-button type="primary" @click="exportExcel">确定导出</el-button>
            <el-button @click="resetexportColumn">重置</el-button>
        </div>
    </div>
</template>

<script>
export default {
    name: 'ExportColumn',
    props:{columnList:Array},
    data() {
        return {
            checkAll:false,
            checkedColumn:[],
            isIndeterminate:false
        }
    },
    mounted(){
        this.$bus.$on('refershcolumn',this.resetexportColumn)
        console.log(this.columnList);
    },
    methods:{
        handleCheckAllChange(val) {
            this.checkedColumn = val ? [...this.columnList] : [];
            this.isIndeterminate = false;
        },
        handleCheckedColumnChange(value) {
            let checkedCount = value.length;
            this.checkAll = checkedCount === this.columnList.length;
            this.isIndeterminate = checkedCount > 0 && checkedCount < this.columnList.length;
        },
        exportExcel(){
            if(this.checkedColumn.length === 0){
                this.openmessage()
            }else{
                this.$emit('exportExcel',this.checkedColumn)
            }
        },
        openmessage(){
            this.$message({message: '请选择导出列',type: 'error',offset:200,duration:2000})
        },
        resetexportColumn(){
            this.checkedColumn.splice(0,this.checkedColumn.length)
            this.checkAll = false
            this.isIndeterminate = false
        },
    }
}
</script>
<style scoped>
.dialog_body{
    margin-bottom: 50px;
}
.right_sub_btn{
    position: absolute;
    right: 10px;
    bottom: 20px;
}
.flexcolumn{
    width: 100%;
    display: flex;
    flex-wrap: wrap;
    justify-content: left;
}
</style>

四、总结

要注意的是,由于数据库数据格式不一样,从后台得到的数据要转换为自己导出需要的格式才能正确导出。如果全部搞懂这些,前端导出Excel表格基本全都会了。要是再遇到导出Excel的需求不慌啦!^0^

转载自:https://juejin.cn/post/7097007976715649061
评论
请登录