整合營銷服務(wù)商
電腦端+手機(jī)端+微信端=數(shù)據(jù)同步管理
免費(fèi)咨詢熱線：
電腦端+手機(jī)端+微信端=數(shù)據(jù)同步管理
免費(fèi)咨詢熱線：
時(shí)候我們?cè)诰W(wǎng)頁上找到資料，需要把數(shù)據(jù)存入表格。利用Excel，很輕松的就可以將網(wǎng)頁上的數(shù)據(jù)導(dǎo)入表格中，不但節(jié)約了工作時(shí)間，而且提高了數(shù)據(jù)的準(zhǔn)確性。
比如我們需要最新行政區(qū)劃代碼。從國家統(tǒng)計(jì)局我們找到這些數(shù)據(jù)。
1.復(fù)制網(wǎng)頁的URL地址（即：http://www.stats.gov.cn/tjsj/tjbz/xzqhdm/201703/t20170310_1471429.html）。
2.我們?cè)贓xcel的數(shù)據(jù)選項(xiàng)卡，點(diǎn)擊 【自網(wǎng)站】
3.在彈出的對(duì)話框中，粘貼上網(wǎng)址，點(diǎn)擊【轉(zhuǎn)到】按鈕，就打開了網(wǎng)頁。
在這窗口中，我們可以看到黃色的小箭頭。點(diǎn)擊表格旁邊的黃色小箭頭，然后點(diǎn)擊【導(dǎo)入】。
有時(shí)候，我們需要的數(shù)據(jù)旁邊沒有小箭頭，則直接可以點(diǎn)擊導(dǎo)入。
4.彈出確認(rèn)對(duì)話框，我們點(diǎn)擊確認(rèn)。這里也可以進(jìn)行屬性設(shè)置。
5.點(diǎn)擊確定后，會(huì)顯示正在獲取數(shù)據(jù)。
6.稍等片刻，我們的數(shù)據(jù)就導(dǎo)入成功了。
網(wǎng)頁中的數(shù)據(jù)已成功導(dǎo)入Excel表格中，然后我們可以根據(jù)自己的需要對(duì)數(shù)據(jù)進(jìn)行處理。
最近在開發(fā)項(xiàng)目時(shí)需要批量導(dǎo)入和導(dǎo)出Excel數(shù)據(jù)，在實(shí)現(xiàn)這個(gè)需求時(shí)，我們既可以在前端完成數(shù)據(jù)解析和文件生成工作，也可以通過前端發(fā)起導(dǎo)入以及導(dǎo)出請(qǐng)求后，后端實(shí)現(xiàn)解析文件流解析文件內(nèi)容以及生成文件并提供下載鏈接的功能。
相較于后端處理Excel數(shù)據(jù)而言，使用前端導(dǎo)入導(dǎo)出可以提供更高的實(shí)時(shí)性，用戶可以直接在瀏覽器中觸發(fā)導(dǎo)出操作，無需等待后端處理。且可以在前端完成數(shù)據(jù)生成以及數(shù)據(jù)校驗(yàn)處理工作，大大減輕后端服務(wù)器的壓力，大幅增強(qiáng)用戶體驗(yàn)。
具體的技術(shù)方案選型主要看業(yè)務(wù)場景，如果對(duì)于小型數(shù)據(jù)集、實(shí)時(shí)性需求較高的導(dǎo)入導(dǎo)出操作，優(yōu)先考慮前端實(shí)現(xiàn)。而對(duì)于大型數(shù)據(jù)集、需要業(yè)務(wù)邏輯處理、以及安全性要求高的場景，則后端處理更為合適。
xlsx與xlsx-style組合方案：xlsx 是目前前端最常用的Excel解決方案，又叫做SheetJS，但社區(qū)版不支持修改Excel的樣式，需要購買Pro版才可以，如果需要修改導(dǎo)出的Excel文件樣式，需要結(jié)合xlsx-style庫一起使用。但遺憾的是xlsx庫已經(jīng)兩年多不更新，而xlsx-style上一個(gè)版本更是8年前發(fā)布，目前已經(jīng)不再推薦使用該方案。
exceljs與file-saver方案：exceljs是一款免費(fèi)開源支持導(dǎo)入導(dǎo)出Excel 操作工具，并且可以實(shí)現(xiàn)樣式的修改以及 Excel 的高級(jí)功能，是非常值得推薦的一個(gè)處理 Excel 的庫，file-saver可以實(shí)現(xiàn)保存文件到本地。本文以exceljs與file-saver操作xlsx格式文件為例介紹如何具體上手使用。
ExcelJS是一個(gè)用于在Node.js和瀏覽器中創(chuàng)建、讀取和修改Excel文件的強(qiáng)大JavaScript庫。它提供了豐富的功能和靈活的API，使你能夠在你的應(yīng)用程序中處理和操作Excel文件。
下面是一些ExcelJS庫的關(guān)鍵特性和功能：
npm倉庫地址：https://www.npmjs.com/package/exceljs
官方中文文檔地址：https://github.com/exceljs/exceljs/blob/HEAD/README_zh.md
exceljs用于Excel數(shù)據(jù)處理，file-sever用于保存到本地文件。
npm i exceljs
npm i file-saver讓我們先從簡單的數(shù)據(jù)導(dǎo)出開始，快速體驗(yàn)如何使用exceljs導(dǎo)出Excel文件，需要注意的是在瀏覽器環(huán)境中運(yùn)行 JavaScript，瀏覽器的安全策略通常不允許直接訪問讀寫本地文件系統(tǒng)。在這種情況下，需要通過其他方式將文件轉(zhuǎn)換為buffer數(shù)據(jù)，在導(dǎo)出Excel時(shí)使用FileSaver.js庫將緩沖區(qū)數(shù)據(jù)保存到文件中。
<template>
<el-button type="primary" @click="exportExcel">導(dǎo)出excel</el-button>
</template>
<script setup>
import ExcelJS from "exceljs";
import FileSaver from "file-saver";
// 導(dǎo)出excel文件
const exportExcel=()=> {
// 創(chuàng)建工作簿
const workbook=new ExcelJS.Workbook();
// 添加工作表，名為sheet1
const sheet1=workbook.addWorksheet("sheet1");
// 導(dǎo)出數(shù)據(jù)列表
const data=[
{"姓名": "張三", "年齡": 18, "身高": 175, "體重": 74},
{"姓名": "李四", "年齡": 22, "身高": 177, "體重": 84},
{"姓名": "王五", "年齡": 53, "身高": 155, "體重": 64}
]
// 獲取表頭所有鍵
const headers=Object.keys(data[0])
// 將標(biāo)題寫入第一行
sheet1.addRow(headers);
// 將數(shù)據(jù)寫入工作表
data.forEach((row)=> {
const values=Object.values(row)
sheet1.addRow(values);
});
// 導(dǎo)出表格文件
workbook.xlsx.writeBuffer().then((buffer)=> {
let file=new Blob([buffer], {type: "application/octet-stream"});
FileSaver.saveAs(file, "ExcelJS.xlsx");
}).catch(error=> console.log('Error writing excel export', error))
}
</script>
<style scoped lang="scss">
</style>當(dāng)我們點(diǎn)擊導(dǎo)出excel按鈕時(shí)，調(diào)用exportFile函數(shù)，完成excel文件下載，下載后的文件內(nèi)容如下：
導(dǎo)入excel文件時(shí)，同樣使用FileReader的readAsArrayBuffer方法，將文件轉(zhuǎn)換為二進(jìn)制字符串，然后從buffer中加載數(shù)據(jù)并解析。
<template>
<input
type="file"
accept=".xls,.xlsx"
class="upload-file"
@change="importExcel($event)"/>
</template>
<script setup>
import ExcelJS from "exceljs";
// 導(dǎo)出excel文件
const importExcel=(event)=> {
//獲取選擇的文件
const files=event.target.files
//創(chuàng)建Workbook實(shí)例
const workbook=new ExcelJS.Workbook();
// 使用FileReader對(duì)象來讀取文件內(nèi)容
const fileReader=new FileReader()
// 二進(jìn)制字符串的形式加載文件
fileReader.readAsArrayBuffer(files[0])
fileReader.onload=ev=> {
console.log(ev)
// 從 buffer中加載數(shù)據(jù)解析
workbook.xlsx.load(ev.target.result).then(workbook=> {
// 獲取第一個(gè)worksheet內(nèi)容
const worksheet=workbook.getWorksheet(1);
// 獲取第一行的標(biāo)題
const headers=[];
worksheet.getRow(1).eachCell((cell)=> {
headers.push(cell.value);
});
console.log("headers", headers)
// 創(chuàng)建一個(gè)空的JavaScript對(duì)象數(shù)組，用于存儲(chǔ)解析后的數(shù)據(jù)
const data=[];
// 遍歷工作表的每一行（從第二行開始，因?yàn)榈谝恍型ǔＪ菢?biāo)題行）
for (let rowNumber=2; rowNumber <=worksheet.rowCount; rowNumber++) {
const rowData={};
const row=worksheet.getRow(rowNumber);
// 遍歷當(dāng)前行的每個(gè)單元格
row.eachCell((cell, colNumber)=> {
// 獲取標(biāo)題對(duì)應(yīng)的鍵，并將當(dāng)前單元格的值存儲(chǔ)到相應(yīng)的屬性名中
rowData[headers[colNumber - 1]]=cell.value;
});
// 將當(dāng)前行的數(shù)據(jù)對(duì)象添加到數(shù)組中
data.push(rowData);
}
console.log("data", data)
})
}
}
</script>
<style scoped lang="scss">
</style>上傳文件后，解析內(nèi)容如下所示：
我們可以通過columns方法添加列標(biāo)題并定義列鍵和寬度，設(shè)置好表頭后，我們可以直接通過addRow方法，根據(jù)key值去添加每一行的數(shù)據(jù)。
參考文檔：https://github.com/exceljs/exceljs/blob/HEAD/README_zh.md#%E5%88%97
完整代碼如下：
<template>
<el-button type="primary" @click="exportExcel">導(dǎo)出excel</el-button>
</template>
<script setup>
import ExcelJS from "exceljs";
import FileSaver from "file-saver";
// 導(dǎo)出excel文件
const exportExcel=()=> {
// 創(chuàng)建工作簿
const workbook=new ExcelJS.Workbook();
// 添加工作表，名為sheet1
const sheet1=workbook.addWorksheet("sheet1");
// 添加表頭列數(shù)據(jù)
sheet1.columns=[
{header: "姓名", key: "name", width: 20},
{header: "年齡", key: "age", width: 10},
{header: "身高", key: "height", width: 10},
{header: "體重", key: "weight", width: 10},
];
// 添加內(nèi)容列數(shù)據(jù)
sheet1.addRow({sort: 1, name: "張三", age: 18, height: 175, weight: 74});
sheet1.addRow({sort: 2, name: "李四", age: 22, height: 177, weight: 88});
sheet1.addRow({sort: 3, name: "王五", age: 53, height: 155, weight: 62});
// 導(dǎo)出表格文件
workbook.xlsx.writeBuffer().then((buffer)=> {
let file=new Blob([buffer], {type: "application/octet-stream"});
FileSaver.saveAs(file, "ExcelJS.xlsx");
}).catch(error=> console.log('Error writing excel export', error))
}
</script>
<style scoped lang="scss">
</style>添加數(shù)據(jù)后導(dǎo)出文件效果如下：
我們可以使用getRow方法，傳入指定行參數(shù)讀取行數(shù)據(jù)。
使用getColumn方法，傳入鍵、字母、id參數(shù)讀取列數(shù)據(jù)。
使用eachCell方法可以遍歷每個(gè)單元格內(nèi)容。
參考文檔：https://github.com/exceljs/exceljs/blob/HEAD/README_zh.md#%E8%A1%8C
代碼如下：
<template>
<el-button type="primary" @click="exportExcel">導(dǎo)出excel</el-button>
</template>
<script setup>
import ExcelJS from "exceljs";
import FileSaver from "file-saver";
// 導(dǎo)出excel文件
const exportExcel=()=> {
// 創(chuàng)建工作簿
const workbook=new ExcelJS.Workbook();
// 添加工作表，名為sheet1
const sheet1=workbook.addWorksheet("sheet1");
// 添加表頭列數(shù)據(jù)
sheet1.columns=[
{header: "姓名", key: "name", width: 20},
{header: "年齡", key: "age", width: 10},
{header: "身高", key: "height", width: 10},
{header: "體重", key: "weight", width: 10},
];
// 添加內(nèi)容列數(shù)據(jù)
sheet1.addRow({sort: 1, name: "張三", age: 18, height: 175, weight: 74});
sheet1.addRow({sort: 2, name: "李四", age: 22, height: 177, weight: 88});
sheet1.addRow({sort: 3, name: "王五", age: 53, height: 155, weight: 62});
// 讀取行數(shù)據(jù)
sheet1.getRow(1).eachCell((cell, rowIdx)=> {
console.log("行數(shù)據(jù)", cell.value, rowIdx);
});
// 讀取列數(shù)據(jù),可以通過鍵(name)，字母(B)和基于id(1)的列號(hào)訪問單個(gè)列
sheet1.getColumn("name").eachCell((cell, rowIdx)=> {
console.log("列數(shù)據(jù)", cell.value, rowIdx);
});
}
</script>
<style scoped lang="scss">
</style>效果
在導(dǎo)出excel文件時(shí)，默認(rèn)沒有任何樣式的，為了美觀我們需要添加樣式，而exceljs支持修改表格樣式，具體內(nèi)容可參考文檔https://github.com/exceljs/exceljs/blob/HEAD/README_zh.md#%E6%A0%B7%E5%BC%8F
例如，我們需要設(shè)置所有單元格居中對(duì)齊，并添加邊框。并分別指定標(biāo)題行和內(nèi)容行字體大小、背景顏色、行高屬性，代碼如下：
<template>
<el-button type="primary" @click="exportExcel">導(dǎo)出excel</el-button>
</template>
<script setup>
import ExcelJS from "exceljs";
import FileSaver from "file-saver";
// 導(dǎo)出excel文件
const exportExcel=()=> {
// 創(chuàng)建工作簿
const workbook=new ExcelJS.Workbook();
// 添加工作表，名為sheet1
const sheet1=workbook.addWorksheet("sheet1");
// 導(dǎo)出數(shù)據(jù)列表
const data=[
{"姓名": "張三", "年齡": 18, "身高": 175, "體重": 74},
{"姓名": "李四", "年齡": 22, "身高": 177, "體重": 84},
{"姓名": "王五", "年齡": 53, "身高": 155, "體重": 64}
]
// 獲取表頭所有鍵
const headers=Object.keys(data[0])
// 將標(biāo)題寫入第一行
sheet1.addRow(headers);
// 將數(shù)據(jù)寫入工作表
data.forEach((row)=> {
const values=Object.values(row)
sheet1.addRow(values);
});
// 修改所有單元格樣式
// 遍歷每一行
sheet1.eachRow((row, rowNumber)=> {
// 遍歷每個(gè)單元格
row.eachCell((cell)=> {
// 設(shè)置邊框樣式
cell.border={
top: {style: 'thin'},
left: {style: 'thin'},
bottom: {style: 'thin'},
right: {style: 'thin'}
};
// 設(shè)置居中對(duì)齊
cell.alignment={
vertical: 'middle',
horizontal: 'center'
};
});
});
// 獲取標(biāo)題行數(shù)據(jù)
const titleCell=sheet1.getRow(1);
// 設(shè)置行高為30
titleCell.height=30
// 設(shè)置標(biāo)題行單元格樣式
titleCell.eachCell((cell)=> {
// 設(shè)置標(biāo)題行背景顏色為黃色
cell.fill={
type: 'pattern',
pattern: 'solid',
fgColor: {argb: 'FFFF00'}
};
// 設(shè)置標(biāo)題行字體
cell.font={
color: {argb: 'FF0000'}, //顏色為紅色
bold: true,// 字體粗體
size: 18 // 設(shè)置字體大小為18
};
})
// 獲取第二行到最后一行的內(nèi)容數(shù)據(jù)
const bodyRows=sheet1.getRows(2, sheet1.rowCount);
// 處理內(nèi)容行的數(shù)據(jù)
bodyRows.forEach((bodyRow)=> {
// 設(shè)置行高為20
bodyRow.height=20
bodyRow.eachCell((cell)=> {
cell.font={
size: 16 // 設(shè)置內(nèi)容行字體大小為16
};
});
});
// 導(dǎo)出表格文件
workbook.xlsx.writeBuffer().then((buffer)=> {
let file=new Blob([buffer], {type: "application/octet-stream"});
FileSaver.saveAs(file, "ExcelJS.xlsx");
}).catch(error=> console.log('Error writing excel export', error))
}
</script>
<style scoped lang="scss">
</style>導(dǎo)出Excel樣式效果如下所示，已經(jīng)成功按我們指定的樣式導(dǎo)出了文件：
在很多的時(shí)候我們需要對(duì)表格中每一列的數(shù)據(jù)進(jìn)行篩選，比如直接篩選姓名等列信息，我們可以通過 autoFilter 來添加篩選。參考文檔：https://github.com/exceljs/exceljs/blob/HEAD/README_zh.md#%E8%87%AA%E5%8A%A8%E7%AD%9B%E9%80%89%E5%99%A8
代碼如下：
<template>
<el-button type="primary" @click="exportExcel">導(dǎo)出excel</el-button>
</template>
<script setup>
import ExcelJS from "exceljs";
import FileSaver from "file-saver";
// 導(dǎo)出excel文件
const exportExcel=()=> {
// 創(chuàng)建工作簿
const workbook=new ExcelJS.Workbook();
// 添加工作表，名為sheet1
const sheet1=workbook.addWorksheet("sheet1");
// 導(dǎo)出數(shù)據(jù)列表
const data=[
{"姓名": "張三", "年齡": 18, "身高": 175, "體重": 74},
{"姓名": "李四", "年齡": 22, "身高": 177, "體重": 84},
{"姓名": "王五", "年齡": 53, "身高": 155, "體重": 64}
]
// 獲取表頭所有鍵
const headers=Object.keys(data[0])
// 將標(biāo)題寫入第一行
sheet1.addRow(headers);
// 將數(shù)據(jù)寫入工作表
data.forEach((row)=> {
const values=Object.values(row)
sheet1.addRow(values);
});
// 單列篩選
// sheet1.autoFilter="A1";
// 多個(gè)列篩選
sheet1.autoFilter="A1:C1";
// 導(dǎo)出表格文件
workbook.xlsx.writeBuffer().then((buffer)=> {
let file=new Blob([buffer], {type: "application/octet-stream"});
FileSaver.saveAs(file, "ExcelJS.xlsx");
}).catch(error=> console.log('Error writing excel export', error))
}
</script>
<style scoped lang="scss">
</style>導(dǎo)入后的效果如下，在姓名、年齡、身高列添加了篩選按鈕：
參考文檔：exceljs/README_zh.md at 5bed18b45e824f409b08456b59b87430ded023ab · exceljs/exceljs · GitHub
我們可以直接對(duì)表格中的數(shù)據(jù)進(jìn)行公式計(jì)算，比如 求和(SUM)，平均數(shù)(AVERAGE) 等。
例如我們需要計(jì)算平均值、最大值、指定公式時(shí)，代碼如下：
<template>
<el-button type="primary" @click="exportExcel">導(dǎo)出excel</el-button>
</template>
<script setup>
import ExcelJS from "exceljs";
import FileSaver from "file-saver";
// 導(dǎo)出excel文件
const exportExcel=()=> {
// 創(chuàng)建工作簿
const workbook=new ExcelJS.Workbook();
// 添加工作表，名為sheet1
const sheet1=workbook.addWorksheet("sheet1");
// 導(dǎo)出數(shù)據(jù)列表
const data=[
{"姓名": "張三", "年齡": 18, "身高": 1.75, "體重": 74},
{"姓名": "李四", "年齡": 22, "身高": 1.77, "體重": 84},
{"姓名": "王五", "年齡": 53, "身高": 1.55, "體重": 64}
]
// 獲取表頭所有鍵
const headers=Object.keys(data[0])
// 將標(biāo)題寫入第一行
sheet1.addRow(headers);
// 將數(shù)據(jù)寫入工作表
data.forEach((row)=> {
const values=Object.values(row)
sheet1.addRow(values);
});
// 添加單元格
sheet1.getCell("E1").value="BMI指數(shù)";
sheet1.getCell("F1").value="平均身高";
sheet1.getCell("G1").value="最大體重";
// 計(jì)算平均身高
sheet1.getCell("F2").value={formula: "=AVERAGE(C2:C4)"};
// 計(jì)算最大體重
sheet1.getCell("G2").value={formula: "=MAX(D2:D4)"};
// 計(jì)算BMI指數(shù)
// 獲取第5列對(duì)象
const BMIRange=sheet1.getColumn(5)
BMIRange.eachCell((cell)=> {
console.log("cell", cell)
console.log(cell.row)
// 從第二列開始添加計(jì)算公式
if (cell.row >=2) {
sheet1.getCell("E" + cell.row).value={formula: "D" + cell.row + "/" + "(C" + cell.row + "*" + "C" + cell.row + ")"};
}
})
// 導(dǎo)出表格文件
workbook.xlsx.writeBuffer().then((buffer)=> {
let file=new Blob([buffer], {type: "application/octet-stream"});
FileSaver.saveAs(file, "ExcelJS.xlsx");
}).catch(error=> console.log('Error writing excel export', error))
}
</script>
<style scoped lang="scss">
</style>導(dǎo)出Excel文件效果如下，E列已經(jīng)自動(dòng)替換為公式計(jì)算。
表格的合并應(yīng)該是業(yè)務(wù)需求中最頻繁的功能。當(dāng)然這一功能使用 xlsx 也可以實(shí)現(xiàn)，我們只需要使用mergeCells方法，傳入合并單元格范圍參數(shù)即可。
參考文檔：https://github.com/exceljs/exceljs/blob/HEAD/README_zh.md#%E5%90%88%E5%B9%B6%E5%8D%95%E5%85%83%E6%A0%BC
具體代碼實(shí)現(xiàn)如下所示：
<template>
<el-button type="primary" @click="exportExcel">導(dǎo)出excel</el-button>
</template>
<script setup>
import ExcelJS from "exceljs";
import FileSaver from "file-saver";
// 導(dǎo)出excel文件
const exportExcel=()=> {
// 創(chuàng)建工作簿
const workbook=new ExcelJS.Workbook();
// 添加工作表，名為sheet1
const sheet1=workbook.addWorksheet("sheet1");
// 導(dǎo)出數(shù)據(jù)列表
const data=[
{"姓名": "張三", "年齡": 18, "身高": 175, "體重": 74},
{"姓名": "李四", "年齡": 18, "身高": '未知', "體重": '未知'},
{"姓名": "王五", "年齡": 53, "身高": '未知', "體重": '未知'},
{"姓名": "趙六", "年齡": 12, "身高": '未知', "體重": '未知'}
]
// 獲取表頭所有鍵
const headers=Object.keys(data[0])
// 將標(biāo)題寫入第一行
sheet1.addRow(headers);
// 將數(shù)據(jù)寫入工作表
data.forEach((row)=> {
const values=Object.values(row)
sheet1.addRow(values);
});
// 上下合并單元格
sheet1.mergeCells("B2:B3");
// 左右合并單元格
sheet1.mergeCells("C3:D3");
// 范圍合并單元格
sheet1.mergeCells("C4:D5");
// 導(dǎo)出表格文件
workbook.xlsx.writeBuffer().then((buffer)=> {
let file=new Blob([buffer], {type: "application/octet-stream"});
FileSaver.saveAs(file, "ExcelJS.xlsx");
}).catch(error=> console.log('Error writing excel export', error))
}
</script>
<style scoped lang="scss">
</style>
單元格合并后導(dǎo)出文件效果如下：
有時(shí)候我們需要為某個(gè)單元格添加數(shù)據(jù)可以方便直接下拉選擇指定的值，此時(shí)就需要使用數(shù)據(jù)驗(yàn)證功能，傳入可填寫的選項(xiàng)列表。
參考文檔：https://github.com/exceljs/exceljs/blob/HEAD/README_zh.md#%E6%95%B0%E6%8D%AE%E9%AA%8C%E8%AF%81
例如我們對(duì)是否注冊(cè)列添加數(shù)據(jù)驗(yàn)證，可填值為"是、否、未知"，具體代碼如下：
<template>
<el-button type="primary" @click="exportExcel">導(dǎo)出excel</el-button>
</template>
<script setup>
import ExcelJS from "exceljs";
import FileSaver from "file-saver";
// 導(dǎo)出excel文件
const exportExcel=()=> {
// 創(chuàng)建工作簿
const workbook=new ExcelJS.Workbook();
// 添加工作表，名為sheet1
const sheet1=workbook.addWorksheet("sheet1");
// 導(dǎo)出數(shù)據(jù)列表
const data=[
{"姓名": "張三", "年齡": 18, "身高": 1.75, "體重": 74, "是否注冊(cè)": ''},
{"姓名": "李四", "年齡": 22, "身高": 1.77, "體重": 84, "是否注冊(cè)": ''},
{"姓名": "王五", "年齡": 53, "身高": 1.55, "體重": 64, "是否注冊(cè)": ''}
]
// 獲取表頭所有鍵
const headers=Object.keys(data[0])
// 將標(biāo)題寫入第一行
sheet1.addRow(headers);
// 將數(shù)據(jù)寫入工作表
data.forEach((row)=> {
const values=Object.values(row)
sheet1.addRow(values);
});
// 獲取第5列對(duì)象
const VerificationRange=sheet1.getColumn(5)
VerificationRange.eachCell((cell)=> {
// 從第二列開始添加數(shù)據(jù)驗(yàn)證規(guī)則
if (cell.row >=2) {
sheet1.getCell("E" + cell.row).dataValidation={
type: "list",
allowBlank: true,
formulae: ['"是,否,未知"']
};
}
})
// 導(dǎo)出表格文件
workbook.xlsx.writeBuffer().then((buffer)=> {
let file=new Blob([buffer], {type: "application/octet-stream"});
FileSaver.saveAs(file, "ExcelJS.xlsx");
}).catch(error=> console.log('Error writing excel export', error))
}
</script>
<style scoped lang="scss">
</style>
導(dǎo)出的excel文件效果如下：
我們可以為指定單元格添加條件格式，對(duì)滿足條件的單元格設(shè)置指定的樣式。
參考文檔：exceljs/README_zh.md at 5bed18b45e824f409b08456b59b87430ded023ab · exceljs/exceljs · GitHub
例如為年齡大于18歲單元格進(jìn)行紅色標(biāo)注，代碼如下：
<template>
<el-button type="primary" @click="exportExcel">導(dǎo)出excel</el-button>
</template>
<script setup>
import ExcelJS from "exceljs";
import FileSaver from "file-saver";
// 導(dǎo)出excel文件
const exportExcel=()=> {
// 創(chuàng)建工作簿
const workbook=new ExcelJS.Workbook();
// 添加工作表，名為sheet1
const sheet1=workbook.addWorksheet("sheet1");
// 導(dǎo)出數(shù)據(jù)列表
const data=[
{"姓名": "張三", "年齡": 18, "身高": 1.75, "體重": 74},
{"姓名": "李四", "年齡": 22, "身高": 1.77, "體重": 84},
{"姓名": "王五", "年齡": 53, "身高": 1.55, "體重": 64}
]
// 獲取表頭所有鍵
const headers=Object.keys(data[0])
// 將標(biāo)題寫入第一行
sheet1.addRow(headers);
// 將數(shù)據(jù)寫入工作表
data.forEach((row)=> {
const values=Object.values(row)
sheet1.addRow(values);
});
// 年齡大于18歲紅色標(biāo)注
sheet1.addConditionalFormatting({
ref: "B2:B4",
rules: [
{
type: "cellIs",
operator: "greaterThan",
priority: 1,
formulae: [18],
style: {
fill: {
type: "pattern",
pattern: "solid",
bgColor: { argb: "FFFFC0CB" },
},
},
},
],
});
// 導(dǎo)出表格文件
workbook.xlsx.writeBuffer().then((buffer)=> {
let file=new Blob([buffer], {type: "application/octet-stream"});
FileSaver.saveAs(file, "ExcelJS.xlsx");
}).catch(error=> console.log('Error writing excel export', error))
}
</script>
<style scoped lang="scss">
</style>導(dǎo)出后的文件效果如下：
為了提高項(xiàng)目代碼的復(fù)用性，通常會(huì)將excel導(dǎo)入導(dǎo)出功能封裝到單獨(dú)的函數(shù)中方便調(diào)用，封裝后的函數(shù)如下：
import ExcelJS from "exceljs";
import FileSaver from "file-saver";
import {timeFile} from "@/utils/timeFormat";
// 導(dǎo)出excel文件
export function exportFile(export_data, filename) {
// 創(chuàng)建工作簿
const workbook=new ExcelJS.Workbook();
// 添加工作表，名為sheet1
const sheet1=workbook.addWorksheet("sheet1");
// 獲取表頭所有鍵
const headers=Object.keys(export_data[0])
// 將標(biāo)題寫入第一行
sheet1.addRow(headers);
// 將數(shù)據(jù)寫入工作表
export_data.forEach((row)=> {
const values=Object.values(row)
sheet1.addRow(values);
});
// 設(shè)置默認(rèn)寬高屬性
sheet1.properties.defaultColWidth=20
sheet1.properties.defaultRowHeight=20
// 修改所有單元格樣式
// 遍歷每一行
sheet1.eachRow((row, rowNumber)=> {
// 遍歷每個(gè)單元格
row.eachCell((cell)=> {
// 設(shè)置邊框樣式
cell.border={
top: {style: 'thin'},
left: {style: 'thin'},
bottom: {style: 'thin'},
right: {style: 'thin'}
};
// 設(shè)置居中對(duì)齊
cell.alignment={
vertical: 'middle',
horizontal: 'center'
};
});
});
// 獲取標(biāo)題行數(shù)據(jù)
const titleCell=sheet1.getRow(1);
// 設(shè)置標(biāo)題行單元格樣式
titleCell.eachCell((cell)=> {
// 設(shè)置標(biāo)題行背景顏色
cell.fill={
type: 'pattern',
pattern: 'solid',
fgColor: {argb: '3498db'}
};
// 設(shè)置標(biāo)題行字體
cell.font={
bold: true,// 字體粗體
};
})
// 導(dǎo)出表格文件
workbook.xlsx.writeBuffer().then((buffer)=> {
let file=new Blob([buffer], {type: "application/octet-stream"});
FileSaver.saveAs(file, filename + timeFile() + ".xlsx");
}).catch(error=> console.log('Error writing excel export', error))
}
// 導(dǎo)入excel文件
export function importFile(content) {
return new Promise((resolve, reject)=> {
// 創(chuàng)建一個(gè)空的JavaScript對(duì)象數(shù)組，用于存儲(chǔ)解析后的數(shù)據(jù)
const data=[];
//創(chuàng)建Workbook實(shí)例
const workbook=new ExcelJS.Workbook();
workbook.xlsx.load(content).then(workbook=> {
// 獲取第一個(gè)worksheet內(nèi)容
const worksheet=workbook.getWorksheet(1);
// 獲取第一行的標(biāo)題
const headers=[];
worksheet.getRow(1).eachCell((cell)=> {
headers.push(cell.value);
});
// console.log("headers", headers)
// 遍歷工作表的每一行（從第二行開始，因?yàn)榈谝恍型ǔＪ菢?biāo)題行）
for (let rowNumber=2; rowNumber <=worksheet.rowCount; rowNumber++) {
const rowData={};
const row=worksheet.getRow(rowNumber);
// 遍歷當(dāng)前行的每個(gè)單元格
row.eachCell((cell, colNumber)=> {
// 獲取標(biāo)題對(duì)應(yīng)的鍵，并將當(dāng)前單元格的值存儲(chǔ)到相應(yīng)的屬性名中
rowData[headers[colNumber - 1]]=cell.value;
});
// 將當(dāng)前行的數(shù)據(jù)對(duì)象添加到數(shù)組中
data.push(rowData);
}
// console.log("data", data)
resolve(data);
}).catch(error=> {
reject(error);
});
})
}以element plus為例，調(diào)用函數(shù)完成Excel文件導(dǎo)入與導(dǎo)出，代碼如下：
<template>
<el-button type="primary" @click="exportExcel">導(dǎo)出excel</el-button>
<el-button type="success" @click="importExcel">導(dǎo)入excel</el-button>
<p>導(dǎo)入數(shù)據(jù)預(yù)覽</p>
{{ uploadData}}
<el-dialog
v-model="uploadDialogVisible"
title="批量添加數(shù)據(jù)"
width="40%"
>
<el-form label-width="120px">
<el-form-item label="模板下載：">
<el-button type="info" @click="downloadTemplate">
<el-icon>
<Download/>
</el-icon>
點(diǎn)擊下載
</el-button>
</el-form-item>
<el-form-item label="文件上傳：">
<el-upload drag accept=".xls,.xlsx" :auto-upload="false" :on-change="handleChange">
<el-icon class="el-icon--upload">
<upload-filled/>
</el-icon>
<div class="el-upload__text">
將文件拖到此處，或<em>點(diǎn)擊上傳</em>
</div>
<template #tip>
<div class="el-upload__tip">
請(qǐng)上傳.xls,.xlsx格式文件，文件最大為500kb
</div>
</template>
</el-upload>
</el-form-item>
</el-form>
<template #footer>
<span class="dialog-footer">
<el-button @click="uploadDialogVisible=false">取消</el-button>
<el-button type="primary" @click="submitUpload">
導(dǎo)入
</el-button>
</span>
</template>
</el-dialog>
</template>
<script setup>
import {ref} from "vue";
import {ElMessage} from "element-plus";
import {Download, UploadFilled} from "@element-plus/icons-vue"
import {getDemo} from "@/api/home";
import {timeFormatConversion} from "@/utils/timeFormat";
import {exportFile, importFile} from "@/utils/excel";
// 表格字段配置
const fieldConfig=ref([
{
'label': 'ID', // 標(biāo)簽
'model': 'id',// 字段名
'is_export': true,// 是否導(dǎo)出該字段
},
{
'label': '用戶名', // 標(biāo)簽
'model': 'username',// 字段名
'is_export': true, // 是否導(dǎo)出該字段
},
{
'label': '省份', // 標(biāo)簽
'model': 'province',// 字段名
'is_export': true // 是否導(dǎo)出該字段
},
{
'label': '性別', // 標(biāo)簽
'model': 'sex_name',// 字段名
'is_export': true // 是否導(dǎo)出該字段
},
{
'label': '生日', // 標(biāo)簽
'model': 'birthday',// 字段名
'is_export': true, // 是否導(dǎo)出該字段
},
{
'label': '身高(cm)', // 標(biāo)簽
'model': 'height',// 字段名
'is_export': true, // 是否導(dǎo)出該字段
},
{
'label': '體重(kg)', // 標(biāo)簽
'model': 'weight',// 字段名
'is_export': true,// 是否導(dǎo)出該字段
},
{
'label': '注冊(cè)時(shí)間', // 標(biāo)簽
'model': 'created_time',// 字段名
'is_export': true, // 是否導(dǎo)出該字段
},
{
'label': '個(gè)人介紹', // 標(biāo)簽
'model': 'introduction',// 字段名
'is_export': true,// 是否導(dǎo)出該字段
}
])
// 導(dǎo)出Excel事件
const exportExcel=()=> {
ElMessage({
message: '開始導(dǎo)出數(shù)據(jù)，請(qǐng)稍候！',
type: 'success',
})
// 導(dǎo)出數(shù)據(jù)查詢參數(shù)
const printParams={
'size': 1000,
'page': 1,
}
// 獲取需要導(dǎo)出的字段配置
const export_fields=fieldConfig.value
.filter(obj=> obj['is_export'])
.map(({label, model})=> ({[model]: label}))
// 處理數(shù)據(jù)結(jié)構(gòu)
getDemo(printParams).then((response)=> {
// console.log(response.results)
const export_data=response.results.map(obj=> {
const newObj={};
export_fields.forEach(field=> {
const [key, value]=Object.entries(field)[0];
if (key==='created_time') {
newObj[value]=timeFormatConversion((obj[key]), 'YYYY-MM-DD HH:mm:ss');
} else {
newObj[value]=obj[key];
}
});
return newObj;
});
let filename='示例用戶'
exportFile(export_data, filename);
}).catch(response=> {
//發(fā)生錯(cuò)誤時(shí)執(zhí)行的代碼
console.log(response)
ElMessage.error('獲取列表數(shù)據(jù)失敗！')
});
}
// 導(dǎo)入excel彈窗是否顯示
const uploadDialogVisible=ref(false)
// 點(diǎn)擊導(dǎo)入excel按鈕事件
const importExcel=()=> {
uploadDialogVisible.value=true
}
// 下載模板文件
const downloadTemplate=()=> {
window.open('https://api.cuiliangblog.cn/static/demo-template.xlsx')
}
// 文件數(shù)據(jù)
const uploadData=ref([])
// 文件上傳事件
const handleChange=(file)=> {
const reader=new FileReader();
reader.onload=()=> {
const content=reader.result;
importFile(content).then((data)=> {
console.log(data)
uploadData.value=data
}).catch(response=> {
//發(fā)生錯(cuò)誤時(shí)執(zhí)行的代碼
console.log(response)
ElMessage.error('獲取列表數(shù)據(jù)失敗！')
});
};
reader.readAsBinaryString(file.raw);
};
// 點(diǎn)擊導(dǎo)入excel提交數(shù)據(jù)事件
const submitUpload=()=> {
uploadDialogVisible.value=false
}
</script>
<style scoped lang="scss">
</style>封裝后的頁面效果如下，至此，一個(gè)簡單的vue前端實(shí)現(xiàn)Excel文件導(dǎo)入導(dǎo)出功能便開發(fā)完成了。
gitee：https://gitee.com/cuiliang0302/vue3_vite_element-plus
github：https://github.com/cuiliang0302/vue3-vite-template
微信公眾號(hào)
微信公眾號(hào)同步更新，歡迎關(guān)注微信公眾號(hào)《崔亮的博客》第一時(shí)間獲取最近文章。
博客網(wǎng)站
崔亮的博客-專注devops自動(dòng)化運(yùn)維，傳播優(yōu)秀it運(yùn)維技術(shù)文章。更多原創(chuàng)運(yùn)維開發(fā)相關(guān)文章，歡迎訪問https://www.cuiliangblog.cn
年年底曾有外媒報(bào)道，由于社區(qū)用戶的強(qiáng)烈要求，微軟正考慮添加 Python 作為官方的一種 Excel 腳本語言。不過在昨天的 Build 2018 開發(fā)者大會(huì)上，微軟沒有透露有關(guān) Python 支持的消息，而是宣布 Excel 新增 JavaScript 支持，這意味著開發(fā)者現(xiàn)在可以在 Excel 中運(yùn)行本地的 JavaScript 自定義函數(shù)。
也就是說，開發(fā)者可以使用 JavaScript 代碼去自定義 Excel 公式，然后從 Excel 表格中插入并調(diào)用這些公式，并通過 JavaScript 解釋器計(jì)算表格數(shù)據(jù)，而不是使用 Excel 自己的計(jì)算引擎。
微軟表示，辦公開發(fā)人員一直希望能夠在 Excel 中編寫 JavaScript 自定義函數(shù)去進(jìn)行一些數(shù)學(xué)運(yùn)算、銀行賬戶數(shù)據(jù)以及股票數(shù)據(jù)等等，此舉將讓他們可以更加靈活地定制 Excel 。
不過該功能尚未正式發(fā)布，目前僅在 Windows 和 Mac 的 Excel 開發(fā)者預(yù)覽版，以及 Excel 在線版中提供，用戶需先加入 Office Insiders 并安裝自定義加載項(xiàng)方可進(jìn)行體驗(yàn)。
*請(qǐng)認(rèn)真填寫需求信息，我們會(huì)在24小時(shí)內(nèi)與您取得聯(lián)系。
