vue2 vue3 使用xlsx导出excel 多个sheet表

vue3

安装依赖
npm install xlsx
引入
import * as XLSX from ‘xlsx’

模拟数据格式

const columns = [
  {
    title: 'name',
    dataIndex: 'name',
    width: '25%',
  },
  {
    title: 'age',
    dataIndex: 'age',
    width: '15%',
  },
  {
    title: 'address',
    dataIndex: 'address',
    width: '40%',
  },
  {
    title: 'operation',
    dataIndex: 'operation',
  },
];

for (let i = 0; i < 100; i++) {
  data.push({
    key: i.toString(),
    name: `Edrward ${i}`,
    age: 32,
    address: `London Park no. ${i}`,
  });
}

处理数据格式

// dataIndex  filterA- filterB都是获取表格dataIndex， 多个表格就会有多个filterA 暂用ABC替代多个， 
 let filterA= columns.map((el: any) => {
        return el.dataIndex
    })
// title
let titleA = columns.map((el: any) => {
        return el.title
    })

let result = [
        {
            tHeader: titleA , //表头
            filterVal: filterA,  // 表头对应字段
            tableDatas: tableData.value,  //数据
            sheetName: '表格A', // sheet名称
        },
        {
            tHeader: titleB,
            filterVal: filterB,
            tableDatas: dataArr,
            sheetName: '表格B',
        },
    ]
    let header = []
    let data = []
    let sheetname = []
    for (var i in result) {
        header.push(result[i].tHeader)
        data.push(formatJson(result[i].filterVal, result[i].tableDatas))
        sheetname.push(result[i].sheetName)
    } 
     exportExcels({ header, data, sheetname, filename: '导出excel测试' })

// exportExcels
let exportExcels = ({ header, data, sheetname, filename }: any) => {
    console.log({ header, data, sheetname, filename })
    // 将表头插入数据数组中
    for (let i = 0; i < header.length; i++) {
        data[i].unshift(header[i])
    }
    let ws_name = sheetname
    // 创建工作簿对象
    let wb = XLSX.utils.book_new()
    let ws = []
    // 创建每个工作表并设置列宽
    for (let j = 0; j < header.length; j++) {
        console.log(1, '274')
        ws.push(XLSX.utils.aoa_to_sheet(data[j]))
        let arr: any = []
        header[j].forEach((val: any) => {
            arr.push({
                wpx: 120
            })
        })
        ws[j]["!cols"] = arr
    }
    // 将工作表对象添加到工作簿中
    for (let k = 0; k < header.length; k++) {
        wb.SheetNames.push(ws_name[k])
        wb.Sheets[ws_name[k]] = ws[k]
    }
    XLSX.writeFile(wb, filename + '.xlsx') // 导出文件
}


let formatJson = (filterVal: any, jsonData: any) => {
    return jsonData.map((v: any) => filterVal.map((j: any) => v[j]))
}

vue2

安装依赖
npm install -S file-saver xlsx
npm install -D script-loader
新建两个js文件（Blob.js和Export2Excel.js）
Blob.j 网上一大堆
Export2Excel.js

/* eslint-disable */
require('script-loader!file-saver');
require('./Blob');
require('script-loader!xlsx/dist/xlsx.core.min');
import XLSX from 'xlsx-style';

function generateArray(table) {
	var out = [];
	var rows = table.querySelectorAll('tr');
	var ranges = [];
	for (var R = 0; R < rows.length; ++R) {
		var outRow = [];
		var row = rows[R];
		var columns = row.querySelectorAll('td');
		for (var C = 0; C < columns.length; ++C) {
			var cell = columns[C];
			var colspan = cell.getAttribute('colspan');
			var rowspan = cell.getAttribute('rowspan');
			var cellValue = cell.innerText;
			if (cellValue !== '' && cellValue == +cellValue) cellValue = +cellValue;

			//Skip ranges
			ranges.forEach(function (range) {
				if (R >= range.s.r && R <= range.e.r && outRow.length >= range.s.c && outRow.length <= range.e.c) {
					for (var i = 0; i <= range.e.c - range.s.c; ++i) outRow.push(null);
				}
			});

			//Handle Row Span
			if (rowspan || colspan) {
				rowspan = rowspan || 1;
				colspan = colspan || 1;
				ranges.push({
					s: {
						r: R,
						c: outRow.length
					},
					e: {
						r: R + rowspan - 1,
						c: outRow.length + colspan - 1
					}
				});
			}
			//Handle Value
			outRow.push(cellValue !== '' ? cellValue : null);

			//Handle Colspan
			if (colspan)
				for (var k = 0; k < colspan - 1; ++k) outRow.push(null);
		}
		out.push(outRow);
	}
	return [out, ranges];
}

function datenum(v, date1904) {
	if (date1904) v += 1462;
	var epoch = Date.parse(v);
	return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000);
}

const defaultCellStyle = {
	font: {
		name: "宋体",
		sz: 12,
		color: {
			auto: 1
		},
	},
	border: {
		color: {
			auto: 1
		},
		top: {
			style: 'thin'
		},
		bottom: {
			style: 'thin'
		},
		left: {
			style: 'thin'
		},
		right: {
			style: 'thin'
		}
	},
	alignment: {
		/// 自动换行
		wrapText: true,
		// 居中
		horizontal: "center",
		vertical: "center",
		indent: 0
	}
};

function sheet_from_array_of_arrays(data, opts) {
	var ws = {};
	var range = {
		s: {
			c: 10000000,
			r: 10000000
		},
		e: {
			c: 0,
			r: 0
		}
	};
	for (var R = 0; R != data.length; ++R) {
		for (var C = 0; C != data[R].length; ++C) {
			if (range.s.r > R) range.s.r = R;
			if (range.s.c > C) range.s.c = C;
			if (range.e.r < R) range.e.r = R;
			if (range.e.c < C) range.e.c = C;
			// var cell = { v: data[R][C] };
			/// 这里生成cell的时候，使用上面定义的默认样式
			const cell = {
				v: data[R][C],
				s: defaultCellStyle
			};
			// if (cell.v == '抵港报') {
			// 	cell.s = {
			// 		alignment: {
			// 			horizontal: 'center',
			// 			vertical: 'center',
			// 			wrapText: true
			// 		}, //设置标题水平竖直方向居中，并自动换行展示
			// 		fill: {
			// 			fgColor: {
			// 				rgb: 'ebebeb'
			// 			} //设置标题单元格的背景颜色
			// 		}
			// 	}
			// }
			// 如果单元格所在的值为空，让其值为---
			if (cell.v == null) {
				cell.v = ' '
			};
			var cell_ref = XLSX.utils.encode_cell({
				c: C,
				r: R
			});

			if (typeof cell.v === 'number') cell.t = 'n';
			else if (typeof cell.v === 'boolean') cell.t = 'b';
			else if (cell.v instanceof Date) {
				cell.t = 'n';
				cell.z = XLSX.SSF._table[14];
				cell.v = datenum(cell.v);
			} else cell.t = 's';

			ws[cell_ref] = cell;
		}
	}
	if (range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range);
	return ws;
}

function Workbook() {
	if (!(this instanceof Workbook)) return new Workbook();
	this.SheetNames = [];
	this.Sheets = {};
}

function s2ab(s) {
	var buf = new ArrayBuffer(s.length);
	var view = new Uint8Array(buf);
	for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xff;
	return buf;
}

export function export_table_to_excel(id) {
	var theTable = document.getElementById(id);
	console.log('a');
	var oo = generateArray(theTable);
	var ranges = oo[1];

	/* original data */
	var data = oo[0];
	var ws_name = 'SheetJS';
	console.log(data);

	var wb = new Workbook(),
		ws = sheet_from_array_of_arrays(data);

	/* add ranges to worksheet */
	// ws['!cols'] = ['apple', 'banan'];
	ws['!merges'] = ranges;

	/* add worksheet to workbook */
	wb.SheetNames.push(ws_name);
	wb.Sheets[ws_name] = ws;

	var wbout = XLSX.write(wb, {
		bookType: 'xlsx',
		bookSST: false,
		type: 'binary'
	});

	saveAs(new Blob([s2ab(wbout)], {
		type: 'application/octet-stream'
	}), 'test.xlsx');
}

function formatJson(jsonData) {
	console.log(jsonData);
}

// 导出单个
export function export_json_to_excel(th, jsonData, defaultTitle) {
	/* original data */

	var data = jsonData;
	data.unshift(th);
	var ws_name = 'SheetJS';

	var wb = new Workbook(),
		ws = sheet_from_array_of_arrays(data);

	/*设置worksheet每列的最大宽度*/
	const colWidth = data.map((row) =>
		row.map((val) => {
			/*先判断是否为null/undefined*/
			if (val == null) {
				return {
					wch: 10
				};
			} else if (val.toString().charCodeAt(0) > 255) {
				/*再判断是否为中文*/
				return {
					wch: val.toString().length * 2
				};
			} else {
				return {
					wch: val.toString().length + 5
				};
			}
		})
	);
	/*以第一行为初始值*/
	let result = colWidth[0];
	colWidth[0][0]['wch'] = 10;
	for (let i = 1; i < colWidth.length; i++) {
		for (let j = 0; j < colWidth[i].length; j++) {
			if (result[j]['wch'] < colWidth[i][j]['wch']) {
				result[j]['wch'] = colWidth[i][j]['wch'];
			}
		}
	}
	ws['!cols'] = result;

	/* add worksheet to workbook */
	wb.SheetNames.push(ws_name);
	wb.Sheets[ws_name] = ws;

	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');
}

// 导出多个sheet
export function export2ExcelMultiSheet({
	multiHeader = [],
	header,
	data,
	sheetname,
	filename,
	merges = [],
	autoWidth = true,
	bookType = "xlsx"
} = {}) {
	/* original data */
	filename = filename || "excel-list"
	data = [...data]

	for (var i = 0; i < header.length; i++) {
		data[i].unshift(header[i])
	}

	// data.unshift(header)

	for (let i = multiHeader.length - 1; i > -1; i--) {
		data.unshift(multiHeader[i])
	}

	var ws_name = sheetname
	var wb = new Workbook(),
		ws = []
	for (var j = 0; j < header.length; j++) {
		ws.push(sheet_from_array_of_arrays(data[j]))
	}

	if (merges.length > 0) {
		if (!ws["!merges"]) ws["!merges"] = []
		merges.forEach(item => {
			ws["!merges"].push(XLSX.utils.decode_range(item))
		})
	}



	// console.log("width", autoWidth)
	if (autoWidth) {
		/*设置worksheet每列的最大宽度*/
		var colWidth = []
		for (var k = 0; k < header.length; k++) {
			colWidth.push(
				data[k].map(row =>
					row.map(val => {
						/*先判断是否为null/undefined*/
						if (val == null) {
							return {
								wch: 10
							}
						} else if (val.toString().charCodeAt(0) > 255) {
							/*再判断是否为中文*/
							return {
								wch: val.toString().length * 2
							}
						} else {
							return {
								wch: val.toString().length + 5
							}
						}
					})
				)
			)
		}

		/*以第一行为初始值*/
		let result = []
		for (var k = 0; k < colWidth.length; k++) {
			result[k] = colWidth[k][0]
			for (let i = 1; i < colWidth[k].length; i++) {
				for (let j = 0; j < colWidth[k][i].length; j++) {
					if (result[k][j]["wch"] < colWidth[k][i][j]["wch"]) {
						result[k][j]["wch"] = colWidth[k][i][j]["wch"]
					}
				}
			}
		}
		// 分别给sheet表设置宽度
		for (var l = 0; l < result.length; l++) {
			ws[l]["!cols"] = result[l]
		}
	}

	/* add worksheet to workbook */
	for (var k = 0; k < header.length; k++) {
		wb.SheetNames.push(ws_name[k])
		wb.Sheets[ws_name[k]] = ws[k]
	}

	var wbout = XLSX.write(wb, {
		bookType: bookType,
		bookSST: false,
		type: "binary"
	})
	saveAs(
		new Blob([s2ab(wbout)], {
			type: "application/octet-stream"
		}),
		`${filename}.${bookType}`
	)
}

使用
项目上使用antd ui库 其他ui库写法稍微有些区别

 // 导出数据
    async exportData() {
      this.loading = true
      let filterVal = this.columns.map((el) => {
        return el.dataIndex
      })
      let th = this.columns.map((el) => {
        return el.title
      })
      let filterVals = this.columnss.map((el) => {
        return el.dataIndex
      })
      let ths = this.columnss.map((el) => {
        return el.title
      })
      let result = [
        {
          tHeader: th, //表头
          filterVal: filterVal,  // 表头对应字段
          tableDatas: this.tableDataList,  //数据
          sheetName: '全部数据', // sheet名称
        },
        {
          tHeader: ths,
          filterVal: filterVals,
          tableDatas: this.reList,
          sheetName: '汇总数据',
        },
      ]
      let obj = {}
       this.shipList.map((el) => {
        if (this.params.imo == el.imo) {
           obj =   el
        }
      })
      // console.log(obj)
      this.json2excel(result, obj.shipName + '~ 停泊油耗', true, 'xlsx')
      this.loading = false
    },

    json2excel(tableJson, filenames, autowidth, bookTypes) {
      var that = this
      import('@/excel/Export2Excel').then((excel) => {
        var tHeader = []
        var dataArr = []
        var sheetnames = []
        for (var i in tableJson) {
          tHeader.push(tableJson[i].tHeader)
          dataArr.push(that.formatJson(tableJson[i].filterVal, tableJson[i].tableDatas))
          sheetnames.push(tableJson[i].sheetName)
        }
        excel.export2ExcelMultiSheet({
          header: tHeader,
          data: dataArr,
          sheetname: sheetnames,
          filename: filenames,
          autoWidth: autowidth,
          bookType: bookTypes,
        })
      })
    },

    // 格式化数据
    formatJson(filterVal, jsonData) {
      return jsonData.map((v) => filterVal.map((j) => v[j]))
    },