長沙網(wǎng)站建設公司排行榜百度信息流推廣技巧
通過lay-excel插件實現(xiàn)Excel導出，并實現(xiàn)單元格合并，樣式設置等功能。
更詳細描述，請去lay-excel插件文檔查看，地址：http://excel.wj2015.com/_book/docs/%E5%BF%AB%E9%80%9F%E4%B8%8A%E6%89%8B.html
一、安裝
這里使用Vue前端框架來演示Excel導出功能，可通過npm進行安裝，代碼如下：
$ npm i lay-excel二、相關(guān)函數(shù)
以下只是將這次演示demo中會使用到函數(shù)及相關(guān)參數(shù)進行列舉，如果需更深入了解或更復雜功能實現(xiàn)，可去lay-excel的插件文檔中查看。
2.1 exportExcel參數(shù)配置
用于將 data 數(shù)據(jù)依次導出，如果需要調(diào)整導出后的文件字段順序或者過濾多余數(shù)據(jù)，可使用filterExportData()來梳理。另個導出每行數(shù)據(jù)對應key數(shù)量必須一致，否則會出現(xiàn)錯位現(xiàn)象。
參數(shù)配置：
參數(shù)名稱 | 描述 | 默認值 |
data | 數(shù)據(jù)列表（需指定表名） | 必填 |
filename | 文件名稱（帶后綴） | 必填 |
type | 導出類型，支持 xlsx、csv、ods、xlsb、fods、biff2 | xlsx |
options | 其他可選配置 | null |
options支持參數(shù)：
參數(shù)名稱 | 描述 | 默認值 |
Props | 配置文檔基礎屬性，支持Title、Subject、Author、Manager、Company、Category、Keywords、Comments、LastAuthor、CreatedData | null |
extend | 表格配置參數(shù)，支持 !merge (合并單元格信息)、!cols(行數(shù))、!protect(寫保護)等，原生配置請參考，其中 !merge 配置支持輔助方法生成，詳見 makeMergeConfig(origin)！ | null |
compression | 是否壓縮，壓縮效果比較明顯，建議開啟（9列9w行有較多重復的數(shù)據(jù)，從 30M 縮小到 3M） | true |
writeOpt | 寫入函數(shù)的可選項覆蓋，一般用默認生成的配置即可，具體請查看 Writing Options | undefined |
2.2 filterExportData參數(shù)配置
輔助方法，梳理導出的數(shù)據(jù)，包括字段排序和多余數(shù)據(jù)過濾。
參數(shù)名稱 | 描述 | 默認值 |
data | 需要梳理的數(shù)據(jù) | 必填 |
fields | 支持數(shù)組、對象和回調(diào)函數(shù)，用于映射關(guān)系和字段排序 | 必填 |
單元格屬性含義：
鍵名稱 | 描述 |
v | 單元格的值 |
w | 格式化文本（如果適用） |
t | 單元格類型: b 布爾值, n 數(shù)字, e 錯誤, s 字符, d 日期 |
f | 單元格公式（如果適用） |
r | 富文本編碼（如果適用） |
h | 富文本的HTML呈現(xiàn)（如果適用） |
c | 與單元格相關(guān)的注釋 |
z | 與單元格關(guān)聯(lián)的數(shù)字格式字符串（如果需要） |
l | 單元格超鏈接對象（目標鏈接，.tooltip是提示） |
s | 單元格的樣式/主題（如果適用） |
樣式設置對應參數(shù)，可去lay-excel插件文檔中查看，地址：http://excel.wj2015.com/_book/docs/%E6%A0%B7%E5%BC%8F%E8%AE%BE%E7%BD%AE%E4%B8%93%E5%8C%BA.html
2.3 makeColConfig參數(shù)配置
輔助方法：用于生成合并表格的配置項，注意需要傳入到 exportExcel 的 opt.extend['!merge'] 中。
參數(shù)名稱 | 描述 | 默認值 |
origin | 二維數(shù)組 | undefined |
2.4 makeMergeConfig參數(shù)配置
輔助方法：生成列寬配置，返回結(jié)果需放置于opt.extend['!cols']中。
參數(shù)名稱 | 描述 | 默認值 |
data | 一個對象，對象的key代表列（如：ABCDE），value代表寬度 | null |
defaultNum | 渲染過程中未指定單元格的默認寬度 | 60 |
三、Demo實現(xiàn)
通過lay-excel來實現(xiàn)如下圖格局，是非常方便的，只需要傳入相應參數(shù)即可。
3.1 創(chuàng)建模板頁
首先，在src中創(chuàng)建永動默認首頁，在頁面中添加導出按鈕，代碼如下：
<template><div class="main-box"><el-button size="small" type="primary" @click="exportEvent">導出表格</el-button></div>
</template><script>export default {data(){return {}},methods: {//導出事件exportEvent(){}}}
</script>頁面效果如下：
3.2 繪制表格框架
如上圖示例，將合并單元格二維數(shù)據(jù)，放到makeMergeConfig函數(shù)中即可，這里合并單元格是從左上角往右邊或下面 進行合并的，代碼如下：
exportEvent(){//合并參數(shù)let mergeConf = LAY_EXCEL.makeMergeConfig([['A1', 'I1'],['A2', 'A3'],['F2', 'F3'],['G2', 'G3'],['B2', 'E2'],['H2', 'I2']]);//導出數(shù)據(jù)LAY_EXCEL.exportExcel([], '導出測試.xlsx', 'xlsx', {extend: {'!merges': mergeConf,}});
}此時點擊導出，則可以導出合并后的表格框架。
3.3 導出數(shù)據(jù)
這里先導出數(shù)據(jù)，再實現(xiàn)標題數(shù)據(jù)導出，先將表格對應列和數(shù)據(jù)中字段關(guān)聯(lián)上。代碼如下：
exportEvent(){var data = [{name: "李四", age: 72, gender: 1, high: 164, weight: 80, mobile: 15627070182, email: "test15627070182@qq.com", province: "湖北省", city: "長沙市"},{name: "張三", age: 60, gender: 1, high: 161, weight: 75, mobile: 15621397301, email: "test15621397301@qq.com", province: "浙江省", city: "臺州市"},{name: "王五", age: 82, gender: 1, high: 170, weight: 72, mobile: 15684572039, email: "test15684572039@qq.com", province: "安徽省", city: "六安市"},{name: "趙六", age: 56, gender: 2, high: 166, weight: 50, mobile: 15694331283, email: "test15694331283@qq.com", province: "江蘇省", city: "蘇州市"}], exportData = [];//先將標題前三行空出來exportData.push.apply(exportData, [{}, {}, {}]);//追加員工數(shù)據(jù)exportData.push.apply(exportData, data);//合并參數(shù)let mergeConf = LAY_EXCEL.makeMergeConfig([['A1', 'I1'],['A2', 'A3'],['F2', 'F3'],['G2', 'G3'],['B2', 'E2'],['H2', 'I2']]);//導出數(shù)據(jù)LAY_EXCEL.exportExcel(exportData, '導出測試.xlsx', 'xlsx', {extend: {'!merges': mergeConf,}});
}此時點擊導出，則可看到每列對應數(shù)據(jù)了，如下圖：
3.4 添加樣式
咱們先定義commonStyle公共樣式屬性，再定義getCellStyle()函數(shù)，用來返回所有對應字段的樣式。然后通過lay-excel的filterExportData()輔助函數(shù)，來梳理導出的數(shù)據(jù)，包括字段排序和多余數(shù)據(jù)過濾。代碼如下：
exportEvent(){var data = [{name: "李四", age: 72, gender: 1, high: 164, weight: 80, mobile: 15627070182, email: "test15627070182@qq.com", province: "湖北省", city: "長沙市"},{name: "張三", age: 60, gender: 1, high: 161, weight: 75, mobile: 15621397301, email: "test15621397301@qq.com", province: "浙江省", city: "臺州市"},{name: "王五", age: 82, gender: 1, high: 170, weight: 72, mobile: 15684572039, email: "test15684572039@qq.com", province: "安徽省", city: "六安市"},{name: "趙六", age: 56, gender: 2, high: 166, weight: 50, mobile: 15694331283, email: "test15694331283@qq.com", province: "江蘇省", city: "蘇州市"}], //公共樣式commonStyle = {font: { sz: 12 },alignment: {vertical: "center",horizontal: "center"},border: {top: {style: 'thin', color: {rgb: '666666'}},bottom: {style: 'thin', color: {rgb: '666666'}},left: {style: 'thin', color: {rgb: '666666'}},right: {style: 'thin', color: {rgb: '666666'}}}},exportData = [];function getCellStyle(styleParam){let tmpData = {};['name', 'age', 'gender', 'high', 'weight', 'mobile', 'email', 'province', 'city'].forEach(item => {tmpData[item] = function(value, line, data){return {v: value,s: {...commonStyle}}}});return tmpData;}//梳理數(shù)據(jù)let dataList = LAY_EXCEL.filterExportData(data, getCellStyle());//先將標題前三行空出來exportData.push.apply(exportData, [{}, {}, {}]);//追加員工數(shù)據(jù)exportData.push.apply(exportData, dataList);//合并參數(shù)let mergeConf = LAY_EXCEL.makeMergeConfig([['A1', 'I1'],['A2', 'A3'],['F2', 'F3'],['G2', 'G3'],['B2', 'E2'],['H2', 'I2']]);//導出數(shù)據(jù)LAY_EXCEL.exportExcel(exportData, '導出測試.xlsx', 'xlsx', {extend: {'!merges': mergeConf,}});
}此時點擊導出，則可看到填充數(shù)據(jù)位置字體居中，并有線框，如下圖：
如上圖會發(fā)現(xiàn)，手機號和郵箱地址的寬度不夠，這個簡單，使用lay-excel的makeColConfig()輔助函數(shù)來調(diào)整列寬即可。代碼如下：
exportEvent(){var data = [{name: "李四", age: 72, gender: 1, high: 164, weight: 80, mobile: 15627070182, email: "test15627070182@qq.com", province: "湖北省", city: "長沙市"},{name: "張三", age: 60, gender: 1, high: 161, weight: 75, mobile: 15621397301, email: "test15621397301@qq.com", province: "浙江省", city: "臺州市"},{name: "王五", age: 82, gender: 1, high: 170, weight: 72, mobile: 15684572039, email: "test15684572039@qq.com", province: "安徽省", city: "六安市"},{name: "趙六", age: 56, gender: 2, high: 166, weight: 50, mobile: 15694331283, email: "test15694331283@qq.com", province: "江蘇省", city: "蘇州市"}], //公共樣式commonStyle = {font: { sz: 12 },alignment: {vertical: "center",horizontal: "center"},border: {top: {style: 'thin', color: {rgb: '666666'}},bottom: {style: 'thin', color: {rgb: '666666'}},left: {style: 'thin', color: {rgb: '666666'}},right: {style: 'thin', color: {rgb: '666666'}}}},exportData = [];function getCellStyle(styleParam){let tmpData = {};['name', 'age', 'gender', 'high', 'weight', 'mobile', 'email', 'province', 'city'].forEach(item => {tmpData[item] = function(value, line, data){return {v: value,s: {...commonStyle}}}});return tmpData;}//梳理數(shù)據(jù)let dataList = LAY_EXCEL.filterExportData(data, getCellStyle());//先將標題前三行空出來exportData.push.apply(exportData, [{}, {}, {}]);//追加員工數(shù)據(jù)exportData.push.apply(exportData, dataList);//合并參數(shù)let mergeConf = LAY_EXCEL.makeMergeConfig([['A1', 'I1'],['A2', 'A3'],['F2', 'F3'],['G2', 'G3'],['B2', 'E2'],['H2', 'I2']]);//配置列寬let colConf = LAY_EXCEL.makeColConfig({F: 100,G: 150});//導出數(shù)據(jù)LAY_EXCEL.exportExcel(exportData, '導出測試.xlsx', 'xlsx', {extend: {'!merges': mergeConf,'!cols': colConf}});
}將列F和列G分別設置為100px，150px，此時點擊導出表格則非常美觀了，效果圖下：
3.5 填充標題數(shù)據(jù)
通過上面數(shù)據(jù)的渲染后，大家應該了解了表格中每列所對應的JSON數(shù)據(jù)中的字段了，所以定義標題是，將對應位置數(shù)據(jù)放到對應字段上即可。因為合并時，是自上往下，自左往右進行合并，除開始位置單元格數(shù)據(jù)，后面單元格會被覆蓋，所以這里姓名，手機號，郵箱需放在第列行數(shù)據(jù)中。代碼如下：
exportEvent(){var titles = [{name: "員工信息統(tǒng)計表"},{name: "姓名", age: "基本信息", mobile: "手機號", email: "郵箱", province: "地址信息"},{age: "年齡", gender: "性別", high: "身高", weight: "體重", province: "所在省", city: "所在市"}], data = [{name: "李四", age: 72, gender: 1, high: 164, weight: 80, mobile: 15627070182, email: "test15627070182@qq.com", province: "湖北省", city: "長沙市"},{name: "張三", age: 60, gender: 1, high: 161, weight: 75, mobile: 15621397301, email: "test15621397301@qq.com", province: "浙江省", city: "臺州市"},{name: "王五", age: 82, gender: 1, high: 170, weight: 72, mobile: 15684572039, email: "test15684572039@qq.com", province: "安徽省", city: "六安市"},{name: "趙六", age: 56, gender: 2, high: 166, weight: 50, mobile: 15694331283, email: "test15694331283@qq.com", province: "江蘇省", city: "蘇州市"}], //公共樣式commonStyle = {font: { sz: 12 },alignment: {vertical: "center",horizontal: "center"},border: {top: {style: 'thin', color: {rgb: '666666'}},bottom: {style: 'thin', color: {rgb: '666666'}},left: {style: 'thin', color: {rgb: '666666'}},right: {style: 'thin', color: {rgb: '666666'}}}},exportData = [];function getCellStyle(styleParam){let tmpData = {};['name', 'age', 'gender', 'high', 'weight', 'mobile', 'email', 'province', 'city'].forEach(item => {tmpData[item] = function(value, line, data){return {v: value,s: {...commonStyle}}}});return tmpData;}//梳理數(shù)據(jù)let titleList = LAY_EXCEL.filterExportData(titles, getCellStyle());//梳理數(shù)據(jù)let dataList = LAY_EXCEL.filterExportData(data, getCellStyle());//先將標題前三行空出來exportData.push.apply(exportData, titleList);//追加員工數(shù)據(jù)exportData.push.apply(exportData, dataList);//合并參數(shù)let mergeConf = LAY_EXCEL.makeMergeConfig([['A1', 'I1'],['A2', 'A3'],['F2', 'F3'],['G2', 'G3'],['B2', 'E2'],['H2', 'I2']]);//配置列寬let colConf = LAY_EXCEL.makeColConfig({F: 100,G: 150});//導出數(shù)據(jù)LAY_EXCEL.exportExcel(exportData, '導出測試.xlsx', 'xlsx', {extend: {'!merges': mergeConf,'!cols': colConf}});
}此時點擊導出，則可以看到對應標題了，效果如下：
但是大家會發(fā)現(xiàn)，上圖中線框并未畫完整，這是因為每行中字段有缺失，只有從開始合并位置單元格有繪制線條。這問題解決也簡單，在梳理標題數(shù)據(jù)位置，將缺失字段補充上并賦值為空即可。代碼如下：
//梳理標題數(shù)據(jù)
let titleList = LAY_EXCEL.filterExportData((() => {let newArr = [];titles.forEach(item => {let tmpData = {};['name', 'age', 'gender', 'high', 'weight', 'mobile', 'email', 'province', 'city'].forEach(key => {//補充缺失字段if('undefined'===typeof item[key]){tmpData[key] = '';}else{tmpData[key] = item[key];}});//創(chuàng)建新數(shù)據(jù)newArr.push(tmpData);});return newArr;
})(), getCellStyle());在梳理標題數(shù)據(jù)時，將傳入filterExportData()函數(shù)中數(shù)據(jù)通過 匿名函數(shù)重新組合，補充缺失部分，最后效果如下：
大家會發(fā)現(xiàn)，getCellStyle()函數(shù)和梳理標題數(shù)據(jù)的 匿名函數(shù)中，都使用到了所有字段數(shù)組，這里可略作修改，以便后期更方便維護，定義一個公共字段數(shù)組，代碼如下：
exportEvent(){var titles = [{name: "員工信息統(tǒng)計表"},{name: "姓名", age: "基本信息", mobile: "手機號", email: "郵箱", province: "地址信息"},{age: "年齡", gender: "性別", high: "身高", weight: "體重", province: "所在省", city: "所在市"}], data = [{name: "李四", age: 72, gender: 1, high: 164, weight: 80, mobile: 15627070182, email: "test15627070182@qq.com", province: "湖北省", city: "長沙市"},{name: "張三", age: 60, gender: 1, high: 161, weight: 75, mobile: 15621397301, email: "test15621397301@qq.com", province: "浙江省", city: "臺州市"},{name: "王五", age: 82, gender: 1, high: 170, weight: 72, mobile: 15684572039, email: "test15684572039@qq.com", province: "安徽省", city: "六安市"},{name: "趙六", age: 56, gender: 2, high: 166, weight: 50, mobile: 15694331283, email: "test15694331283@qq.com", province: "江蘇省", city: "蘇州市"}], //公共樣式commonStyle = {font: { sz: 12 },alignment: {vertical: "center",horizontal: "center"},border: {top: {style: 'thin', color: {rgb: '666666'}},bottom: {style: 'thin', color: {rgb: '666666'}},left: {style: 'thin', color: {rgb: '666666'}},right: {style: 'thin', color: {rgb: '666666'}}}},keys = ['name', 'age', 'gender', 'high', 'weight', 'mobile', 'email', 'province', 'city'],exportData = [];function getCellStyle(styleParam){styleParam = styleParam || {};let tmpData = {};keys.forEach(item => {tmpData[item] = function(value, line, data){return {v: value,s: {...commonStyle,...styleParam}}}});return tmpData;}//梳理標題數(shù)據(jù)let titleList = LAY_EXCEL.filterExportData((() => {let newArr = [];titles.forEach(item => {let tmpData = {};keys.forEach(key => {//補充缺失字段if('undefined'===typeof item[key]){tmpData[key] = '';}else{tmpData[key] = item[key];}});//創(chuàng)建新數(shù)據(jù)newArr.push(tmpData);});return newArr;})(), getCellStyle({font: { sz: 12, bold: true }}));//梳理數(shù)據(jù)let dataList = LAY_EXCEL.filterExportData(data, getCellStyle());//略...
}3.6 修改標題樣式
這里我們想給標題進行加粗，要如何實現(xiàn)呢。在前面已定義了getCellStyle()函數(shù)，這里我們可以通過傳入新新式參數(shù)，將公共部分進行替換，代碼如下：
exportEvent(){//略...function getCellStyle(styleParam){styleParam = styleParam || {};let tmpData = {};['name', 'age', 'gender', 'high', 'weight', 'mobile', 'email', 'province', 'city'].forEach(item => {tmpData[item] = function(value, line, data){return {v: value,s: {...commonStyle,...styleParam}}}});return tmpData;}//梳理標題數(shù)據(jù)let titleList = LAY_EXCEL.filterExportData((() => {let newArr = [];titles.forEach(item => {let tmpData = {};['name', 'age', 'gender', 'high', 'weight', 'mobile', 'email', 'province', 'city'].forEach(key => {//補充缺失字段if('undefined'===typeof item[key]){tmpData[key] = '';}else{tmpData[key] = item[key];}});//創(chuàng)建新數(shù)據(jù)newArr.push(tmpData);});return newArr;})(), getCellStyle({font: { sz: 12, bold: true }}));//梳理數(shù)據(jù)let dataList = LAY_EXCEL.filterExportData(data, getCellStyle());//略...
}效果圖下：
