xlxs-js-style 2023 带样式导出excel

import * as XLSX from 'xlsx';
import * as XLSXD from 'xlsx-js-style';
import { keys } from 'lodash';
import type { CellStyle } from 'xlsx-js-style';
import type { WorkSheet } from 'xlsx';
import { Range } from 'xlsx';
export interface xlsxType {
  jsonData: Record<string, any>[];
  alias: Record<string, any>;
  fileName: string;
}
const topStyle: CellStyle = {
  alignment: { horizontal: 'center', vertical: 'center' },
  font: {
    name: '宋体',
    sz: 14,
    bold: true,
    color: { rgb: '#000' },
  },
  border: {
    bottom: { style: 'thin', color: { rgb: '#000' } },
    left: { style: 'thin', color: { rgb: '#000' } },
    top: { style: 'thin', color: { rgb: '#000' } },
    right: { style: 'thin', color: { rgb: '#000' } },
  },
};
const titleStyle: CellStyle = {
  alignment: { horizontal: 'left', vertical: 'center' },
  font: {
    name: '宋体',
    sz: 14,
    bold: true,
    color: { rgb: '#000' },
  },
  border: {
    bottom: { style: 'thin', color: { rgb: '#000' } },
    left: { style: 'thin', color: { rgb: '#000' } },
    top: { style: 'thin', color: { rgb: '#000' } },
    right: { style: 'thin', color: { rgb: '#000' } },
  },
};
const contentStyle: CellStyle = {
  alignment: { horizontal: 'left', vertical: 'center' },
  font: {
    name: '宋体',
    sz: 12,
    bold: false,
    color: { rgb: '#000' },
  },
  border: {
    bottom: { style: 'thin', color: { rgb: '#000' } },
    left: { style: 'thin', color: { rgb: '#000' } },
    top: { style: 'thin', color: { rgb: '#000' } },
    right: { style: 'thin', color: { rgb: '#000' } },
  },
};

const addContentStyle = (ws) => {
  keys(ws).forEach((key) => {
    if (!key.startsWith('!') && !ws[key].s) {
      ws[key] = {
        ...ws[key],
        s: contentStyle,
      };
    }
  });
};
// 数据源 [{c1:1}]   列明['第一列']
export const downloadXlsxFormJson = ({ jsonData, alias, fileName }: xlsxType) => {
  /* 新建空的工作表 */
  const wb = XLSX.utils.book_new();
  // 将JS数据数组转换为工作表。
  jsonData.unshift(alias);
  const ws = XLSX.utils.json_to_sheet(jsonData, { skipHeader: true });
  ws['!cols'] = [];
  keys(alias).forEach((item, index) => {
    const ch = `${String.fromCharCode(index + 65)}1`;
    ws[ch] = {
      ...ws[ch],
      s: titleStyle,
    };
    ws['!cols'].push({ width: 25 });
  });

  addContentStyle(ws);
  // 可以自定义下载之后的sheetname
  XLSX.utils.book_append_sheet(wb, ws, fileName);

  /* 生成xlsx文件 */
  XLSXD.writeFile(wb, `${fileName}.xlsx`);
};

export const downloadXlsxFormJsonExt = ({ jsonData, alias, fileName }: xlsxType) => {
  /* 新建空的工作表 */
  const wb = XLSX.utils.book_new();
  // 将JS数据数组转换为工作表。
  jsonData.unshift(alias);
  const ws: WorkSheet = XLSX.utils.json_to_sheet([], { skipHeader: true });
  XLSX.utils.sheet_add_json(ws, jsonData, { origin: 'A2', skipHeader: true });
  const toLength = keys(alias).length;
  ws['!cols'] = [];
  ws['!merges'] = [{ s: { c: 0, r: 0 }, e: { c: toLength - 1, r: 0 } }];
  ws.A1 = {
    v: fileName,
    s: topStyle,
  };
  keys(alias).forEach((item, index) => {
    const ch = `${String.fromCharCode(index + 65)}2`;
    ws[ch] = {
      ...ws[ch],
      s: titleStyle,
    };
    ws['!cols'].push({ width: 25 });
  });
  addContentStyle(ws);
  // 可以自定义下载之后的sheetname
  XLSX.utils.book_append_sheet(wb, ws, fileName);

  /* 生成xlsx文件 */
  XLSXD.writeFile(wb, `${fileName}.xlsx`);
};
export const helpAliasData = (searchProp) => {
  const alias = {};
  searchProp.forEach((item: any) => {
    alias[item.dataKey] = item.title;
  });
  return alias;
};
export const helpJsonData = (alias, exportData) => {
  exportData = exportData.map((item) => {
    const res = {};
    keys(alias).forEach((key) => {
      res[key] = item[key];
    });
    return res;
  });
  return exportData;
};
const test = () => {
  const jsonData = [
    { name: '张三', age: 18, sex: '男' },
    { name: '李四', age: 21, sex: '男' },
    { name: '王五', age: 45, sex: '男' },
  ];
  const alias = { name: '姓名', age: '年龄', sex: '性别' };
  downloadXlsxFormJson({ jsonData, alias, fileName: '下载测试' });
};