Disable the automatic conversion to Date

Question

In one workbook (from somebody else) that I need to analyse, they use a formula to construct a list of strings: 04-09-01, 04-09-02, 04-09-03, etc, which have general as format. In a part of my code, I will need to copy these values to somewhere else. However, because these values are quite special, they are automatically considered as Date (whereas they are clearly not dates for users) and transformed to 09/04/2001, 09/04/2002, 09/04/2003, etc. As a consequence, the values are completely changed, and the calculation based on these pasted values lead to errors.

Here is a 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 the values are completely changed:

And in Excel, it shows:

Note that, Excel itself does NOT systematically transform these values to dates. For instance, if we value-only copy 04-09-01 to another cell. Excel does raise a warning and suggests to convert it to a date, but users could well ignore this warning and keep 04-09-01 as it is:

So my question is, is there a way or workaround to disable this automatic conversion in JavaScript API, so that we could faithfully copy values?

Edit 1:

I tried to use numberFormat to keep the initial formats of a range. First, I put A2:A0 as follows with General as 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 turned out to be the follows, and has Date as format.

So the restoring of numberFormat does not help?

Edit 2: I made an example which copies a range to another. I want r1 to have exactly same number format and values as r0. But the result shows that 04-09-01 as general in r0 produces 04/09/2001 as Date in r1. So basically, the problem is the same as in the previous example: numberFormat cannot be faithfully 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 should be able to do one of two things:

1) If you set the number format of the range to text ("@") BEFORE you apply the values, the values should stay as is.

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

UPDATE

There's actually two things at stake here. There is the displayed text vs. the underlying value.

To get the text, access the range.text property (2D array). This will be the text as displayed in the UI. To get the underlying value, access the range.values property (also a 2D array)

If you are copying values from one place to another, or storing them and then restoring, be sure to store both the range.numberFormat and range.values. And then restore them back, with .numberFormat first.

UPDATE 2

See the screenshot below. The code works as expected.

~ Michael Zlatkovsky, Developer on Office Extensibility Platform, MSFT

Answer 2

You can copy the number format after, so maybe 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; 
        })
    });
}