javascript - Office.js | Excel 加载项 | office365 - “处理请求时出错”错误将数据呈现到电子表格

Question

我正在使用 office.js 创建 Excel 加载项。我确实需要在单击任务窗格按钮时进行 Ajax API 调用并将 API 响应呈现到 excel 单元格中。API 响应包含数千个对象，我认为这些对象如下所示：

dataApiResponse = [{AccName: "ABC", ID: 173, Name: "FDI",... contains 20 attributes in single object}, .... contains upto 5000 objects in array]

使用以下方法将上述响应数组呈现到 excel 中。“outputHeaders”是已经在 excel 单元格上呈现并希望在每个标题下方呈现 api 响应的标题数组。

    const ExcelGetData = () => {
    // below is the outputData array contains upto 10000 or 15000 objects and each object represent rows for sheet and each object contains 
    // upto 20 attributs means in sheet the column id would be from 'A - T'
    const outputData = [{AccName: "ABC", ID: 173, Name: "FDI",... contains 20 attributes in single object}, .... contains upto 10000 objects in array];
    
    let range;
    let end = 0;
    let start = 2;
    let newEndRange;
    let newStartRange;
    const endRow = start + outputData.length;
    
    Excel.run(context => {
        const app = context.workbook.application;
        const sheet = context.workbook.worksheets.getItem('sheet1');
        return context.sync().then(() => {
            app.suspendApiCalculationUntilNextSync();
            outputData
                .map(() => outputData.splice(0, 500))
                .map(response => {
                    newStartRange = `A${start}`;
                    end = start + 500 - 1;
                    if (end < endRow) {
                        start = end + 1;
                        newEndRange = `T${end}`;
                        range = sheet.getRange(
                            `${newStartRange}:${newEndRange}`,
                        );
                    } else {
                        newEndRange = `T${endRow}`;
                        range = sheet.getRange(
                            `${newStartRange}:${newEndRange}`,
                        );
                    }
                    range.values = response;
                    range.format.autofitColumns();
                    context.sync();
                    return null;
                });
            return context.sync();
        });
    }).catch(err => {
        console.log(`error occured${err}`);
    });
};

在 web 版本的 office365 下，excel API 失败并出现以下错误：

https://excel.officeapps.live.com/x/_vti_bin/EwaInternalWebService.json/ExecuteRichApiRequest?waccluster=PUS1

错误：

{"Message":"There was an error processing the request.","StackTrace":"","ExceptionType":""}

还检查了在此处输入链接描述问题答案并了解 excel web 具有有效负载大小限制。你能帮忙解决这个问题或者我如何拆分这个操作吗？由于此问题导致我们的可交付成果被阻止，这将很有帮助。

标签： javascriptoffice365office-jsoffice-addinsoffice365api