xlsx-style 设置行宽，行高，字体大小方法。（Vue2—基于项目需求）
1.首先安装依赖
- npm install xlsx –save
- npm install xlsx-style –save
报错解决方法
1.1.在vue.config.js中添加这段代码，然后重启。
module.exports = {
externals: {
‘./cptable’: ‘var cptable’
}
}1.2.修改源码
位置在\node_modules\xlsx-style\dist\cpexcel.js
将var cpt = require(‘./cpt’ + ‘able’);修改成var cpt = cptable;
2.随便创建一个目录（vendor）
2.1创建ExcelUtil2.js。
/* eslint-disable eqeqeq */
/**
* Excel带样式的工具类
*/
/**
// * @param dataList Array 查询的数据列表
// * @param tHeader Array 最后一行表头
// * @param filterKey Array 要导出的字段
// * @param merges Array 控制表头的跨行跨列
// * @param filename String 导出的excel文件名称
* @param percent Array 填写有百分号行数，处理百分号
* @param num Number||string 根据index_type的值来判断，处理百分号
* @param indexArr Array 获取index_type的值，进行判断
*/
export function exportStyleExcel(tHeader, filterKey, dataList, filename, multiHeader, merges, num, indexArr) {
import('@/vendor/styleExcel').then(excel => {
// 过滤表格输出的数据
const filterData = formatJson(filterKey, dataList)
const percent = searchKeys(num, indexArr)
excel.export_json_to_excel({
multiHeader,
header: tHeader,
data: filterData,
filename,
percent,
merges,
autoWidth: true,
bookType: 'xlsx',
myRowFont: '2'
})
})
}
function formatJson(filterKey, dataList) {
return dataList.map(v => filterKey.map(j => {
// 如果是金额相关的字段，则导出时返回数字格式，以便计算合计
if (j.indexOf('money') > -1) {
var num = v[j]
if (num === null || num === '' || num === undefined) {
num = 0
}
return parseFloat(num)
} else {
return v[j]
}
}))
}
function searchKeys(needle, haystack) {
var percent = []
for (const i in haystack) {
if (haystack[i] == needle) {
percent.push(+i + 2)
}
}
return percent
}2.2创建styleExcel.js
/* eslint-disable no-empty */
/* eslint-disable eqeqeq */
import {saveAs} from 'file-saver'
// import XLSX from "xlsx";
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
// eslint-disable-next-line eqeqeq
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
}
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()
var 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({
multiHeader = [],
header,
data,
filename,
merges = [],
percent = [],
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])
}
var ws_name = 'SheetJS'
var wb = new Workbook()
var 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))
})
}
//设置单元格高度
const colHeight = data.map(row => row.map(val => {
/*先判断是否为null/undefined*/
if (val == null || val == undefined) {
return {
'hpx': 50
};
}
/*再判断是否为中文*/
else if (val.toString().charCodeAt(0) > 255) {
return {
'hpx': 50
};
} else {
return {
'hpx': 50
};
}
}))
/*以第一行为初始值*/
let result1 = colHeight[0];
for (let i = 1; i < colHeight.length; i++) {
for (let j = 0; j < colHeight[i].length; j++) {
if (result1[j]['hpx'] < colHeight[i][j]['hpx']) {
result1[j]['hpx'] = colHeight[i][j]['hpx'];
}
}
}
debugger
ws['!rows'] = result1;
// 设置单元格宽度
if (autoWidth) {
/* 设置worksheet每列的最大宽度*/
const colWidth = data.map(row =>
row.map(val => {
/* 先判断是否为null/undefined*/
if (val == null || val == undefined) {
return {
wch: 120,
}
} else if (val.toString().charCodeAt(0) > 255) {
/* 再判断是否为中文*/
return {
wch: val.toString().length * 3.5,
}
} else {
return {
wch: val.toString().length * 2,
}
}
})
)
/* 以主表第二行为初始值，因为我的第一行是表格标题，会比较长，所以以主表第二行为初始值*/
const 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 == '!rows' ||
i == 'A1'
) {
} else {
dataInfo[i + ''].s = {
border: borderAll,
alignment: {
horizontal: 'center',
vertical: 'center'
},
font: {
name: '宋体',
row: 50,
sz: 18
}
}
}
}
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 < (data.length + 1); j++) {
const _v = v + j
if (dataInfo[_v]) {
dataInfo[_v].s = {}
// 标题部分A1-Z1
dataInfo['A' + j].s = {
border: borderAll,
font: {
name: '宋体',
row: 50,
sz: 18,
color: {
rgb: '000000'
},
bold: true
},
alignment: {
horizontal: 'center',
vertical: 'center'
}
}
dataInfo[v + j].s = {
border: borderAll,
numFmt: '0.00',
font: {
name: '宋体',
row: 50,
sz: 18,
color: {
rgb: '000000'
}
},
alignment: {
horizontal: 'center',
vertical: 'center'
}
}
// 头部
if (j == 1) {
dataInfo[v + j].s = {
border: borderAll,
font: {
name: '宋体',
row: 50,
sz: 18,
color: {
rgb: '000000'
},
bold: true
},
alignment: {
horizontal: 'center',
vertical: 'center'
},
fill: {
fgColor: {
rgb: 'f0f0f0'
}
}
}
}
// 百分比 %
if (percent.length != 0) {
for (let index = 0; index < percent.length; index++) {
if (j == percent[index]) {
dataInfo[v + percent[index]].s = {
numFmt: '0.00%',
border: borderAll,
font: {
name: '宋体',
sz: 18,
row: 50,
color: {
rgb: '000000'
}
},
alignment: {
horizontal: 'center',
vertical: 'center'
},
fill: {
fgColor: {
rgb: 'FFF8DC'
}
}
}
}
}
}
}
}
})
var wbout = XLSX.write(wb, {
bookType: bookType,
bookSST: false,
type: 'binary'
})
saveAs(
new Blob([s2ab(wbout)], {
type: 'application/octet-stream'
}),
`${filename}.${bookType}`
)
}3.因为xlsx是支持行高的，所以要将xlsx.js(node_modules\xlsx\dist\xlsx.js)里的write_ws_xml_data方法替换到xlsx-style(node_modules\xlsx-style\dist\xlsx.js)里面的write_ws_xml_data。
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("");
}
效果图
调用方法
import {exportStyleExcel} from '@/vendor/ExcelUtil2'
handleDownload() {
this.$message('正在导出，请稍等')
const tHeader = ['隐患名称', '线路名称', '端点名称', '隐患类型', '隐患位置', '隐患时间', '像素面积', '活动开始时间', '活动结束时间', '是否已读', '是否处理',] //导出表头
const filterVal = ['objectName', 'lineName', 'siteName', 'objectType', 'actualPosition', 'time', 'objectArea', 'startTime', 'endTime', 'isRead', 'isHandle',] //表头对应的字段
const todoListXlsx = this.DetailsForm //接口调取的数据
//tHeader 指的是表头 filterVal 指的是表头字段 this.todoListXlsx 指的是内容 重要的这三个 4 是我处理百分号导出为文本型改为数字型
exportStyleExcel(tHeader, filterVal, todoListXlsx, '隐患测试报告', [], [], null, false)
},
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
