专栏首页全栈修仙之路Node.js 小打小闹之Excel解析

Node.js 小打小闹之Excel解析

近期公司开始做绩效,某一天上午 HR 妹纸发了份 Excel 模板过来,让我下发给小组内成员填写。当天下午下班前,组内的绩效表就收齐了,接下来我就开始进入下一个环节,逐一打开每个 Excel 表为每个人打分。由于只有十几份绩效表,所以很快就打完分了。 不过问题来了,虽然已经打完分了,但我对小组内成员的每个考核项得分和总分的情况却还是一片空白。想要一目了然,当然得简单做个统计报表咯。那么如何收集每个人的数据呢?当然最简单的方式就一个个打开组内成员的绩效表,然后一个个统计,不过对于这种方式,我是拒绝的。作为一个小小的程序猿,还是得施展一下雕虫小技 —— “自动提取数据”。

现在基于目前的需求,我们来梳理一下流程:

  1. 读取某个目录下绩效文件列表;
  2. 读取某个绩效文件;
  3. 解析文件并提取相关信息;
  4. 保存每份文件的数据并输出。

既然流程已基本清楚,下面就要撸起柚子加油干咯,工具当然就是选用我们前端的神器 —— Node.js,嘿嘿,不然就挂羊头卖狗肉了(Node.js 小打小闹系列)。

在上面流程中,Excel 解析是核心的步骤,因此我们先来完成 “选酷” 环节。经过一番筛选,我们最终选定了 js-xlsx 这个库。

js-xlsx 简介

在介绍这个库之前,我们先来介绍 Excel 的一些相关概念。

  • workbook 对象:整个 Excel 文档,使用 js-xlsx 读取 Excel 文档之后就会获得 workbook 对象。
  • worksheet 对象:Excel 文档中的表,一份 Excel 文档中可以包含很多表,而每张表对应的就是 worksheet 对象。
  • cell 对象:worksheet 中的单元格,一个单元格就是一个 cell 对象。

它们之间的关系如下:

// workbook
{
    SheetNames: ["sheet1", "sheet2"],
    Sheets: {
        // worksheet
        "sheet1": {
            // cell
            "A1": { ... },
            // cell
            "A2": { ... },
            ...
        },
        // worksheet
        "sheet2": {
            // cell
            "A1": { ... },
            // cell
            "A2": { ... },
            ...
        }
    }
}

了解完 Excel 的基本概念,我们来继续介绍一下 js-xlsx 这个库的基本用法:

  • 使用 XLSX.readFile() 读取 Excel 文件
import XLSX from "xlsx";
const workbook = XLSX.readFile(fileName, {});
  • 通过 workbook.Sheets[SHEET_NAME] 获取 SHEET_NAME 对应的表名
const REPORT_SHEET_NAME = "员工绩效";
const worksheet = workbook.Sheets[REPORT_SHEET_NAME];
  • 按需进行数据读取和处理
// 获取 A1 单元格对象
let a1 = worksheet["A1"]; // 返回 { v: "hello", t: "s", ... }
// 获取 A1 中的值
a1.v // 返回 "hello"
// 获取表的有效范围
worksheet["!ref"] // 返回 "A1:B20"
worksheet["!range"] // 返回 range 对象,{ s: { r: 0, c: 0}, e: { r: 100, c: 2 } }
// 获取合并过的单元格
worksheet["!merges"] // 返回一个包含 range 对象的列表,[ {s: { r: 0, c: 0 }, c: { r: 2, c: 1 } } ]

此外通过查看文档,我们发现 XLSX.utils 为我们提供了一系列有用的工具方法,比如:

  • XLSX.utils.sheet_to_csv —— 生成 CSV 文件。
  • XLSX.utils.sheet_to_txt —— 生成 UTF16 格式的文本。
  • XLSX.utils.sheet_to_html —— 生成 HTML 文件。
  • XLSX.utils.sheet_to_json —— 生成 JSON 格式的数组对象。

当看到 XLSX.utils.sheet_to_json 这个函数时,感觉 Excel 解析的事情都完成一半了。时机已经成熟了,现在我们马上进入实战环节。

js-xlsx 实战

在开始动手前,我们先来看分析一下 Excel 绩效表,具体如下图所示:

图中,红色框用于表示 Excel 表名,青色框用于表示需要提取的数据区域,需要提取的内容,主要有:姓名、考核月份、常规工作得分、重点工作得分、工作态度与能力得分及总得分等。

const REPORT_SHEET_NAME = "员工绩效";

const workbook = XLSX.readFile(file, {});
const worksheet = workbook.Sheets[REPORT_SHEET_NAME];
const sheetJSONArray = XLSX.utils.sheet_to_json(worksheet);

通过上面的代码,我们就能把 “员工绩效” 表单转换为 JSON 对象,输出的结果如下:

[ { "员工绩效考核表": "姓名",
    __EMPTY: "黄意涵",
    __EMPTY_1: "部门",
    __EMPTY_2: "前端",
    __EMPTY_4: "评分主管",
    __EMPTY_6: "考核月份",
    __EMPTY_7: "2018-07" },
  ...
  { "员工绩效考核表": "合计", __EMPTY_6: "11.72 " },
  { __EMPTY: "合计", __EMPTY_6: "12.41 " },
  { "员工绩效考核表": "合计", __EMPTY_2: "17.00 ", __EMPTY_4: "合计" },
  { "员工绩效考核表": "总得分", __EMPTY_2: "41.14 ", __EMPTY_5: "被考核人签字" } 
]

通过观察以上的输出信息,我们发现了数据的共同特点,比如:

"姓名", __EMPTY: "黄意涵",
"考核月份", __EMPTY_7: "2018-07"
"合计", __EMPTY_6: "11.72 "
"总得分", __EMPTY_2: "41.14 "

因此要想提取相应的信息,我们可以定义以下正则表达式:

const DATA_REG = /("姓名"|"考核月份"|"合计"|"总得分"),\s*"__EMPTY_?\d?":\s*"([^"]+)/g;

基于上面的表达式,我们可以抽取一个通用的函数来解析 Excel Sheet 表中的数据:

function parseSheetData(jsonArray) {
    let jsonArrayStr, values = [], matches;
    try {
        jsonArrayStr = JSON.stringify(jsonArray);
        while (matches = DATA_REG.exec(jsonArrayStr)) {
            if (matches && matches.length > 2) values.push(matches[2]);
        }
    } catch (error) {
        console.error("SheetData序列化失败");
    }
    console.log(values);
}

运行 parseSheetData 函数后,将会输出以下信息:

[ '黄意涵', '2018-07', '11.72 ', '12.41 ', '17.00 ', '41.14 ' ]

获取完每个 Excel 表的信息后,我们再来为每一个项定义一个对应的键:

const DATA_KEYS = ["name", "month", "normalWork", "importantWork", "others", "total"];

接下来我们可以利用 lodashzipObject() 方法生成相应的对象:

{ name: '黄意涵',  month: '2018-07',  normalWork: '11.72',  importantWork: '12.41',  others: '17.00',  total: '41.14' }

此时,我们已经知道如何解析单个 Excel 文件,后面我们要做的事情就是获取某个目录下的所有绩效表,然后逐个进行解析,然后把解析的数据保存到数组中,最终我们就能获取团队成员整个月份的绩效数据了。

数据已经收集完成了,那下一步要做什么呢?嘿嘿,那还用说,当然是做数据展示了,其实还有一个更加高大尚的名字 —— 数据可视化。目前数据可视化,也是一个比较热门的领域,涉及的知识也很多。

最后我还简单设计了一个报表页,期间用到了以下几个库,感兴趣的同学可以了解一下:

  • antvis/f2 —— F2 移动端可视化解决方案
  • odometer —— 实现平滑的数字动画效果
  • canvas-nest.js —— 一个基于 html5 canvas 绘制的网页背景效果,非常赞!

Excel 解析器

Excel 绩效表的解析器实现如下,有兴趣的同学可以参考一下:

import * as fs from 'fs-extra';
import * as path from 'path';
import * as zipObject from 'lodash/zipObject';
import * as XLSX from 'xlsx';

class XlsParser {
  filePaths: string[] = [];
  result: any[] = [];

  async parse(rootPath: string) {
    await this.collectFilePaths(rootPath);
    this.parseData();
    this.write();
  }

  async collectFilePaths(rootPath) {
    let fileStat, filePath;
    try {
      const files = await fs.readdir(rootPath);
      for (let file of files) {
        filePath = path.join(rootPath, file);
        fileStat = await fs.stat(filePath);
        if (!fileStat.isDirectory()) this.filePaths.push(filePath);
      }
    } catch (error) {
      console.error(`collectFilePaths: ${error}`);
    }
  }
  
  parseData(): void {
    let workbook, worksheet, jsonArrStr, sheetData;
    this.filePaths.forEach(file => {
      if (/.xlsx?$/.test(file)) {
        workbook = XLSX.readFile(file, {});
        worksheet = workbook.Sheets['员工绩效'];
        jsonArrStr = this.stringify(XLSX.utils.sheet_to_json(worksheet));
        sheetData = this.parseSheetData(jsonArrStr);
        this.result.push(sheetData);
      }
    });
  }

  write() {
    console.dir(`已成功解析: ${this.result.length}条数据`);
  }

  private stringify(jsonData) {
    let jsonStr;
    try {
      jsonStr = JSON.stringify(jsonData);
    } catch (error) {
      console.error('序列化失败');
    }
    return jsonStr;
  }

  private parseSheetData(sheetJSONStr: string) {
    let matches, values = [];
    let dataReg = /("姓名"|"考核月份"|"合计"|"总得分"),\s*"__EMPTY_?\d?":\s*"([^"]+)/g;
    let dataKeys = ['name', 'month', 'normalWork', 'importantWork', 'others','total'];
    while ((matches = dataReg.exec(sheetJSONStr))) {
      if (matches && matches.length > 2) values.push(matches[2].trim());
    }
    return zipObject(dataKeys, values);
  }
}

const xlsRootPath = path.join(__dirname, 'xls07');
const xlsxParser = new XlsParser();
xlsxParser.parse(xlsRootPath);

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Spring Boot 集成 Spring Security

    Spring Security 是一个能够为基于 Spring 的企业应用系统提供声明式的安全访问控制解决方案的安全框架。它提供了一组可以在 Spring 应用...

    阿宝哥
  • 深入学习 Node.js Http

    Expect 是一个请求消息头,包含一个期望条件,表示服务器只有在满足此期望条件的情况下才能妥善地处理请求。规范中只规定了一个期望条件,即 Expect: 10...

    阿宝哥
  • Node.js 小打小闹之爬虫入门

    网络爬虫(英语:web crawler),也叫网络蜘蛛(spider),是一种用来自动浏览万维网的网络机器人。此外爬虫还可以验证超链接和 HTML 代码,用于网...

    阿宝哥
  • C# try catch finally

     catch 和 finally 一起使用的常见方式是:在 try 块中获取并使用资源,在 catch 块中处理异常情况,并在 finally 块中释放资源。

    aehyok
  • 三维世界中的坐标系

    上篇文章中介绍了threejs中几个基本概念,例如场景、相机、渲染器以及组件等,并通过一个简单的案例向小伙伴展示了这些东西的用法,本文来看看threejs中的坐...

    江南一点雨
  • 1亿中国人已被AI批改过作业

    量子位
  • 在 Github 上找「好东西」的方法

    ? 你是不是有以下困惑? 身边总有神奇的同事,总是能分享一些很赞的资源,不好意思问他从哪里知道的。 打算学习一门新技术,怎么最快找到优质的资源(工具,第三方...

    非著名程序员
  • 6个最好用的 AR/VR开源框架:无需任何插件安装,只用浏览器即可

    1. AR.js AR.js 是一款应用于 Web 的高效增强现实(AR)库,基于 three.js + jsartoolkit5,无需安装。它适用于任何带有 ...

    BestSDK
  • 说出你的需求,我们AI给你写代码 | MIT新研究

    他们提出了一种灵活组合模式识别和推理的方法,在无监督学习的情况下, 来解决AI自动编程遇到的问题。

    量子位
  • 微服务架构实践

    作者:赵计刚 来源:http://www.cnblogs.com/java-zhao/p/5538232.html(点击文末阅读原文前往) 一、微服务架构图: ...

    java达人

扫码关注云+社区

领取腾讯云代金券