前端实现导出Excel

最新推荐文章于 2024-07-21 10:17:51 发布

savethink

最新推荐文章于 2024-07-21 10:17:51 发布

阅读量272

点赞数 5

分类专栏：功能实现文章标签：前端 excel javascript

本文链接：https://blog.csdn.net/weixin_42395075/article/details/140372455

版权

功能实现专栏收录该内容

1 篇文章 0 订阅

订阅专栏

前端开发中最常见的功能就是导出列表为excel文件，下面是开发中常见的实现方式

后端文件流

文件下载方法

const sourceBlobType = {
  bin: "application/octet-stream",
  avi: "video/x-msvideo",
  wav: "audio/wav",
  mp3: "audio/mpeg",
  gif: "image/gif",
  jpg: "image/jpeg",
  jpeg: "image/jpeg",
  png: "image/png",
  webp: "image/webp",
  htm: "text/html",
  html: "text/html",
  css: "text/css",
  js: "text/javascript",
  json: "application/json",
  pdf: "application/pdf",
  txt: "text/plain",
  doc: "application/msword",
  docx: "application/vnd.openxmlformats-officedocument.wordprocessingml.document",
  xls: "application/vnd.ms-excel",
  xlsx: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
  ppt: "application/vnd.ms-powerpoint",
  pptx: "application/vnd.openxmlformats-officedocument.presentationml.presentation",
  rar: "application/x-rar-compressed",
  zip: "application/zip",
  "7z": "application/x-7z-compressed",
};

/**
 * @param {*} data:文件二进制数据流
 * @param {*} filename 文件名
 */
const downloadResources = (data, type, filename) => {
  // 1.设置Blob数据
  const blob = new Blob([data], {
    //文件类型
    type: `${sourceBlobType[type]};charset=utf-8;`,
  });

  //兼容IE11
  if (navigator.appVersion.toString().indexOf(".NET") > 0) {
    window.navigator.msSaveBlob(blob, filename);
  } else {
    const url = window.URL.createObjectURL(blob);
    const link = document.createElement("a");
    link.style.display = "none";
    link.href = url;
    link.setAttribute("download", filename); // 重命名文件
    document.body.appendChild(link);
    link.click();
    document.body.removeChild(link);
    window.URL.revokeObjectURL(url); // 释放掉blob对象
  }
};

接口请求

Axios.post(`${IP}/getListData`,data,{
	responseType:'blob',//二进制文件流添加
}).then(res=>{
	const data = res.data
	downloadResources(data,'xlsx','文件下载')
})

纯前端

js类实现

将table标签，tr、td等对json数据进行拼接，然后table输出到表格上实现，这种方法的弊端在于输出的是伪excel，虽说生成xls为后缀的文件，但文件形式上还是html

class JsonToTable {
  constructor(config) {
    this.config = config ? config : {};
    // 允许的header类型，header权重类型
    this.allowHeaderType = ["tr", "th"];
    this.allowHeaderWeightType = ["block", "inline"]; //block为单独的style优先级高 inline为行内样式优先级高
    this.setHeader(this.config);
    this.setBody(this.config);
  }
  //   设置表头
  setHeader(config) {
    const { columns, type, headerStyle, headerStyleWeight } = config;
    const headerType = this.allowHeaderType.includes(type)
      ? type
      : this.allowHeaderType[0];
    const headerWeightType = this.allowHeaderWeightType.includes(
      headerStyleWeight
    )
      ? headerStyleWeight
      : this.allowHeaderWeightType[0];
    const style = headerStyle || {};
    this.headerType = headerType; // header类型
    this.headerWeightType = headerWeightType; // header权重类型
    this.headerStyle = style; // header样式
    this.columns = columns || []; //header列
    this.worksheet = config?.worksheet || "sheet1"; //表格名称
    this.uri = config?.uri || "data:application/vnd.ms-excel;base64,"; //表格前缀
  }
  //   设置表体
  setBody(config) {
    this.dataSource = config?.data || []; //数据
    this.fileName = config?.fileName || this.fileName || "下载"; //文件名
    this.dataStyle = config?.dataStyle || this.dataStyle || {}; //通用的数据样式
    this.dataComputedStyle =
      config?.dataComputedStyle || this.dataComputedStyle || {}; //数据计算样式
  }
  //   获取头部样式
  getHeaderStyle(lineStyle = {}) {
    let style = {};
    if (this.headerWeightType === "inline") {
      style = {
        ...this.headerStyle,
        ...lineStyle,
      };
    } else if (this.headerWeightType === "block") {
      style = {
        ...lineStyle,
        ...this.headerStyle,
      };
    }
    let styleStr = "";
    for (const key in style) {
      styleStr += `${key}:${style[key]};`;
    }
    return styleStr;
  }

  //   处理表体样式
  getBodyStyle(setStyle) {
    let style = {
      ...this.dataStyle,
      ...setStyle,
    };
    let styleStr = "";
    for (const key in style) {
      styleStr += `${key}:${style[key]};`;
    }
    return styleStr;
  }

  getTemplate(tableStr) {
    // 下载的表格模板数据
    const template = `
     <html xmlns:o="urn:schemas-microsoft-com:office:office" 
           xmlns:x="urn:schemas-microsoft-com:office:excel" 
           xmlns="http://www.w3.org/TR/REC-html40">
         <head>
             <meta charset='utf-8' />
             <!--[if gte mso 9]>
                 <xml>
                     <x:ExcelWorkbook>
                         <x:ExcelWorksheets>
                             <x:ExcelWorksheet>
                                 <x:Name>${this.worksheet}</x:Name>
                                 <x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions>
                             </x:ExcelWorksheet>
                         </x:ExcelWorksheets>
                     </x:ExcelWorkbook>
                 </xml>
             <![endif]-->
         </head>
         <body>
            ${tableStr}
         </body>
     </html>`;
    return this.uri + window.btoa(unescape(encodeURIComponent(template)));
  }

  export() {
    let tableStr = `<table><${this.headerType}>`;
    // 添加表头
    for (let i = 0; i < this.columns.length; i++) {
      const item = this.columns[i];
      tableStr += `<td style="${this.getHeaderStyle(item.style)}">${
        item.value
      }</td>`;
    }
    tableStr += `</${this.headerType}>`;
    // 添加表体
    for (let i = 0; i < this.dataSource.length; i++) {
      tableStr += `<tr>`;
      const info = this.dataSource[i];
      for (let j = 0; j < this.columns.length; j++) {
        const item = this.columns[j];
        // 检查是否有样式
        const bodyStyle = this.dataComputedStyle[item.key]
          ? this.dataComputedStyle[item.key](info[item.key])
            ? this.dataComputedStyle[item.key](info[item.key])
            : {}
          : {};
        const style = this.getBodyStyle(bodyStyle);
        tableStr += `<td style=${style}>${info[item.key]}</td>`;
      }
      tableStr += `</tr>`;
    }
    tableStr += `</table>`;
    const template = this.getTemplate(tableStr);
    // 创建下载
    const link = document.createElement("a");
    link.download = this.fileName;
    link.href = template;
    link.click();
    link.remove();
  }
}

类配置

参数	说明	默认值	可选值
columns	表头数组	[]
type	表头的类型	tr	tr,td
headerStyle	应用于所有表头的样式	{}
headerStyleWeight	表头样式采取的权重	inline	inline列里样式为主,block通用样式为主
data	数据	[]
dataStyle	应用于所有数据的样式	{}
dataComputedStyle	应用具体某一列的特殊处理样式	{}
worlsheet	表格名称（非文件名称）	sheet1
uri	前缀	data:application/vnd.ms-excel;base64
fileName	文件名	测试表格

columns配置

参数	说明	是否必传	备注
key	字段值	是
value	表头显示的文字	是
style	针对这一个表头的样式	否	显示的权重根据headStyleWeight进行判断

dataComputedStyle

注：dataComputedStyle的里面的方法字段是表头的key字段，需要返回样式对象

dataComputedStyle:{
  age(value){
  	return value>25 ? {color:'red'} : {}
  }
}

使用

方式一：直接初始化使用

const dataJson =  [
	{name: "路人甲",phone: "123456",email: "123@123456.com",ge: 20},
	{name: "路人甲",phone: "123456",email: "123@123456.com",ge: 28},
	{name: "路人甲",phone: "123456",email: "123@123456.com",ge: 29},
]
const tableJs = new JsonToTable({
  columns: [
      { key: "name", value: "姓名", style: { color: "red" } },
      { key: "phone", value: "电话" },
      { key: "email", value: "邮箱" },
      { key: "age", value: "年龄" },
    ],
    type: "tr", //tr th
    headerStyle: {
       "font-weight": "bold",
       "font-size": "25px",
       "font-family": "微软雅黑",
       "font-style": "italic",
     },
     headStyleWeight: "inline", //inline,block
     data: dataJson ,
     fileName: "测试文件下载",
     dataStyle: {
       "font-weight": "bold",
     },
     dataComputedStyle: {
       age(value) {
         return value>25 ? {color:'red'} : {}
       },
     },
});
tableJs.export()

方式二：分别注入表头，表体

const dataJson =  [
	{name: "路人甲",phone: "123456",email: "123@123456.com",ge: 20},
	{name: "路人甲",phone: "123456",email: "123@123456.com",ge: 28},
	{name: "路人甲",phone: "123456",email: "123@123456.com",ge: 29},
]
//初始化类
const tableJs = new JsonToTable();
//注入表头配置
tableJs.setHeader({
   columns: [
      { key: "name", value: "姓名", style: { color: "red" } },
      { key: "phone", value: "电话" },
      { key: "email", value: "邮箱" },
      { key: "age", value: "年龄" },
    ],
    type: "tr", //tr th
    headerStyle: {
      "font-weight": "bold",
      "font-size": "25px",
      "font-family": "微软雅黑",
      "font-style": "italic",
    },
    headStyleWeight: "inline", //inline,block
});
//注入表体
tableJs.setBody({
	data:dataJson,
	fileName: "步骤测试",
    dataStyle: {
      "font-weight": "bold",
    },
    dataComputedStyle: {
     age(value) {
        return value>25 ? {color:'red'} : {}
      },
    },
})
//导出
tableJs.export();