CODEWITHSUNDEEP
exceloffice-js
agradl
agradl

Reputation: 3536

How to stop excel from auto formatting when setting values using office.js

I am trying to set a cell value to consistently be a text value. I do not want any formatting at all.

I'm using the following code:

async function setValue() {
    try {
        await Excel.run(async (context) => {
            const sheet = context.workbook.worksheets.getItem("Sample");

            const range = sheet.getRange("C3");
            range.values = [[ 'June 2018' ]];
            range.format.autofitColumns();

            await context.sync();
        });
    }
    catch (error) {
        OfficeHelpers.UI.notify(error);
        OfficeHelpers.Utilities.log(error);
    }
}

And this results in a date of Jun-18 in the cell. I've found this answer which could be a workaround, but prefixing all of my data with ' is going to confuse my users. The issue also happens with values like 01.

Upvotes: 3

Views: 1429

Answers (2)

Michael Zlatkovsky
Michael Zlatkovsky

Reputation: 8670

Great question! First, let me make a general statement: Excel programmability is, at its core, about automation -- which is doing the same thing that the user would do, but programmatically. As such, you can often gleam from the UI what you should do in programming.

Now, in regular Excel, if you type in June 2018 into a blank cell on a new worksheet, you will get the same experience as you just described. That's because the cell is formatted as "General", which means it will try to snap into a particular type. If you want it to stay as text (rather than a formatted date, which is what Ricky suggested in the last line of his answer), you need to first set the number formatting to text, which in Excel is the @ symbol, and then enter in the value.

await Excel.run(async (context) => {
    const range = context.workbook.getSelectedRange();
    range.numberFormat = [["@"]];
    range.values = [["June 2018"]];

    await context.sync();
});

Again, let me emphasize that -- just like in the UI -- you need to set the formatting before you set the value. Doing it the other way around would get you the text "43252" into the cell (which, coincidentally, the number of days since January 0th [yes, 0th] of 1900 -- random trivia for the day).

Of course, in telling Excel "this is just text", you lose the ability to do other date things on it -- so Ricky's answer re. number formatting ("mmmm yyyy") might still be a better fit. But in terms of your stated question of not having Excel auto-format anything for you, just be sure to first set the number formatting and then set the values.

Upvotes: 3

Rick Kirkham
Rick Kirkham

Reputation: 9659

If you really need it to be a string, you have to get the data into a string that won't trigger Excel's coercion of date-like strings to Date objects. One way to do this is to swap the blank space with a non-breaking space. Replace this line:

range.values = [[ 'June 2018' ]];

with the following lines:

let date = 'June 2018';
let dateAsString = date.replace(' ', '\xa0');
range.values = [[dateAsString]];

BTW, if you just want it to look like the input string, but are willing to allow it to be a Date type, then you could just add the following line below the line that creates the range object:

range.numberFormat = [["mmmm yyyy"]];

Upvotes: 0

Related Questions