table-export.util.js

import { csvInjectionProtector } from '~/common/utils/common.utils.js';

export async function exportTableAsXLSX(
  table,
  filename,
  applyFilters = true,
) {
  const XLSX = await import('xlsx-js-style');
  const wb = XLSX.utils.book_new();

  const lastHeaderGroup = table.getHeaderGroups().at(-1);
  if (!lastHeaderGroup) {
    console.error('No header groups found', table.getHeaderGroups());
    return;
  }

  const parent_headers = [];
  let actual_headers = [];
  let is_add_parent_headers = false;
  actual_headers = lastHeaderGroup.headers
    .filter(h => h.column.getIsVisible())
    .map((header) => {
      if (header.column.parent === undefined) {
        parent_headers.push({
          v: '',
          s: { font: { bold: true } },
        });
      }
      else {
        parent_headers.push({
          v: header.column.parent.columnDef.header,
          s: { font: { bold: true } },
        });
        is_add_parent_headers = true;
      }

      return {
        v: header.column.columnDef.header,
        s: { font: { bold: true } },
      };
    });

  const exportRows = applyFilters ? table.getFilteredRowModel().rows : table.getCoreRowModel().rows;

  const data = [];
  exportRows.forEach((row) => {
    const cells = row.getVisibleCells();
    const values = cells.map(cell => cell.getValue() ?? '');
    data.push(values);
  });
  const all_headers = [];
  if (is_add_parent_headers)
    all_headers.push(parent_headers);
  all_headers.push(actual_headers);

  for (const arr of all_headers)
    for (let j = 0; j < arr.length; j++)
      if (typeof arr[j] === 'string' || arr[j] instanceof String)
        arr[j] = csvInjectionProtector(arr[j]);
  for (const arr of data)
    for (let j = 0; j < arr.length; j++)
      if (typeof arr[j] === 'string' || arr[j] instanceof String)
        arr[j] = csvInjectionProtector(arr[j]);

  const ws = XLSX.utils.aoa_to_sheet([
    ...all_headers,
    ...data,
  ]);

  const to_merge_pairs = [];
  let current_pair = { first: null, second: null };
  for (let i = 0; i < parent_headers.length - 1; i++) {
    if (parent_headers[i].v === '') {
      if (current_pair.first !== null) {
        to_merge_pairs.push(current_pair);
        current_pair = { first: null, second: null };
      }
      continue;
    }
    if (parent_headers[i].v === parent_headers[i + 1].v) {
      if (current_pair.first === null)
        current_pair.first = i;
      current_pair.second = i + 1;
    }
    else {
      if (current_pair.first !== null)
        to_merge_pairs.push(current_pair);
      current_pair = { first: null, second: null };
    }
  }

  const mergeCell = [];
  to_merge_pairs.forEach((item) => {
    mergeCell.push({ s: { r: 0, c: item.first }, e: { r: 0, c: item.second } });
  });
  ws['!merges'] = mergeCell;

  // for csv: await wb.csv.writeBuffer();
  XLSX.utils.book_append_sheet(wb, ws, 'Sheet 1');
  XLSX.writeFile(wb, `${filename}.xlsx`);
}