vue使用xlsx-style 导出excel 并带有样式
一、下载插件npm install xlsx-style --savenpm install xlsx --save下载好在 vue.config.js 修改一下 xlsx-style 不然会报错// 添加这一句config.externals({ './cptable': 'var cptable' })二 创建此文件文件,如果想要修改样式就在 红色圈出的(export_json_to_exce
·
一、下载插件
npm install xlsx-style --save
npm install xlsx --save
下载好在 vue.config.js 修改一下 xlsx-style 不然会报错
// 添加这一句
config.externals({ './cptable': 'var cptable' })
二 创建此文件文件,如果想要修改样式就在 红色圈出的(export_json_to_excel)这里修改,其他不变
这些就是修改的样式,可以看一下官方文档,文章下面有链接
styleExcel文件
/* 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))
})
}
// 设置单元格宽度
if (autoWidth) {
/* 设置worksheet每列的最大宽度*/
const colWidth = data.map(row =>
row.map(val => {
/* 先判断是否为null/undefined*/
if (val == null || val == undefined) {
return {
wch: 20
}
} else if (val.toString().charCodeAt(0) > 255) {
/* 再判断是否为中文*/
return {
wch: val.toString().length * 2
}
} 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: '微软雅黑',
sz: 12
}
}
}
}
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: '微软雅黑',
sz: 12,
color: {
rgb: '000000'
},
bold: true
},
alignment: {
horizontal: 'center',
vertical: 'center'
}
}
dataInfo[v + j].s = {
border: borderAll,
numFmt: '0.00',
font: {
name: '微软雅黑',
sz: 12,
color: {
rgb: '000000'
}
},
alignment: {
horizontal: 'right',
vertical: 'center'
}
}
// 头部
if (j == 1) {
dataInfo[v + j].s = {
border: borderAll,
font: {
name: '微软雅黑',
sz: 13,
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: 12,
color: {
rgb: '000000'
}
},
alignment: {
horizontal: 'right',
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}`
)
}
三、在 utils 里创建 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
}
四、引用刚刚封装的ExcelUtil2.js文件
import { exportStyleExcel } from '@/utils/ExcelUtil2'
给导出设置个点击事件
// 导出
handleDownload() {
this.$message('正在导出,请稍等')
this.indexDataList('xlsx')
const tHeader = []
this.headerList.forEach((item) => {
tHeader.push(item.name)
})
const filterVal = []
this.headerList.forEach((item) => {
filterVal.push(item.code)
})
const filename = '应收账款报表'
//tHeader 指的是表头 filterVal 指的是表头字段 this.todoListXlsx 指的是内容 重要的这三个 4 是我处理百分号导出为文本型改为数字型
exportStyleExcel(tHeader, filterVal, this.todoListXlsx, filename, [], [], 4, this.indexArr)
}
最终效果
如果要修改样式 可以看看文档
添加链接描述
更多推荐
已为社区贡献3条内容
所有评论(0)