vue实现导出+ 样式修改

1.安装插件

npm install xlsx-style   ^0.18.5

npm install xlsx -S  ^0.8.13

2. 修改代码 node_modules里面找到  以下位置xlsx.js  搜索 write_ws_xml_data

替换成以下代码

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("");
	}

3.封装组件

import * as XLSX from "xlsx"
import XLSXStyle from "xlsx-style";
let _needSetStyles = []
let cols = []
let ifsubhead = false

/**
 * 导出excel表格
 * @param {Array} tableData - 需要的出表格的数组
 * @param {Array} header - 表头
 * @param {Array} headerItem - 子表
 * @param {String} notice - 注意事项
 * @param {Array} needChangeColor - 需要修改颜色的属性
 * @param {Array} clo - 每一项的宽度
 * @param {Array} subhead - 副标题信息
 * @param {string} filename - 导出文件名称
 */
export function exportData(tableData, header, headerItem, notice, needSetStyles = [], col = [], subhead = [], filename) {
    // 检查是否有需要修改颜色的属性
    _needSetStyles = needSetStyles.length > 0 ? needSetStyles : [];
    // 检查是否有指定每一列的宽度
    cols = col.length > 0 ? col : [];

    if (tableData.length > 0) {
        // 表头设置
        const aoa = [[notice]];

        console.log(tableData);
        if (subhead.length > 0) {
            aoa.push(subhead);
            ifsubhead = true
        }
        aoa.push(header);

        console.log(headerItem, 'headerItem');
        if (headerItem.length > 0) {
            headerItem.forEach(item => {
                aoa.push(item)
            })
        }

        // 创建 Excel 表格并设置样式
        const sheet = xlsxAddStyle(aoa, notice);

        // 导出文件
        openDownloadDialog(sheet2blob(sheet), `${filename}.xlsx`);
    } else {
        // 表格数据为空，可以进行相应的处理
    }

}
export function openDownloadDialog(url, saveName) {
    let urlA;
    if (typeof url === "object" && url instanceof Blob) {
        urlA = URL.createObjectURL(url); // 创建blob地址
    }
    const aLink = document.createElement("a");
    aLink.href = urlA;
    // HTML5新增的属性，指定保存文件名，可以不要后缀，注意，file:///模式下不会生效
    aLink.download = saveName || "";
    let 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);
}
// 将一个sheet转成最终的excel文件的blob对象，然后利用URL.createObjectURL下载
export function sheet2blob(sheet, sheetName) {
    var sheetNameS = sheetName || "sheet3";
    var workbook = {
        SheetNames: [sheetNameS],
        Sheets: {},
    };
    workbook.Sheets[sheetNameS] = sheet;
    workbook.Sheets['sheet100'] = sheet;
    // 生成excel的配置项
    var wopts = {
        bookType: "xlsx", // 要生成的文件类型
        bookSST: false, // 是否生成Shared String Table，官方解释是，如果开启生成速度会下降，但在低版本IOS设备上有更好的兼容性
        type: "binary",
    };
    var wbout = XLSXStyle.write(workbook, wopts);
    // XLSXStyle.write(wb, { bookType: bookType, bookSST: false, type: 'binary' });
    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;
}
// 表格样式的设置
export function xlsxAddStyle(xlsx, filename) {
    // 创建工作簿
    // const workbook = XLSX.utils.book_new();
    const sheet = XLSX.utils.aoa_to_sheet(xlsx);
    console.log(sheet, 'sheet');
    const mergeArr = []; // 合并的单元格
    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];
            console.log(element, 'element');
            if (typeof element === "object") {
                const index = Number(key.slice(1)) - 1;
                rowH[index] = { hpt: 20, hpx: 20 };
                element.s = {
                    alignment: {
                        horizontal: "center", // 所有单元格右对齐
                        vertical: "center", // 所有单元格垂直居中
                    },
                    font: {
                        name: "宋体",
                        sz: 10,
                        italic: false,
                        underline: false,
                    },
                    // border: borderAll,
                    // fill: {
                    //   fgColor: { rgb: "FFFFFFFF" },
                    // },
                };
                // 指标值表格的样式
                if (key.indexOf("C") > -1) {
                    element.s.alignment.horizontal = "center";
                }
                // 标题的样式
                if (index === 1) {
                    element.s.font.bold = true;
                    element.s.rows = { hpx: 150 }
                }
                // 如果传来副标题，那么之前设置的样式会被副标题顶替掉，需要重新设置样式
                if (ifsubhead) {
                    if (index === 1) {
                        element.s.font.bold = true;
                        // 高
                        rowH[1] = { hpt: 20, hpx: 20 }
                    }
                    if (index === 2) {
                        element.s.font.bold = true;
                        rowH[2] = { hpt: 30, hpx: 30 }

                    }
                }
                // 处理合并单元格数组 s 开始 e 结束  c 列 r行
                if (element.v == filename) {
                    mergeArr.push({
                        s: { c: 0, r: 0 },
                        e: { c: cols.length - 1, r: 0 },
                    });
                    rowH[0] = { hpt: 110, hpx: 110 }

                }
                if (element.v.includes('职业工种')) {
                    mergeArr.push({
                        s: { c: 0, r: 1 },
                        e: { c: 1, r: 1 },
                    });
                }
            }
        }
    }
    // 表头的样式设置
    sheet["A1"].s.alignment.horizontal = "left";
    // sheet["A1"].s.font.underline = true;
    sheet["A1"].s.font.sz = 11;
    sheet["A1"].s.font.color = { rgb: 'FFFFFF' }
    sheet["A1"].s.border = borderAll
    sheet['A1'].s.fill = { fgColor: { rgb: '217346' } }
    // 单元格的列宽
    sheet["!cols"] = cols
    // 设置行高
    sheet['!rows'] = rowH;


    // 自定义样式
    _needSetStyles.forEach(item => {
        sheet[item.name].s = item.style
    })

    sheet["!merges"] = mergeArr;
    return sheet
}

4.页面使用

<template>
  <div>
    <el-button type="success" @click="createTemplate">导出excel</el-button>
  </div>

</template>
<script>
import {exportData} from '@/untils/exportData'  //上面组件的地址

export default {
  methods: {
    createTemplate() {
      let templateList = [{examNumber:'物质名称1',name:'物质因子1'}]
      let header = ['物质名称', '物质因子', '时间']
      let subhead = ['不存在日校准的日期请对整列进行删除；请按实际校准数据时间修改日期和小时值，若时间列不够可自行增加列；缺失数据填充-999；请勿填充非数值内容；若模板中与实际监测因子个数不一致请联系平台管理人员'];
      let notice = [
        1、xxxxxxxxxxxxxxx；
        2、xxxxxxxxxxxxxxx；
      ]
      let headerItem = templateList.map(item => {
        return [item.examNumber, item.name, '0']
      })

      const clos = [
        { wpx: 210 }, { wpx: 210 }, { wpx: 420 }
      ];
      // 字体样式
      const fontColorStyle = {
        name: "宋体",
        sz: 10,
        italic: false,
        underline: false,
        color: { rgb: 'FFFFFF' },
      };
      // 单元格外侧边框
      const borderStyle = {
        top: { style: "thin" },
        bottom: { style: "thin" },
        left: { style: "thin" },
        right: { style: "thin" },
      };

      const setStyles = [
        {
          name: 'A3',
          style: {
            font: fontColorStyle,
            fill: {
              fgColor: { rgb: 'FF4949' } // 设置背景颜色
            },
            alignment: {
              horizontal: "center", // 所有单元格右对齐
              vertical: "center", // 所有单元格垂直居中
            },
            border: borderStyle
          }
        },
        {
          name: 'B3',
          style: {
            font: fontColorStyle,
            fill: {
              fgColor: { rgb: 'FF4949' } // 设置背景颜色
            },
            alignment: {
              horizontal: "center", // 所有单元格右对齐
              vertical: "center", // 所有单元格垂直居中
            },
            border: borderStyle
          }
        },
        {
          name: 'C3',
          style: {
            font:
            fontColorStyle,
            fill: {
              fgColor: { rgb: 'FF4949' } // 设置背景颜色
            },
            alignment: {
              horizontal: "center", // 所有单元格右对齐
              vertical: "center", // 所有单元格垂直居中
            },
            border: borderStyle
          }
        }
      ]
      exportData(templateList, header, headerItem, notice, setStyles, clos, subhead, '导出模板')
    },
  },
}
</script>

就导出成功啦

ps 会报错

解决办法

 修改vue.config.js

npm install node-polyfill-webpack-plugin

const {defineConfig} = require('@vue/cli-service')

const NodePolyfillPlugin = require("node-polyfill-webpack-plugin")
module.exports = defineConfig({
    transpileDependencies: true,
    lintOnSave: false,
    parallel: false,
    productionSourceMap: false,
    chainWebpack: config => {
        //在 chainWebpack 添加下面的一段代码
        config.externals({ "./cptable": "var cptable" });
    },
    configureWebpack: {
        plugins: [
            new NodePolyfillPlugin()
        ],
        externals: {
            './cptable': 'var cptable',
            fs: require('fs')
        },
        resolve: {
            fallback: {
                fs: false
            }
        }
    },
})

如果还报错

源码中找到这个位置

搜索 var cpt = require('./cpt' + 'able'); 替换成var cpt = cptable;  解决