How to get Default Range.numberFormat for different data types

[rettoua]

Hi there!
I have a small Excel Office Js addin that loads some data into the worksheet. The main data types that are used are following:

• Date
• DateTime
• Number (with different formats).

I struggle with finding information about the default formats (I don’t want Excel to create a custom format) for each type. It’s important that these formats are not dependent on the locale(culture). I initially thought that Range.numberFormat was defined for invariant culture. In other words, if I set m/d/yyyy as the format, it should work in any locale, but that was not the case. Recently I've got a ticket from the customer that an Excel Addin is not populating the data. After investigating the Excel file, I found the following:

• It's reached the maximum number of the Custom formats (it's 214 for windows os and 207 for macOS, strange number but it is as it is)
• When m/d/yyyy format is set to the Cell.numberFormat the error is thrown with the error saying Parameter out of range.
• If I try to get any of existed date cell number format, it gives me m/d/yy or m/d/yyyy (macOS and Windows OS respectively) . If I set this format to the cell (via JS) I get Parameter out of range error (whaat???).

After further investigation, I spotted that changing the local culture to a US-based one resolved the problem, and I didn't get errors by setting the same format m/d/yy or m/d/yyyy as before. An error was thrown before because Excel tried to create a custom format, but there was no space for it (limits).

So, how can I set up default formatters for different data types?

Any help is appreciated.