Search code examples
javascriptexceloffice-jsexcel-addins

Apply String type to excel range - Javascript

do anyone knows how to apply to two column the property type = string instead of date?

My problem is that when a user inserts a new date Excel automatically translate this into date format (so in a number) i want to block this behaviour to work with the plain string.

Solution

  • Dates are stored as integers representing the number of days since January 1, 1900, which is stored as number 1, to December 31, 9999 stored as 2958465.

    So when a user types the 2020/1/1, it will automatically convert to 43831, which means 43831 days after 1900/1/1, the reason is that Excel converts what use type to date format, because it matches the default date format.

    enter image description here

    If you want to change this default behavior, You could use range.numberFormat API to preset the cell that user might want to type. which will convert numberFormat of the used Range to Text.

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

    const range = sheet.getUsedRange();
    range.numberFormat = '@';
    await context.sync();
  });