vue excel导出分多个sheet

一只害虫

已于 2023-02-07 10:28:49 修改

阅读量704

点赞数 1

文章标签： excel javascript

于 2023-02-07 10:26:25 首次发布

本文链接：https://blog.csdn.net/qq_35363709/article/details/128914054

版权

单页面中使用

<!DOCTYPE html>
<html lang="en">
	<head></head>
	<meta charset="UTF-8" />
	<meta name="viewport" content="width=device-width, initial-scale=1.0" />
	<meta http-equiv="X-UA-Compatible" content="ie=edge" />
	<title>Document</title>
	<script src="https://cdn.bootcss.com/xlsx/0.15.1/xlsx.full.min.js"></script>
	</head>
	<body>
      <a href="#" onclick="test()">导出</a>
	</body>
</html>

<script>
	function test() {
		// 数据
		let data = {
			'sheet1': [{
				name: '李四',
				sex: 'nan',
				age: 12
			}],
			
			'sheet2': [{
				class: '计算机',
				teacher: 'Mrs wang',
				score: 90
			}]
		};
		let columnHeaders = {
			'sheet1': ['name', 'sex', 'age'],
			'sheet2': ['class', 'teacher', 'score']
		}
		outputXlsxFile(
			data,
			[{
				wch: 50
			}, {
				wch: 50
			}, {
				wch: 10
			}],
			"test-xlsx"
		);

		function outputXlsxFile(data, wscols, xlsxName) {
			let sheetNames = [];
			let sheetsList = {};
			const wb = XLSX.utils.book_new();

			for (let key in data) {
				sheetNames.push(key);
				let columnHeader = columnHeaders[key] // 此处是每个sheet的表头
				let temp = transferData(data[key], columnHeader);
				sheetsList[key] = XLSX.utils.aoa_to_sheet(temp);
				debugger
				sheetsList[key]["!cols"] = wscols;
			}
            
			wb["SheetNames"] = sheetNames;
			debugger
			wb["Sheets"] = sheetsList;

		 XLSX.writeFile(wb, xlsxName + ".xlsx");
		}

		function transferData(data, columnHeader) {
			let content = [];
			content.push(columnHeader);
			data.forEach((item, index) => {
				let arr = [];
				columnHeader.map(column => {
					arr.push(item[column]);
				})
				content.push(arr);
			});
			return content;
		}
	}
</script>

vue 项目中使用

一：在created中创建并引入js

  created() {
      var element = document.createElement("script");
      element.src =
        "https://cdn.bootcss.com/xlsx/0.15.1/xlsx.full.min.js";
      document.body.appendChild(element);
    },

二：根据后台返回数据从构所需要的json结构

//采购计划导出
      handleOrder() {
        ExeclExportMainContractMRP({
          MainContractCode: this.excelMainContractCode
        }).then(res => {
          if (res.data.Data.ReturnCode == 0) {
            let newData = {}
            let self = this
            let result = res.data.Data.ExeclExportMainContractMRPRows.map(o => {
              return {
                '供应商名称': o.SupplierName,
                '物料编号': o.MaterialNumber,
                '物料名称': o.MaterialDescription,
                '计划批次': o.ContractCode,
                '计划数量': o.Quantity,
                '计划单位': o.Unit,
                '采购节点': o.PurchaseEndDateTime
              }
            });


            result.forEach(item => {
              //新建属性名
              if (Object.keys(newData).indexOf('' + item.供应商名称) === -1) {
                newData[item.供应商名称] = []
                self.newArr[item.供应商名称] = ['供应商名称', '物料编号', '物料名称',
                  '计划批次', '计划数量', '计划单位', '采购节点'
                ]
              }
              console.log('item.供应商名称', item.供应商名称)
              //对应插入属性值
              newData[item.供应商名称].push(item)
            })
            console.log('newData', newData)
            console.log('self.newArr', self.newArr)
            let columnHeaders = self.newArr
            outputXlsxFile(
              newData,
              [{
                wch: 45
              }, {
                wch: 20
              }, {
                wch: 40
              }, {
                wch: 10
              }, {
                wch: 10
              }, {
                wch: 10
              }, {
                wch: 20
              }],
              "采购计划.excel"
            );

            function outputXlsxFile(newData, wscols, xlsxName) {
              let sheetNames = [];
              let sheetsList = {};
              const wb = XLSX?.utils?.book_new();

              for (let key in newData) {
                sheetNames.push(key);
                let columnHeader = columnHeaders[key] // 此处是每个sheet的表头
                let temp = transferData(newData[key], columnHeader);
                sheetsList[key] = XLSX?.utils?.aoa_to_sheet(temp);
                sheetsList[key]["!cols"] = wscols;
              }

              wb["SheetNames"] = sheetNames;
              wb["Sheets"] = sheetsList;

              XLSX.writeFile(wb, xlsxName + ".xlsx");
            }

            function transferData(newData, columnHeader) {
              let content = [];
              content.push(columnHeader);
              newData.forEach((item, index) => {
                let arr = [];
                columnHeader?.map(column => {
                  arr.push(item[column]);
                })
                content.push(arr);
              });
              return content;
            }
          } else {
            this.$message.error(res.newData.Data.Message);
          }
        })
      },