基于node编写excel读取和导出的小工具-牛翰网

背景

领导给同事安排了一个体力活，根据表1（7k+条）筛选表2(4W+条)，然后合并两个表相同的数据。听起来是挺简单的需求，加个外表筛选就完事了么，基础的excel操作他还是会的，但是仔细研究了下，发现行不通，FILTER和MATCH都会有一定的局限。并且最后的和并新表也是个困难。于是来求助，看能不能写个小脚本，跑一下解决问题。我了解了下需求，觉得可行，于是便有了这个脚本

需求描述

表1格式

姓名	年龄	住址
张三	20	上海外滩18号
李四	21	上海浦东新区
王五	22	御青路1号
。。。	。。。	。。。

表2格式

昵称	注册时间	住址
二蛋	2021	18号
双下巴大圆脸	2015	上海
狗子	2022	御青路
。。。	。。。	。。。

他的需求，就是根据表2的住址模糊筛选出表格1住址列所有匹配的数据。然后合并信息。

介绍结束、开始码代码

1、 node读取excel并解析数据

我这里是用了node的xlsx

// 安装xlsx
npm install --save xlsx

xlsx本身提供了一个读取文件方法，就不需要用fs的读取了，核心代码如下

 const workbook = xlsx.readFile(filePath);

 // 我这里只需要第一个，对于多个sheet的情况，这里加个循环就好了
 const sheetName = workbook.SheetNames[0];
 const sheet = workbook.Sheets[sheetName];

 // 将表内容转换为 JSON 数据
 const data = xlsx.utils.sheet_to_json(sheet);

这里推荐使用vscode的调试模式，可以很方便的查看变量的实际值，不用console一遍一遍查看

2、过滤数据,并合并需要的数据

拿到表1和表2的数据后，就是过滤数据的逻辑了

// 过滤文件
const processingData = ({filterFileData,resourceFileData}) => {
    // 创建一个包含所有 filterFileData.地址 的正则表达式
    const areaPattern = new RegExp(filterFileData.map(item => item['地址']).join('|'));
    
    // 记录匹配结果
    const matches = resourceFileData.reduce((acc, item2) => {
        const match = item2['地址'].match(areaPattern);
        if (match) {
            // 找到匹配的 arr1 项
            const matchedItem1 = filterFileData.find(item1 => item1['地址'] === match[0]);
            
            acc.push({
                filterFileData: matchedItem1,
                resourceFileData: item2
            });
        }
        return acc;
    }, []);
    
    
    return matches
}

对于这种大数据过滤，尽可能的减少循环套循环，我这里是直接把表2住址拼接了一个正则，用match去校验表1的地址列，是否包含表2字符串，有更好的方案，可以在评论区打出来交流下

3、导出excel

在第二步，我把匹配成功的表1和表2数据都存在了一个数组，然后再第三步使用,这里就是拼数据和写数据了

// 生成筛选后的excel
const writeExcel = ({writeData,resourceFileName}) => {
    const data = writeData.map((item, index) => {
        const {filterFileData,resourceFileData} = item
        return {
            '序号': index + 1,
            '所属区县': filterFileData['所属区县'],
            '户号': resourceFileData['户号'],
            '户名': resourceFileData['户名'],
            '户号地址': resourceFileData['地址'],
            '机构全称': filterFileData['机构全称'],
            '机构类型': filterFileData['机构类型'],
            '机构地址': filterFileData['地址'],
        }
    })
    // 将数据转换为 worksheet
    const worksheet = xlsx.utils.json_to_sheet(data);

    // 创建一个新的 workbook，并附加 worksheet
    const workbook = xlsx.utils.book_new();
    xlsx.utils.book_append_sheet(workbook, worksheet, 'Sheet1');

    let outputFileName = `${resourceFileName.split('.')[0]}-筛选结果.xlsx`
    let outputFilePath = path.join(outputFileDirPath,`./${outputFileName}`)

    // 保存 Excel 文件
    xlsx.writeFile(workbook, outputFilePath);
    
    console.log(`导出文件成功,地址在:${outputFilePath}`)
}

完整代码

// 1、读取filterFile和resourceFile目录下的文件
// 2、过滤源数据
// 3、将过滤好的数据，写入一个excel

const fs = require("fs");
const xlsx = require("xlsx");
const path = require("path");

const resourceFileDirPath = path.join(__dirname, "./resourceFile");
const filterFileName = "filter.xlsx";
const filterFileDirPath = path.join(__dirname, `./filterFile/${filterFileName}`);
const outputFileDirPath = path.join(__dirname, `./outputFile`);

// 读取excel文件
const readFile = (filePath) => {
  return new Promise((resolve, reject) => {
    const workbook = xlsx.readFile(filePath);
    
    // 暂时只解析第一个sheet
    const sheetName = workbook.SheetNames[0];
    const sheet = workbook.Sheets[sheetName];
    
    // 将表内容转换为 JSON 数据
    const data = xlsx.utils.sheet_to_json(sheet);
    
    resolve(data);
  });
};

// 过滤文件
const processingData = ({filterFileData,resourceFileData}) => {
    // 创建一个包含所有 filterFileData.地址 的正则表达式
    const areaPattern = new RegExp(filterFileData.map(item => item['地址']).join('|'));

    // 记录匹配结果
    const matches = resourceFileData.reduce((acc, item2) => {
        const match = item2['地址'].match(areaPattern);
        if (match) {
            // 找到匹配的 arr1 项
            const matchedItem1 = filterFileData.find(item1 => item1['地址'] === match[0]);
            acc.push({
                filterFileData: matchedItem1,
                resourceFileData: item2
            });
        }
        return acc;
    }, []);

    return matches
}
// 生成筛选后的excel
const writeExcel = ({writeData,resourceFileName}) => {
    const data = writeData.map((item, index) => {
        const {filterFileData,resourceFileData} = item
        return {
            '序号': index + 1,
            '所属区县': filterFileData['所属区县'],
            '户号': resourceFileData['户号'],
            '户名': resourceFileData['户名'],
            '户号地址': resourceFileData['地址'],
            '机构全称': filterFileData['机构全称'],
            '机构类型': filterFileData['机构类型'],
            '机构地址': filterFileData['地址'],
        }
    })
    // 将数据转换为 worksheet
    const worksheet = xlsx.utils.json_to_sheet(data);

    // 创建一个新的 workbook，并附加 worksheet
    const workbook = xlsx.utils.book_new();
    xlsx.utils.book_append_sheet(workbook, worksheet, 'Sheet1');

    let outputFileName = `${resourceFileName.split('.')[0]}-筛选结果.xlsx`
    let outputFilePath = path.join(outputFileDirPath,`./${outputFileName}`)

    // 保存 Excel 文件
    xlsx.writeFile(workbook, outputFilePath);
    
    console.log(`导出文件成功,地址在:${outputFilePath}`)
}

const main = async () => {
  
  console.time("总用时");
  const files = fs.readdirSync(resourceFileDirPath);
  let filterFile = await readFile(filterFileDirPath);
//   过滤掉没有地址 和指定关键字的文件
  filterFile = filterFile.filter(item => {
    let area = item['地址']
    let blackList = ['0','无', '无地址']
    return area && !blackList.includes(area)
  })


  console.log(files);
  for (const fileName of files) {
    
    console.time(`${fileName}用时：`);
    console.log(`正在读取文件${fileName}`)
    // 读取待筛选数据
    let resourceFile = await readFile(
      path.resolve(resourceFileDirPath, fileName)
    );
    console.log(`读取文件成功，正在解析文件`)
    // 解析文件
    const writeData = processingData({
        filterFileData: filterFile,
        resourceFileData: resourceFile,
    })
    console.log(`解析文件成功，正在导出文件`)
    // 写入数据
    writeExcel({
        writeData,
        resourceFileName: fileName
    })
    console.timeEnd(`${fileName}用时：`);

  }

  console.timeEnd("总用时");
};

// 执行
try {
  main();
  
} catch (error) {
    console.log('error',error)
}