Search code examples
javascriptexceloffice-jsoffice-js-helpersexcel-dates

Persisting the date format in excel while fetching data

We have a excel add in built using office.js. Where we read data from a data range ( ie A1:C10 ) which might consist of a date column. Please refer the image below

enter image description here

Our add in reads the data and form a CSV dataset which is downloaded when the user clicks download button. We read the data using the following lines of code

  Excel.run({ delayForCellEdit: true }, async context => {
   this.rangeInput.nativeElement.blur();
   let sheet;
   sheet = context.workbook.worksheets.getItem("Sheet1");
   let range = sheet.getRange("A1:D10");
​   range.load("values");
   range.load("numberFormat");
   this.isDataLoading = true;
   this.updateLoading();
   await context.sync();
   let rangeData = range.values;
   let dataFormat = range.numberFormat;
 })

In the above code we have fetched the data format of each cell by loading number format of the range ( ierange.load("numberFormat") which returns a format array. We use this format array to decide if we have a date in our selection. The major challenge is that we have a lot of date format supported in excel and we are implementing date conversion logic for each pattern separately. Do we have any generic method that converts excel serial into any supported ( ie all formats in 270 locations ) date pattern in excel.

To checkout the patterns supported in excel please try selecting different patterns by formatting the cell. ( ie right click on a cell -> Format cell-> Date, then change the format ).

enter image description here

we did try https://stackoverflow.com/a/16233621/10523731 which converts to ISO format but we are expecting the date's to be in same pattern as user formats them in excel ( ie Wednesday, April 8 2020 should be the same when we convert it from excel serial ).

Sample excel serial - "8/22/2011 is 40777 in excel". Please click here to understand excel serial

Solution

  • Thanks Naveen for your question, we actually are building a new API for this scenario. This API is now in beta preview. This API is targeting to release in 1.12 which would be GA in this fall.

    API range.numberFormatCategories which represent the category of number format of each cell of the range. it will return the categories in

    enter image description here

    here is the sample code for this API

      await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getActiveWorksheet();
    sheet.activate();
    let range = sheet.getRange("A1:C3");
    range.load("address");
    range.load("numberFormatCategories");
    await context.sync();
    console.log("address: " + range.address);
    console.log("NumberFormatCategory: " + range.numberFormatCategories);
  });

    Here is the document https://learn.microsoft.com/en-us/javascript/api/excel/excel.range?view=excel-js-preview#numberformatcategories

    You can try this gist in Windows and Excel online. https://gist.github.com/lumine2008/475327d889031ced8daf4a87b08c832c