Javascript 角材料数据表导出到excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/50398284/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
angular material data table export to excel
提问by Veda
I am using angular material data table to display data in a tabular format. I need to include a functionality which exports tabular data to excel. I am not able to find any documents which will help me to export the data. Can you please let me know how to export data to excel in angular which uses angular material data table.
我正在使用角材料数据表以表格格式显示数据。我需要包含一个将表格数据导出到 excel 的功能。我找不到任何可以帮助我导出数据的文件。您能否让我知道如何将数据导出到使用角度材料数据表的角度中的 excel。
I tried using XLSX.utils and facing "Bad range (0): A1:A0 at check_ws" issue.
我尝试使用 XLSX.utils 并面临“Bad range (0): A1:A0 at check_ws”问题。
Location.component.html
位置.component.html
<div class="example-container" #TABLE>
<mat-table #table [dataSource]="dataSource" matSort matSortActive="locationName" matSortDirection="asc" matSortDisableClear>
<ng-container matColumnDef="locationName">
<mat-header-cell *matHeaderCellDef mat-sort-header>Location Name </mat-header-cell>
<mat-cell *matCellDef="let location"> {{location.locationName}} </mat-cell>
</ng-container>
<ng-container matColumnDef="address">
<mat-header-cell *matHeaderCellDef>Address </mat-header-cell>
<mat-cell *matCellDef="let location"> {{location.address}} </mat-cell>
</ng-container>
<ng-container matColumnDef="city">
<mat-header-cell *matHeaderCellDef mat-sort-header> City </mat-header-cell>
<mat-cell *matCellDef="let location"> {{location.city}} </mat-cell>
</ng-container>
<ng-container matColumnDef="country">
<mat-header-cell *matHeaderCellDef mat-sort-header>Country </mat-header-cell>
<mat-cell *matCellDef="let location"> {{location.country}} </mat-cell>
</ng-container>
<ng-container matColumnDef="zipcode">
<mat-header-cell *matHeaderCellDef>ZipCode </mat-header-cell>
<mat-cell *matCellDef="let location"> {{location.zipcode}} </mat-cell>
</ng-container>
<ng-container matColumnDef="phone">
<mat-header-cell *matHeaderCellDef>Phone </mat-header-cell>
<mat-cell *matCellDef="let location"> {{location.phone}} </mat-cell>
</ng-container>
<ng-container matColumnDef="timezone">
<mat-header-cell *matHeaderCellDef> TimeZone </mat-header-cell>
<mat-cell *matCellDef="let location"> {{location.timezone}} </mat-cell>
</ng-container>
<ng-container matColumnDef="action">
<mat-header-cell *matHeaderCellDef> Action </mat-header-cell>
<!-- <mat-cell *matCellDef="let location"> {{location.timezone}} </mat-cell> -->
<mat-cell *matCellDef="let location">
<a href ="#" class="btn Action-Tab" >Edit</a>
<a href ="#" class="btn Action-Tab" >Delete</a>
</mat-cell>
</ng-container>
<mat-header-row *matHeaderRowDef="displayedColumns"></mat-header-row>
<mat-row *matRowDef="let row; columns: displayedColumns;">
</mat-row>
</mat-table>
<mat-paginator [pageSizeOptions]="[10, 20, 50,100]"></mat-paginator>
</div>
<button mat-raised-button color="primary" (click)="ExportTOExcel()">Export as Excel</button>
Location.component.ts
位置.component.ts
import { Component, OnInit, OnDestroy , ViewChild,ElementRef} from '@angular/core';
import { ILocation } from '../../Ilocation';
import { LocationService } from '../../services/location.service';
import { DataTableResource } from 'angular5-data-table';
import { Subscription } from 'rxjs';
import {MatPaginator, MatSort, MatTableDataSource} from '@angular/material';
import {DataSource} from '@angular/cdk/table';
import * as XLSX from 'xlsx';
// import { CdkTableModule } from '@angular/cdk/table';
@Component({
selector: 'app-location',
templateUrl: './location.component.html',
styleUrls: ['./location.component.css']
})
export class LocationComponent implements OnInit , OnDestroy{
errorMessage: string;
filterBy : string;
locations: ILocation[];
items : ILocation[]=[];
itemCount :number = 0;
subscription:Subscription;
limits = [5, 10, 20, 80];
tableResource : DataTableResource<ILocation>;
displayedColumns = ['locationName', 'address', 'city', 'country','zipcode', 'phone','timezone','action'];
// dataSource: MatTableDataSource<ILocation>;
dataSource;
@ViewChild(MatPaginator) paginator: MatPaginator;
@ViewChild(MatSort) sort : MatSort;
@ViewChild('TABLE',{ read: ElementRef }) table: ElementRef;
constructor( private locationService: LocationService) {
}
applyFilter(filterValue: string) {
filterValue = filterValue.trim(); // Remove whitespace
filterValue = filterValue.toLowerCase(); // Datasource defaults to lowercase matches
this.dataSource.filter = filterValue;
}
ngOnInit() {
this.subscription = this.locationService.getLocations()
.subscribe(locations =>{
this.locations = locations;
this.dataSource = new MatTableDataSource(locations);
this.dataSource.sort = this.sort;
this.dataSource.paginator = this.paginator;
this.dataSource.table = this.table;
},
error => this.errorMessage = <any>error);
}
ngOnDestroy(){
this.subscription.unsubscribe();
}
ExportTOExcel()
{
console.log("export");
this.table.nativeElement.style.background = "red";
const ws: XLSX.WorkSheet=XLSX.utils.table_to_sheet(this.table.nativeElement);
const wb: XLSX.WorkBook = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, ws, 'Sheet1');
/* save to file */
XLSX.writeFile(wb,'SheetJS.xlsx');
console.log("exported");
}
}
回答by Vikas
You can use xlsxfor exporting table as excel.
usage
Execute npm i xlsx
您可以使用xlsx将表格导出为 excel。
用法
执行npm i xlsx
HTML:
HTML：
<div class="example-container mat-elevation-z8 " #TABLE>
<table mat-table #table [dataSource]="dataSource">
<!--- Note that these columns can be defined in any order.
The actual rendered columns are set as a property on the row definition" -->
<!-- Position Column -->
<ng-container matColumnDef="position">
<th mat-header-cell *matHeaderCellDef> No. </th>
<td mat-cell *matCellDef="let element"> {{element.position}} </td>
//..................................rest of the html
<button mat-raised-button color="primary" (click)="exportAsExcel()">Export as Excel</button></div>
In your Component
在您的组件中
import {Component,ViewChild, ElementRef} from '@angular/core';
import * as XLSX from 'xlsx';
//......
export class AppComponent {
@ViewChild('TABLE') table: ElementRef;
exportAsExcel()
{
const ws: XLSX.WorkSheet=XLSX.utils.table_to_sheet(this.table.nativeElement);//converts a DOM TABLE element to a worksheet
const wb: XLSX.WorkBook = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, ws, 'Sheet1');
/* save to file */
XLSX.writeFile(wb, 'SheetJS.xlsx');
}
}
回答by talhature
It is a late reply but, you can use mat-table-exporterwhich utilizes xlsx sheetjs and provides paginated table export as excel, csv, json and txt formats.
这是一个迟到的回复，但是，您可以使用mat-table-exporter，它利用 xlsx sheetjs 并提供分页表导出为 excel、csv、json 和 txt 格式。
回答by Jovo Skorupan
Export to excel is easy to do with something like this:
导出到 excel 很容易做到：
exportExcel() {
const workSheet = XLSX.utils.json_to_sheet(this.dataSource.data, {header:['dataprop1', 'dataprop2']});
const workBook: XLSX.WorkBook = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(workBook, workSheet, 'SheetName');
XLSX.writeFile(workBook, 'filename.xlsx');
}
Call it from button:
从按钮调用它：
<button (click)="exportExcel()">Export</button>
回答by klaydze
In my case, alternative to table_to_sheetis to use json_to_sheet. Since I don't know how to properly export the table (with pagination) and the filtered table, I leverage the json_to_sheetand in the dataSourceinstead of dataSource.data, I use the dataSource.filteredData.
就我而言，替代方法table_to_sheet是使用json_to_sheet. 由于我不知道如何正确导出表（带分页）和过滤后的表，我利用json_to_sheet和dataSource代替dataSource.data，我使用dataSource.filteredData.
So with this, it covers the ff:
因此，它涵盖了ff：
- Export table with pagination
- Export table with filters
- 带分页的导出表
- 带过滤器的导出表
exportToExcel() {
let dataToExport = this.dataSource.filteredData
.map(x => ({
DisplayName: x.DisplayName,
Name: x.Name,
Type: x.Type == '0' ? 'Partial' : 'Full'
}));
let workSheet: XLSX.WorkSheet = XLSX.utils.json_to_sheet(dataToExport, <XLSX.Table2SheetOpts>{ sheet: 'Sheet 1' });
let workBook: XLSX.WorkBook = XLSX.utils.book_new();
// Adjust column width
var wscols = [
{ wch: 50 },
{ wch: 50 },
{ wch: 30 }
];
workSheet["!cols"] = wscols;
XLSX.utils.book_append_sheet(workBook, workSheet, 'Sheet 1');
XLSX.writeFile(workBook, `${this.exportToExcelFileName}.xlsx`);
}
回答by vinayofficial
I was having the same issue and I found this solution, which worked for me...
我遇到了同样的问题，我找到了这个对我有用的解决方案......
(#1) Pay attention to div table wrapper, it MUST contain #TABLE
(#1) 注意 div 表包装器，它必须包含 #TABLE
<div #TABLE>
<table mat-table>
<!-- Your table code goes here -->
</table>
</div>
(#2) Now, before the closing of your </table>or </mat-table>, make these changes...
(#2) 现在，在关闭</table>or 之前</mat-table>，进行这些更改...
<mat-header-row></mat-header-row>==> <tr mat-header-row>...</tr>
<mat-header-row></mat-header-row>==> <tr mat-header-row>...</tr>
<mat-row></mat-row>==> <tr mat-row></tr>
<mat-row></mat-row>==> <tr mat-row></tr>
At this point, If you lose some CSS style of your table, use below code to fix the style back...
此时，如果您丢失了表格的一些 CSS 样式，请使用以下代码将样式修复回来...
tr.mat-header-row, tr.mat-row {
display: flex;
}
Hope this can help you.
希望这可以帮到你。
回答by abhishek bhokare
you need to make changes in your HTML file. instead of :
您需要对 HTML 文件进行更改。代替 ：
<mat-header-row *matHeaderRowDef="displayedColumns"></mat-header-row>
<mat-row *matRowDef="let row; columns: displayedColumns;">
Do it like,
这样做，
<tr mat-header-row *matHeaderRowDef="displayedColumns"></tr>
<tr mat-row *matRowDef="let row; columns: displayedColumns;"></tr>
<tr mat-header-row *matHeaderRowDef="displayedColumns"></tr>
<tr mat-row *matRowDef="let row; columns: displayedColumns;"></tr>
