Web导出操作流程
1、首先先安装三个插件
npm i xlsx -save
npm i xlsx-style -save
npm i file-saver -save2、先写一个js文件，把刚刚下的三个包导入
- 我这里会把表格每一个单元格里的数据全角转成半角，来自适应每一列的单元格，只取每一列最长的
/* eslint-disable */
import { saveAs } from "file-saver";
import * as XLSX from 'xlsx'
import * as XLSXS from 'xlsx-style'
// 导出excel方法
const exportJsonToExcel = ({ header, data, footer, filename, info }) => {
let tempData = data.map(val => changeNull(val))
let tempFooter = footer.map(val => changeNull(val))
// 第一种是经过处理后的数据
let firstTitle = `${filename}`
let secondTitle = `导出人: ${info.name} 导出时间: ${info.date}`
let tableData = [[firstTitle], [secondTitle], header, ...tempData, ...tempFooter]
const wsa = XLSX.utils.aoa_to_sheet(tableData)
const wb = XLSX.utils.book_new()
XLSX.utils.book_append_sheet(wb, wsa, '数据表')
// 第二种是原生table的数据格式，需要dom获取表格数据
// let wsa = XLSX.utils.table_to_book(this.$refs.table, { sheet: '数据表' })
// 样式添加
xlsxAddStyle(wsa, header.length, header, firstTitle, secondTitle, tempData.length)
var wopts = {
bookType: 'xlsx', // 要生成的文件类型
bookSST: false, // 是否生成Shared String Table，官方解释是，如果开启生成速度会下降，但在低版本IOS设备上有更好的兼容性
type: 'binary',
}
var ws = XLSXS.write(wb, wopts)
try {
saveAs(new Blob([s2ab(ws)], { type: 'application/octet-stream' }), `${filename}.xlsx`)
} catch (e) {
if (typeof console !== 'undefined') console.log(e, ws)
}
// 字符串转ArrayBuffer
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
}
}
// 表格样式的设置
const xlsxAddStyle = (sheet, rowCLength, header, firstTitle, secondTitle, listLength) => {
const tableTopLine = 2 // 表格离上面 2 行的距离
const mergeArr = [] // 合并的单元格
const rowH = [] // 表格每列高度
const rowC = [] // 表格每列宽度
// 单元格外侧有框线
const borderAll = {
top: { style: 'thin' },
bottom: { style: 'thin' },
left: { style: 'thin' },
right: { style: 'thin' },
}
// 单元格外侧无框线
const noBorder = {
top: { style: '' },
bottom: { style: '' },
left: { style: '' },
right: { style: '' },
}
for (const key in sheet) {
if (Object.hasOwnProperty.call(sheet, key)) {
const element = sheet[key]
if (typeof element === 'object') {
const index = Number(letter(key)) - 1
rowH[index] = { hpx: index == 0 ? 40 : 20 }
element.s = {
alignment: {
horizontal: index == 0 ? 'center' : 'left', // 所有单元格水平居中
vertical: 'center', // 所有单元格垂直居中
},
font: {
name: '宋体',
sz: index == 0 ? 24 : 12,
italic: false,
underline: false,
bold: index == 0 ? true : false
},
border: borderAll,
fill: {
fgColor: { rgb: 'FFFFFFFF' },
},
}
// 指标值表格的样式
// if (key.indexOf('C') > -1) {
// element.s.alignment.horizontal = 'right'
// }
// 标题的样式
if (index === 2) {
element.s.font.bold = true
element.s.fill.fgColor = { rgb: 'CCCCCC' }
}
// 处理合并单元格数组 s开始 e结束 r行 c列
if (element.v === firstTitle) {
mergeArr.push({
s: { r: 0, c: 0 },
e: { r: 0, c: rowCLength - 1 },
})
} else if (element.v === secondTitle) {
mergeArr.push({
s: { r: 1, c: 0 },
e: { r: 1, c: rowCLength - 1 },
})
element.s.alignment.horizontal = 'right'
}
}
}
}
console.log(sheet);
// 遍历每一列
for (let i = 0; i < rowCLength; i++) {
if (i > tableTopLine) {
for (const key in sheet) {
if (Object.hasOwnProperty.call(sheet, key)) {
const element = sheet[key]
if (typeof element === 'object') {
// 把之前的'chl_nb'改回''
if (element.v == 'chl_nb') {
element.v = ''
}
}
}
}
}
}
// 整理数据，计算内容宽度，并设置列宽
let tempSheet = JSON.parse(JSON.stringify(sheet))
delete tempSheet.A1
delete tempSheet.A2
const newArray = Object.entries(tempSheet).map(([key, value]) => value.v && value.v.toString().replace(/[^\x00-\xff]/g, '**').length * 8 || 40)
console.log(newArray)
// const resultArray = Array.from({ length: newArray.length / (listLength + 2) }, (item, i) =>
// newArray.slice(i * (listLength + 2), (i + 1) * (listLength + 2))
// )
// rowCLength newArray.length
const tempArray = []
for (let i = 0; i < newArray.length; i += rowCLength) {
const subArray = newArray.slice(i, i + rowCLength)
tempArray.push(subArray)
}
const resultArray = tempArray[0].map((item, i) => tempArray.map(row => row[i] || 40))
console.log(resultArray, 'resultArray')
let rowWisthList = resultArray.map(v => Math.max(...v))
rowWisthList.forEach(v => {
rowC.push({ wpx: v })
})
console.log(rowC)
sheet['!cols'] = rowC
// 单元格的行宽
sheet['!rows'] = rowH
// 合并单元格
sheet['!merges'] = mergeArr
// // 冻结前两行
// sheet['!freeze'] = { xSplit: 0, ySplit: 2, top: 3000, activePane: 'bottomLeft', state: 'frozen' };
// 合并项添加边框
const sheetNew = addRangeBorder(sheet['!merges'], sheet);
return sheetNew
}
// 把空值转一下
const changeNull = (arr) => {
let deepArr = []
if (Array.isArray(deepArr)) {
deepArr = JSON.parse(JSON.stringify(arr))
for (let i = 0; i < deepArr.length; i++) {
if ((deepArr[i] == null || deepArr[i] == undefined || deepArr[i] == '') && deepArr[i] != 0) {
deepArr[i] = 'chl_nb'
}
}
}
return deepArr
}
// 去除字母方法，当列的数量超过25的时候会变成AA所以需要用正则匹配去掉所有的字母，剩下数字
const letter = (str) => {
let result
const reg = /[a-zA-Z]+/
while ((result = str.match(reg))) {
//判断str.match(reg)是否没有字母了
str = str.replace(result[0], '') //替换掉字母 result[0] 是 str.match(reg)匹配到的字母
}
return str
}
// 为合并项添加边框
const addRangeBorder = (range, ws) => {
let arr = ['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', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ'];
range.forEach(item => {
let startColNumber = Number(item.s.r), endColNumber = Number(item.e.r)
let startRowNumber = Number(item.s.c), endRowNumber = Number(item.e.c)
const test = ws[arr[startRowNumber] + (startColNumber + 1)]
for (let col = startColNumber; col <= endColNumber; col++) {
for (let row = startRowNumber; row <= endRowNumber; row++) {
ws[arr[row] + (col + 1)] = test
}
}
})
return ws
}
const formatJson = (filterVal, jsonData) => {
return jsonData.map(v => filterVal.map(j => v[j]))
}
export { formatJson, exportJsonToExcel }
3、在vue文件里使用
- 我这里使用的是 vxe-table
- 导入刚刚在js文件里的这两个方法 formatJson, exportJsonToExcel
- 页面显示的样子以及要处理的数据
- 在下面代码里的 exportExcel 方法我会处理数据，处理成上图这样要传的 header 和 date
<template>
<div>
<vxe-toolbar>
<template #buttons>
<vxe-button @click="allAlign = 'left'">居左</vxe-button>
<vxe-button @click="allAlign = 'center'">居中</vxe-button>
<vxe-button @click="allAlign = 'right'">居右</vxe-button>
<vxe-button @click="exportExcel">导出</vxe-button>
</template>
</vxe-toolbar>
<vxe-table ref="xTable" :align="allAlign" :data="tableData">
<vxe-column type="seq" title="No" width="60"></vxe-column>
<vxe-column field="name" title="姓名"></vxe-column>
<vxe-column field="sex" title="性别"></vxe-column>
<vxe-column field="age" title="年龄"></vxe-column>
</vxe-table>
</div>
</template>
<script>
import { formatJson, exportJsonToExcel } from '@/utils/Export2Excel.js'
export default {
name: 'WebExport',
data() {
return {
allAlign: null,
tableData: [
{
id: 10001,
name: 'Test1',
role: 'Develop',
sex: 'Man',
age: 28,
address: 'test abc'
},
{
id: 10002,
name: 'Test2',
role: 'Test',
sex: 'Women',
age: 22,
address: 'Guangzhou'
},
{
id: 10003,
name: 'Test3',
role: 'PM',
sex: 'Man',
age: 32,
address: 'Shanghai'
},
{
id: 10004,
name: 'Test4',
role: 'Designer',
sex: 'Women',
age: 24,
address: 'Shanghai'
}
]
}
},
methods: {
exportExcel: function () {
const $table = this.$refs.xTable
const tHeaderField = $table.getColumns().map((v) => v.field)
tHeaderField[0] = 'no'
const tHeader = $table.getColumns().map((v) => v.title)
const list = JSON.parse(JSON.stringify($table.getTableData().fullData))
if (list && list.length) {
list.forEach((v, i) => {
v.no = i + 1
})
}
const data = formatJson(tHeaderField, list)
const tFooter = $table.footerTableData
let info = {
name: 'admin',
date: new Date().toLocaleString()
}
exportJsonToExcel({
header: tHeader,
data,
footer: tFooter,
filename: `数据报表`,
info
})
}
},
created() {},
mounted() {}
}
</script>4、安装这样操作完之后会有一个报错，关于 cptable 的报错 Can‘t resolve ‘./cptable‘ in ‘xxx\node_modules_xlsx
- 需要在vue.config.js文件配置如下：
const { defineConfig } = require('@vue/cli-service')
const path = require('path')
module.exports = defineConfig({
transpileDependencies: true,
pluginOptions: {
electronBuilder: {
nodeIntegration: true
}
},
configureWebpack: {
resolve: {
fallback: { crypto: false, fs: false },
},
externals: [{
'./cptable': 'var cptable'
}]
}
})
5、以上代码在webpack3+vue2是没有问题的，但是3以上就需要安装另外一个插件了
- 这个问题我也找了很久最后还是发现一篇博客上有记录
【vue】关于前端vue使用xlsx插件提示“jszip not a constructor”问题：_jszip is not a constructor-CSDN博客
npm i node-polyfill-webpack-plugin -D- 最终vue.config.js文件配置如下：
const { defineConfig } = require('@vue/cli-service')
const NodePolyfillPlugin = require("node-polyfill-webpack-plugin")
const path = require('path')
module.exports = defineConfig({
transpileDependencies: true,
pluginOptions: {
electronBuilder: {
nodeIntegration: true
}
},
configureWebpack: {
resolve: {
fallback: { crypto: false, fs: false },
},
plugins: [
new NodePolyfillPlugin()
],
externals: [{
'./cptable': 'var cptable'
}]
}
})
6、导出最终效果:
9753
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
