Can't set NumberFormat with Excel JavaScript API

Question

I'm using the excel Javascript API and after searching through the docs, still can't find a solution for what I'm trying to achieve.

So I want to set everything to have the number format "Text" so none of Excel's auto-formatting messes with any of the cells' content (no removing leading zeros or changing date formats).

The docs suggest changing the format via:

Worksheets("Sheet1").Range("A17").NumberFormat = "General"

But it seems the sheet object doesn't contain a Range method.

Instead I tried to use the getRange method on my table like this:

constructionTable.getRange().NumberFormat = "Text"

This didn't throw any errors, but didn't seem to do anything.

Formatting the table range had previously worked for stuff like this:

constructionTable.getRange().format.autofitColumns()

So I also checked whether constructionTable.getRange().range.format contains a method called NumberFormat or numberFormat, but it does not :(

Has anyone had any success doing this?

Side note: Worksheets("Sheet1").Range("A17").NumberFormat = "General" is VBA, not the JS API. — BigBen
– BigBen, Commented Apr 24, 2020 at 14:50

Raymond Lu · Accepted Answer · 2020-04-24 15:29:19Z

5

Welcome to Office JS, Tluther. Yes, worksheet dont have numberFormat API. if you want to set the whole sheet, you could use getUsedRange() to get the range that used in the worksheet. You can use range.numberFormat = '@' to set it to text;

Here is the sample code:

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

    const range = sheet.getUsedRange();
    range.numberFormat = '@';
    await context.sync();
  });

Document can be found at here

answered Apr 24, 2020 at 15:29

Raymond Lu

2,2561 gold badge8 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

FreeSoftwareServers Over a year ago

@ worked fine, but I can't seem to get it to do anything else how about numbers or currency can you give one or two more examples?

FreeSoftwareServers Over a year ago

range.numberFormat = '0'; also worked for me for #'s thanks!

Xiao · Accepted Answer · 2022-05-25 01:39:07Z

-1

Now, the API numberFormat now should use format like '[][]', So how can l set the 'accounting' or 'general' by JS API.

answered May 25, 2022 at 1:39

Xiao

1

Collectives™ on Stack Overflow

Can't set NumberFormat with Excel JavaScript API

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related