{"id":10006284,"date":"2022-08-22T20:53:42","date_gmt":"2022-08-22T12:53:42","guid":{"rendered":"https:\/\/ityet.com\/?p=10006284"},"modified":"2022-08-22T20:53:42","modified_gmt":"2022-08-22T12:53:42","slug":"vue%e4%b8%8b%e8%bd%bdexcel%e4%bb%a5%e5%8f%8a%e8%87%aa%e9%80%82%e5%ba%94%e8%a1%a8%e6%a0%bc%e5%ae%bd%e5%ba%a6%ef%bc%88%e5%89%8d%e5%90%8e%e7%ab%af%ef%bc%89","status":"publish","type":"post","link":"https:\/\/itmax.cn\/?p=10006284","title":{"rendered":"vue\u4e0b\u8f7dexcel\u4ee5\u53ca\u81ea\u9002\u5e94\u8868\u683c\u5bbd\u5ea6\uff08\u524d\u540e\u7aef\uff09"},"content":{"rendered":"

\u672c\u6587\u4f7f\u7528 SpringBoot + vue + easyExcel \u5b9e\u73b0\u5bfc\u51fa Excel \u529f\u80fd\uff0c\u5e76\u89e3\u51b3\u6587\u4ef6\u4e2d\u6587\u4e71\u7801\u95ee\u9898\u4ee5\u53ca Excel \u5bbd\u5ea6\u81ea\u9002\u5e94\u7684\u95ee\u9898\u3002\u9700\u8981\u5148\u5bfc\u5165 pom \u5305\u3002<\/p>\n

<dependency>\n    <groupId>com.alibaba<\/groupId>\n    <artifactId>easyexcel<\/artifactId>\n<\/dependency><\/code><\/pre>\n
easyExcel \u5f00\u6e90\u5730\u5740\uff1ahttps:\/\/github.com\/alibaba\/ea…<\/a><\/p>\n
1 excel \u6587\u4ef6\u4e0b\u8f7d<\/h2>\n
vue\u4e2d\u4e0b\u8f7dexcel\u6d41\u6587\u4ef6\u53ca\u8bbe\u7f6e\u4e0b\u8f7d\u6587\u4ef6\u540d\uff1ahttps:\/\/segmentfault.com\/a\/11…<\/a><\/p>\n
\u4f7f\u7528 vue-json-excel \u63a7\u4ef6\uff1a https:\/\/www.npmjs.com\/package…<\/a><\/p>\n
\u65b9\u5f0f\u4e00\uff1a<\/p>\n
\u4eba\u4e3a\u6784\u9020 a \u6807\u7b7e\uff0c\u81ea\u52a8\u70b9\u51fb\u3002<\/p>\n
PS\uff1a\u6ce8\u610f\uff0c\u6587\u4ef6\u540d\u79f0\uff0c\u4e0d\u80fd\u901a\u8fc7\u4e0a\u8ff0\u94fe\u63a5\u4e2d\u7684 this.filename \u83b7\u53d6\u5230\u3002<\/p>\n
axios\n    .get(`\/api\/audit\/export`, {\n      responseType: "blob" \/\/\u670d\u52a1\u5668\u54cd\u5e94\u7684\u6570\u636e\u7c7b\u578b\uff0c\u53ef\u4ee5\u662f 'arraybuffer', 'blob', 'document', 'json', 'text', 'stream'\uff0c\u9ed8\u8ba4\u662f'json'\n    })\n    .then((res) => {\n      if (!res) return;\n      const blob = new Blob([res.data], { type: "application\/vnd.ms-excel" }); \/\/ \u6784\u9020\u4e00\u4e2ablob\u5bf9\u8c61\u6765\u5904\u7406\u6570\u636e\uff0c\u5e76\u8bbe\u7f6e\u6587\u4ef6\u7c7b\u578b\n\n      if (window.navigator.msSaveOrOpenBlob) {\n        \/\/\u517c\u5bb9IE10\n        navigator.msSaveBlob(blob, this.filename);\n      } else {\n        const href = URL.createObjectURL(blob); \/\/\u521b\u5efa\u65b0\u7684URL\u8868\u793a\u6307\u5b9a\u7684blob\u5bf9\u8c61\n        const a = document.createElement("a"); \/\/\u521b\u5efaa\u6807\u7b7e\n        a.style.display = "none";\n        a.href = href; \/\/ \u6307\u5b9a\u4e0b\u8f7d\u94fe\u63a5\n        a.download = "test.xlsx"; \/\/\u6307\u5b9a\u4e0b\u8f7d\u6587\u4ef6\u540d\n        a.click(); \/\/\u89e6\u53d1\u4e0b\u8f7d\n        URL.revokeObjectURL(a.href); \/\/\u91ca\u653eURL\u5bf9\u8c61\n      }\n      \/\/ \u8fd9\u91cc\u4e5f\u53ef\u4ee5\u4e0d\u521b\u5efaa\u94fe\u63a5\uff0c\u76f4\u63a5window.open(href)\u4e5f\u80fd\u4e0b\u8f7d\n    })\n    .catch((err) => {\n      console.log(err);\n    });<\/code><\/pre>\n
\u65b9\u5f0f\u4e8c\uff1a<\/p>\n
\u4f7f\u7528 js-file-download \u5305<\/p>\n
    \n
  1. get \u8bf7\u6c42<\/li>\n<\/ol>\n
    axios\n.get(`\u540e\u7aef\u63a5\u53e3\u94fe\u63a5`, {\n  responseType: "blob" \/\/\u8fd4\u56de\u7684\u6570\u636e\u7c7b\u578b\n})\n.then((res) => {\n  fileDownload(res.data, "test111.xlsx");\n});<\/code><\/pre>\n
      \n
    1. post \u8bf7\u6c42<\/li>\n<\/ol>\n
      formData.value.pageNum = 1;\nformData.value.pageSize = 200;\n\naxios\n.post(`\u540e\u7aef\u63a5\u53e3\u94fe\u63a5`, formData.value, {\n  responseType: "blob" \/\/\u8fd4\u56de\u7684\u6570\u636e\u7c7b\u578b\n})\n.then((res) => {\n  fileDownload(res.data, "test111.xlsx");\n});<\/code><\/pre>\n
      2 \u6587\u4ef6\u540d\u83b7\u53d6\uff0c\u4ee5\u53ca\u4e71\u7801\u89e3\u51b3<\/h2>\n
      https:\/\/www.jianshu.com\/p\/22d…<\/a><\/p>\n
      \u540e\u7aef\u8bbe\u7f6e\u7f16\u7801\u65b9\u5f0f\u4e3a utf-8<\/p>\n
      fileName = URLEncoder.encode("\u4e2d\u6587excel\u6587\u4ef6\u540d\u79f0", "UTF-8").replaceAll("\/\/+", "%20");<\/code><\/pre>\n
      vue \u524d\u7aef\uff1a\u4f7f\u7528 decodeURIComponent \u53cd\u7f16\u8bd1\u4e2d\u6587\u6587\u4ef6\u6807\u9898<\/p>\n
      let dis = res.headers["content-disposition"];\nlet filename = decodeURIComponent(dis.split("attachment;filename*=")[1]);<\/code><\/pre>\n
      3 \u81ea\u9002\u5e94 excel \u5bbd\u5ea6<\/h2>\n
      https:\/\/chowdera.com\/2022\/02\/…<\/a><\/p>\n
      https:\/\/www.codetd.com\/en\/art…<\/a><\/p>\n
      3.1 \u7ee7\u627f AbstractColumnWidthStyleStrategy \u5b9e\u73b0\u4e00\u4e2a\u5bbd\u5ea6\u7b56\u7565<\/h3>\n
      \u8fd9\u4e2a\u5bbd\u5ea6\u7b56\u7565\u4f1a\u6839\u636e\u6807\u9898\u7684\u5bbd\u5ea6\u6216\u8005\u5185\u5bb9\u7684\u5bbd\u5ea6\u8bbe\u7f6e\u6807\u9898\u7684\u5bbd\u5ea6\u3002<\/p>\n
      PS\uff1a\u9002\u5f53\u589e\u52a0\u5bbd\u5ea6\uff0c\u80fd\u907f\u514d\u6570\u5b57\u663e\u793a\u4e3a * \u7684\u95ee\u9898\uff1b\u907f\u514d\u65f6\u95f4\u663e\u793a\u4e3a # \u7684\u95ee\u9898<\/p>\n
      import com.alibaba.excel.enums.CellDataTypeEnum;\nimport com.alibaba.excel.metadata.Head;\nimport com.alibaba.excel.metadata.data.CellData;\nimport com.alibaba.excel.metadata.data.WriteCellData;\nimport com.alibaba.excel.write.metadata.holder.WriteSheetHolder;\nimport com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;\nimport com.qunar.base.meerkat.util.DateUtil;\nimport lombok.extern.slf4j.Slf4j;\nimport org.apache.commons.collections4.CollectionUtils;\nimport org.apache.poi.ss.usermodel.Cell;\n\nimport java.util.HashMap;\nimport java.util.List;\nimport java.util.Map;\n\n\/**\n * easyExcel \u81ea\u9002\u5e94\u5217\u5bbd\n *\n *\/\n@Slf4j\npublic class ExcelWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {\n\n    private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();\n    \n    \/\/ \u9002\u5f53\u589e\u52a0\u5bbd\u5ea6\uff0c\u80fd\u907f\u514d \u6570\u5b57\u663e\u793a\u4e3a * \u7684\u95ee\u9898\n    public static final int DEFAULT = 2;\n\n    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {\n        boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);\n        if (needSetWidth) {\n            Map<Integer, Integer> maxColumnWidthMap = CACHE.get(writeSheetHolder.getSheetNo());\n            if (maxColumnWidthMap == null) {\n                maxColumnWidthMap = new HashMap<>();\n                CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);\n            }\n\n            Integer columnWidth = this.dataLength(cellDataList, cell, isHead);\n            if (columnWidth >= 0) {\n                if (columnWidth > 255) {\n                    columnWidth = 255;\n                }\n\n                Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());\n                if (maxColumnWidth == null || columnWidth > maxColumnWidth) {\n                    maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);\n                    writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);\n                }\n\n            }\n        }\n    }\n\n    private Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, Boolean isHead) {\n        if (isHead) {\n            return cell.getStringCellValue().getBytes().length + DEFAULT;\n        } else {\n            CellData cellData = cellDataList.get(0);\n            CellDataTypeEnum type = cellData.getType();\n            if (type == null) {\n                return -1;\n            } else {\n                switch (type) {\n                    case STRING:\n                        return cellData.getStringValue().getBytes().length + DEFAULT;\n                    case BOOLEAN:\n                        return cellData.getBooleanValue().toString().getBytes().length + DEFAULT;\n                    case NUMBER:\n                        return cellData.getNumberValue().toString().getBytes().length + DEFAULT;\n                    case DATE:\n                        return DateUtil.PATTERN_YYYY_MM_DD_HH_MM_SS.getBytes().length + DEFAULT;\n                    default:\n                        return -1;\n                }\n            }\n        }\n    }\n}<\/code><\/pre>\n
      3.2 easyExcel \u5199\u5165\u6587\u4ef6\u6ce8\u518c handler<\/h3>\n
      EasyExcel.write(response.getOutputStream(), DownloadData.class).registerWriteHandler(new ExcelWidthStyleStrategy()).sheet("\u6a21\u677f").doWrite(downloadDataList);<\/code><\/pre>\n
      https:\/\/segmentfault.com\/a\/1190000042358524<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
      \u672c\u6587\u4f7f\u7528 SpringBoot + vue + easyExcel \u5b9e\u73b0\u5bfc\u51fa Excel \u529f\u80fd\uff0c\u5e76\u89e3\u51b3\u6587\u4ef6\u4e2d\u6587 […]<\/p>\n","protected":false},"author":0,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[],"tags":[],"class_list":["post-10006284","post","type-post","status-publish","format-standard","hentry"],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/itmax.cn\/index.php?rest_route=\/wp\/v2\/posts\/10006284","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/itmax.cn\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/itmax.cn\/index.php?rest_route=\/wp\/v2\/types\/post"}],"replies":[{"embeddable":true,"href":"https:\/\/itmax.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=10006284"}],"version-history":[{"count":0,"href":"https:\/\/itmax.cn\/index.php?rest_route=\/wp\/v2\/posts\/10006284\/revisions"}],"wp:attachment":[{"href":"https:\/\/itmax.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=10006284"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/itmax.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=10006284"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/itmax.cn\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=10006284"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}