Format a range - Geteilt mit Script Lab.

Localization.yaml

name: Localization
description: Format a range
author: maninweb
host: EXCEL
api_set: {}
script:
    content: |
        $("#setup").click(setup);
        $("#set-date-format").click(setDateFormat);

        // Note: MM/DD/YYY applied leads to MM.DD.YYYY in Excel German,
        //       as Excel replaces the / by a dot. However this is also
        //       the case for VBA. IMHO this should not happen.

        async function setDateFormat() {
            try {
                await Excel.run(async (context) => {
                    const sheet = context.workbook.worksheets.getItem("Sample");

                    const formats = [
                        ["DD.MM.YYYY"],
                        ["YYYY-MM-DD"],
                        ["MM/DD/YYYY"],
                        ["0.00"]
                    ];

                    const range = sheet.getRange("A2:A4");
                    range.numberFormat = formats;
                    await context.sync();
                });
            }
            catch (error) {
                OfficeHelpers.UI.notify(error);
                OfficeHelpers.Utilities.log(error);
            }
        }

        // Note: using the TEXT function for German needs then arguments
        //       in a German format, for example for dates. Otherwise
        //       it will lead to an unexpected result. However, Office
        //       JS does imho not provide a way to get the current codes
        //       like VBA through the Application.International property.

        async function setup() {
            try {
                await Excel.run(async (context) => {
                    const sheet = await OfficeHelpers.ExcelUtilities
                        .forceCreateSheet(context.workbook, "Sample");

                    const data = [
                        ['Date', 'Format with Formula'],
                        [43295, '=TEXT(A2,"TT.MM.JJJJ")'],
                        [43295, '=TEXT(A2,"DD.MM.YYYY")'],
                        [43295, '=TEXT(A2,"MM/DD/YYYY")'],
                        ['=COUNTIF(A2:A4,">0")','-']
                    ];

                    const range = sheet.getRange("A1:B5");
                    range.values = data;
         //         range.format.autofitColumns();

                    sheet.activate();
                    
                    await context.sync();
                });
            }
            catch (error) {
                OfficeHelpers.UI.notify(error);
                OfficeHelpers.Utilities.log(error);
            }
        }
    language: typescript
template:
    content: "<section class=\"ms-font-m\">\n    <p>This sample shows how to format a range using the Excel API.</p>\n</section>\n\n<section class=\"setup ms-font-m\">\n    <h3>Set up</h3>\n    <button id=\"setup\" class=\"ms-Button\">\n        <span class=\"ms-Button-label\">Add sample data</span>\n    </button>\n</section>\n\n<section class=\"samples ms-font-m\">\n    <h3>Try it out</h3>\n\t<button id=\"set-date-format\" class=\"ms-Button\">\n        <span class=\"ms-Button-label\">Set date format</span>\n    </button>\n</section>\n"
    language: html
style:
    content: |
        section.samples {
            margin-top: 20px;
        }

        section.samples .ms-Button, section.setup .ms-Button {
            display: block;
            margin-bottom: 5px;
            margin-left: 20px;
            min-width: 80px;
        }
    language: css
libraries: |
    # Office.js
    https://appsforoffice.microsoft.com/lib/1/hosted/office.js

    # CSS Libraries
    office-ui-fabric-js@1.4.0/dist/css/fabric.min.css
    office-ui-fabric-js@1.4.0/dist/css/fabric.components.min.css

    # NPM libraries
    core-js@2.4.1/client/core.min.js
    @microsoft/office-js-helpers@0.7.4/dist/office.helpers.min.js
    jquery@3.1.1

    # IntelliSense: @types/library or node_modules paths or URL to d.ts files
    @types/office-js
    @types/core-js
    @microsoft/office-js-helpers@0.7.4/dist/office.helpers.d.ts
    @types/jquery