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: “朱砂红
咖啡色
藏蓝色”, // 产品规格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.导出表格图示
暂无评论内容