前端js数据导出excel,并修改样式
使用方法参考原文:https://blog.csdn.net/weixin_43965143/article/details/106572755
作出部分修改:该修改仅满足本人当前需求,并不完善,仅供参考
1.【修改】 自动计算列宽度 原本没有title时,header行不参与计算
2.【修改】 改了一些头部样式
3.【增加】 数字下标转excel列下标函数 number_to_excel
4.【增加】 设置修改列样式的参数 styles
import Export2Excel from "@/assets/js/Export2Excel.js"
let header = ['标签','姓名','性别','出生年月日'];
let filterVal = ["tag", "childName", "gender", "birthday"];
let list = [{
tag:'示例班',
childName:'示例',
gender:'男',
birthday:'2016/10/12',
}];
let data = Export2Excel.formatJson(filterVal, list);
let styles = {};//按列改样式
let length_str = Export2Excel.number_to_excel(header.length-1)
styles[length_str]={
alignment: {
horizontal: "left",
},
font:{
color: {
rgb: "DE3151"
},
}
}
let merges = ["A2:A64"];
Export2Excel.export_json_to_excel({
filename:“这是一个表格”,
header,
autoWidth:true,
merges:merges,
styles:styles,
data:data,
});
Export2Excel.js代码如下
import { saveAs } from "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) {
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;
}
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"
);
}
//数字转excel表头
function number_to_excel(number){
let str_1 = parseInt(number/26)
let str_2 = Math.round(number%26) + 1
return (str_1?String.fromCharCode(0x60+(str_1)).toUpperCase():'') + String.fromCharCode(0x60+(str_2)).toUpperCase();
}
// 主要修改内容在这里
function export_json_to_excel({
title, // 新增的参数,表格标题
multiHeader = [],
header,
data,
filename,
styles= {},
merges = [],
autoWidth = true,
bookType = "xlsx"
} = {}) {
/* original data */
filename = filename || "excel-list";
data = [...data];
data.unshift(header);
for (let i = multiHeader.length - 1; i > -1; i--) {
data.unshift(multiHeader[i]);
}
if(title){
data.unshift(title); // 表格标题
}
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 || val == undefined) {
return {
wch: 10
};
} else if ((val.toString().charCodeAt(3)||val.toString().charCodeAt(0)) > 255) {
/*再判断是否为中文*/
return {
wch: 10 + val.toString().length * 2
};
} else {
return {
wch: 10 + val.toString().length * 1.5
};
}
})
);
/*如果第一行是表格标题,会比较长,以主表第二行为初始值*/
let result = colWidth[title?1:0];
for (let i = 1; i < colWidth.length; i++) {
for (let j = 0; j < colWidth[i].length; j++) {
if (result[j] && 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 == "!rows" ||
i == "A1"
) { } else {
dataInfo[i + ""].s = {
border: borderAll,
alignment: {
horizontal: "center",
vertical: "center"
},
font: {
name: "微软雅黑",
sz: 10
}
};
if(styles[i.replace(/[^A-Z]+/ig,'')]){
Object.assign(dataInfo[i + ""].s,styles[i.replace(/[^A-Z]+/ig,'')])
}
}
}
// 设置表格样式
const arrabc = ["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"
]
// 给标题、表格描述信息、表头等部分加上特殊格式
arrabc.some(function (v) {
for (let j = 1; j < multiHeader.length + (title?3:2); j++) {
const _v = v + j
if (dataInfo[_v]) {
dataInfo[_v].s = {};
// 标题部分A1-Z1
if (j == 1) {
dataInfo[v + j].s = {
font: {
name: "微软雅黑",
sz: 12,
color: {
rgb: "000000"
},
bold: true,
italic: false,
underline: false
},
alignment: {
horizontal: "center",
vertical: "center"
}
};
} else {
// 表头部分,根据表头特殊格式设置
if (multiHeader.length == 0) {
// multiHeader.length = 0 时表头没有合并单元格,表头只占1行A2-Z2
const fv = v + (multiHeader.length + (title?2:1))
dataInfo[fv].s = {
border: borderAll,
font: {
name: "微软雅黑",
sz: 12,
bold: true
},
alignment: {
horizontal: "center",
vertical: "center"
},
fill: {
fgColor: {
rgb: "f0f0f0"
},
},
}
} else if (multiHeader.length == 1) {
// multiHeader.length = 0 时表头有合并单元格,表头只占2行A2-Z2,A3-Z3,这是没有描述信息只有表头合并的
dataInfo[v + j].s = {
border: borderAll,
font: {
name: "微软雅黑",
sz: 12,
},
alignment: {
horizontal: "center",
vertical: "center"
},
fill: {
fgColor: {
rgb: "f0f0f0"
}
},
}
} else {
// multiHeader.length = 0 时表头有合并单元格,表头多行
dataInfo[v + j].s = {
border: borderAll,
font: {
name: "微软雅黑",
sz: 10,
},
alignment: {
horizontal: "left",
vertical: "center"
}
}
}
}
// multiHeader.length + 2 是表头的最后1行
// console.log(dataInfo,dataInfo[v + (multiHeader.length + 2)],v + (multiHeader.length + 2))
dataInfo[v + (multiHeader.length + (title?2:1))].s = {
border: borderAll,
font: {
name: "微软雅黑",
sz: 12,
},
alignment: {
horizontal: "center",
vertical: "center"
},
fill: {
fgColor: {
rgb: "f0f0f0"
}
},
}
}
}
});
var wbout = XLSX.write(wb, {
bookType: bookType,
bookSST: false,
type: "binary"
});
saveAs(
new Blob([s2ab(wbout)], {
type: "application/octet-stream"
}),
`${filename}.${bookType}`
);
}
function formatJson(filterVal, jsonData) {
jsonData.map((row, index) => {
row.index = index + 1;
});
return jsonData.map(v =>
filterVal.map(j => {
return v[j];
})
);
}
export default {
formatJson,
number_to_excel,
export_json_to_excel,
export_table_to_excel
}