Excel自定义函数键值对缓存实现及Office Runtime Storage替代方案咨询

一、基础内存缓存实现（会话级） #

如果只需要在当前Excel会话中缓存数据（关闭后清空），直接用全局对象存储最简便，无需依赖Excel API：

// 全局内存缓存，仅当前Excel会话有效
const memoryCache = {};

/**
 * @customfunction
 * @param {string} searchKey 搜索关键词
 * @returns {Promise<string>} 搜索结果
 */
async function SEARCH_WITH_CACHE(searchKey) {
  // 优先从缓存读取
  if (memoryCache.hasOwnProperty(searchKey)) {
    return memoryCache[searchKey];
  }
  
  // 执行实际搜索/计算逻辑（示例为模拟异步请求）
  const result = await fetchExternalData(searchKey);
  
  // 将结果存入缓存
  memoryCache[searchKey] = result;
  return result;
}

// 模拟外部数据请求（替换为你的实际逻辑）
async function fetchExternalData(key) {
  await new Promise(resolve => setTimeout(resolve, 1000));
  return `查询结果：${key}`;
}

二、Office Runtime Storage 类型错误排查 #

你遇到的"wrong data type"错误，通常是因为存储了不可序列化的数据类型（比如函数、Date对象、Symbol、循环引用对象等）。解决方法是将数据转换为可JSON序列化的基本类型：

• 日期对象转ISO字符串：new Date().toISOString()
• 复杂对象转JSON字符串：JSON.stringify(yourObject)
• 读取时再反序列化：JSON.parse(storedValue)

示例修正代码：

// 存储时序列化
await OfficeRuntime.storage.setItem("user_data", JSON.stringify({name: "John", age: 30}));

// 读取时反序列化
const storedData = await OfficeRuntime.storage.getItem("user_data");
const userData = JSON.parse(storedData);

三、替代Office Runtime Storage的缓存方案 #

1. 隐藏工作表存储（持久化，跨会话） #

将缓存数据存在隐藏工作表的单元格中，通过键值对映射存储，适合所有Excel版本：

// 从缓存工作表读取数据
async function getSheetCache(key) {
  return await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItemOrNullObject("CacheSheet");
    await context.sync();
    if (sheet.isNullObject) return null;
    
    const usedRange = sheet.getUsedRange();
    await context.sync();
    const values = usedRange.values;
    
    for (const row of values) {
      if (row[0] === key) return row[1];
    }
    return null;
  });
}

// 写入缓存工作表
async function saveSheetCache(key, value) {
  await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItemOrNullObject("CacheSheet");
    await context.sync();
    
    // 不存在则创建隐藏工作表
    if (sheet.isNullObject) {
      sheet = context.workbook.worksheets.add("CacheSheet");
      sheet.visibility = Excel.SheetVisibility.hidden;
    }
    
    // 找到最后一行写入新数据
    const lastRow = sheet.getRange("A:A").getLastCell().rowIndex + 1;
    sheet.getRange(`A${lastRow}`).values = [[key]];
    sheet.getRange(`B${lastRow}`).values = [[value]];
    await context.sync();
  });
}

2. 自定义XML部件（持久化，结构化存储） #

在Excel文件中嵌入自定义XML部件存储键值对，不会被用户误编辑，适合结构化数据：

// 从XML部件读取缓存
async function getXmlCache(key) {
  return await Excel.run(async (context) => {
    const xmlParts = context.workbook.customXmlParts.getByNamespace("http://excel.cache.namespace");
    await context.sync();
    if (xmlParts.items.length === 0) return null;
    
    const xmlString = await xmlParts.items[0].getXml();
    const xmlDoc = new DOMParser().parseFromString(xmlString, "text/xml");
    const entry = xmlDoc.querySelector(`entry[key="${key}"]`);
    
    return entry ? entry.textContent : null;
  });
}

// 写入XML部件缓存
async function saveXmlCache(key, value) {
  await Excel.run(async (context) => {
    let xmlParts = context.workbook.customXmlParts.getByNamespace("http://excel.cache.namespace");
    await context.sync();
    let xmlPart;
    
    // 不存在则创建新XML部件
    if (xmlParts.items.length === 0) {
      xmlPart = context.workbook.customXmlParts.add(`<cache xmlns="http://excel.cache.namespace"></cache>`);
    } else {
      xmlPart = xmlParts.items[0];
    }
    
    // 更新XML内容
    const xmlString = await xmlPart.getXml();
    const xmlDoc = new DOMParser().parseFromString(xmlString, "text/xml");
    let entry = xmlDoc.querySelector(`entry[key="${key}"]`);
    
    if (!entry) {
      entry = xmlDoc.createElementNS("http://excel.cache.namespace", "entry");
      entry.setAttribute("key", key);
      xmlDoc.documentElement.appendChild(entry);
    }
    entry.textContent = value;
    
    const updatedXml = new XMLSerializer().serializeToString(xmlDoc);
    xmlPart.setXml(updatedXml);
    await context.sync();
  });
}

3. LocalStorage（仅Excel Web版可用） #

如果只针对Web版Excel，可直接使用浏览器的localStorage，操作简单：

// 读取缓存
function getLocalCache(key) {
  return localStorage.getItem(`excel_cache_${key}`);
}

// 写入缓存
function saveLocalCache(key, value) {
  localStorage.setItem(`excel_cache_${key}`, value);
}

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