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; })
});
}
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.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With