安装

 npm i xlsx
 npm i xlsx-style

xlsx-style安装后使用会报错

This relative module was not found:  * ./cptable in ./node_modules/xlsx-style/dist/cpexcel.js

需要在vue.config.js添加设置来解决报错

configureWebpack:{
	externals: [{
     './cptable': 'var cptable'
    }]
}
import { exportSheetExcel } from "@/utils/export/exportExcelFile";
// 数据形式
let header = ["学号","姓名","性别","出身年月"]
let filter = ["studentId","name","sex","birth"]
let data1 = [
    [
        {studentId:'A001',name:"张三",sex:"男",birth:"2020-02-14"},
        {studentId:'A002',name:"张三",sex:"男",birth:"2020-02-14"},
        {studentId:'A003',name:"张三",sex:"男",birth:"2020-02-14"},
        {studentId:'A004',name:"张三",sex:"男",birth:"2020-02-14"}
    ],
    [
        {studentId:'B001',name:"张三",sex:"男",birth:"2020-02-14"},
        {studentId:'B002',name:"张三",sex:"男",birth:"2020-02-14"},
        {studentId:'B003',name:"张三",sex:"男",birth:"2020-02-14"},
        {studentId:'B004',name:"张三",sex:"男",birth:"2020-02-14"}
    ]
]
let sheets = ["一(1)班","一(2)班"]
exportSheetExcel(
  data1,
   header,
   filter,
   sheets,
   '一年级学生表'
 );



// utils/export/exportExcelFile.js 添加方法
import {
	export_json_to_excel,
	export_json_to_multistage_excel,
	export_json_to_sheet_excel
} from '@/utils/export/vendor/Export2Excel';
export function exportSheetExcel(listDataList, tHeade, filter, sheetNames, name) {
	//参数listDataList为表格数据
	//参数name为导出文件名
	//参数tHeadeList为表格表头
	//参数filterList为表格字段
	// 导出时添加日志信息
	// addLogs('导出', '导出' + name)
	require.ensure([], () => {
		var tHeader = tHeade;
		var filterVal = filter;
		const list = listDataList; //把data里的tableData存到list
		//execl文件处理

		const data = list.map(element => {
			return element.map(v => filterVal.map(j => v[j]))
		});
		console.log(tHeader, data, sheetNames, name)
		export_json_to_sheet_excel(tHeader, data, sheetNames, name);
	})
}
// /utils/export/vendor/Export2Excel.js 添加方法
require('script-loader!file-saver');
require('./Blob.js');
require('script-loader!xlsx/dist/xlsx.core.min');
import XLSX from "xlsx-style";
......
export function export_json_to_sheet_excel(th, jsonData,sNames,defaultTitle) {

    var data = jsonData;
    //添加表头标题
    for (var i = 0; i < data.length; i++) {
      data[i].unshift(th)
    }
    // console.log("data",data)
    //这里是定义sheet的名称 有几个sheet就加几个 ["Sheet1","Sheet2"]
    var ws_name = sNames;

    var wb = new Workbook(),ws=[];
    //数据转换
    for (var j = 0; j < data.length; j++) {
      ws.push(sheet_from_array_of_arrays(data[j]))
    }

    /* add worksheet to workbook */
    //生成多个sheet
    for (var k = 0; k < data.length; k++) {
      wb.SheetNames.push(ws_name[k])
      wb.Sheets[ws_name[k]] = ws[k]
    }
    
    // 需要设置样式的sheet
	var dataInfo = wb.Sheets[wb.SheetNames[0]];
    var tableTitleFont = {
        font: {
            name: "微软雅黑", //字体
            sz: 18, //字体大小
        },
        alignment: {//居中和换行
            horizontal: "center",
            vertical: "center",
            wrapText: 1,
            indent: 0
        },
    }
    var tableColumnFont = {
        font: {
            name: "微软雅黑", //字体
            sz: 11, //字体大小
            bold: true,
        },
        alignment: {//居中和换行
            horizontal: "center",
            vertical: "center",
            wrapText: 1,
            indent: 0
        },
    }
    //给A1添加样式
    dataInfo["A1"].s = tableTitleFont;
    // 匹配第2行,修改样式
    let regexColumn = /[A-Z]2$/;
    for (var b in dataInfo) {
        if (regexColumn.test(b)) {
            dataInfo[b].s = tableColumnFont;
        }
    }
	// 设置单元格合并,将需要合并的放入数组中
	let merges = [ 'A1:B1','C2:C10' ]
	if (merges.length > 0) {
        if (!ws['!merges']) ws['!merges'] = [];
        merges.forEach(item => {
            ws['!merges'].push(XLSX.utils.decode_range(item))
        })
    }


    var wbout = XLSX.write(wb, {bookType: 'xlsx', bookSST: false, type: 'binary'});
    var title = defaultTitle || '列表'
    saveAs(new Blob([s2ab(wbout)], {type: "application/octet-stream"}), title + ".xlsx")
} 

在这里插入图片描述

参考地址:
在VUE中使用Export2Excel导出表格,导出多个sheet

Logo

前往低代码交流专区

更多推荐