前端如何下载excel表格

1.1下载表单，需要自己配置，引用别人封装好的ts文件

import { downloadXlsxFile } from "./downloadFile"; //引入自定义xlxs ts

const form = reactive({val:{}})
form.val=res.data.trsCircuit;

 const labelAll = 
    {
        aaddress:'地址',
        abuzPort: "设备端口",
        actnId: "系统ID",
        acustId:'A用户信息'
     }

//下载电路基本信息自定义xlxs
const downloadBasic = () => {
  var data = [form.val];
  var fields = labelAll;
  let filename = "电路基本信息";
  //data表格信息 fields表头 filename文件名
  downloadXlsxFile(data, fields, filename);
};

downloadFile.ts

//装 file-saver 和 xlsx 包
import fs  from 'file-saver'
import  * as XLSX from 'xlsx'
function downloadXlsxFile(dataArray: any, fields: any, filename: string) {
  /**
   * 表格数据 => dataArray=[{id:"1",name:"666",sex:"男"}] 
   * 表格数据的title => fields={id:"id",name:"名字",sex:"性别"}
   * 表格名字 => filename
   */
  var data = JSON.stringify(dataArray);
  var newData = JSON.parse(data);
  newData.forEach((item: any) => {
    if (item) {
      for (let i in item) {
        if (fields.hasOwnProperty(i)) {
          item[fields[i]] = item[i];
        }
        delete item[i]; //删除原先的对象属性
      }
    }

  })
  let sheetName = filename //excel的文件名称
  let wb = XLSX.utils.book_new()  //工作簿对象包含一SheetNames数组，以及一个表对象映射表名称到表对象。XLSX.utils.book_new实用函数创建一个新的工作簿对象。
  let ws = XLSX.utils.json_to_sheet(newData, { header: Object.values(fields) }) //将JS对象数组转换为工作表。
  wb.SheetNames.push(sheetName)
  wb.Sheets[sheetName] = ws
  const defaultCellStyle = { font: { name: "Verdana", sz: 13, color: "FF00FF88" }, fill: { fgColor: { rgb: "FFFFAA00" } } };//设置表格的样式
  let wopts = { bookType: 'xlsx', bookSST: false, type: 'binary', cellStyles: true, defaultCellStyle: defaultCellStyle, showGridLines: false }  //写入的样式
  let wbout = XLSX.write(wb, wopts)
  let blob = new Blob([s2ab(wbout)], { type: 'application/octet-stream' })
  fs.saveAs(blob, filename + '.xlsx') 
}
function  s2ab(s) {
  if (typeof ArrayBuffer !== 'undefined') {
    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
  } else {
    var buf = new Array(s.length);
    for (var i = 0; i != s.length; ++i) buf[i] = s.charCodeAt(i) & 0xFF;
    return buf;
  }
}
export { downloadXlsxFile };

1.2自己封装下载方法

//装 xlsx 包
import * as XLSX from 'xlsx'
const exportExcelfn=(successData,filename,fileName)=>{
      // 设置表格样式，!cols为列宽
    const options = {
        '!cols': [
            { wpx: 100 },
            { wpx: 100 },
            { wpx: 100 },
            { wpx: 100 },
            { wpx: 100 },
        ]
    }; 
   const successSheet1 = XLSX.utils.json_to_sheet(successData);
   const successSheet2 = XLSX.utils.json_to_sheet(successData);
   // 设置每列的列宽，10代表10个字符，注意中文占2个字符   
   successSheet1 ['!cols']=successSheet2 ['!cols'] = options['!cols']; 

   //创建虚拟工作簿workbook
    const workbook = XLSX.utils.book_new();

   /* 将工作表添加到工作簿,生成xlsx文件(book,sheet数据,sheet命名)*/
   XLSX.utils.book_append_sheet(workbook, successSheet1 , filename);
   XLSX.utils.book_append_sheet(workbook, successSheet2 , filename);

   /* 输出工作表， 由文件名决定的输出格式(book,xlsx文件名称)*/
   XLSX.writeFile(workbook, `${fileName+filename}.xlsx`);
}

const downloadBasic = (fileName) => {
    console.log( form.val)
    let data=form.val;
    const successData = [{
        'ID':data.Id,
        '名称':data.Name,
        '别名':data.alias,
    }];
    exportExcelfn(successData,'文件名',fileName)
}

2. 获取页面中的table元素，下载表格

import FileSaver from "file-saver"; //下载excel
import * as XLSX from "xlsx";

const exportExcel = (el) => {
  var xlsxParam = { raw: true };
  var wb = XLSX.utils.table_to_book(
    document.querySelector("#" + el),
    xlsxParam
  );
  var wbout = XLSX.write(wb, {
    bookType: "xlsx",
    bookSST: true,
    type: "array",
  });
  try {
    FileSaver.saveAs(
      new Blob([wbout], { type: "application/octet-stream" }),
      "详情.xlsx"
    );
  } catch (e) {
    if (typeof console !== "undefined") {
      console.log(e, wbout);
    }
  }
  return wbout;
};

<el-table ref="selfTable">
     <el-table-column prop="province" label="组织机构" align="center" width="150">
          <template slot-scope="scope">
                <div>
                    {{ scope.row.province }}
                </div>
            </template>
     </el-table-column>
</el-table>
<el-button @click="exportExcel(123)">导出</el-button>

 

    import FileSaver from 'file-saver'
    import * as XLSX from 'xlsx'

    exportExcel(excelName) {
      try {
        const $e = this.$refs['selfTable'].$el
        let $table = $e.querySelector('.el-table__fixed')
        if (!$table) {
          $table = $e
        }
        const wb = XLSX.utils.table_to_book($table, { raw: true })
        const wbout = XLSX.write(wb, { bookType: 'xlsx', bookSST: true, type: 'array' })
        FileSaver.saveAs(
          new Blob([wbout], { type: 'application/octet-stream' }),
          `${excelName}.xlsx`,
        )
      } catch (e) {
        if (typeof console !== 'undefined') console.error(e)
      }
    }

3.后端写好的导出excel接口，前端如何发送请求

方法一： 

const downloadAll = () => {
  window.open(
  //接口url
  `http://111.11.11.11:1111/export-data?${qs.stringify(queryData)}`,'_blank'
  );
};

方法二

 首先需要下载 js-file-download 包

npm install js-file-download

在需要使用的页面引入

import fileDownload from 'js-file-download';

封装导出请求方法，必须配置blob流文件， responseType: "blob"，否则可能出现文件损坏问题

export function export(data) {
  return request({
    responseType: "blob",
    url: "export/data",
    method: "post",
    data: JSON.stringify(data),
    headers: {
      "Content-Type": "application/json",
    },
  });
}

调用导出接口

const res = await export(queryData);
fileDownload(res, 'xxxx.xlsx')
//如果后台格式是 .xls的，下载的文件可能会损坏，需要把文件名改成对应的格式 xxx.xls

方法三：使用原生js下载或导出

封装导出方法，必须配置 responseType: 'arraybuffer',

export const fileDownload = (id) => (
  service({
    url: `/template?id=${id}`,
    method: 'get',
    responseType: 'arraybuffer',
    showLoading: true,
    developer: 'local_url',
  })
)

fileDownload(row.id).then(res => {
        let {data, headers} = res
        let type = headers['content-type']
        let name = headers['content-disposition'] || ''
        let nameArr = name.split('=')
        let fileName = decodeURI(nameArr[nameArr.length - 1])
        let blob = new Blob([data], {type})
        if (typeof window.navigator.msSaveBlob !== 'undefined') {
          /*
           * IE workaround for "HTML7007: One or more blob URLs were revoked by closing
           * the blob for which they were created. These URLs will no longer resolve as
           * the data backing the URL has been freed."
           */
          window.navigator.msSaveBlob(blob, fileName)
        } else {
          let URL = window.URL || window.webkitURL
          let objectUrl = URL.createObjectURL(blob)
          if (fileName) {
            var a = document.createElement('a')
            // safari doesn't support this yet
            if (typeof a.download === 'undefined') {
              window.location = objectUrl
            } else {
              a.href = objectUrl
              a.download = fileName
              document.body.appendChild(a)
              a.click()
              a.remove()
            }
          } else {
            window.location = objectUrl
          }
        }
      })