Angular2+: 通过npm xlsx开源模块包实现导出excel文件的功能(从<table> --> .xlsx)
- 使用npm下载xlsx包
npm i xlsx
- 模版部分(tableExport.html)：
...
<table class="ars_table table-bordered" #reportTable> <thead> <tr> <th colspan="30" bgcolor="#ffffff"></th> <th colspan="15" bgcolor="#CC99FF"><font color="#000000">Gross Amount</font></th> <th colspan="15" bgcolor="#FF99CC"><font color="#000000">Agency Commission</font></th> <th colspan="15" bgcolor="#99CCFF"><font color="#000000">Production Cost (Nett)</font></th> </tr> <tr> <th bgcolor="#FF9900"><font color="#000000">Contract No.</font></th> <th bgcolor="#FF9900"><font color="#000000">BU</font></th> <th bgcolor="#FF9900"><font color="#000000">Commitment No.</font></th> <th bgcolor="#FF9900"><font color="#000000">Sub-contract</font></th> <th bgcolor="#FF9900"><font color="#000000">Contract Upload Date</font></th> <th bgcolor="#FF9900"><font color="#000000">Approval Date</font></th> <th bgcolor="#FF9900"><font color="#000000">Execution Date</font></th> <th bgcolor="#FF9900"><font color="#000000">Contract Status</font></th> <th bgcolor="#FF9900"><font color="#000000">Finatnce Acceplance **</font></th> <th bgcolor="#FF9900"><font color="#000000">Sales Name</font></th> <th bgcolor="#FF9900"><font color="#000000">Team Head</font></th> <th bgcolor="#FF9900"><font color="#000000">Team Name</font></th> <th bgcolor="#FF9900"><font color="#000000">Billing Party</font></th> <th bgcolor="#FF9900"><font color="#000000">Advertiser Name</font></th> <th bgcolor="#FF9900"><font color="#000000">Brand Name</font></th> <th bgcolor="#FF9900"><font color="#000000">Master Package Name</font></th> <th bgcolor="#FF9900"><font color="#000000">Entitlement Type</font></th> <th bgcolor="#FF9900"><font color="#000000">Entitlement Item</font></th> <th bgcolor="#FF9900"><font color="#000000">Sub-contract Package</font></th> <th bgcolor="#FF9900"><font color="#000000">TX Start Date</font></th> <th bgcolor="#FF9900"><font color="#000000">TX End Date</font></th> <th bgcolor="#FF9900"><font color="#000000">First TX Date (From ForeTV) *if available</font></th> <th bgcolor="#FF9900"><font color="#000000">Billing Start Date</font></th> <th bgcolor="#FF9900"><font color="#000000">Billing End Date</font></th> <th bgcolor="#FF9900"><font color="#000000">Contract Gross Amount (Gross b4 AC)</font></th> <th bgcolor="#FF9900"><font color="#000000">Contract Nett(Net after AC)</font></th> <th bgcolor="#FF9900"><font color="#000000">Sub Contract Amount</font></th> <th bgcolor="#FF9900"><font color="#000000">Agency Commission</font></th> <th bgcolor="#FF9900"><font color="#000000">Total amount of COS (Gross)</font></th> <th bgcolor="#FF9900"><font color="#000000">Total amount of COS (Nett)</font></th> <th bgcolor="#CC99FF">Before Current Year</th> <th bgcolor="#CC99FF"><font color="#000000">Current Year</font></th> <th bgcolor="#CC99FF"><font color="#000000">Jan-19</font></th> <th bgcolor="#CC99FF"><font color="#000000">Feb-19</font></th> <th bgcolor="#CC99FF"><font color="#000000">Mar-19</font></th> <th bgcolor="#CC99FF"><font color="#000000">Apr-19</font></th> <th bgcolor="#CC99FF"><font color="#000000">May-19</font></th> <th bgcolor="#CC99FF"><font color="#000000">Jun-19</font></th> <th bgcolor="#CC99FF"><font color="#000000">Jul-19</font></th> <th bgcolor="#CC99FF"><font color="#000000">Aug-19</font></th> <th bgcolor="#CC99FF"><font color="#000000">Sep-19</font></th> <th bgcolor="#CC99FF"><font color="#000000">Oct-19</font></th> <th bgcolor="#CC99FF"><font color="#000000">Nov-19</font></th> <th bgcolor="#CC99FF"><font color="#000000">Dec-19</font></th> <th bgcolor="#CC99FF">After Current Year</th> <th bgcolor="#FF99CC">2018 Or Before</th> <th bgcolor="#FF99CC"><font color="#000000">Current Year</font></th> <th bgcolor="#FF99CC"><font color="#000000">Jan-19</font></th> <th bgcolor="#FF99CC"><font color="#000000">Feb-19</font></th> <th bgcolor="#FF99CC"><font color="#000000">Mar-19</font></th> <th bgcolor="#FF99CC"><font color="#000000">Apr-19</font></th> <th bgcolor="#FF99CC"><font color="#000000">May-19</font></th> <th bgcolor="#FF99CC"><font color="#000000">Jun-19</font></th> <th bgcolor="#FF99CC"><font color="#000000">Jul-19</font></th> <th bgcolor="#FF99CC"><font color="#000000">Aug-19</font></th> <th bgcolor="#FF99CC"><font color="#000000">Sep-19</font></th> <th bgcolor="#FF99CC"><font color="#000000">Oct-19</font></th> <th bgcolor="#FF99CC"><font color="#000000">Nov-19</font></th> <th bgcolor="#FF99CC"><font color="#000000">Dec-19</font></th> <th bgcolor="#FF99CC">2020 Or After</th> <th bgcolor="#99CCFF">2018 Or Before</th> <th bgcolor="#99CCFF"><font color="#000000">Current Year</font></th> <th bgcolor="#99CCFF"><font color="#000000">Jan-19</font></th> <th bgcolor="#99CCFF"><font color="#000000">Feb-19</font></th> <th bgcolor="#99CCFF"><font color="#000000">Mar-19</font></th> <th bgcolor="#99CCFF"><font color="#000000">Apr-19</font></th> <th bgcolor="#99CCFF"><font color="#000000">May-19</font></th> <th bgcolor="#99CCFF"><font color="#000000">Jun-19</font></th> <th bgcolor="#99CCFF"><font color="#000000">Jul-19</font></th> <th bgcolor="#99CCFF"><font color="#000000">Aug-19</font></th> <th bgcolor="#99CCFF"><font color="#000000">Sep-19</font></th> <th bgcolor="#99CCFF"><font color="#000000">Oct-19</font></th> <th bgcolor="#99CCFF"><font color="#000000">Nov-19</font></th> <th bgcolor="#99CCFF"><font color="#000000">Dec-19</font></th> <th bgcolor="#99CCFF">2020 Or After</th> </tr> </thead> <tbody> <tr *ngFor="let report of revenueReportResults;"> <td>{{report.contractNo}}</td> <td>{{report.bu}}</td> <td><li *ngFor="let commitmentNo of report.commitmentNo";>{{commitmentNo}}</li></td> <td>{{report.subContractNo}}</td> <td>{{report.contractUploadDate | date:'yyyy/MM/dd'}}</td> <td>{{report.contractApprovalDate | date:'yyyy/MM/dd'}}</td> <td>{{report.contractExecuteDate | date:'yyyy/MM/dd'}}</td> <td>{{report.contractStatus}}</td> <td>{{report.financeAcceptance}}</td> <td>{{report.salesName}}</td> <td>{{report.teamHead}}</td> <td>{{report.teamName}}</td> <td>{{report.billingParty}}</td> <td>{{report.advertiserName}}</td> <td>{{report.brandName}}</td> <td>{{report.masterPackageName}}</td> <td>{{report.entitlementType}}</td> <td><font *ngFor="let item of report.entitlementItems;">{{item}}; </font></td> <td>{{report.subContractPackage}}</td> <td>{{report.txStartDate | date:'yyyy/MM/dd'}}</td> <td>{{report.txEndDate | date:'yyyy/MM/dd'}}</td> <td>{{report.firstTxDateFromForeTv}}</td> <td>{{report.billingStartDate | date:'yyyy/MM/dd'}}</td> <td>{{report.billingEndDate | date:'yyyy/MM/dd'}}</td> <td>{{report.contractGrossAmount | number: '1.2-2'}}</td> <td>{{report.contractNettAmount | number: '1.2-2'}}</td> <td>{{report.subContractAmount | number: '1.2-2'}}</td> <td>{{report.agencyCommission | number: '1.2-2'}}</td> <td>{{report.totalGrossCosAmount | number: '1.2-2'}}</td> <td>{{report.totalNettCosAmount | number: '1.2-2'}}</td> <td>{{report.grossAmountSchedule.totalBeforeCurrentYearAmount | number: '1.2-2'}}</td> <td>{{report.grossAmountSchedule.totalCurrentYearAmount | number: '1.2-2'}}</td> <td>{{report.grossAmountSchedule.currentYearJanAmount | number: '1.2-2'}}</td> <td>{{report.grossAmountSchedule.currentYearFebAmount | number: '1.2-2'}}</td> <td>{{report.grossAmountSchedule.currentYearMarAmount | number: '1.2-2'}}</td> <td>{{report.grossAmountSchedule.currentYearAprAmount | number: '1.2-2'}}</td> <td>{{report.grossAmountSchedule.currentYearMayAmount | number: '1.2-2'}}</td> <td>{{report.grossAmountSchedule.currentYearJunAmount | number: '1.2-2'}}</td> <td>{{report.grossAmountSchedule.currentYearJulAmount | number: '1.2-2'}}</td> <td>{{report.grossAmountSchedule.currentYearAugAmount | number: '1.2-2'}}</td> <td>{{report.grossAmountSchedule.currentYearSepAmount | number: '1.2-2'}}</td> <td>{{report.grossAmountSchedule.currentYearOctAmount | number: '1.2-2'}}</td> <td>{{report.grossAmountSchedule.currentYearNovAmount | number: '1.2-2'}}</td> <td>{{report.grossAmountSchedule.currentYearDecAmount | number: '1.2-2'}}</td> <td>{{report.grossAmountSchedule.totalAfterCurrentYearAmount | number: '1.2-2'}}</td> <td>{{report.agencyCommissionSchedule.totalBeforeCurrentYearAmount | number: '1.2-2'}}</td> <td>{{report.agencyCommissionSchedule.totalCurrentYearAmount | number: '1.2-2'}}</td> <td>{{report.agencyCommissionSchedule.currentYearJanAmount | number: '1.2-2'}}</td> <td>{{report.agencyCommissionSchedule.currentYearFebAmount | number: '1.2-2'}}</td> <td>{{report.agencyCommissionSchedule.currentYearMarAmount | number: '1.2-2'}}</td> <td>{{report.agencyCommissionSchedule.currentYearAprAmount | number: '1.2-2'}}</td> <td>{{report.agencyCommissionSchedule.currentYearMayAmount | number: '1.2-2'}}</td> <td>{{report.agencyCommissionSchedule.currentYearJunAmount | number: '1.2-2'}}</td> <td>{{report.agencyCommissionSchedule.currentYearJulAmount | number: '1.2-2'}}</td> <td>{{report.agencyCommissionSchedule.currentYearAugAmount | number: '1.2-2'}}</td> <td>{{report.agencyCommissionSchedule.currentYearSepAmount | number: '1.2-2'}}</td> <td>{{report.agencyCommissionSchedule.currentYearOctAmount | number: '1.2-2'}}</td> <td>{{report.agencyCommissionSchedule.currentYearNovAmount | number: '1.2-2'}}</td> <td>{{report.agencyCommissionSchedule.currentYearDecAmount | number: '1.2-2'}}</td> <td>{{report.agencyCommissionSchedule.totalAfterCurrentYearAmount | number: '1.2-2'}}</td> <td>{{report.productionCostSchedule.totalBeforeCurrentYearAmount | number: '1.2-2'}}</td> <td>{{report.productionCostSchedule.totalCurrentYearAmount | number: '1.2-2'}}</td> <td>{{report.productionCostSchedule.currentYearJanAmount | number: '1.2-2'}}</td> <td>{{report.productionCostSchedule.currentYearFebAmount | number: '1.2-2'}}</td> <td>{{report.productionCostSchedule.currentYearMarAmount | number: '1.2-2'}}</td> <td>{{report.productionCostSchedule.currentYearAprAmount | number: '1.2-2'}}</td> <td>{{report.productionCostSchedule.currentYearMayAmount | number: '1.2-2'}}</td> <td>{{report.productionCostSchedule.currentYearJunAmount | number: '1.2-2'}}</td> <td>{{report.productionCostSchedule.currentYearJulAmount | number: '1.2-2'}}</td> <td>{{report.productionCostSchedule.currentYearAugAmount | number: '1.2-2'}}</td> <td>{{report.productionCostSchedule.currentYearSepAmount | number: '1.2-2'}}</td> <td>{{report.productionCostSchedule.currentYearOctAmount | number: '1.2-2'}}</td> <td>{{report.productionCostSchedule.currentYearNovAmount | number: '1.2-2'}}</td> <td>{{report.productionCostSchedule.currentYearDecAmount | number: '1.2-2'}}</td> <td>{{report.productionCostSchedule.totalAfterCurrentYearAmount | number: '1.2-2'}}</td> </tr> </tbody> </table>
...
其中#reportTable指向的表单对象将会被组件类中的xlsx包引用。
- 组件类部分(tableExport.ts):
... //引入xlsx包 import * as XLSX from "xlsx"; ... export class SearchOutputComponent { //使用ViewChild标记获取table的DOM对象 @ViewChild("reportTable") reportTable: ElementRef; //调用xlsx包内导出功能的函数（通过table对象自动导出） public exportReport(){ //定义并初始化一个工作簿对象 let wb: XLSX.WorkBook = XLSX.utils.book_new(); //定义并初始化一个工作表对象（使用的是table_tosheet(...)方法通过模版中的表单 DOM对象自动生成对应的工作表对象） let ws:XLSX.WorkSheet = XLSX.utils.table_to_sheet(this.reportTable.nativeElement, {raw:true});//raw:true 表示生成xlsx时不触发excel的函数 //将生成后的工作表放进工作簿中，并且为其取名 XLSX.utils.book_append_sheet( wb, ws, "Now in CMS" ); //调用生成xlsx文件的方法 XLSX.writeFile(wb,"Revenue_Report_"+new Date().toISOString()+".xlsx"); } }
PS：通过<table>表单对象自动生成出来的xlsx不会自动继承其样式，比如单元格背景颜色，单元格宽度等。需要参考npm xlsx官方的API文档作另外的调整。
参考网址：
https://www.npmjs.com/package/xlsx
不积跬步无以至千里，不积小流无以成江海。
浙公网安备 33010602011771号