使用 node xlsx 獲取 xlsx文件數據,以及導出excel數據
//獲取數據
<el-upload class="upload-demo" action="###" accept='.xlsx' :http-request="customUpload"> <el-button size="small" type="primary">點擊上傳</el-button> </el-upload> import xlsx from 'xlsx' async readAsDataBinaryString (file) { const render = new FileReader() if (!render) { return '' } return new Promise((resolve, reject) => { render.onload = (res) => { // 在onload 可以獲得上傳文件 resolve(res.target.result) } render.readAsBinaryString(file) }) }, customUpload (content) { (async () => { if (!content.file) return const file = content.file const binaryString = await this.readAsDataBinaryString(file) const workbook = xlsx.read(binaryString, { type: 'binary', codepage: 936 }) const sheetNames = workbook.SheetNames const sheet = workbook.Sheets[sheetNames[0]] const data = xlsx.utils.sheet_to_json(sheet) const filterData = data.slice(1, data.length) if (filterData.length === 0) return this.$message.error('文件為空!') _.each(filterData, async item => { const params = { menuPath: 'CASH_VALUE-CASH_VALUE', compareVersion: 0, data: item } await addVersion(params) await sleep(600) }) this.$message.success('導入成功!') })() }
// 導出
toHump (str) {
// 下劃線轉駝峰命名
return str.replace(/_(\w)/g, function (all, letter) {
return letter.toUpperCase()
})
},
handleDownload (row) {
const printData = [
{
comboCode: '方案編碼',
riskCode: '險種編碼',
dutyCode: '繳費責任編碼',
policyYear: '保單年度',
applicantAge: '投保人年齡',
applicantGender: '投保人性別',
age: '被保人年齡',
gender: '被保人性別',
collectPeriod: '交費期間',
insurePeriod: '保險期間',
modx: '繳別',
unitAmount: '單位保額',
unitPremium: '單位保費',
getYear: '年金起領時間',
getPeriod: '年金領取期限',
cashvalueRate: '現價費率'
}
]
const keys = Object.keys(row)
const pushkey = ['comboCode', 'riskCode', 'dutyCode', 'policyYear', 'applicantAge', 'applicantGender', 'age', 'gender', 'collectPeriod', 'collectPeriod', 'insurePeriod', 'modx', 'unitAmount', 'unitPremium', 'getYear', 'getPeriod', 'cashvalueRate']
const pushData = {}
_.each(keys, item => {
const k = this.toHump(item)
if (pushkey.indexOf(k) !== -1) {
pushData[k] = row[item]
}
})
printData.push(pushData)
// 創建一個新sheet
const newSheet = xlsx.utils.json_to_sheet(printData)
// 設置每列的列寬(可選),10代表10個字符,注意中文占2個字符
newSheet['!cols'] = [
{ wch: 15 },
{ wch: 15 },
{ wch: 15 },
{ wch: 15 },
{ wch: 15 },
{ wch: 15 },
{ wch: 15 },
{ wch: 15 },
{ wch: 15 },
{ wch: 15 },
{ wch: 15 },
{ wch: 15 },
{ wch: 15 },
{ wch: 15 },
{ wch: 15 },
{ wch: 15 }
]
// 新建book
const newBook = xlsx.utils.book_new()
// 將 sheet 添加到 book 中
xlsx.utils.book_append_sheet(newBook, newSheet, '現價表')
// 導出excel文件
xlsx.writeFile(newBook, '現價表.xlsx')
}
浙公網安備 33010602011771號