我是天元,立志做1000个有趣的项目的前端。
顺便吆喝一声,技术大厂,内推捞人,前/后端or测试←感兴趣
要求学历:全日制统招本科(非学院派即可):
--加班偶尔较多,但周末加班两倍工资。
--15-35K,工资在一线城市属于一般,但二线城市很可以。
背景我老婆从事HR的工作,公司有很多连锁店,她需要将所有的门店的工资汇总计算,然后再拆分给各门店请确认,最后再提供给财务发工资。 随着门店数量渐渐增多,渐渐的我老婆已经不堪重负,每天加班都做不完,严重影响夫妻感情生活。 最终花费了2天的时间,完成了整个node程序,她只需要传入工资表,相应的各种表格在10s内自动输出。目前已正式交付,得到了每月零花钱提高100元的重大成果。 整体需求- 表格的导入和识别
- 表格的计算(计算公式要代入),表格样式正确
- 最终结果按照门店拆分为工资表
需求示例(删减版)![]()
需求为,根据传入的基本工资及补发补扣,生成总工资表,门店工资表,财务工资表发放表。
工资表中字段为门店,姓名,基本工资,补发补扣,最终工资(基本工资+补发补扣)。最后一行为总计
门店工资表按照每个门店,单独一个表格,字段同工资表。最后一行为总计 工资表![]()
基础工资![]()
补发补扣![]()
技术选型这次的主力库为exceljs,官方文档介绍如下 读取,操作并写入电子表格数据和样式到 XLSX 和 JSON 文件。 一个 Excel 电子表格文件逆向工程项目
选择exceljs是因为它支持完整的excel的样式及公式。 安装及目录结构优先安装exceljs npm inityarn add exceljs创建input,out,src三个文件夹,src放入index.js ![]()
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`);})最终效果![]()
![]()
相关代码地址github.com/tinlee/1000…
|