CostAccountingSys/src/utils/tableToExcel.ts

/*
 * @Author: code4eat awesomedema@gmail.com
 * @Date: 2023-05-31 11:18:30
 * @LastEditors: code4eat awesomedema@gmail.com
 * @LastEditTime: 2024-09-10 10:31:52
 * @FilePath: /BudgetManaSystem/src/utils/tableToExcel.ts
 * @Description: 这是默认设置,请设置`customMade`, 打开koroFileHeader查看配置 进行设置: https://github.com/OBKoro1/koro1FileHeader/wiki/%E9%85%8D%E7%BD%AE
 */
import * as XLSX from 'xlsx';
import { saveAs } from 'file-saver';

type DataType = { [key: string]: string | number | null };

type Column = {
  title: string;
  dataIndex: string;
  key: string;
  children?: Column[];
};

type Merge = {
  s: { r: number; c: number };
  e: { r: number; c: number };
};

function getMerges(columns: Column[], startRow = 0, startCol = 0): Merge[] {
  let merges: Merge[] = [];
  let colIndex = startCol;

  for (const column of columns) {
    let endRow = startRow;
    let endCol = colIndex;

    if (column.children) {
      // 如果有子列，递归处理子列
      const childMerges = getMerges(column.children, startRow + 1, colIndex);
      merges = merges.concat(childMerges);

      // 计算结束列的位置
      endCol += column.children.length - 1;
    }

    // 添加合并项
    merges.push({ s: { r: startRow, c: colIndex }, e: { r: endRow, c: endCol } });

    // 更新列索引
    colIndex = endCol + 1;
  }

  return merges;
}

function exportTableToExcel(tableData: DataType[], columns: Column[], fileName = 'data') {
  // 获取列标题
  const columnHeaders = columns.map(column => column.title);

  // 将列标题和表格数据合并
  const allData = [columnHeaders as any].concat(
    tableData.map(row => 
      Object.values(row).map(value => 
        (typeof value === 'string' ? value : value)
      )
    )
  );

  // 获取合并项
  const merges = getMerges(columns);

  // 创建一个新的工作簿
  const wb = XLSX.utils.book_new();

  // 将数据转化为工作表
  const ws = XLSX.utils.aoa_to_sheet(allData);

  // 设置合并项
  ws['!merges'] = merges;

  // 自动计算列宽
  const colWidths = allData[0].map((col: any, index: number) => {
    const maxLength = allData.reduce((max, row) => {
      const cellValue = row[index] ? row[index].toString() : '';
      return Math.max(max, cellValue.length);
    }, 0);
    return { wch: maxLength + 2 }; // 加上额外的2个字符的宽度来提供一些空间
  });

  ws['!cols'] = colWidths;  // 设置工作表的列宽

  // 将工作表添加到工作簿中
  XLSX.utils.book_append_sheet(wb, ws, 'Sheet1');

  // 将工作簿转化为blob
  const wbout = XLSX.write(wb, { bookType: 'xlsx', type: 'array' });
  const blob = new Blob([wbout], { type: 'application/octet-stream' });

  // 使用file-saver保存文件
  saveAs(blob, `${fileName}.xlsx`);
}



export default exportTableToExcel;