Angular Material数据表格导出Excel方案及报错解决

Let's break down why you're seeing that "Bad range" error and how to fix it properly.

Why the Error Happens #

The error occurs because you're passing the entire container <div class="example-container" #TABLE> (which includes the mat-paginator) to XLSX.utils.table_to_sheet(). XLSX tries to parse all elements inside that div as table content, and the paginator's structure throws off its row/range calculations—leading to the invalid "A1:A0" range error.

Solution 1: Export All Data (Recommended) #

This approach skips DOM parsing entirely and uses your original data source, which is more reliable and exports all your location records (not just the current page).

Step 1: Update the Export Function #

Replace your ExportTOExcel() method with this code:

ExportTOExcel() {
  // Exclude the 'action' column since it contains buttons, not data
  const exportColumns = this.displayedColumns.filter(col => col !== 'action');

  // Map your location data to include only the columns we want to export
  const exportData = this.locations.map(location => {
    return exportColumns.reduce((exportObj, column) => {
      exportObj[column] = location[column];
      return exportObj;
    }, {});
  });

  // Convert the filtered data to an Excel worksheet
  const worksheet: XLSX.WorkSheet = XLSX.utils.json_to_sheet(exportData);
  
  // Create a new workbook and add the worksheet
  const workbook: XLSX.WorkBook = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(workbook, worksheet, 'Locations');

  // Trigger the file download
  XLSX.writeFile(workbook, 'Locations.xlsx');
}

Why This Works: #

• Uses your raw locations array instead of the DOM, avoiding any Material table structure quirks
• Excludes the non-data "action" column automatically
• Exports all your records, not just the page currently visible in the table

Solution 2: Export Only the Current Visible Page #

If you specifically want to export just the data shown on the current paginated page, target the actual mat-table element instead of the container div:

Step 1: Adjust Your Component's ViewChild #

Update the @ViewChild in your .ts file to target the mat-table directly (you already have a #table reference on it):

@ViewChild('table', { read: ElementRef }) table: ElementRef;

Step 2: Update the Export Function #

ExportTOExcel() {
  // Get the actual mat-table element
  const tableElement = this.table.nativeElement;
  
  // Convert the table to a worksheet
  const worksheet: XLSX.WorkSheet = XLSX.utils.table_to_sheet(tableElement);
  
  // Create workbook and export
  const workbook: XLSX.WorkBook = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(workbook, worksheet, 'Current Page');
  XLSX.writeFile(workbook, 'Locations_CurrentPage.xlsx');
}

Note: #

This approach depends on the DOM structure of the Material table. If you run into formatting issues, stick with Solution 1—it's far more robust for most use cases.

Additional Checks #

• Ensure your locations array is properly populated before export (your ngOnInit code already handles this, but double-check if you're filtering data later)
• If you need custom column names in Excel (instead of the raw property names), modify the exportData mapping to use friendly labels (e.g., exportObj["Location Name"] = location.locationName;)

内容的提问来源于stack exchange，提问作者Veda