vue导出excel表格并支持样式、行高修改xlsx&xlsx-style

最新推荐文章于 2024-04-26 17:54:13 发布

Norimaki紫菜

最新推荐文章于 2024-04-26 17:54:13 发布

阅读量3.1k

点赞数

文章标签： vue.js excel 前端 javascript

本文链接：https://blog.csdn.net/sk44149650/article/details/131110467

版权

前言

使用插件xlsx、xlsx-style导出带有样式的表格，两者都可以实现创建excel，但是xlsx免费版仅仅只能做单元格合并，收费版才能设置单元格样式，所以需要用到xlsx-style插件实现表格样式的修改

一、使用步骤

1. 安装环境

vue: 2.5.22
xlsx: 0.18.5
xlsx-style: 0.8.13

2. 引入xlsx库

npm i xlsx xlsx-style -S

3. 修改js文件

这里我们需要两个文件：

node_modules/xlsx/dist里的xlsx.extendscript.js
node_modules/xlsx/dist里的xlsx.js

修改xlsx.extendscript.js里所有XLSX变量为XLSX2，避免和xlsx.js里的变量冲突

var XLSX2 = {};
...

修改xlsx.js里的方法，使导出支持自定义行高功能

// 增加行高设置
var DEF_PPI = 96, PPI = DEF_PPI;
function px2pt(px) { return px * 96 / PPI; }
function pt2px(pt) { return pt * PPI / 96; }
function write_ws_xml_data(ws, opts, idx, wb) {
    var o = [], r = [], range = safe_decode_range(ws['!ref']), cell="", ref, rr = "", cols = [], R=0, C=0, rows = ws['!rows'];
    var dense = Array.isArray(ws);
    var params = ({r:rr}), row, height = -1;
    for(C = range.s.c; C <= range.e.c; ++C) cols[C] = encode_col(C);
    for(R = range.s.r; R <= range.e.r; ++R) {
        r = [];
        rr = encode_row(R);
        for(C = range.s.c; C <= range.e.c; ++C) {
            ref = cols[C] + rr;
            var _cell = dense ? (ws[R]||[])[C]: ws[ref];
            if(_cell === undefined) continue;
            if((cell = write_ws_xml_cell(_cell, ref, ws, opts, idx, wb)) != null) r.push(cell);
        }
        if(r.length > 0 || (rows && rows[R])) {
            params = ({r:rr});
            if(rows && rows[R]) {
                row = rows[R];
                if(row.hidden) params.hidden = 1;
                height = -1;
                if (row.hpx) height = px2pt(row.hpx);
                else if (row.hpt) height = row.hpt;
                if (height > -1) { params.ht = height; params.customHeight = 1; }
                if (row.level) { params.outlineLevel = row.level; }
            }
            o[o.length] = (writextag('row', r.join(""), params));
        }
    }
    if(rows) for(; R < rows.length; ++R) {
        if(rows && rows[R]) {
            params = ({r:R+1});
            row = rows[R];
            if(row.hidden) params.hidden = 1;
            height = -1;
            if (row.hpx) height = px2pt(row.hpx);
            else if (row.hpt) height = row.hpt;
            if (height > -1) { params.ht = height; params.customHeight = 1; }
            if (row.level) { params.outlineLevel = row.level; }
            o[o.length] = (writextag('row', "", params));
        }
    }
    return o.join("");
}

创建自定义的js文件export2Excel.js 内容如下

// 将一个sheet转成最终的excel文件的blob对象，然后利用URL.createObjectURL下载
function sheet2blob(sheet, sheetName) {
    sheetName = sheetName || 'sheet1';
    var workbook = {
        SheetNames: [sheetName],
        Sheets: {}
    };
    workbook.Sheets[sheetName] = sheet; // 生成excel的配置项
 
    var wopts = {
        bookType: 'xlsx', // 要生成的文件类型
        bookSST: false, // 是否生成Shared String Table，官方解释是，如果开启生成速度会下降，但在低版本IOS设备上有更好的兼容性
        type: 'binary'
    };
    var wbout = XLSX.write(workbook, wopts);
    var blob = new Blob([s2ab(wbout)], {
        type: "application/octet-stream"
    }); // 字符串转ArrayBuffer
    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;
    }
    return blob;
}
 
// 将workbook装化成blob对象,可支持多个sheet
function workbook2blob(workbook) {
    // 生成excel的配置项
    var wopts = {
        // 要生成的文件类型
        bookType: "xlsx",
        // 是否生成Shared String Table，官方解释是，如果开启生成速度会下降，但在低版本IOS设备上有更好的兼容性
        bookSST: false,
        type: "binary"
    };
    var wbout = XLSX.write(workbook, wopts);
    // 将字符串转ArrayBuffer
    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;
    }
    var blob = new Blob([s2ab(wbout)], {
        type: "application/octet-stream"
    });
    return blob;
}
/**
 * 通用的打开下载对话框方法
 * @param url 下载地址，也可以是一个blob对象，必选
 * @param saveName 保持文件名，可选
 */
function openDownloadDialog(url, saveName) {
    if (typeof url == 'object' && url instanceof Blob) {
        // 创建blob地址
        url = URL.createObjectURL(url);
    }
    var aLink = document.createElement('a');
    aLink.href = url;
    // HTML5新增的属性，指定保存文件名，可以不要后缀，注意，file:///模式下不会生效
    aLink.download = saveName || '';
    var event;
    if (window.MouseEvent) event = new MouseEvent('click');
    else {
        event = document.createEvent('MouseEvents');
        event.initMouseEvent('click', true, false, window, 0, 0, 0, 0, 0, false, false, false, false, 0, null);
    }
    aLink.dispatchEvent(event);
}
// 为合并项添加边框
function addRangeBorder(range, ws) {
    let arr = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"];
    range.forEach(item => {
        let startColNumber = Number(item.s.r), endColNumber = Number(item.e.r);
        let startRowNumber = Number(item.s.c), endRowNumber = Number(item.e.c);
        const test = ws[arr[startRowNumber] + (startColNumber + 1)];
        for (let col = startColNumber; col <= endColNumber; col++) {
            for (let row = startRowNumber; row <= endRowNumber; row++) {
                ws[arr[row] + (col + 1)] = test;
            }
        }
    })
    return ws;
}

4. index.html引入

<script src="xlsx.js"></script>
<script src="export2Excel.js"></script>
<script src="xlsx.extendscript.js"></script>

5. vue代码

<template>
	<div class="config-form">
		<el-button @click="exportExcel">下载文档</el-button>
	</div>
</template>
<script>
	export default {
		data() {
			return {
				value: [20, 50],
				excelAoa: [
					['XXXXXXXXXX记录表', null, null, null, null, null, null],
					['与会者', null, null, null, null, null, null]
					['序号', '姓名', '性别', '年龄', '修改人', '修改日期', '提交日期']
				]
			}
		},
		methods: {
			exportExcel() {
				const aoa = [
					...this.excelAoa
				];
				//这个就是修改格式的代码
				sheet["A1"].s = {
					font: {
						sz: 14,
						bold: true,
						color: {
							rgb: "0000FF"
						}
					},
					fill: {
						bgColor: {
							indexed: 64
						},
						fgColor: {
							rgb: "FFFF00"
						}
					}
				}; //<====设置xlsx单元格样式
				const sheet = this.xlsxAddStyle(aoa);
				openDownloadDialog(sheet2blob(sheet), '导出.xlsx');
			},
			// 表格样式设置
			xlsxAddStyle(aoa) {
				// 注意XLSX2变量
				const sheet = XLSX2.utils.aoa_to_sheet(aoa);
				// 合并单元格 s 开始坐标  e结束坐标  c-x轴  r-y轴
				const mergeArr = [
					{s: {c: 0, r: 0}, e: {c: 6, r: 0}},
					{s: {c: 1, r: 1}, e: {c: 6, r: 1}}
				]
				const rowH = []; // 表格每列高度
				// 单元格外侧有框线
				const borderAll = {
					top: { style: "thin" },
					bottom: { style: "thin" },
					left: { style: "thin" },
					right: { style: "thin" },
				};
				// 单元格外侧无框线
				const noBorder = {
					top: { style: "" },
					bottom: { style: "" },
					left: { style: "" },
					right: { style: "" },
				};
				for (const key in sheet) {
					if (Object.hasOwnProperty.call(sheet, key)) {
						const element = sheet[key];
						if (typeof element === "object") {
							const index = Number(key.slice(1)) - 1;
							rowH[index] = { hpx: 30 };
							element.s = {
								alignment: {
									horizontal: "center", // 所有单元格右对齐
									vertical: "center", // 所有单元格垂直居中
									wrapText: true // 换行
								},
								font: {
									name: "微软雅黑",
									sz: 10,
									italic: false,
									underline: false,
								},
								border: borderAll,
								fill: {
									fgColor: { rgb: "FFFFFFFF" },
								},
							};
							// 标题的样式
							if (['A1'].includes(key)) {
								element.s.alignment.horizontal = 'center';
								element.s.font.bold = true;
								element.s.font.sz = 15;
								element.s.fill.border = noBorder;
							}
						}
					}
				}

				// 单元格的列宽
				sheet["!cols"] = [
					{ wpx: 60 }, 
					{ wpx: 100 },
					{ wpx: 120 }, 
					{ wpx: 120 }, 
					{ wpx: 100 }, 
					{ wpx: 100 },
					{ wpx: 100 }
				];
				sheet["!rows"] = rowH;
				sheet["!merges"] = mergeArr;
				// 合并项添加边框
				const sheetNew = addRangeBorder(sheet['!merges'], sheet);
				return sheetNew
			}
		}
	}
</script>

Norimaki紫菜

关注

0
点赞
踩
5

收藏

觉得还不错? 一键收藏
2
评论
vue导出excel表格并支持样式、行高修改xlsx&xlsx-style

使用插件xlsx、xlsx-style导出带有样式的表格，两者都可以实现创建excel，但是xlsx免费版仅仅只能做单元格合并，收费版才能设置单元格样式，所以需要用到xlsx-style插件实现表格样式的修改// 增加行高设置 var DEF_PPI = 96 , PPI = DEF_PPI;rows' ];
复制链接

扫一扫