能否在JavaScript中使用Power Query的M语言？如何集成至NestJS函数？

需求背景 #

需要把以下Power Query的M语言处理逻辑，移植到现有的NestJS服务函数中：

let
    Fonte = Excel.Workbook(File.Contents("C:\Users\INTEL\Downloads\Planilha  POWER BI SPC.xlsx"), null, true),
    Geral_Sheet = Fonte{[Item="Geral",Kind="Sheet"]}[Data],
    #"Cabeçalhos Promovidos" = Table.PromoteHeaders(Geral_Sheet, [PromoteAllScalars=true]),
    #"Valor Substituído" = Table.ReplaceValue(#"Cabeçalhos Promovidos","",null,Replacer.ReplaceValue,{"ID", "Nome", "IP", "porta", "SN", "MAC", "Modelo", "TIpo", "Máx. Canais"}),
    #"Preenchido Abaixo" = Table.FillDown(#"Valor Substituído",{"ID", "Nome", "IP", "porta", "SN", "MAC", "Modelo", "TIpo", "Máx. Canais"})
in
    #"Preenchido Abaixo"

现有NestJS函数基础代码：

import { Injectable } from '@nestjs/common';

import * as XLSX from 'xlsx';

/* load 'fs' for readFile and writeFile support */
import * as fs from 'fs';
XLSX.set_fs(fs);

import { Readable } from 'stream';
XLSX.stream.set_readable(Readable);

@Injectable()
export class TablesTreatmentService {
  async getTable(table: string) {
    const url = process.env.WOORKBOOK_URL;

    const data = await (await fetch(url)).arrayBuffer();

    const sheet = XLSX.read(data).Sheets[table];

    return XLSX.utils.sheet_to_json(sheet);
  }
}

实现方案 #

基于现有xlsx库，手动实现Power Query中的三个核心步骤即可，无需额外安装工具包：

1. 核心步骤对应实现 #

• 提升表头：将表格第一行转为对象的键，替代Power Query的Table.PromoteHeaders
• 空值替换：将指定字段的空字符串转为null，对应Table.ReplaceValue
• 向下填充：用前一行的非空值填充当前行的空字段，对应Table.FillDown

完整改造后的代码 #

import { Injectable } from '@nestjs/common';
import * as XLSX from 'xlsx';
import * as fs from 'fs';
import { Readable } from 'stream';

XLSX.set_fs(fs);
XLSX.stream.set_readable(Readable);

@Injectable()
export class TablesTreatmentService {
  async getTable(table: string) {
    const url = process.env.WOORKBOOK_URL;
    const data = await (await fetch(url)).arrayBuffer();
    const workbook = XLSX.read(data);
    const sheet = workbook.Sheets[table];

    // 1. 转换为二维数组，提取表头和数据行
    const sheetArray = XLSX.utils.sheet_to_json(sheet, { header: 1 });
    const headers = sheetArray[0] as string[];
    const rows = sheetArray.slice(1);

    // 2. 提升表头，转换为对象数组
    let formattedData = rows.map(row => {
      const obj: Record<string, any> = {};
      headers.forEach((header, index) => {
        obj[header] = row[index];
      });
      return obj;
    });

    const targetFields = ["ID", "Nome", "IP", "porta", "SN", "MAC", "Modelo", "TIpo", "Máx. Canais"];

    // 3. 空值替换：将指定字段的空字符串替换为null
    formattedData = formattedData.map(row => {
      targetFields.forEach(field => {
        if (row[field] === "") {
          row[field] = null;
        }
      });
      return row;
    });

    // 4. 向下填充：用前一行的非空值填充当前行的null字段
    const lastValues: Record<string, any> = {};
    formattedData = formattedData.map(row => {
      targetFields.forEach(field => {
        if (row[field] !== null) {
          lastValues[field] = row[field];
        } else if (lastValues[field] !== undefined) {
          row[field] = lastValues[field];
        }
      });
      return row;
    });

    return formattedData;
  }
}

关键说明 #

• 提升表头：通过header:1获取二维数组后手动映射表头，比直接用sheet_to_json默认配置更灵活，便于后续处理
• 空值替换：精准遍历指定字段，只处理目标列的空字符串
• 向下填充：维护lastValues对象记录每个字段的最近非空值，逐行检查填充，完全匹配Power Query的Table.FillDown逻辑

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