JavaScript实现VLOOKUP功能：从XLSX/CSV文件查询账户余额

Got it, let's tackle this problem of building a VLOOKUP-like function in JavaScript to fetch account balances from your Excel/CSV file. Since you mentioned a local file path (C:\Users\Username\Desktop\AccountBalances.xlsx), I'll cover both Node.js (for server/desktop use cases) and browser scenarios (in case you need this in a web app).

Node.js 实现（适合本地/后端场景） #

For accessing local files directly, Node.js is the way to go. We'll use the xlsx library to handle both Excel and CSV files seamlessly.

步骤1：安装依赖 #

First, install the required package:

npm install xlsx

步骤2：完整代码示例 #

const XLSX = require('xlsx');
const path = require('path');

// 假设用户登录后获取的账户号（根据实际场景替换为动态值）
const ACCTNO = '95785879'; 
// 本地文件路径，注意Windows系统要转义反斜杠
const filePath = 'C:\\Users\\Username\\Desktop\\AccountBalances.xlsx';

function getAccountBalance(targetAcctNo, fileLocation) {
  try {
    // 读取文件并解析为工作簿对象
    const workbook = XLSX.readFile(fileLocation);
    
    // 取第一个工作表（可根据实际修改为指定工作表名称，比如 workbook.Sheets['账户余额表']）
    const firstSheetName = workbook.SheetNames[0];
    const worksheet = workbook.Sheets[firstSheetName];
    
    // 将工作表转换为二维数组，header:1 表示用第一行作为表头参考
    const dataRows = XLSX.utils.sheet_to_json(worksheet, { header: 1 });
    
    // 定位表头列索引（请根据你的实际表头名称修改）
    const headerRow = dataRows[0];
    const acctNoColIndex = headerRow.indexOf('账户号');
    const balanceColIndex = headerRow.indexOf('余额');
    
    if (acctNoColIndex === -1 || balanceColIndex === -1) {
      throw new Error('文件中未找到"账户号"或"余额"列，请检查表头');
    }
    
    // 遍历数据行（跳过表头行），匹配账户号
    for (let i = 1; i < dataRows.length; i++) {
      const currentRow = dataRows[i];
      // 统一类型避免匹配失败（比如文件中是数字，变量是字符串）
      if (String(currentRow[acctNoColIndex]) === String(targetAcctNo)) {
        return currentRow[balanceColIndex];
      }
    }
    
    // 未找到匹配账户的情况
    return '未查询到该账户的余额';
  } catch (error) {
    console.error('余额查询出错:', error.message);
    return '查询失败';
  }
}

// 执行查询并输出结果
const accountBalance = getAccountBalance(ACCTNO, filePath);
console.log(`账户 ${ACCTNO} 的余额为: ${accountBalance}`);

处理CSV文件的说明 #

If your file is a CSV, the code above works exactly the same—just update the filePath to point to your .csv file. The xlsx library handles CSV parsing natively.

浏览器端实现（适合Web应用场景） #

Note: Browsers can't directly access local file paths like C:\Users\..., so we'll need users to upload the file manually, then parse it in the frontend.

步骤1：引入xlsx库 #

Add the library via CDN in your HTML:

<script src="https://cdn.jsdelivr.net/npm/xlsx@0.18.5/dist/xlsx.full.min.js"></script>

步骤2：HTML + JS 代码示例 #

<!-- 文件上传控件 -->
<input type="file" id="balanceFileInput" accept=".xlsx,.csv">
<!-- 查询按钮 -->
<button onclick="fetchAccountBalance()">查询我的余额</button>
<!-- 结果展示区域 -->
<p id="balanceResult"></p>

<script>
// 假设用户登录后从后端获取的账户号
const ACCTNO = '95785879';

function fetchAccountBalance() {
  const fileInput = document.getElementById('balanceFileInput');
  const selectedFile = fileInput.files[0];
  
  if (!selectedFile) {
    document.getElementById('balanceResult').textContent = '请先选择账户余额文件';
    return;
  }

  // 读取文件内容
  const fileReader = new FileReader();
  fileReader.onload = function(e) {
    try {
      const fileData = new Uint8Array(e.target.result);
      const workbook = XLSX.read(fileData, { type: 'array' });
      
      // 解析第一个工作表
      const firstSheet = workbook.Sheets[workbook.SheetNames[0]];
      const dataRows = XLSX.utils.sheet_to_json(firstSheet, { header: 1 });
      
      // 定位表头列
      const headerRow = dataRows[0];
      const acctNoColIndex = headerRow.indexOf('账户号');
      const balanceColIndex = headerRow.indexOf('余额');
      
      if (acctNoColIndex === -1 || balanceColIndex === -1) {
        throw new Error('文件中未找到"账户号"或"余额"列');
      }
      
      // 查找匹配的账户余额
      let result = '未查询到你的账户余额';
      for (let i = 1; i < dataRows.length; i++) {
        const row = dataRows[i];
        if (String(row[acctNoColIndex]) === String(ACCTNO)) {
          result = `你的账户余额为: ${row[balanceColIndex]}`;
          break;
        }
      }
      
      document.getElementById('balanceResult').textContent = result;
    } catch (error) {
      document.getElementById('balanceResult').textContent = `查询失败: ${error.message}`;
    }
  };
  fileReader.readAsArrayBuffer(selectedFile);
}
</script>

关键注意事项 #

• 类型统一：Always convert both the target account number and the file's account number to the same type (e.g., string) to avoid mismatches (e.g., a number 95785879 vs string "95785879").
• 表头匹配：Modify the header names ("账户号"/"余额") to match exactly what's in your file—case sensitivity matters here.
• 错误处理：The code includes basic error handling for missing columns, file issues, and unmatched accounts. You can expand this for your specific use case.

内容的提问来源于stack exchange，提问作者CaptainVolvo