Disable automatic conversion to Date

Question

In one book (from someone else) that I need to analyze, they use the formula to build a list of lines: 04-09-01 , 04-09-02 , 04-09-03 , etc., which have the format general as a format. In part of my code, I will need to copy these values ​​to another location. However, since these values ​​are quite special, they are automatically treated as Date (whereas they are clearly not dates for users) and converted to 09/04/2001 , 09/04/2002 , 09/04/2003 , etc. As a result, the values ​​are completely changed, and the calculation based on these inserted values ​​leads to errors.

Here is the test code:

 function test () { Excel.run(function (ctx) { var r0 = ctx.workbook.worksheets.getItem("Sheet1").getRange("A2:A10"); var r1 = ctx.workbook.worksheets.getItem("Sheet1").getRange("F2:F10"); r0.load(["values"]); return ctx.sync() .then(function () { console.log(r0.values.toString()); r1.values = r0.values; }) .then(ctx.sync) .then(function () { r1.load(["values"]); }) .then(ctx.sync) .then(function () { console.log(r1.values.toString()); }) }); } 

The result in the console shows that the values ​​are completely changed:

And in Excel, this shows:

Note that Excel itself does NOT systematically convert these values ​​to dates. For example, if we stand only for copying 04-09-01 to another cell. Excel makes a warning and suggests converting it to a date, but users can ignore this warning and save 04-09-01 as is:

So my question is: is there a way or workaround to turn off this automatic conversion to the JavaScript API so that we can reliably copy the values?

Change 1:

I tried using numberFormat to keep the initial range formats. First, I set A2:A0 as follows: general as a format.

Then I run the following code:

 function test () { Excel.run(function (ctx) { var r0 = ctx.workbook.worksheets.getItem("Sheet1").getRange("A2:A10"); var saveValues, saveNumberFormat; r0.load(["values", "numberFormat"]); return ctx.sync().then(function () { saveNumberFormat = r0.numberFormat; saveValues = r0.values; r0.numberFormat = saveNumberFormat; r0.values = saveValues; }); }); } 

The result was as follows and has Date as a format.

So, does recovering numberFormat not help?

Edit 2: I made an example that copies a range to another. I want r1 have exactly the same format and number values ​​as r0 . But the result shows that 04-09-01 as general in r0 creates 04/09/2001 as Date in r1 . Thus, the problem is the same as in the previous example: numberFormat cannot be copied or restored.

 function test () { Excel.run(function (ctx) { var r0 = ctx.workbook.worksheets.getItem("Sheet1").getRange("A2:A10"); var r1 = ctx.workbook.worksheets.getItem("Sheet1").getRange("K2:K10"); r0.load(["values", "numberFormat"]); return ctx.sync() .then(function () { r1.numberFormat = r0.numberFormat; }) .then(ctx.sync) .then(function () { r1.values = r0.values; }) }); } 

Answer 1

You can copy the number format after, so it could be something like:

 function test () { Excel.run(function (ctx) { var ws = ctx.workbook.worksheets.getItem("Sheet1"); var r0 = ws.getRange("A2:A10"); var r1 = ws.getRange("K2:K10"); r0.load(["values", "numberFormat"]); return ctx.sync().then(function () { r1.numberFormat = "@"; r1.values = r0.values; r1.numberFormat = r0.numberFormat; }) }); } 

Answer 2

You should be able to do one of two things:

1) If you set the format of the range number to text ("@"). Before applying the values, the values ​​must remain as they are.

2) You can also add an apostrophe (') to the beginning of the text to force Excel to treat it as plain text, regardless of what the internal engine would normally consider as.

UPDATE

There are actually two things here. The text and base value are displayed.

To get the text, open the range.text property (2D array). This will be the text displayed in the user interface. To get the base value, refer to the range.values ​​property (also a two-dimensional array)

If you copy values ​​from one place to another or save them and then restore, remember to save both range.numberFormat and range.values . And then bring them back, first with .numberFormat.

UPDATE 2

See screenshot below. The code works as expected.

~ Mikhail Zlatkovsky, developer of the Office Extensibility Platform, MSFT