Excel的JS操作小总结

  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();
        });
    }