Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Disable the automatic conversion to Date

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:

enter image description here

And in Excel, it shows:

enter image description here

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:

enter image description here

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.

enter image description here

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.

enter image description here

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; })
        });
}
like image 567
SoftTimur Avatar asked Jul 06 '16 13:07

SoftTimur


People also ask

How do I stop Excel from automatically converting text?

Prevent converting text string to number by Format Cells To stop converting text string to number, you just need to format the cells as text formatting before you typing the string. 1. Select the cells you want to enter the text strings, and right click to display the context menu, then select Format Cells.

Why is Excel converting my numbers to dates?

While this issue is not directly related to Datawrapper, it's a common problem with Excel that can be frustrating and time-consuming: Excel is trained to "detect" formats, and sometimes the software wrongly assumes that a certain number represents a date. If you enter 12/2, it will change to 2-Dec or 12.


1 Answers

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.

enter image description here

~ Michael Zlatkovsky, Developer on Office Extensibility Platform, MSFT

like image 175
Michael Zlatkovsky - Microsoft Avatar answered Oct 09 '22 10:10

Michael Zlatkovsky - Microsoft