使用Office脚本从URL向特定单元格添加图像

Question

我正在尝试使用office脚本在特定单元格上添加QR码，以下代码工作正常，但是，我无法弄清楚如何指定位置(单元格G1)，我希望QR码左上角从单元格"G1"开始。

async function main(workbook: ExcelScript.Workbook) {
// Fetch the image from a URL.
const link ="https://chart.googleapis.com/chart?chs=150x150&cht=qr&chl=Samundra";
const response = await fetch(link);
// Store the response as an ArrayBuffer, since it is a raw image file.
const data = await response.arrayBuffer();
// Convert the image data into a base64-encoded string.
const image = convertToBase64(data);
// Add the image to a worksheet.
workbook.getWorksheet("Sheet1").addImage(image);
}

function convertToBase64(input: ArrayBuffer) {
const uInt8Array = new Uint8Array(input);
const count = uInt8Array.length;
// Allocate the necessary space up front.
const charCodeArray = new Array(count) as string[];

// Convert every entry in the array to a character.
for (let i = count; i >= 0; i--) { 
charCodeArray[i] = String.fromCharCode(uInt8Array[i]);
}
// Convert the characters to base64.
const base64 = btoa(charCodeArray.join(''));
return base64;
}

Answer 1

您需要获取刚刚添加到工作簿中的图像对象，并将其左侧和顶部设置为"锚点"的左侧和顶部。单元格(在您的示例中是G1)。

所以像这样:

const imageShape = workbook.getWorksheet("Sheet1").addImage(image);
const range = workbook.getWorksheet("Sheet1").getRange("G1");
imageShape.setLeft(range.getLeft());
imageShape.setTop(range.getTop());