导出Excel-- JSON转Excel

最新推荐文章于 2024-05-16 13:24:58 发布

qq_45422185

最新推荐文章于 2024-05-16 13:24:58 发布

阅读量232

点赞数

文章标签： excel

本文链接：https://blog.csdn.net/qq_45422185/article/details/133954743

版权

这是一个纯js实现JSON转换成excel并下载的demo

通过拼接table实现的,

这是原帖

我这里基于产品需求重新改了一下下

<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="UTF-8" />
    <meta
      name="viewport"
      content="width=device-width, initial-scale=1.0" />
    <title>导出Excel</title>
  </head>
  <body>
    <button onclick="excelExportFn()">导出</button>
    <script>
      /**
       * @params Object:
       *         Object.data            -- 需要转换成excel的数组,目前不支持对象嵌套数组嵌套对象的多层嵌套情况  !!必传参数!! Array
       *         Object.FileName        -- 文件名  !!不传默认以时间戳命名!!    String
       *         Object.titleMapping    --表头名称和对应字段的映射以及排序  !!非必须参数!!   Array
       *         Object.tableName       --工作表名称默认为 'Sheet1'  !!非必选参数!!   String
       */
      function exportExcel(params) {
        const { data, FileName = Date.now(), titleMapping, tableName = "Sheet1" } = params;
        if (!data) return;
        let JSONData = data;
        let title = [];
        if (titleMapping?.length) {
          let sortData = titleMapping.sort((a, b) => a - b);
          JSONData = jsonData.map((ele) => {
            let obj = {};
            sortData.forEach((item) => {
              obj[item.field] = ele[item.field];
            });
            return obj;
          });
          title = titleMapping.map((ele) => ele.title);
        } else {
          title = Object.keys(data[0]);
          JSONData = jsonData.map((ele) => {
            let obj = {};
            title.forEach((item) => {
              obj[item] = ele[item];
            });
            return obj;
          });
        }
        let arrData = typeof JSONData != "object" ? JSON.parse(JSONData) : JSONData;
        let excel = "<table>";
        let row = "<tr>";
        if (title) {
          for (let i in title) {
            row += "<th align='center'>" + title[i] + "</th>";
          }
        } else {
          for (let i in arrData[0]) {
            row += "<th align='center'>" + i + "</th>";
          }
        }
        excel += row + "</tr>";
        for (let i = 0; i < arrData.length; i++) {
          let row = "<tr>";
          for (let index in arrData[i]) {
            let value = arrData[i][index] == null ? "" : arrData[i][index];
            row += "<td align='center'>" + value + "</td>";
          }
          excel += row + "</tr>";
        }
        excel += "</table>";
        let excelFile = "<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'>";
        excelFile += '<meta http-equiv="content-type" content="application/vnd.ms-excel; charset=UTF-8">';
        excelFile += '<meta http-equiv="content-type" content="application/vnd.ms-excel';
        excelFile += '; charset=UTF-8">';
        excelFile += "<head>";
        excelFile += "<!--[if gte mso 9]>";
        excelFile += "<xml>";
        excelFile += "<x:ExcelWorkbook>";
        excelFile += "<x:ExcelWorksheets>";
        excelFile += "<x:ExcelWorksheet>";
        excelFile += "<x:Name>";
        excelFile += `{${tableName}}`;
        excelFile += "</x:Name>";
        excelFile += "<x:WorksheetOptions>";
        excelFile += "<x:DisplayGridlines/>";
        excelFile += "</x:WorksheetOptions>";
        excelFile += "</x:ExcelWorksheet>";
        excelFile += "</x:ExcelWorksheets>";
        excelFile += "</x:ExcelWorkbook>";
        excelFile += "</xml>";
        excelFile += "<![endif]-->";
        excelFile += "</head>";
        excelFile += "<body>";
        excelFile += excel;
        excelFile += "</body>";
        excelFile += "</html>";
        let uri = "data:application/vnd.ms-excel;charset=utf-8," + encodeURIComponent(excelFile);
        let link = document.createElement("a");
        link.href = uri;
        link.style = "visibility:hidden";
        link.download = FileName + ".xls";
        document.body.appendChild(link);
        link.click();
        document.body.removeChild(link);
      }

      const jsonData = [
        { phone: "123456", email: "123@123456.com", bool: true, name: "路人甲" },
        {
          name: "炮灰乙",
          phone: "123456",
          email: "123@123456.com",
          bool: false,
        },
        {
          email: "123@123456.com",
          name: "土匪丙",
          phone: "123456",
          bool: true,
        },
        {
          email: "123@123456.com",
          phone: "123456",
          bool: false,
          name: "流氓丁",
        },
      ];
      function excelExportFn() {
        exportExcel({
          data: jsonData,
          FileName: "文件名",
          titleMapping: [
            {
              title: "姓名",
              sort: 1,
              field: "name",
            },
            {
              title: "邮箱",
              sort: 3,
              field: "email",
            },
            {
              title: "电话",
              sort: 2,
              field: "phone",
            },
          ],
          tableName: "表名",
        });
      }
    </script>
  </body>
</html>