Search code examples
office-jsscriptlab

Format entire column of Excel table as String

I'm working on an Excel add-in using the JavaScript APIs to build add-ins in Excel Office 365. For Excel tables, how can I format the entire column to have strings only by default. [ I need the numbers to appear as 012 instead of 12 in all the cells for a particular column.]

Solution

  • You can set the number formatting to text first (set to @, which is Excel's format for saying that something is a text), and then set the values.

    Here's a Range example (table would be very similar -- just get the range of the column)

    try {
    await Excel.run(async (context) => {
        const sheet = context.workbook.worksheets.getActiveWorksheet();
        const range = sheet.getRange("A1:A3");
        range.numberFormat = <any>"@";
        range.values = [
            ["012"],
            ["013"],
            ["014"]
        ];
        await context.sync();
    });
}
catch (error) {
    OfficeHelpers.Utilities.log(error);
}

    The above example is in TypeScript -- but there too, it should be trivial to convert to plain ES5 JS, if you prefer.

    You can try this snippet live in literally five clicks in the new Script Lab (https://aka.ms/getscriptlab). Simply install the Script Lab add-in (free), then choose "Import" in the navigation menu, and use the following GIST URL: https://gist.github.com/Zlatkovsky/741c3313825df988ac9840289c012440