用node帮老婆做excel工资表

1,008 阅读5分钟

我是天元,立志做1000个有趣的项目的前端。如果你喜欢的话,请点赞,收藏,转发。评论领取零花钱+100勋章

背景

我老婆从事HR的工作,公司有很多连锁店,她需要将所有的门店的工资汇总计算,然后再拆分给各门店请确认,最后再提供给财务发工资。

随着门店数量渐渐增多,渐渐的我老婆已经不堪重负,每天加班都做不完,严重影响夫妻感情生活。

最终花费了2天的时间,完成了整个node程序,她只需要传入工资表,相应的各种表格在10s内自动输出。目前已正式交付,得到了每月零花钱提高100元的重大成果

整体需求

  • 表格的导入和识别
  • 表格的计算(计算公式要代入),表格样式正确
  • 最终结果按照门店拆分为工资表

需求示例(删减版)

image.png
需求为,根据传入的基本工资及补发补扣,生成总工资表,门店工资表,财务工资表发放表。

工资表中字段为门店,姓名,基本工资,补发补扣,最终工资(基本工资+补发补扣)。最后一行为总计
门店工资表按照每个门店,单独一个表格,字段同工资表。最后一行为总计

工资表

image.png

基础工资

image.png

补发补扣

image.png

技术选型

这次的主力库为exceljs,官方文档介绍如下

读取,操作并写入电子表格数据和样式到 XLSX 和 JSON 文件。

一个 Excel 电子表格文件逆向工程项目

选择exceljs是因为它支持完整的excel的样式及公式。

安装及目录结构

优先安装exceljs

npm init
yarn add exceljs

创建input,out,src三个文件夹,src放入index.js
image.png

package.json增加start脚本

"scripts": {
    "start": "node src/index.js"
  },

代码相关

导入

通过new Excel.Workbook();创建一个工作簿,通过workbook.xlsx.readFile来导入文件, 注意这是个promise

const ExcelJS = require("exceljs");
const path = require("path");
const inputPath = path.resolve(__dirname, "../input");
const outputPath = path.resolve(__dirname, "../out");

const loadInput =async () => {
  const workbook = new ExcelJS.Workbook();
  const inputFile = await workbook.xlsx.readFile(inputPath + "/工资表.xlsx")
};

loadInput()

数据拆分

通过getWorksheetApi,我们可以获取到对应的工作表的内容

  const loadInput =async () => {
	...
  // 基本工资
  const baseSalarySheet = inputFile.getWorksheet("基本工资");
  // 补发补扣
  const supplementSheet = inputFile.getWorksheet("补发补扣");

}

然后我们需要进一步的来进行拆分,因为第一行为每个工作表的头,这部分在我们实际数据处理中不会使用,所以通过getRows来获取实际的内容。

  const baseSalaryContent = baseSalarySheet.getRows(
    2,
    baseSalarySheet.rowCount
  );
  baseSalaryContent.map((row) => {
    console.log(row.values);
  });

  /**
[ <1 empty item>, '2024-02', '海贼王', '路飞', 12000 ]
[ <1 empty item>, '2024-02', '海贼王', '山治', 8000 ]
[ <1 empty item>, '2024-02', '火影忍者', '鸣人', '6000' ]
[ <1 empty item>, '2024-02', '火影忍者', '佐助', 7000 ]
[ <1 empty item>, '2024-02', '火影忍者', '雏田', 5000 ]
[ <1 empty item>, '2024-02', '一拳超人', '琦玉', 4000 ]
[]
[]
**/

可以看到实际的内容已经拿到了,我们要根据这些内容拼装一下最终便于后续的调用。
我们可以通过 row.getCellApi获取到对应某一列的内容,例如门店是在B列,那么我们就可以使用row.getCell('B')来获取。
因为我们需要拆分门店,所以这里的基本工资,我们以门店为单位,把数据进行拆分


  const baseSalary = {};

  baseSalaryContent.forEach((row) => {
    const shopName = row.getCell("B").value;
    if (!shopName) return; // 过滤空行

    const name = row.getCell("C").value;
    const salary = row.getCell("D").value;

    if (!baseSalary[shopName]) {
      baseSalary[shopName] = [];
    }
    baseSalary[shopName].push({
      name,
      salary,
    });
  });

这样我们得到了一个以门店名称为key的对象,value为该门店的员工信息数组。利用相同方法,获取补发补扣。因为每个人已经确定了门店,所以后续只需要根据姓名来做key,拆分成一个object即可

  // 补发补扣
  const supplement = {};
  supplementSheet.getRows(2, supplementSheet.rowCount).forEach((row) => {
    const name = row.getCell("C").value;
    const type = row.getCell("H").value;

    let count = row.getCell("D").value;
    // 如果为补扣,则金额为负数
    if (type === "补扣") {
      count = -count; 
    }
    if (!supplement[name]) {
      supplement[name] = 0;
    }
    supplement[name] += count;
  });

  

数据组合

门店工资表

因为每个门店需要独立一张表,所以需要遍历baseSalary


  
  Object.keys(baseSalary).forEach((shopName) => {
    const workbook = new ExcelJS.Workbook();
    const worksheet = workbook.addWorksheet("工资表");

    // 添加表头
    worksheet.addRow([
      "序号",
      "门店",
      "姓名",
      "基本工资",
      "补发补扣",
      "最终工资",
    ]);
    baseSalary[shopName].forEach((employee, index) => {
      worksheet.addRow([
        index + 1,
        shopName,
        employee.name,
        +employee.salary,
        supplement[employee.name] || 0,
        +employee.salary + (supplement[employee.name] || 0),
      ]);
    });
  });

此时你也可以快进到表格输出来查看输出的结果,以便随时调整

这样我们就把基本工资已经写入工作表了,但是这里存在问题,最终工资使用的是一个数值,而没有公式。所以我们需要改动下

 worksheet.addRow([        index + 1,        shopName,        employee.name,        employee.salary,        supplement[employee.name] || 0,
        {
          formula: `D${index + 2}+E${index + 2}`,
          result: employee.salary + (supplement[employee.name] || 0),
        },
      ]);

这里的formula将对应到公式,而result是显示的值,这个值是必须写入的,如果你写入了错误的值,会在表格中显示该值,但是双击后,公式重新计算,会替换为新的值。所以这里必须计算正确

合计

依照上方的逻辑,继续添加一行作为合计,但是之前计算的时候,需要添加一个临时变量,记录下合计的相关内容。

     const count = [0, 0, 0];
    baseSalary[shopName].forEach((employee, index) => {
      count[0] += +employee.salary;
      count[1] += supplement[employee.name] || 0;
      count[2] += +employee.salary + (supplement[employee.name] || 0);
      worksheet.addRow([
        index + 1,
        shopName,
        employee.name,
        +employee.salary,
        supplement[employee.name] || 0,
        {
          formula: `D${index + 2}+E${index + 2}`,
          result: +employee.salary + (supplement[employee.name] || 0),
        },
      ]);
    });

然后在尾部添加一行

worksheet.addRow([      "合计",      "",      "",      {        formula: `SUM(D2:D${baseSalary[shopName].length + 1})`,
        result: count[0],
      },
      {
        formula: `SUM(E2:E${baseSalary[shopName].length + 1})`,
        result: count[1],
      },
      {
        formula: `SUM(F2:F${baseSalary[shopName].length + 1})`,
        result: count[2],
      },
    ]);

美化

表格的合并,可以使用mergeCells

   worksheet.mergeCells(
      `A${baseSalary[shopName].length + 2}:C${baseSalary[shopName].length + 2}`
    );

这样就合并了我们的最后一行的前三列,接下来我们要给表格添加线条。
对于批量的添加,可以直接使用addConditionalFormatting,它将在一个符合条件的单元格范围内添加规则


    worksheet.addConditionalFormatting({
      ref: `A1:F${baseSalary[shopName].length + 2}`,
      rules: [
        {
          type: "expression",
          formulae: ["true"],
          style: {
            border: {
              top: { style: "thin" },
              left: { style: "thin" },
              bottom: { style: "thin" },
              right: { style: "thin" },
            },
            alignment: { vertical: "top", horizontal: "left", wrapText: true },
          },
        },
      ],
    });

表格输出

现在门店工资表已经拆分完成,我们可以直接保存了,使用xlsx.writeFileApi来保存文件

 Object.keys(baseSalary).forEach((shopName) => {
 
	...

	  workbook.xlsx.writeFile(outputPath + `/${shopName}工资表.xlsx`);
})

最终效果

image.png

image.png

相关代码地址

github.com/tinlee/1000…