table表格导出xlsx表格带图片

H_hl2021

已于 2024-03-15 17:44:55 修改

阅读量490

点赞数 4

文章标签： java 前端 javascript vue.js npm

于 2024-03-15 17:43:02 首次发布

本文链接：https://blog.csdn.net/H_hl2021/article/details/136045479

版权

使用的是 vxe-table 插件中 vxe-grid

vxe-table v4

这是项目实现效果：

页面展示效果：

导出 xlxs 的效果

导出 xlsx 带图片的实现

vue html部分


<div class="install"  @click="exp()">下载报表</div>

<vxe-grid ref="tablex" border :data="tableData2" :columns="columnx" > </vxe-grid>

这里的exportData是 vxe-table 提供的API，具体可查vxe-table v4 (vxetable.cn)这里的方法

如果不需要导出图片出来，只需要导出文档，那直接

		exp() {
			this.$refs.tablex.exportData({
				filename: '监控图表',//导出名称
				type: "xlsx",//导出格式
		},

注意：如果 type为‘xlsx'导出失败的话，那还需要安装xlsx插件===npm install xlsx

我的版本是"xlsx": "^0.16.8"

需要像上述效果图导出图片的话===主要是sheetMethod方法实现，我这里就是通过循环把表格的是值为issuccess给它替换成✅，值为isfail替换成❎，用的是 base64

		exp() {
			this.$refs.tablex.exportData({
				filename: '监控图表',//导出名称
				type: "xlsx",//导出格式
				sheetMethod: this.sheetMethod, // 调用实现方法
				useStyle: true,//是否启用样式
				modes: ["current", "selected"] // current当前页，selected选中的，all所有的
			})
		},

		sheetMethod({ options, workbook, worksheet }) {
			const { data } = options;
			const cellValues = {};
			console.log(workbook, worksheet, worksheet.rowCount, worksheet.columnCount)
			for (let rowIndex = 1; rowIndex <= worksheet.rowCount; rowIndex++) {
				for (let colIndex = 1; colIndex <= worksheet.columnCount; colIndex++) {
					const cell = worksheet.getCell(rowIndex, colIndex); // 获取单元格
					const cellValue = cell.value; // 获取单元格的值
					cellValues[`${rowIndex},${colIndex}`] = cellValue; // 存储单元格的值
					if (cellValue === 'issuccess') {
						console.log(rowIndex, colIndex)
						const imgid = workbook.addImage({
							base64: '',//这里我用的是 base64,把需要图片的 base64 放进来
							extension: "png"//使用 png 减小导出大小
						});
						worksheet.addImage(imgid, {
							tl: { col: colIndex - 1 + 0.8, row: rowIndex - 1 + 0.5 }, // 小数0.5是图片在单元格居中显示
							ext: { width: 40, height: 40 } // 设置图片大小
						});
						worksheet.getCell(rowIndex, colIndex).value = "";//这个是清掉原来字段值

					} else if (cellValue === 'isfail') {
						//cellValue=''
						const imgid = workbook.addImage({
							base64: '',//这里我用的是 base64,把需要图片的 base64 放进来
							extension: "png"//使用 png 减小导出大小
						});
						worksheet.addImage(imgid, {
							tl: { col: colIndex - 1 + 0.8, row: rowIndex - 1 + 0.5 }, // 小数0.5是图片在单元格居中显示
							ext: { width: 40, height: 40 } // 设置图片大小
						});
						worksheet.getCell(rowIndex, colIndex).value = "";//这个是清掉原来字段值

					}

				}


			}
			console.log(cellValues)
		},

完整代码实现（可直接复制，替换图片可使用）：

下面 2 个表格，一个是展示，一个是导出，2 者可独立开来使用，具体根据 data 数据和columns去替换，我这里tableData数据和columns是根据月份日期来着，数据 true为 ✅，false为❎，

<template>
	<div>

		<div style="width: 100px; line-height:40px; text-align: center; margin: 20px; background-color:blue; color: #fff; border-radius: 5px; cursor: pointer;" @click="exp()">下载报表</div>
		<!--页面展示列表-->
		<div style="margin: 20px; height:calc(100vh - 150px);">
			<vxe-grid ref="tables" border :data="tableData" :columns="columns" max-height="100%"> </vxe-grid>
		</div>

		<!--导出使用，不作展示-->
		<div style="position: fixed; left: -999px; top:-999px ;">
			<vxe-grid ref="tablex" border :data="tableData2" :columns="columnx" max-height="100%"> </vxe-grid>
		</div>


	</div>
</template>
<script>
export default {
	data() {
		return {
			columns: [],
			columnx: [],
			tableData: [],
			tableData2: [],
			dataform: {
				dateTime: "2023-01",
				projectName: ""

			},

		};
	},
	created() {
		this.getMonth()
		this.tableData = [{
			"projectId": "11",
			"projectName": "测试项目1",
			"one": true,
			"two":null,
			"three": false,
			"four": false,
			"five": false,
			"six": false,
			"seven": false,

		}, {
			"projectId": "22",
			"projectName": "测试项目2",
			"one": true,
			"two": null,
			"three": true,
			"four": false,
			"five": true,
			"six": true,
			"seven": false,

		}]

		this.tableData2= this.replaceTrueWithImage(JSON.parse(JSON.stringify(this.tableData)))
		this.setgrid();

	},
	methods: {
		exp() {
			this.$refs.tablex.exportData({
				filename: '监控图表',
				type: "xlsx",
				sheetMethod: this.sheetMethod, // 调用实现方法
				useStyle: true,
				modes: ["current", "selected"] // current当前页，selected选中的，all所有的
			})
		},
		sheetMethod({ options, workbook, worksheet }) {
			const { data } = options;
			const cellValues = {};
			console.log(workbook, worksheet, worksheet.rowCount, worksheet.columnCount)
			for (let rowIndex = 1; rowIndex <= worksheet.rowCount; rowIndex++) {
				for (let colIndex = 1; colIndex <= worksheet.columnCount; colIndex++) {
					const cell = worksheet.getCell(rowIndex, colIndex); // 获取单元格
					const cellValue = cell.value; // 获取单元格的值
					cellValues[`${rowIndex},${colIndex}`] = cellValue; // 存储单元格的值
					if (cellValue === 'issuccess') {
						console.log(rowIndex, colIndex)
						const imgid = workbook.addImage({
							base64: 'image/png;base64,iVBORw0KGgoAAAANSUhEUgAAADAAAAAwCAYAAABXAvmHAAAAAXNSR0IArs4c6QAABIlJREFUaEPtmGtoHFUUx//nToxJqKQ7eewmRKO1ohTx0RKUghUflaK7k1Ro6qf4QCgINlAKrYKa6ocaKBYVQQSpFkFM1JrZLEVpBf2gCLZSX1F81pYks2lmSVuSpnXvkVk3EJPZmTuzk4ZK5ttyzz3n/7vn3nPPXcIl/tElrh9LAIudwaUM/G8z0NLXUn2h5kwTpNZIEqcrK/IjfyUnclEDR7aF6gfqmwXlUwQyAKwFsNxF7DkCDQGcJiHMkeSpI+UClQ3QnK5fnWe5G8B6IHBV+4OJerJHxt9BD2QYmNAATZnlrVKKF8HYHEL4XK3fs8D2bNL+OChEKIDEQP1dTLIfQF3QgF72RHhhNGk/BwKr+g0MkDBjTzDoZQAVqkEC2TEOyGmta6xz7KzKvEAARfGvqTguy4ZwyJqyN6ATeT8/ygDFbfPJgq38PKX8qmXktkYCUDiweeGUvEj3vK84wmOjKXuf57nxc+KMx9P6u2A8pGIbsY19/hyvyHXmJkr59d1CxTr/dQSlMhwb8W4rlXs6NEDc1J3afF+46JHMmpSadu3YA2Ojbt48M+C0BxrJkwu1+gTCpis3g1mi/2RfaVqibis1/kpggEYztoVAr0eyjnOcOOJ7b9qDrtZHCiO3HV6D45N/lgp12DLsewMDxE09A+D+qAHmiv/5zE+4+7N1yHOJsk+4cH6KG9wOs+cWipu60/66dZWhmeaK//XsL3jwCwPZ6ay3T0nrrY7xQ3ONSgIU+vmqycnQSl0mhhYPgIkezqbG9ysDxAdrV0Bqv6kAbGrpBJFA/4n3wCX6sHLEOxoIvHPUyPWqA5j67QC+9ANorbkaX93z77tk//G3sOPb7fMgyhVf1LDXMuxtygCJj/RVLPCDH4BGGj6983Ncf8UNrhARiQcxdo222z3KAFcN1sampWb7ATjjjZc34sO1JlYuu+4/EM6P2aVS+cC6BGXmLdn23BvKAI5h3NSnAFSFhXDmzdT5csQX4hMbViqXDgSQMOuOMvhWFQC3TMzMK1u8U4Xy+ZXZjRPziornPZAw9V0MPKsK4AYRhXgAQ5Zhr3LT4QnQNFi/RkrpdKKBPudMvHSz8+oEth3r9r+kfL1Tr2WM7wwMUDwHvwO4xjfGAhoIFm0j7adcF9L3PdCYrusi5rcXUJ+3a0LGStnJUka+AOiBiK/WjwG4cREgJCTfYnXkvgsPAKBhQN8gCAcXAeBNy7Af94rrn4Hi7ERaf54Zz1wsCAYfraDqO4ZTw54NpTIAGBQ39Q9A2HgRICxNom24wz7hF0sdwNlKfQ3LRHX+ABiuryO/YIrjlmCRLFV1At3ErgH7oMWrYnsBelJRkLIZgb4RkttVVn7GaaAMzFaSSOuPMmMPAF1ZYWlD56/1fRpVbfXb8+VnYJaHWF+strIaO8DUDaAmFAghgzw/5VUqI6lCXk4aMg0JIWUnmA0Q1oFxmQ/MEECmYHpfda+XdQ8EWdlCVipFG2toFiybnOJFjNMSPEwCI/y3/NGtqwwSY7Zt6DMQNmDU85YAol7RoP6WMhB0xaK2/wfKK8NAsJLZJQAAAABJRU5ErkJggg==',
							extension: "png"
						});
						worksheet.addImage(imgid, {
							tl: { col: colIndex - 1 + 0.8, row: rowIndex - 1 + 0.5 }, // 小数0.5是图片在单元格居中显示
							ext: { width: 40, height: 40 } // 设置图片大小
						});
						worksheet.getCell(rowIndex, colIndex).value = "";//这个是清掉原来字段值

					} else if (cellValue === 'isfail') {
						//cellValue=''
						const imgid = workbook.addImage({
							base64: 'data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAB4AAAAeCAYAAAA7MK6iAAAAAXNSR0IArs4c6QAAAvJJREFUSEvFl09IFFEcx79vdl2kkFLLGc0xtZTOEtHBwhWiS1DRQYuiokCihFy9VRSEx12DIgoqigLtENihQ5dW0ENEf66Fxpi76kylBh60dXZ+8WZb23FX581mOefv+31+v9977/e+w7BGH8uHq51CoVlUssnvC/jxQ9drHmLeaxwhcLQJ/voG+YgFHCCwRsZQnQkiolGJ4bVloS85Y7wUSWRFMAdu3ymfJAuXGWMO2HIVEkiXiK5VRr7eXakLy4JjHaVbmFTQR0Cj1zZyPQOiEtjpivDkl1zrc4InOsu3mkQDS1uaRwKaDyyYC54FXkVoOk9tPpHYW3dzOp6ZeBY41qlEATS5VVcUvGBLZqO33KS874Mjb/Xm4ADMtNgBjneVtRFJd9wjARXXP9qyiSs7ROSARWfVHuN+FpjfTV+prDEwRSSSZzBIG3ln1KerXqx4vEs+aBHrF4FyjXcwQEStVRHj6e9Tn0LFO+VeAmv9l2AQPVEjxgkHeCwka6JDIt+KAWhqWK9dBPP99Zcqc6LV/gWYzCl9HR+p9h5rF5Vqvw/a/wDPJxJV/E7b4OH2ksrCQCD2P8BmErU1N/TRVMVr1WoOHwspmpfZvPl8P0DAt9uHPDSKNDVs/DlcfGUsJD8GY8c9RMlDSr1q2DjmuE7jIbnFYqxPJBoLrEdh3R5bOvcpCpg/RZYRWXS4qsd47gDzR39bgzwscpd9xSrKL72xYZPdu5CcETmXNGlOGbVpd+J4JGId8hlI7J5b+r4N5ZC7+CMGGJF9ImBiZJ3LdCUOsG11GpRXYEj1cbU+QlSN6M2Z4bLe43h7SaVVEBj0csJd8svpQnJaHz7JfBKiqwAXtz7p7O0xKuEBGIJ5dJxAGAItHFV7psZzrXf11fFQWRsxdhUpg+CmJ4BGYaF75IPxKNPqLIW7BbL1tjsplvczCS0AdgNZHpvP3iGL6MXn98azlYDpBITAS7PliWCjohQkE+bC7PR3kT+HvCrOY49dl/wCbbEtLnJqqOUAAAAASUVORK5CYII=',
							extension: "png"
						});
						worksheet.addImage(imgid, {
							tl: { col: colIndex - 1 + 0.8, row: rowIndex - 1 + 0.5 }, // 小数0.5是图片在单元格居中显示
							ext: { width: 40, height: 40 } // 设置图片大小
						});
						worksheet.getCell(rowIndex, colIndex).value = "";//这个是清掉原来字段值

					}

				}


			}
			console.log(cellValues)
		},
		//判断当前年月
		getMonth() {
			const currentDate = new Date();
			const currentYear = currentDate.getFullYear();
			const currentMonth = (currentDate.getMonth() + 1).toString().padStart(2, '0');
			const currentYearMonth = `${currentYear}-${currentMonth}`;
			this.dataform.dateTime = currentYearMonth
			console.log("当前年月是：" + currentYearMonth);
		},
		//设置
		setgrid() {
			let startColumns = [{ title: this.dataform.dateTime, field: 'projectName', minWidth: 200, fixed: "left" }];
			const dateArray = this.dataform.dateTime.split('-');
			const currentYear = dateArray[0]
			const currentMonth = dateArray[1]
			// 获取当前月的天数
			const daysInMonth = new Date(currentYear, currentMonth, 0).getDate();
			// 截取 initialData，只保留当前月的数据
			let initialData = [
				{ title: '01', field: 'one' },
				{ title: '02', field: 'two' },
				{ title: '03', field: 'three' },
				{ title: '04', field: 'four' },
				{ title: '05', field: 'five' },
				{ title: '06', field: 'six' },
				{ title: '07', field: 'seven' },
				{ title: '08', field: 'eight' },
				{ title: '09', field: 'nine' },
				{ title: '10', field: 'ten' },
				{ title: '11', field: 'eleven' },
				{ title: '12', field: 'twelve' },
				{ title: '13', field: 'thirteen' },
				{ title: '14', field: 'fourteen' },
				{ title: '15', field: 'fifteen' },
				{ title: '16', field: 'sixteen' },
				{ title: '17', field: 'seventeen' },
				{ title: '18', field: 'eighteen' },
				{ title: '19', field: 'nineteen' },
				{ title: '20', field: 'twenty' },
				{ title: '21', field: 'twentyOne' },
				{ title: '22', field: 'twentyTwo' },
				{ title: '23', field: 'twentyThree' },
				{ title: '24', field: 'twentyFour' },
				{ title: '25', field: 'twentyFive' },
				{ title: '26', field: 'twentySix' },
				{ title: '27', field: 'twentySeven' },
				{ title: '28', field: 'twentyEight' },
				{ title: '29', field: 'twentyNine' },
				{ title: '30', field: 'thirty' },
				{ title: '31', field: 'thirtyOne' }
			].slice(0, daysInMonth);
			//let endColumn = [
			//	{ title: '完成', field: 'istrue', width: 100, align: 'center', fixed: "right" },
			//	{ title: '未上传', field: 'isfalse', width: 100, align: 'center', fixed: "right" },
			//];
			//展示 table 的
			const columns = [
				...startColumns,
				...initialData.map((item, index) => {
					return {
						width: 100,
						align: 'center',
						title: item.title,
						field: item.field,
						slots: {
							default: ({ row, column }, h) => {
								return row[column.field] === null
									? '' : (row[column.field] ? h('img', {
										attrs: {
											style: 'display: block; width: 24px; height: 24px;margin: auto',
											src: require('/@/assets/images/icon/icon_wancheng_xiao.png'),
										},
									}) : h('img', {
										attrs: {
											style: 'display: block; width: 24px; height: 24px;margin: auto',
											src: require('/@/assets/images/icon/icon_weiwancheng.png'),
										},
									}))
							},
						},
					};
				}),
				//...endColumn,
			];
			//导出 table 的
			const columnx = [
				...startColumns,
				...initialData.map((item, index) => {
					return {
						width: 100,
						align: 'center',
						title: item.title,
						field: item.field,

					};
				}),
				//...endColumn,
			];
			this.columns = columns;
			this.columnx = columnx;
		},
		replaceTrueWithImage(data) {
			for (let obj of data) {
				for (let key in obj) {
					if (obj.hasOwnProperty(key) && obj[key] === true) {
						obj[key] = 'issuccess';
					}
					else if (obj.hasOwnProperty(key) && obj[key] === false) {
						obj[key] = 'isfail';
					}
				}
			}
			return data;
		}

	}
};
</script>