前端将表格数据导入excel中,隐藏单元格,设置单元格格式
引入xlsx依赖
import xlsx from 'xlsx'
引入相关方法
import { downLoadExcelAs, strBuffer } from '@/validate.js'
处理表格
methods: {
downloadMfgExcel(){
const data = []
const sheet = 'Sheet1'
const excelName = 'excel名称'
const wb = { SheetNames: [sheet], Sheets: {}, Props: {}}
wb.Sheets[sheet] = xlsx.utils.json_to_sheet(data)
wb.Sheets[sheet]['!cols'] = []
wb.Sheets[sheet]['!cols'][0] = { hidden: true }
labelList.forEach((item, index) => {
if (item.property === 'q_number') {
wb.Sheets[sheet]['!cols'][index] = { wch: 30 }
} else if (item.property === 's_name') {
wb.Sheets[sheet]['!cols'][index] = { wch: 20 }
} else if (item.property === 'desc') {
wb.Sheets[sheet]['!cols'][index] = { wch: 30 }
} else {
wb.Sheets[sheet]['!cols'][index] = { wch: 15 }
}
})
for (let i = 2; i < data.length + 2; i++) {
wb.Sheets[sheet]['N' + i] = { t: 'n', f: 'L' + i + '*(1+' + 'P' + i + ')', z: '#,##0.00' }
wb.Sheets[sheet]['O' + i] = { t: 'n', f: 'M' + i + '+N' + i, z: '#,##0.00' }
wb.Sheets[sheet]['R' + i] = { t: 'n', v: '0', z: '0%' }
}
const wopts = {
bookType: 'xlsx',
bookSST: false,
type: 'binary'
}
downLoadExcelAs(new Blob([strBuffer(xlsx.write(wb, wopts))],
{
type: 'application/octet-stream'
}),
excelName + '.' + wopts.bookType)
}
}
js
export function downLoadExcelAs(obj, fileName) {
const tamp = document.createElement('a')
tamp.download = fileName || '下载'
tamp.href = URL.createObjectURL(obj)
tamp.click()
URL.revokeObjectURL(obj)
}
export function strBuffer(s) {
if (typeof ArrayBuffer !== 'undefined') {
const buf = new ArrayBuffer(s.length)
const view = new Uint8Array(buf)
for (let i = 0; i !== s.length; ++i) {
view[i] = s.charCodeAt(i) & 0xFF
}
return buf
} else {
const buf = new Array(s.length)
for (let i = 0; i !== s.length; ++i) {
buf[i] = s.charCodeAt(i) & 0xFF
}
return buf
}
}