Excel的JS操作小总结

透视表对象模型Pivot：

  async function setupPivot() {
        await Excel.run(async (context) => {
            context.workbook.worksheets.getItemOrNullObject("Data").delete();
            const dataSheet = context.workbook.worksheets.add("Data");
            context.workbook.worksheets.getItemOrNullObject("Pivot").delete();
            const pivotSheet = context.workbook.worksheets.add("Pivot");

            const data = [
                ["Farm", "Type", "Classification", "Crates Sold at Farm", "Crates Sold Wholesale"],
                ["A Farms", "Lime", "Organic", 300, 2000],
                ["A Farms", "Lemon", "Organic", 250, 1800],
                ["A Farms", "Orange", "Organic", 200, 2200],
                ["B Farms", "Lime", "Conventional", 80, 1000],
                ["B Farms", "Lemon", "Conventional", 75, 1230],
                ["B Farms", "Orange", "Conventional", 25, 800],
                ["B Farms", "Orange", "Organic", 20, 500],
                ["B Farms", "Lemon", "Organic", 10, 770],
                ["B Farms", "Kiwi", "Conventional", 30, 300],
                ["B Farms", "Lime", "Organic", 50, 400],
                ["C Farms", "Apple", "Organic", 275, 220],
                ["C Farms", "Kiwi", "Organic", 200, 120],
                ["D Farms", "Apple", "Conventional", 100, 3000],
                ["D Farms", "Apple", "Organic", 80, 2800],
                ["E Farms", "Lime", "Conventional", 160, 2700],
                ["E Farms", "Orange", "Conventional", 180, 2000],
                ["E Farms", "Apple", "Conventional", 245, 2200],
                ["E Farms", "Kiwi", "Conventional", 200, 1500],
                ["F Farms", "Kiwi", "Organic", 100, 150],
                ["F Farms", "Lemon", "Conventional", 150, 270]
            ];

            const range = dataSheet.getRange("A1:E21");
            range.values = data;
            range.format.autofitColumns();

            pivotSheet.activate();

            await context.sync();
        });
    }


    async function setupPivot1() {
        await Excel.run(async (context) => {
            context.workbook.worksheets.getItemOrNullObject("Data").delete();
            const dataSheet = context.workbook.worksheets.add("Data");
            context.workbook.worksheets.getItemOrNullObject("Pivot").delete();
            const pivotSheet = context.workbook.worksheets.add("Pivot");
            //
            const apiUrl = 'https://localhost:/Home/GetData'; 
            const response = await fetch(apiUrl); // 发送请求获取数据 
            const data = await response.json(); // 将响应数据解析为 JSON 格式



            /*
            const data = [
                ["Farm", "Type", "Classification", "Crates Sold at Farm", "Crates Sold Wholesale"],
                ["A Farms", "Lime", "Organic", 300, 2000],
                ["A Farms", "Lemon", "Organic", 250, 1800],
                ["A Farms", "Orange", "Organic", 200, 2200],
                ["B Farms", "Lime", "Conventional", 80, 1000],
                ["B Farms", "Lemon", "Conventional", 75, 1230],
                ["B Farms", "Orange", "Conventional", 25, 800],
                ["B Farms", "Orange", "Organic", 20, 500],
                ["B Farms", "Lemon", "Organic", 10, 770],
                ["B Farms", "Kiwi", "Conventional", 30, 300],
                ["B Farms", "Lime", "Organic", 50, 400],
                ["C Farms", "Apple", "Organic", 275, 220],
                ["C Farms", "Kiwi", "Organic", 200, 120],
                ["D Farms", "Apple", "Conventional", 100, 3000],
                ["D Farms", "Apple", "Organic", 80, 2800],
                ["E Farms", "Lime", "Conventional", 160, 2700],
                ["E Farms", "Orange", "Conventional", 180, 2000],
                ["E Farms", "Apple", "Conventional", 245, 2200],
                ["E Farms", "Kiwi", "Conventional", 200, 1500],
                ["F Farms", "Kiwi", "Organic", 100, 150],
                ["F Farms", "Lemon", "Conventional", 150, 270]
            ];
           */
            const range = dataSheet.getRange("A1:E21");
            range.values = data;
            range.format.autofitColumns();

            pivotSheet.activate();

            await context.sync();
        });
    }

透视表数据Data可以改为Api请求方式

 微软VS 2022建立的Web Api服务例子：

 

Fetch API

Fetch API提供了一个 JavaScript 接口，用于访问和操纵HTTP的请求和响应等。提供了一个全局 fetch() 方法来跨网络异步获取资源。

Fetch 规范与 jQuery.ajax() 主要有三种方式的不同：

• 当接收到一个代表错误的 HTTP 状态码， 即使响应的 HTTP 状态码是 404 或 500。从 fetch() 返回的 Promise **不会被标记为 reject，**相反，会标记为 resolve （但是会把 resolve 的返回值的 ok 属性设置为 false ），仅当网络故障时或请求被阻止时，才会标记为 reject。
• fetch() 可以接收跨域 cookies；也可以使用 fetch() 建立起跨域会话。
• fetch 不会发送 cookies。除非你使用了credentials 的初始化选项。

 

•