如何导出Angular Clarity DataGrid过滤/排序后的结果到Excel？

我刚接触Angular，希望能通过简单易懂的方式实现导出Clarity DataGrid中经过过滤、排序或者隐藏特定列后的当前数据，而不是导出全部原始数据。请问有没有可行的实现方法？

我的代码 #

excel.service.ts #

import { Injectable } from '@angular/core';
import * as FileSaver from 'file-saver';
import * as XLSX from 'xlsx';


const EXCEL_TYPE = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8';
const EXCEL_EXTENSION = '.xlsx';

@Injectable({
  providedIn: 'root'
})
export class ExcelService {

  constructor() { }

  public exportAsExcelFile(json: any[], excelFileName: string): void { 
    const worksheet: XLSX.WorkSheet = XLSX.utils.json_to_sheet(json);  
    delete (worksheet[1])
    const workbook: XLSX.WorkBook = { Sheets: { 'data': worksheet }, SheetNames: ['data'] };  
    const excelBuffer: any = XLSX.write(workbook, { bookType: 'xlsx', type: 'array' });  
    this.saveAsExcelFile(excelBuffer, excelFileName);  
  }  
  private saveAsExcelFile(buffer: any, fileName: string): void {  
     const data: Blob = new Blob([buffer], {type: EXCEL_TYPE});  
     FileSaver.saveAs(data, fileName + '_' + new Date().toLocaleDateString() + EXCEL_EXTENSION);  
  }

}

table.component.ts #

showTCExportMenu=false
showTEExportMenu=false
exportAsXLSX(data, name, reimport=false):void {
  let table = data

  reimport? null: table.forEach(
                  table => { table.justification? table.justification = table.justification.replace(/<\/?(?!a)\w*\b[^>]*>/ig, ''): null,
                            table.evidence?  table.evidence = table.evidence.replace(/<\/?(?!a)\w*\b[^>]*>/ig, ''): null,
                            delete table.id, delete table.log
                            })

  this.excelService.exportAsExcelFile(table, name);
  this.showTCExportMenu=false
  this.showTEExportMenu=false
}

table.component.html #

<div>
  <clr-datagrid  [(clrDgSelected)]="selected">
    <clr-dg-column
      *ngFor="let column of columns"
      [clrDgField]="getStringKey(column.key)">
      <ng-container *clrDgHideableColumn="{hidden: false}">
        {{column.title}}
      </ng-container>
      <clr-dg-filter
        *ngIf="column?.filter"
        [clrDgFilter]="getFilter(column)">
        <ng-container filterHost></ng-container>
      </clr-dg-filter>

    </clr-dg-column>
    <clr-dg-row
      *clrDgItems="let item of records"
      [clrDgItem]="item">
      <clr-dg-cell
        *ngFor="let column of columns"
        [innerHTML]="renderValue(column, item)">
      </clr-dg-cell>
      <clr-dg-row-detail
        [innerHTML]="expandRowRender && expandRowRender(item)"
        *clrIfExpanded>
      </clr-dg-row-detail>
    </clr-dg-row>

    <clr-dg-footer>
      <button class="btn" (click)="exportAsXLSX(tableData, 'example', true)">Export to excel</button>
      <clr-dg-pagination #pagination >
        <clr-dg-page-size [clrPageSizeOptions]="[5,10,20,30, 50, 100]">Users per page</clr-dg-page-size>
        {{pagination.firstItem + 1}} - {{pagination.lastItem + 1}}
        of {{pagination.totalItems}} records
      </clr-dg-pagination>
    </clr-dg-footer>
  </clr-datagrid>
</div>

catone.component.ts #

import { Component, OnInit } from '@angular/core';
import {TableColumns, TableData} from "../tableone/tableone.types";
import { faker } from '@faker-js/faker';
import {Observable, Subject} from "rxjs";
import {HttpClient} from "@angular/common/http";
import {ClrDatagridFilterInterface} from "@clr/angular";
import {AgefilterComponent} from "../agefilter/agefilter.component";
import {DatefilterComponent} from "../datefilter/datefilter.component";


interface InventoryData {
  test_name: any;
  description: any;
  status: string;
  test_level: string;
  test_category: string;
  test_type: string;
  test_approach: string;
  test_applicability: string;
  requirement_accountability: string;
  test_plan_accountability: string;
  trigger_type: string;
  applicable_metadata_statuses: string;
  assessment_level_targeted: string;
  comp_control_flag: string;
  testing_platform: string;
  effective_date: Date;
  test_retired_date: Date;
  metric_inclusion: string;
  policy_requirement: string
}


export  class AgeFilter implements ClrDatagridFilterInterface<InventoryData> {
  changes = new Subject<any>();
  public minAge!: number;
  public  maxAge!: number;
  accepts(person: InventoryData): boolean {
    return true
  }

  isActive(): boolean {
    return false
  }

}

export class BirthdayFilter implements ClrDatagridFilterInterface<InventoryData> {
  changes = new Subject<any>;
  value = '';
  accepts(item: InventoryData): boolean {
    return true
  }

  isActive(): boolean {
    return false
  }
}

@Component({
  selector: 'catone',
  templateUrl: './catone.component.html',
  styleUrls: ['./catone.component.scss']
})
export class CatoneComponent implements OnInit {
  columns: TableColumns<InventoryData> = [

    {
      title: 'Test_name',
      key: 'test_name'
    },
    {
      title: 'Description',
      key: 'description'
    },
    {
      title: 'Status',
      key: 'status'
    },
    {
      title: 'Test_level',
      key: 'test_level'
    },
    {
      title: 'Test_category',
      key: 'test_category'
    },
    {
      title: 'Test_type',
      key: 'test_type',
    },
    {
      title: 'Test_approach',
      key: 'test_approach',
    },
    {
      title: 'Test_applicability',
      key: 'test_applicability',
    },
    {
      title: 'Requirement_accountability',
      key: 'requirement_accountability',
    },
    {
      title: 'Test_plan_accountability',
      key: 'test_plan_accountability',
    },
    {
      title: 'Trigger_type',
      key: 'trigger_type',
    },
    {
      title: 'Applicable_metadata_statuses',
      key: 'applicable_metadata_statuses',
    },
    {
      title: 'Assessment_level_targeted',
      key: 'assessment_level_targeted',
    },
    {
      title: 'Comp_control_flag',
      key: 'comp_control_flag',
    },
    {
      title: 'Testing_platform',
      key: 'testing_platform',
    },
    {
      title: 'Effective_date',
      key: 'effective_date',
    },
    {
      title: 'Test_retired_date',
      key: 'test_retired_date',
    },
    {
      title: 'Metric_inclusion',
      key: 'metric_inclusion',
    },
    {
      title: 'Policy_requirement',
      key: 'policy_requirement',
    },

  ];
  tableData: TableData<InventoryData> | Observable<TableData<InventoryData>> = [];
  page = 1;
  pageSize = 10;
  total = 1000;
  constructor(private httpClient: HttpClient) { }


  renderExpand(p: InventoryData) {
    return `
      <pre>${'test name: ' + p.test_name}</pre>
    `
  }

  handlePageChange(page: number) {
    this.page = page;
    this.requestApi();
  }

  requestApi() {
    this.httpClient.get<InventoryData[]>(`myapi`)
      .subscribe(data => {
        this.tableData = data
      })
  }

  ngOnInit(): void {
    this.requestApi();
  }

}

实现方案 #

Clarity DataGrid自带获取当前视图数据的API，同时我们可以跟踪列的显示状态，最终只导出用户看到的内容，分三步实现：

1. 获取过滤、排序后的当前数据 #

首先给DataGrid添加模板引用，方便获取实例：
修改table.component.html中的clr-datagrid标签：

<clr-datagrid #datagrid [(clrDgSelected)]="selected">

然后在table.component.ts中引入ViewChild获取DataGrid实例：

import { ViewChild } from '@angular/core';
import { ClrDatagrid } from '@clr/angular';

export class TableComponent {
  @ViewChild('datagrid') datagrid!: ClrDatagrid;
  // ... 其他原有代码
}

之后用this.datagrid.rows就能拿到当前经过过滤、排序后的所有行数据（注：如果是服务器端分页，这里只能拿到当前页数据，需要后端配合返回全量过滤结果；客户端分页则能拿到所有符合条件的数据）。

2. 跟踪列的显示/隐藏状态 #

修改列定义，给每个列添加hidden属性（以catone.component.ts为例）：

columns: TableColumns<InventoryData> = [
  {
    title: 'Test_name',
    key: 'test_name',
    hidden: false // 添加这个属性
  },
  // 其他列全部加上hidden: false
];

然后修改table.component.html中的列隐藏绑定，同步状态：

<ng-container *clrDgHideableColumn="{hidden: column.hidden, change: (hidden) => column.hidden = hidden}">
  {{column.title}}
</ng-container>

这样用户切换列显示时，columns数组里的hidden值会自动更新。

3. 修改导出方法，只导出当前视图内容 #

更新table.component.ts中的exportAsXLSX方法：

exportAsXLSX(name, reimport=false):void {
  // 1. 获取过滤排序后的行数据
  const filteredRows = this.datagrid.rows.map(row => row.item);
  
  // 2. 筛选出当前显示的列的key
  const visibleColumnKeys = this.columns.filter(col => !col.hidden).map(col => col.key);
  
  // 3. 只保留显示列的字段
  let table = filteredRows.map(item => {
    const filteredItem: any = {};
    visibleColumnKeys.forEach(key => {
      filteredItem[key] = item[key];
    });
    return filteredItem;
  });

  // 原有HTML标签清理逻辑保留
  if(!reimport) {
    table.forEach(item => {
      if(item.justification) {
        item.justification = item.justification.replace(/<\/?(?!a)\w*\b[^>]*>/ig, '');
      }
      if(item.evidence) {
        item.evidence = item.evidence.replace(/<\/?(?!a)\w*\b[^>]*>/ig, '');
      }
      delete item.id;
      delete item.log;
    });
  }

  this.excelService.exportAsExcelFile(table, name);
  this.showTCExportMenu=false;
  this.showTEExportMenu=false;
}

最后修改导出按钮的点击事件，不用再传tableData：

<button class="btn" (click)="exportAsXLSX('example', true)">Export to excel</button>

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