前段时间，我们项目提出一个前端导出Excel表格的需求，

这个很简单，利用xlsx，file-saver很容易实现(网上很多教程)。

后来需要加入标题，标题居中显示，加入边框等等样式需求，这就给我很多困扰，查阅大量资料和研究，最终选定xlsx-style进行修改(借鉴https://www.jianshu.com/p/23ee264ecde8)。

以下便是xlsx-style踩坑之旅：

导入问题cnpm install --save xlsx-style

import XLSX from "xlsx-style"报错：This relative module was not found: ./cptable in ./node_modules/xlsx-style@0.8.13@xlsx-style/dist/cpexcel.js

需要修改源码：

在\node_modules\xlsx-style\dist\cpexcel.js 807行 的 var cpt = require(’./cpt’ + ‘able’); 改成 var cpt = cptable;

本地src目录新建vendor/Export2Excel.js：

/* eslint-disable */

require('script-loader!file-saver');

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

}

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]

};

if (cell.v == null) continue;

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

var oo = generateArray(theTable);

var ranges = oo[1];

/* original data */

var data = oo[0];

var ws_name = "SheetJS";

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")

}

export function export_json_to_excel({

title,

multiHeader = [],

header,

data,

filename,

merges = [],

autoWidth = true,

bookType = 'xlsx'

} = {}) {

/* original data */

filename = filename || 'excel-list'

data = [...data]

data.unshift(header);

data.unshift(title);

for (let i = multiHeader.length - 1; i > -1; i--) {

data.unshift(multiHeader[i])

}

var ws_name = "SheetJS";

var wb = new Workbook(),

ws = sheet_from_array_of_arrays(data);

if (merges.length > 0) {

if (!ws['!merges']) ws['!merges'] = [];

merges.forEach(item => {

ws['!merges'].push(XLSX.utils.decode_range(item))

})

}

if (autoWidth) {

/*设置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

};

}

}))

/*以第一行为初始值*/

let result = colWidth[0];

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 dataInfo = wb.Sheets[wb.SheetNames[0]];

const borderAll = { //单元格外侧框线

top: {

style: 'thin'

},

bottom: {

style: 'thin'

},

left: {

style: 'thin'

},

right: {

style: 'thin'

}

};

//给所以单元格加上边框

for (var i in dataInfo) {

if (i == '!ref' || i == '!merges' || i == '!cols' || i == 'A1') {

} else {

dataInfo[i + ''].s = {

border: borderAll

}

}

}

// 去掉标题边框

let arr = ["A1", "B1", "C1", "D1", "E1", "F1", "G1", "H1", "I1", "J1", "K1", "L1", "M1", "N1", "O1", "P1", "Q1", "R1", "S1", "T1", "U1", "V1", "W1", "X1", "Y1", "Z1"];

arr.some(function (v) {

let a = merges[0].split(':')

if (v == a[1]) {

dataInfo[v].s = {}

return true;

} else {

dataInfo[v].s = {}

}

})

//设置主标题样式

dataInfo["A1"].s = {

font: {

name: '宋体',

sz: 18,

color: {rgb: "ff0000"},

bold: true,

italic: false,

underline: false

},

alignment: {

horizontal: "center",

vertical: "center"

},

// fill: {

// fgColor: {rgb: "008000"},

// },

};

// console.log(merges)

// console.log(dataInfo)

var wbout = XLSX.write(wb, {

bookType: bookType,

bookSST: false,

type: 'binary'

});

saveAs(new Blob([s2ab(wbout)], {

type: "application/octet-stream"

}), `${filename}.${bookType}`);

}

组建中使用：

exportExcel() {

import('@/vendor/Export2Excel').then(excel => {

const tHeader = ['船名', '船长', '货种', '载重吨', '净吨', '锚地', '预抵时间', '下锚时间', '预靠泊位'] //表头

const title = ['锚地船舶', '', '', '', '', '', '', '', ''] //标题

//表头对应字段

const filterVal = ['NAME', 'VESSEL_LENGTH', 'CARGO_NAME', 'DEADWEIGHT_TONNAGE', 'NET_TONNAGE', 'ANCHORAGE_ID', 'EXP_ARCHORAGE_TIME', 'AC_ARCHORAGE_TIME', 'RECOMMEND_BERTH']

const list = this.anchorTable

const data = this.formatJson(filterVal, list)

data.map(item => {

// console.log(item)

item.map((i, index) => {

if (!i) {

item[index] = ''

}

})

})

const merges = ['A1:I1'] //合并单元格

excel.export_json_to_excel({

title: title,

header: tHeader,

data,

merges,

filename: '锚地船舶',

autoWidth: true,

bookType: 'xlsx'

})

})

},

formatJson(filterVal, jsonData) {

return jsonData.map(v => filterVal.map(j => v[j]))

},

此时问题解决，但是另一个问题就来了，因为修改了xlsx-style源码所以我们cnpm i的时候不能获取修改后的包，所以，我想到了将修改后的源码上传自己的npm再下载自己修改后的包。

上传npm (百度)

借鉴https://blog.csdn.net/hahahhahahahha123456/article/details/82055227

解决npm publish报错问题。

最后在Export2Excel.js中

import XLSX from 'yxg-xlsx-style'

yxg-xlsx-style是自己npm上传的解决引入错误的包。

或者直接npm install --save yxg-xlsx-style引入项目

此文后期如有修改继续更新，文中借鉴部分帮助很大，研究了很久，不喜勿喷。