Angular - How to merged cells with DevExtreme DataGrid

文章目錄

DevExtreme DataGrid provides a grouping function to organize data by groups. However, we want to display traditional table cells with merged cells.

In this article, I will demonstrate how to merge cells using the HTML rowspan attribute. You can also adjust the code for colspan if needed. Also, I will show how to export to Excel with merged cells.

Set up merged Cell

1. Create Service

Decide which row to set HTML rowspan attribute?
Count how many rows to span?

export class UiService {
  /**
   * table should setup span
   */
  isNewGroup<T>(data: T[], index: number, property: keyof T): boolean {
    return index === 0 || data[index][property] !== data[index - 1][property];
  }

  /**
   * count the table rowspan
   */
  getRowSpan<T>(data: T[], index: number, property: keyof T): number {
    let span = 1;
    for (let i = index + 1; i < data.length; i++) {
      if (data[i][property] === data[index][property]) span++;
      else break;
    }
    return span;
  }

  /**
   * set up cellTemplate of devextreme datagrid column
   */
  groupTemplate<T>(dataSource: T[], container: HTMLTableCellElement, options: ColumnCellTemplateData) {
    const { data, rowIndex, column } = options;
    const property = column.dataField as keyof T;
    const isNewGroup = this.isNewGroup(dataSource, rowIndex, property);
    if (isNewGroup) {
      const rowspan = this.getRowSpan(dataSource, rowIndex, property);
      container.setAttribute("rowspan", `${rowspan}`);
      container.textContent = data[property];
    } else container.style.display = 'none';
  }
}

2. Consume Service

Add [cellTemplate] to execute groupTemplate().

<dxi-column dataField="station" caption="Station" [cellTemplate]="groupTemplate" [allowEditing]="false" [allowFiltering]="false"></dxi-column>
<dxi-column dataField="sysName" caption="System" [cellTemplate]="groupTemplate" [allowEditing]="false" [allowFiltering]="false"></dxi-column>

constructor(private _uiService: UiService) {
    this.groupTemplate = this.groupTemplate.bind(this);
}

groupTemplate(container: HTMLTableCellElement, options: ColumnCellTemplateData) {
    this._uiService.groupTemplate(this.execList, container, options);
}

Export Excel with merged cells

1. Add dependencies

{
  "dependencies": {
    "@types/file-saver": "^2.0.5",
    "exceljs": "^4.3.0",
    "file-saver": "^2.0.5"
  }
}

2. Create Service to export Excel

import { Inject, Injectable } from '@angular/core';
import { Workbook, Worksheet } from 'exceljs';
import saveAs from 'file-saver';
import { exportDataGrid } from 'devextreme/excel_exporter';
import { DxDataGridComponent } from 'devextreme-angular';

export class MaintPrintService {

   exportExcel(title: string, grid: DxDataGridComponent, groupTemplateName: string) {
    const fileName = prompt("Please enter a filename:", title);
  
    if (!fileName) {
      if (fileName === '') alert("Filename is required. Please try again!");
      return;
    }
  
    const workbook = new Workbook();
    const worksheet = workbook.addWorksheet(title);
  
    const mergeInfo: Record<string, { start: string | null, end: string | null; prevValue: any }> = {};
  
    return exportDataGrid({
      component: grid.instance,
      worksheet,
      autoFilterEnabled: true,
      customizeCell(options) {
        const { gridCell, excelCell } = options;
        if (gridCell?.rowType === 'data') {
          const dataField = gridCell.column?.dataField;
          const cellTemplate = gridCell.column?.cellTemplate;
          const isGroupTemplate = typeof cellTemplate === 'function' && cellTemplate.name === groupTemplateName;
          if (dataField && isGroupTemplate) {
            const dataField = gridCell.column!.dataField!;
            const cellInfo = mergeInfo[dataField] || { start: null, end: null, prevValue: null };
  
            if (cellInfo.prevValue === gridCell.value) {
              cellInfo.end = excelCell.address;
            } else {
              if (cellInfo.start && cellInfo.end)
                worksheet.mergeCells(cellInfo.start, cellInfo.end);
              cellInfo.start = excelCell.address;
              cellInfo.end = null;
            }
            cellInfo.prevValue = gridCell.value;
            mergeInfo[dataField] = cellInfo;
          }
        }
      }
    }).then(() => {
      this.mergeLastInfo(mergeInfo, worksheet);
      this.saveWorkbook(workbook, fileName);
      return fileName;
    })
  }

  mergeLastInfo(mergeInfo: Record<string, { start: string | null, end: string | null; prevValue: any }>, worksheet: Worksheet) {
    Object.values(mergeInfo).forEach(({ start, end }) => {
      if (start && end) worksheet.mergeCells(start, end);
    });
  }

  saveWorkbook(workbook: Workbook, fileName: string) {
    workbook.xlsx.writeBuffer().then(buffer => {
      saveAs(new Blob([buffer], { type: 'application/octet-stream' }), `${fileName}.xlsx`);
    });
  }
}