1 . 安装 npm i excxljs
2. 安装 npm i file-saver
3. 封装函数
import * as ExcelJS from 'exceljs'
import fileSaver from 'file-saver'
/**
*
* @param {*} json 表格数据，对象数组
* @param {*} tHead 表头，数组，定义表头和对应的键,eg:{header:'年龄',key:'age'}
* @param {*} fileName 导出文件的文件名
* @param {*} sheetName 导出文件的表名
*/
export const exportByExcelJs = (json, tHead, fileName = '导出文件', sheetName = 'Sheet1') => {
//初始化一个工作表，填充一些基础信息
let workbook = new ExcelJS.Workbook()
let worksheet = workbook.addWorksheet(sheetName)
//设置表头，并用addRows添加我们要导出的数据
worksheet.columns = tHead
worksheet.addRows(json)
//设置表头样式
let style = {
font: {
name: '宋体',
size: 11,
bold: true,
color: { argb: '#00000000' }
},
alignment: {
vertical: 'middle',
horizontal: 'center',
wrapText: false
},
border: {
top: {style: 'thin'},
bottom: {style: 'thin'},
left: {style: 'thin'},
right: {style: 'thin'}
},
fill: {
type: 'pattern',
pattern: 'solid',
fgColor: { argb: 'b2b2b200' }
// bgColor: { argb: 'FF0000FF' }
}
}
//遍历工作表，给需要添加样式的单元格添加相应的样式
for (let i = 1; i < worksheet._columns.length + 1; i++) {
let cellName = getColumnNameByIndex(i - 1) + 1
worksheet.getCell(cellName).style = style
let maxLen = 0
for (let k = 1; k < worksheet._rows.length + 1; k++) {
worksheet.getRow(k).height = 13.5 //行高
if (k > 1) worksheet.getRow(k).height = 102
worksheet.getRow(k).getCell(i).alignment = {
vertical: "middle",
horizontal: "center",
wrapText: true
}
worksheet.getRow(k).getCell(i).font = {
name: "Arial Unicode MS",
size: 10
}
worksheet.getRow(k).getCell(i).border = {
top: {style: 'thick'},
bottom: {style: 'thick'},
left: {style: 'thick'},
right: {style: 'thick'}
}
// 计算列自适应宽度，限制最宽为20（一个中文字符宽度为2，英文和数字宽度为1.5）
let cellValue = worksheet.getCell(getColumnNameByIndex(i - 1) + k).value
let strLen = 0
if (cellValue !== null && cellValue !== '') {
cellValue = cellValue.toString()
let chVal = cellValue.match(/[\u4e00-\u9fa5]/g)
? cellValue.match(/[\u4e00-\u9fa5]/g).join('')
: ''
// let engVal = cellValue.replace(/([^\u0000-\u00FF])/g, '') || ''
let engVal = ''
strLen += chVal.length * 2 + engVal.length * 1.5
if (strLen > maxLen) {
maxLen = strLen
}
}
}
maxLen > 20 ? (maxLen = 20) : null
// worksheet.getColumn(i).width = maxLen //列宽
}
// 导出
workbook.xlsx.writeBuffer().then(function(buffer) {
fileSaver(
new Blob([buffer], {
type: 'application/octet-stream'
}),
`${fileName}.xlsx`
)
})
}
//通过列数获取excel表头编号
const getColumnNameByIndex = i => {
let result = String.fromCharCode('A'.charCodeAt() + (i % 26))
while (i >= 26) {
i /= 26
i--
result = String.fromCharCode('A'.charCodeAt() + (i % 26)) + result
}
return result
}
4.页面中使用
<template>
<div>
<button @click="exportTableWithImages(toExcelData,headers)">导出</button>
</div>
</template>
5.内容数据
const toExcelData = ref( [
{
index: 1, // 序号
banner: "", // 图片
title: "vmagiccare丝绒抗静电梳气囊梳子气垫梳女士梳头蓬松头皮按摩", // 产品名称
goods_sku: "朱砂红\n咖啡色\n藏蓝色", // 产品规格
bottom_price: "9.90", // SKU最低价
max_price: "9.90", // SKU最高价
commission_ratio: "12.00", // 佣金比例
partner_url: "", // 佣金链接
delivery_place: "48小时内从广东省发货，包邮", // 发货地点
platform_product_id: "3646387021733308549", // 商品ID
store_store_creadit_goods: 97, // 商品体验分
store_store_creadit_service: 100, // 商家服务分
store_store_creadit_logistics: 94 // 物流体验分
}
])
6.表格头
const headers = ref( [
{ header: "序号", key: "index", width: 11 },
{ header: "图片", key: "banner", width: 18.5 },
{ header: "产品名称", key: "title", width: 34 },
{ header: "产品规格", key: "goods_sku", width: 50 },
{ header: "SKU最低价", key: "bottom_price", width: 33 },
{ header: "SKU最高价", key: "max_price", width: 33 },
{ header: "佣金比例", key: "commission_ratio", width: 33 },
{ header: "佣金链接", key: "partner_url", width: 40 },
{ header: "发货地点", key: "delivery_place", width: 33 },
{ header: "商品id", key: "platform_product_id", width: 33 },
{ header: "商品体验分", key: "store_store_creadit_goods", width: 33 },
{ header: "商家服务分", key: "store_store_creadit_service", width: 33 },
{ header: "物流体验分", key: "store_store_creadit_logistics", width: 33 }
])
7.导出
const exportTableWithImages = (data,datas) =>{
exportByExcelJs(data,datas)
}
8.导出表格图示
