数据导出
node: 16.10.0
vue.config.js 修复 ./cptable in ./node_modules/xlsx-style/dist/cpexcel.js
module.exports = {
...
configureWebpack: {
...
externals: [{
'./cptable': 'var cptable'
}],
...
},
...
}
package.json
"xlsx": "^0.18.5",
"xlsx-style": "^0.8.13"
"file-saver": "^2.0.5",
导入
import * as XLSX from 'xlsx/xlsx.mjs'
import XLSXS from 'xlsx-style'
import FileSaver from 'file-saver'
导出
exportLine1 () {
const title = '成绩表'
const data = [{
time: '学生姓名',
yuwen: '成绩',
shuxue: '成绩',
yingyu: '成绩',
tiyu: '成绩'
},
{
time: '学生1',
yuwen: 21.35,
shuxue: 43.71,
yingyu: 0,
tiyu: 27.79
},
{
time: '学生2',
yuwen: 21.35,
shuxue: 43.71,
yingyu: 0,
tiyu: 27.79
},
{
time: '学生3',
yuwen: 10.96,
shuxue: 19.02,
yingyu: 0,
tiyu: 12.24
},
{
time: '学生4',
yuwen: 10.99,
shuxue: 18.93,
yingyu: 0,
tiyu: 12.34
},
{
time: '学生5',
yuwen: 11.03,
shuxue: 18.84,
yingyu: 0,
tiyu: 12.44
}
]
// 展示名称
const headerReplace = {
time: '学生姓名',
yuwen: '语文',
shuxue: '数学',
yingyu: '英语',
tiyu: '体育'
}
const sheet = [headerReplace, ...data]
// 创建一个sheet表格 使用json_to_sheet, 数据格式比较为 数组包对象, 不然会报错
const worksheet = XLSX.utils.json_to_sheet(sheet, {
header: Object.keys(headerReplace),
// 跳过 Header, 就是把原来表格数据的表头去掉了, headerReplace渲染的数据 "冒充" 表头了
skipHeader: true,
origin: 'A2' // 设置插入位置
})
// 插入带样式title
worksheet.A1 = {
t: 's',
v: title,
s: {
font: {
bold: true,
color: {
rgb: '000000'
},
name: '微软雅黑',
sz: 12
},
alignment: {
// 居中
horizontal: 'center',
vertical: 'center'
}
}
}
const range = XLSX.utils.decode_range(worksheet['!ref'])
// 合并单元格：s:起始位置，e:结束位置，r:行，c:列
worksheet['!merges'] = [
{ s: { r: 0, c: 0 }, e: { r: 0, c: range.e.c } },
// { s: { r: 1, c: 0 }, e: { r: 1, c: 1 } }
{ s: { r: 1, c: 0 }, e: { r: 2, c: 0 } }
]
worksheet['!cols'] = []
for (let row = range.s.r + 1; row <= range.e.r; row++) {
for (let col = range.s.c; col <= range.e.c; col++) {
// console.log({ r: row, c: col })
const cellAddress = XLSX.utils.encode_cell({ r: row, c: col })
// console.log('cellAddress', cellAddress)
// 设置单元格宽度
worksheet['!cols'][col] = {
wpx: 150
}
const cellStyle = {
font: {
bold: false,
color: {
rgb: '000000'
},
name: '微软雅黑',
sz: 12
},
alignment: {
horizontal: 'center',
vertical: 'center'
}
}
if (row === 1) {
// 设置表头的样式
worksheet[cellAddress].s = {
// fill: {
// fgColor: {
// rgb: 'EBF0FE'
// }
// },
font: {
bold: true,
name: '微软雅黑',
sz: 12
},
border: {
top: {
style: 'thin',
color: {
auto: 1
}
},
left: {
style: 'thin',
color: {
auto: 1
}
},
right: {
style: 'thin',
color: {
auto: 1
}
},
bottom: {
style: 'thin',
color: {
auto: 1
}
}
},
alignment: {
horizontal: 'center',
vertical: 'center'
}
}
} else {
// 设置其他单元格样式
worksheet[cellAddress].s = cellStyle
if (col === 0) {
// 第一列为时间：格式指定
worksheet[cellAddress].z = 'yyyy-mm-dd hh:mm:ss'
}
}
}
}
const wb = XLSX.utils.book_new()
XLSX.utils.book_append_sheet(wb, worksheet, '学生成绩')
var wbout = XLSXS.write(wb, { bookType: 'xlsx', bookSST: true, type: 'binary' })
try {
FileSaver.saveAs(
new Blob([this.s2ab(wbout)], {
type: 'application/octet-stream'
}),
title + '.xlsx'
)
} catch (e) { if (typeof console !== 'undefined') console.log(e, wbout) }
return wbout
},
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
},
表格导出
<el-table
:data="tableData2"
id="outTable2"
style="width: 100%">
<el-table-column
prop="date"
label="日期"
width="180">
</el-table-column>
<el-table-column
prop="name"
label="姓名"
width="180">
</el-table-column>
<el-table-column
prop="address"
label="地址">
</el-table-column>
</el-table>
tableData2: [{
date: '2016-05-02 10:10:10',
name: '王小虎',
address: '上海市普陀区金沙江路 1518 弄'
}, {
date: '2016-05-04 10:10:10',
name: '王小虎',
address: '上海市普陀区金沙江路 1517 弄'
}, {
date: '2016-05-01 10:10:10',
name: '王小虎',
address: '上海市普陀区金沙江路 1519 弄'
}, {
date: '2016-05-03 10:10:10',
name: '王小虎',
address: '上海市普陀区金沙江路 1516 弄'
}],
exportExcel2 () {
const title = '表格标题'
var wb = XLSX.utils.table_to_book(document.querySelector('#outTable2'), { sheet: '表格sheet' }, { raw: false })
const sheet = wb
sheet.SheetNames.forEach(sheetName => {
const worksheetDataOld = sheet.Sheets[sheetName]
// 获取数组并插入一行标题
const jsonData = XLSX.utils.sheet_to_json(worksheetDataOld, { header: 1 })
jsonData.unshift([[title]])
// ！！！！属性丢失，暂时不处理
const worksheetData = XLSX.utils.aoa_to_sheet(jsonData)
// 数据回写
sheet.Sheets[sheetName] = worksheetData
// 增减插入标题的样式
worksheetData.A1 = {
...worksheetData.A1,
s: {
font: {
bold: true,
color: {
rgb: '000000'
},
name: '微软雅黑',
sz: 12
},
alignment: {
// 居中
horizontal: 'center',
vertical: 'center'
}
}
}
const range = XLSX.utils.decode_range(worksheetData['!ref'])
// 合并标题单元格：从--0行0列开始,到0行最后列
worksheetData['!merges'] = [ { s: { r: 0, c: 0 }, e: { r: 0, c: range.e.c } } ]
// 丢失属性添加
worksheetData['!cols'] = []
for (let row = range.s.r + 1; row <= range.e.r; row++) {
for (let col = range.s.c; col <= range.e.c; col++) {
// console.log({ r: row, c: col })
const cellAddress = XLSX.utils.encode_cell({ r: row, c: col })
// console.log('cellAddress', cellAddress)
// 设置单元格宽度
worksheetData['!cols'][col] = {
wpx: 150
}
const cellStyle = {
font: {
bold: false,
color: {
rgb: '000000'
},
name: '微软雅黑',
sz: 12
},
alignment: {
horizontal: 'center',
vertical: 'center'
}
}
if (row === 1) {
// 设置表头的样式
worksheetData[cellAddress].s = {
fill: {
fgColor: {
rgb: 'EBF0FE'
}
},
font: {
bold: false,
name: '微软雅黑',
sz: 12
},
border: {
top: {
style: 'thin',
color: {
auto: 1
}
},
left: {
style: 'thin',
color: {
auto: 1
}
},
right: {
style: 'thin',
color: {
auto: 1
}
},
bottom: {
style: 'thin',
color: {
auto: 1
}
}
},
alignment: {
horizontal: 'center',
vertical: 'center'
}
}
} else {
// 设置其他单元格样式
worksheetData[cellAddress].s = cellStyle
if (col === 0) {
// 第一列为时间：格式指定
worksheetData[cellAddress].z = 'yyyy-mm-dd hh:mm:ss'
}
}
}
}
})
/* get binary string as output */
var wbout = XLSXS.write(sheet, { bookType: 'xlsx', bookSST: false, type: 'binary' })
try {
FileSaver.saveAs(
new Blob([this.s2ab(wbout)], {
type: 'application/octet-stream'
}),
title + '.xlsx'
)
} catch (e) { if (typeof console !== 'undefined') console.log(e, wbout) }
return wbout
},
