yunhaifinance/backend/scripts/generate-excel-template.js

const ExcelJS = require('exceljs');
const path = require('path');
const db = require('../db');

async function generateTemplate() {
  const workbook = new ExcelJS.Workbook();
  workbook.creator = '公司财务管理系统';
  workbook.created = new Date();

  const projectResult = await db.query('SELECT name FROM projects ORDER BY name');
  const projectNames = projectResult.rows.map(r => r.name);

  const categoryResult = await db.query('SELECT * FROM expense_categories WHERE is_active = true ORDER BY category_level1, sort_order');
  const categories = categoryResult.rows;

  const incomeCategories = categories.filter(c => c.category_level1 === 'income').map(c => c.label);
  const projectCategories = categories.filter(c => c.category_level1 === 'project').map(c => c.label);
  const companyCategories = categories.filter(c => c.category_level1 === 'company').map(c => c.label);

  const MAX_PROJECTS = 100;
  const MAX_CATEGORIES = 20;

  // ========== Sheet2: 参考数据 ==========
  const sheet2 = workbook.addWorksheet('参考数据', {
    properties: { tabColor: { argb: '4472C4' } }
  });

  // A列: 项目名称（预留100行，用户可在此添加新项目）
  sheet2.getColumn(1).width = 40;
  sheet2.getCell('A1').value = '项目名称（可在此添加新项目）';
  sheet2.getCell('A1').font = { bold: true, size: 11, color: { argb: 'FFFFFF' } };
  sheet2.getCell('A1').fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: '4472C4' } };
  sheet2.getCell('A1').alignment = { horizontal: 'center' };

  projectNames.forEach((name, i) => {
    const cell = sheet2.getCell(`A${i + 2}`);
    cell.value = name;
    cell.border = { bottom: { style: 'thin', color: { argb: 'D9D9D9' } } };
  });
  for (let i = projectNames.length; i < MAX_PROJECTS; i++) {
    sheet2.getCell(`A${i + 2}`).border = { bottom: { style: 'thin', color: { argb: 'E8E8E8' } } };
  }

  // C列: 收入分类
  sheet2.getColumn(3).width = 18;
  sheet2.getCell('C1').value = '收入';
  sheet2.getCell('C1').font = { bold: true, size: 11, color: { argb: 'FFFFFF' } };
  sheet2.getCell('C1').fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: '52C41A' } };
  sheet2.getCell('C1').alignment = { horizontal: 'center' };
  incomeCategories.forEach((v, i) => {
    sheet2.getCell(`C${i + 2}`).value = v;
    sheet2.getCell(`C${i + 2}`).border = { bottom: { style: 'thin', color: { argb: 'D9D9D9' } } };
  });

  // D列: 项目支出分类
  sheet2.getColumn(4).width = 18;
  sheet2.getCell('D1').value = '项目支出';
  sheet2.getCell('D1').font = { bold: true, size: 11, color: { argb: 'FFFFFF' } };
  sheet2.getCell('D1').fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: '1890FF' } };
  sheet2.getCell('D1').alignment = { horizontal: 'center' };
  projectCategories.forEach((v, i) => {
    sheet2.getCell(`D${i + 2}`).value = v;
    sheet2.getCell(`D${i + 2}`).border = { bottom: { style: 'thin', color: { argb: 'D9D9D9' } } };
  });

  // E列: 公司支出分类
  sheet2.getColumn(5).width = 18;
  sheet2.getCell('E1').value = '公司支出';
  sheet2.getCell('E1').font = { bold: true, size: 11, color: { argb: 'FFFFFF' } };
  sheet2.getCell('E1').fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FA8C16' } };
  sheet2.getCell('E1').alignment = { horizontal: 'center' };
  companyCategories.forEach((v, i) => {
    sheet2.getCell(`E${i + 2}`).value = v;
    sheet2.getCell(`E${i + 2}`).border = { bottom: { style: 'thin', color: { argb: 'D9D9D9' } } };
  });

  // G列: 币种
  sheet2.getColumn(7).width = 10;
  sheet2.getCell('G1').value = '币种';
  sheet2.getCell('G1').font = { bold: true };
  ['CNY', 'LAK', 'USD', 'THB'].forEach((v, i) => { sheet2.getCell(`G${i + 2}`).value = v; });

  // H列: 对方类型
  sheet2.getColumn(8).width = 12;
  sheet2.getCell('H1').value = '对方类型';
  sheet2.getCell('H1').font = { bold: true };
  ['供应商', '分包商', '客户', '员工', '物流公司', '股东', '其他'].forEach((v, i) => { sheet2.getCell(`H${i + 2}`).value = v; });

  // 定义命名范围（用于 INDIRECT 联动下拉）
  // ExcelJS 不直接支持 named ranges，我们通过 workbook.definedNames 添加
  // 格式: 'SheetName!$Col$Start:$Col$End'

  // 收入分类命名范围
  workbook.definedNames.add(`参考数据!$C$2:$C$${incomeCategories.length + 1}`, '收入');
  // 项目支出分类命名范围
  workbook.definedNames.add(`参考数据!$D$2:$D$${projectCategories.length + 1}`, '项目支出');
  // 公司支出分类命名范围
  workbook.definedNames.add(`参考数据!$E$2:$E$${companyCategories.length + 1}`, '公司支出');

  // ========== Sheet1: 记账主表 ==========
  const sheet1 = workbook.addWorksheet('财务记账', {
    properties: { tabColor: { argb: '52C41A' } }
  });

  const headers = [
    { key: 'date', header: '日期', width: 13 },
    { key: 'txn_type', header: '收支类型', width: 11 },
    { key: 'level1', header: '一级分类', width: 11 },
    { key: 'level2', header: '二级分类', width: 14 },
    { key: 'project', header: '项目名称', width: 35 },
    { key: 'amount', header: '金额', width: 14 },
    { key: 'currency', header: '币种', width: 8 },
    { key: 'rate', header: '汇率', width: 10 },
    { key: 'amount_cny', header: '等效人民币', width: 14 },
    { key: 'counterparty', header: '对方名称', width: 18 },
    { key: 'counterparty_type', header: '对方类型', width: 10 },
    { key: 'user', header: '人员姓名', width: 10 },
    { key: 'desc', header: '描述', width: 30 },
    { key: 'voucher', header: '凭证编号', width: 14 },
  ];

  sheet1.columns = headers;

  const headerRow = sheet1.getRow(1);
  headerRow.height = 28;
  headerRow.eachCell((cell) => {
    cell.font = { bold: true, size: 11, color: { argb: 'FFFFFF' } };
    cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: '4472C4' } };
    cell.alignment = { horizontal: 'center', vertical: 'middle' };
    cell.border = {
      top: { style: 'thin' }, bottom: { style: 'thin' },
      left: { style: 'thin' }, right: { style: 'thin' }
    };
  });

  sheet1.views = [{ state: 'frozen', ySplit: 1 }];

  const dataRows = 500;
  for (let i = 2; i <= dataRows + 1; i++) {
    const row = sheet1.getRow(i);

    const cnyCell = row.getCell(9);
    cnyCell.value = { formula: `F${i}*H${i}` };
    cnyCell.numFmt = '#,##0.00';
    cnyCell.protection = { locked: true };

    row.getCell(6).numFmt = '#,##0.00';
    row.getCell(8).numFmt = '0.000000';
    row.getCell(1).numFmt = 'YYYY-MM-DD';

    if (i % 2 === 0) {
      row.eachCell({ includeEmpty: true }, (cell) => {
        cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'F2F7FB' } };
      });
    }

    row.eachCell({ includeEmpty: true }, (cell) => {
      cell.border = {
        bottom: { style: 'thin', color: { argb: 'D9D9D9' } },
        vertical: { style: 'thin', color: { argb: 'D9D9D9' } }
      };
    });
  }

  for (let i = 2; i <= dataRows + 1; i++) {
    // B列: 收支类型
    sheet1.getCell(`B${i}`).dataValidation = {
      type: 'list',
      allowBlank: true,
      formulae: ['"收入,支出"'],
      showErrorMessage: true,
      errorTitle: '输入错误',
      error: '请选择：收入 或 支出'
    };

    // C列: 一级分类
    sheet1.getCell(`C${i}`).dataValidation = {
      type: 'list',
      allowBlank: true,
      formulae: ['"收入,项目支出,公司支出"'],
      showErrorMessage: true,
      errorTitle: '输入错误',
      error: '请选择：收入 / 项目支出 / 公司支出'
    };

    // D列: 二级分类（联动！根据C列一级分类的值，用INDIRECT引用对应命名范围）
    sheet1.getCell(`D${i}`).dataValidation = {
      type: 'list',
      allowBlank: true,
      formulae: [`INDIRECT(C${i})`],
      showErrorMessage: true,
      errorTitle: '输入错误',
      error: '请先选择一级分类，再选择对应的二级分类'
    };

    // E列: 项目名称（从Sheet2引用，预留100行可扩展）
    sheet1.getCell(`E${i}`).dataValidation = {
      type: 'list',
      allowBlank: true,
      formulae: [`参考数据!$A$2:$A$${MAX_PROJECTS + 1}`],
      showErrorMessage: false
    };

    // G列: 币种
    sheet1.getCell(`G${i}`).dataValidation = {
      type: 'list',
      allowBlank: true,
      formulae: ['"CNY,LAK,USD,THB"'],
      showErrorMessage: true,
      errorTitle: '输入错误',
      error: '请选择：CNY / LAK / USD / THB'
    };

    // K列: 对方类型
    sheet1.getCell(`K${i}`).dataValidation = {
      type: 'list',
      allowBlank: true,
      formulae: ['"供应商,分包商,客户,员工,物流公司,股东,其他"'],
      showErrorMessage: true,
      errorTitle: '输入错误',
      error: '请从下拉列表中选择'
    };

    // A列: 日期验证
    sheet1.getCell(`A${i}`).dataValidation = {
      type: 'date',
      allowBlank: true,
      operator: 'greaterThan',
      formulae: [new Date(2020, 0, 1)],
      showErrorMessage: true,
      errorTitle: '日期格式错误',
      error: '请输入有效日期'
    };
  }

  // 示例数据
  const exampleRow = sheet1.getRow(2);
  exampleRow.getCell(1).value = new Date(2026, 2, 15);
  exampleRow.getCell(2).value = '支出';
  exampleRow.getCell(3).value = '项目支出';
  exampleRow.getCell(4).value = '材料采购';
  exampleRow.getCell(5).value = projectNames[0] || '';
  exampleRow.getCell(6).value = 150000;
  exampleRow.getCell(7).value = 'CNY';
  exampleRow.getCell(8).value = 1;
  exampleRow.getCell(10).value = '云南XX电力器材有限公司';
  exampleRow.getCell(11).value = '供应商';
  exampleRow.getCell(13).value = '采购190×12m电杆50根';

  const exampleRow2 = sheet1.getRow(3);
  exampleRow2.getCell(1).value = new Date(2026, 2, 20);
  exampleRow2.getCell(2).value = '支出';
  exampleRow2.getCell(3).value = '项目支出';
  exampleRow2.getCell(4).value = '食宿费用';
  exampleRow2.getCell(5).value = projectNames[0] || '';
  exampleRow2.getCell(6).value = 100000;
  exampleRow2.getCell(7).value = 'LAK';
  exampleRow2.getCell(8).value = 0.0003;
  exampleRow2.getCell(10).value = 'XX招待所';
  exampleRow2.getCell(11).value = '其他';
  exampleRow2.getCell(12).value = '李四';
  exampleRow2.getCell(13).value = '3月驻现场住宿费';

  const exampleRow3 = sheet1.getRow(4);
  exampleRow3.getCell(1).value = new Date(2026, 1, 20);
  exampleRow3.getCell(2).value = '收入';
  exampleRow3.getCell(3).value = '收入';
  exampleRow3.getCell(4).value = '项目合同收款';
  exampleRow3.getCell(5).value = projectNames[0] || '';
  exampleRow3.getCell(6).value = 300000;
  exampleRow3.getCell(7).value = 'CNY';
  exampleRow3.getCell(8).value = 1;
  exampleRow3.getCell(10).value = '老挝国家电力公司';
  exampleRow3.getCell(11).value = '客户';
  exampleRow3.getCell(13).value = '合同首付款30%';

  const exampleRow4 = sheet1.getRow(5);
  exampleRow4.getCell(1).value = new Date(2026, 3, 1);
  exampleRow4.getCell(2).value = '支出';
  exampleRow4.getCell(3).value = '公司支出';
  exampleRow4.getCell(4).value = '工资薪酬';
  exampleRow4.getCell(6).value = 25000;
  exampleRow4.getCell(7).value = 'CNY';
  exampleRow4.getCell(8).value = 1;
  exampleRow4.getCell(12).value = '赵六';
  exampleRow4.getCell(13).value = '3月份办公室人员工资';

  // 保护工作表
  sheet1.protect('', {
    selectLockedCells: true,
    selectUnlockedCells: true,
    formatCells: true,
    formatColumns: true,
    formatRows: true,
    insertRows: true,
    insertColumns: false,
    deleteRows: false,
    deleteColumns: false,
    sort: true,
    autoFilter: true,
  });

  for (let i = 2; i <= dataRows + 1; i++) {
    [1,2,3,4,5,6,7,8,10,11,12,13,14].forEach(col => {
      sheet1.getRow(i).getCell(col).protection = { locked: false };
    });
  }

  const outputPath = path.join(__dirname, '..', 'public', 'templates', '财务记账导入模板.xlsx');
  const fs = require('fs');
  fs.mkdirSync(path.dirname(outputPath), { recursive: true });

  await workbook.xlsx.writeFile(outputPath);
  console.log('✅ Excel模板已生成:', outputPath);
  return outputPath;
}

generateTemplate().then(() => process.exit(0)).catch(e => { console.error(e); process.exit(1); });