如何用Office Script与Power Automate在Excel BoQ报表中插入SharePoint图片

我正尝试用Office Script结合Power Automate生成Excel工程量清单（BoQ）报表，每行需要包含BoQ项的相关信息和对应图片。已经通过Power Automate从SharePoint列表获取数据，以及SharePoint文档库中的图片URL；Office Script能正确把数据填充到Excel行里，但图片没法按要求添加到对应行的最后一列。我已经按照微软官方文档把图片从URL转成Base64格式，现在想知道怎么像填充formattedBoqs数组那样，把图片添加到BoQ项所在行的最后一列，我的脚本代码如下：

async function main(workbook: ExcelScript.Workbook, boqs: BoQs[],) 
{
  // 获取第一个工作表
  const sheet = workbook.getFirstWorksheet();

  // 遍历BoQs数据并填充到formattedBoqs数组
  const boqOffset = 11; // BoQ项列表的起始行
  for (let i = 0; i < boqs.length; i++) {
    const currentBoQs = boqs[i];

// 以下是文档中的图片转换代码块
    // 从URL获取图片
    const link = currentBoQs.imageLink;
    const response = await fetch(link);
    // 将响应存储为ArrayBuffer（因为是原始图片文件）
    const data = await response.arrayBuffer();
    // 将图片数据转换为Base64编码字符串
    const image = convertToBase64(data);
    // 将图片添加到工作表
    sheet.addImage(image)
// 图片转换代码块结束
    
    // 构造BoQ行数据并填充到Excel
    const formattedBoqs = [[currentBoQs.boqNumber, currentBoQs.location, currentBoQs.category, currentBoQs.item, currentBoQs.damageLevel, currentBoQs.repairType, currentBoQs.unit, currentBoQs.quantity, currentBoQs.width, currentBoQs.BoQlength, currentBoQs.thickness, currentBoQs.direction, currentBoQs.note /* 这里需要添加图片 */]];
    const boqCell = `A${boqOffset + i}:M${boqOffset + i}`;
    sheet.getRange(boqCell).setValues(formattedBoqs);
  }
}

// 工程量清单数据结构
interface BoQs {
  boqNumber: number,
  location: string,
  category: string,
  item: string,
  damageLevel: string,
  repairType: string,
  unit: string,
  quantity: string,
  width: string,
  BoQlength: string,
  thickness: string,
  direction: string,
  imageLink: string,
  note: string,
}

/**
 * 将包含.png图片的ArrayBuffer转换为Base64编码字符串
 */
 function convertToBase64(input: ArrayBuffer) {
  const uInt8Array = new Uint8Array(input);
  const count = uInt8Array.length;

  // 预先分配所需空间
  const charCodeArray = new Array(count) as string[];
  
  // 将数组中的每个条目转换为字符
  for (let i = count; i >= 0; i--) { 
    charCodeArray[i] = String.fromCharCode(uInt8Array[i]);
  }

  // 将字符转换为Base64
  const base64 = btoa(charCodeArray.join(''));
  return base64;
}

解决方案 #

问题出在sheet.addImage(image)只是把图片默认插入到工作表左上角，没有绑定到目标行的最后一列。需要修改图片插入逻辑，将图片定位到对应行的指定单元格：

1. 定位目标单元格：你的数据填充在A-M列（共13列），所以图片要放在第14列（N列），获取当前BoQ项所在行的N列单元格。
2. 设置图片位置与大小：利用addImage()返回的Shape对象，将图片的位置和大小与目标单元格绑定，确保图片嵌入到单元格内。

修改后的完整代码：

async function main(workbook: ExcelScript.Workbook, boqs: BoQs[],) 
{
  // 获取第一个工作表
  const sheet = workbook.getFirstWorksheet();

  // 遍历BoQs数据并填充到Excel
  const boqOffset = 11; // BoQ项列表的起始行
  for (let i = 0; i < boqs.length; i++) {
    const currentBoQs = boqs[i];

    // 从URL获取并转换图片
    const link = currentBoQs.imageLink;
    const response = await fetch(link);
    const data = await response.arrayBuffer();
    const image = convertToBase64(data);
    
    // 构造BoQ行数据并填充到Excel
    const formattedBoqs = [[currentBoQs.boqNumber, currentBoQs.location, currentBoQs.category, currentBoQs.item, currentBoQs.damageLevel, currentBoQs.repairType, currentBoQs.unit, currentBoQs.quantity, currentBoQs.width, currentBoQs.BoQlength, currentBoQs.thickness, currentBoQs.direction, currentBoQs.note]];
    const boqCell = `A${boqOffset + i}:M${boqOffset + i}`;
    sheet.getRange(boqCell).setValues(formattedBoqs);

    // 获取目标单元格（第boqOffset+i行，第14列即N列）
    const targetCell = sheet.getRange(boqOffset + i, 14);
    // 添加图片并获取Shape对象
    const imageShape = sheet.addImage(image);
    // 设置图片位置为单元格左上角
    imageShape.setTop(targetCell.getTop());
    imageShape.setLeft(targetCell.getLeft());
    // 设置图片大小适配单元格（可选，可根据需求调整）
    imageShape.setHeight(targetCell.getHeight());
    imageShape.setWidth(targetCell.getWidth());
  }
}

// 工程量清单数据结构
interface BoQs {
  boqNumber: number,
  location: string,
  category: string,
  item: string,
  damageLevel: string,
  repairType: string,
  unit: string,
  quantity: string,
  width: string,
  BoQlength: string,
  thickness: string,
  direction: string,
  imageLink: string,
  note: string,
}

/**
 * 将包含.png图片的ArrayBuffer转换为Base64编码字符串
 */
 function convertToBase64(input: ArrayBuffer) {
  const uInt8Array = new Uint8Array(input);
  const count = uInt8Array.length;

  const charCodeArray = new Array(count) as string[];
  
  // 修复循环索引错误，避免数组越界
  for (let i = 0; i < count; i++) { 
    charCodeArray[i] = String.fromCharCode(uInt8Array[i]);
  }

  const base64 = btoa(charCodeArray.join(''));
  return base64;
}

关键修改说明 #

• 调整代码顺序：先填充数据到单元格，再处理图片插入，避免单元格位置未确定导致的定位偏差
• 通过targetCell.getTop()和targetCell.getLeft()获取单元格坐标，将图片精准定位到对应行的最后一列
• 可选设置图片大小适配单元格，让图片刚好填满目标列的单元格
• 修复convertToBase64函数中的循环索引错误（原代码从count开始递减会导致数组越界，改为从0到count-1）

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