if (this.expectData.length===0)return
const handleExport =async () => {
// 表格合并需要添加一行合并表头
const header =this.columns.map((item)=>item.label)
let exportList=this.expectData.map((item)=>([item.row_num,item.material_code,item.material_name,item.spec,item.unit_name,item.prod_model,item.jd,item.supplier_name,item.buy_cycle,item.prod_process,item.kc_num,item.dj_num,item.vmi_num,item.total_stock,item.avail_days,item.status,item.data_type,item.day1,item.day2,item.day3,item.day4,item.day5,item.day6,item.day7,item.day8,item.day9,item.day10,item.day11,item.day12,item.day13,item.day14,item.day15]))
// 生成表格
const sheet1 = {
name:'LeavePay',
data: [header, ...exportList], // 使用处理好的mock数据
merges: [],
rowHeights: [{hpx:20 }, {hpx:20 }]
}
// 合并：第0列、第1列、第三列、第四列、第五列、第六列、第七列和第八列的相同值进行行合并
const mergedRows =new Map()
for (let i =1; i < sheet1.data.length; i++) {
const cellValue0 = sheet1.data[i][0]
const cellValue1 = sheet1.data[i][1]
const prevValue0 = sheet1.data[i -1][0]
const prevValue1 = sheet1.data[i -1][1]
if (
cellValue0 === prevValue0 &&
cellValue1 === prevValue1
) {
if (mergedRows.has(cellValue0)) {
// 更新合并的结束行索引
mergedRows.get(cellValue0).end = i
}else {
// 添加新的合并信息
mergedRows.set(cellValue0, {start: i -1, end: i })
}
}
}
// 添加行合并信息到mergesHeader
for (const [value, { start, end }]of mergedRows.entries()) {
sheet1.merges.push({s: { r: start, c:0 }, e: { r: end, c:0 } })
sheet1.merges.push({s: { r: start, c:1 }, e: { r: end, c:1 } })
sheet1.merges.push({s: { r: start, c:2 }, e: { r: end, c:2 } })
sheet1.merges.push({s: { r: start, c:3 }, e: { r: end, c:3 } })
sheet1.merges.push({s: { r: start, c:4 }, e: { r: end, c:4 } })
sheet1.merges.push({s: { r: start, c:5 }, e: { r: end, c:5 } })
sheet1.merges.push({s: { r: start, c:6 }, e: { r: end, c:6 } })
sheet1.merges.push({s: { r: start, c:7 }, e: { r: end, c:7 } })
sheet1.merges.push({s: { r: start, c:8 }, e: { r: end, c:8 } })
sheet1.merges.push({s: { r: start, c:9 }, e: { r: end, c:9 } })
sheet1.merges.push({s: { r: start, c:10 }, e: { r: end, c:10 } })
sheet1.merges.push({s: { r: start, c:11 }, e: { r: end, c:11 } })
sheet1.merges.push({s: { r: start, c:12 }, e: { r: end, c:12 } })
sheet1.merges.push({s: { r: start, c:13 }, e: { r: end, c:13 } })
sheet1.merges.push({s: { r: start, c:14 }, e: { r: end, c:14 } })
sheet1.merges.push({s: { r: start, c:15 }, e: { r: end, c:15 } })
}
// 合并表头
const mergesHeader = []
const sheetData = [sheet1]
// 导出
OutExcelSheet.exportSheetExcel(sheetData, mergesHeader, `物料齐套表.xlsx`)
}
handleExport()
import OutExcelSheet from "@/utils/exportExcel"
这个放在utils文件夹下
import XLSXfrom 'xlsx-js-style'
import FileSaverfrom 'file-saver'
export default {
// 三个参数：sheetData、mergesHeader 和文件名。
exportSheetExcel(sheetData, mergerArr, fileName ='karlaExport.xlsx') {
const wb = XLSX.utils.book_new()// 创建一个新工作簿
for (let i =0; i < sheetData.length; i++) {
const sheet = sheetData[i]
// 检查数据项是否存在
if (!sheet.data) {
continue // 如果数据项不存在，则跳过当前循环
}
const ws = XLSX.utils.aoa_to_sheet(sheet.data)// 将数据数组转换为工作表
// 设置合并单元格
ws['!merges'] = sheet.merges && sheet.merges.length >0 ? [...sheet.merges, ...(mergerArr || [])] : mergerArr;
// 设置列宽为自适应
if (sheet.data.length >0) {
ws['!cols'] = sheet.data[0].map((_, index) => ({wch:15 }))
}
// 设置行高
if (sheet.rowHeights && sheet.rowHeights.length >0) {
ws['!rows'] = sheet.rowHeights.map((height) => ({hpt: height, hpx: height }))
}
const borderAll = {
top: {style:'thin' },
bottom: {style:'thin' },
left: {style:'thin' },
right: {style:'thin' }
}
// 设置单元格样式
for (const keyin ws) {
if (ws.hasOwnProperty(key)) {
const cell = ws[key]
if (typeof cell ==='object') {
cell.s = {
border: borderAll,
alignment: {
horizontal:'center',
vertical:'center',
wrapText:true
},
font: {
sz:12,
color: {
rgb:'000000'
}
},
numFmt:'General',
fill: {
fgColor: {rgb:'FFFFFF' }
}
}
}
}
}
XLSX.utils.book_append_sheet(wb, ws, sheet.name)// 将工作表添加到工作簿并指定名称
}
const wbout = XLSX.write(wb, {bookType:'xlsx', type:'array' })// 将工作簿转换为数组
const file =new Blob([wbout], {type:'application/octet-stream' })// 创建Blob对象
FileSaver.saveAs(file, fileName)// 下载文件
},
// 二维数组中空的数据设置为0
emptyValues(array, defaultValue) {
for (let i =0; i < array.length; i++) {
for (let j =0; j < array[i].length; j++) {
if (array[i][j] ===null || array[i][j] ===undefined || array[i][j] ==='') {
array[i][j] = defaultValue
}
}
}
return array
},
// 生成excel列表数据
handleExcelTable(columnHeader, list) {
if (list.length ===0)return []
// 表头
const tableColumn =Object.keys([columnHeader][0])
// 表格生成的数据
const sheet = [tableColumn]
list.forEach((item) => {
const row = tableColumn.map((column) => item[column])
sheet.push(row)
})
// 表头匹配对应的中文
const firstRow = sheet[0].map((column) => columnHeader[column])
sheet[0] = firstRow
return sheet || []
}
}
