Excel Template Date Formatting

Excel Template Date Formatting

Posted by: jonhenning on 7 August 2023, 9:31 am EST

• 

  jonhenning

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 7 August 2023, 9:31 am EST

  I am having trouble getting dates to format. Is there some way to have a date passed in for a template and have it formatted. I can specify a format on the cell (in Excel), but the initial render does not format it. If I put the cell in edit mode, then tab off it formats correctly.

  Background
  
  I am using a Json datasource, which by default uses ISO8601 format. I have customized my json serializer to specify the following formant.

  M/d/yyyy hh:mm:ss tt

  Workaround
  
  I have found a hack to workaround this. It involves binding the field in one cell and setting a custom format on it to

  ;;;

  so it is hidden, then in another cell specify

  =DATEVALUE(A1)

  and placing a format on that

  Placing hidden fields for every thing I need to format is a bit much. Hoping you can provide a better answer.

• 

  nitin.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 7 August 2023, 5:39 pm EST

  Hi,

  We have replicated this issue. This seems like a bug. So, we have escalated it to the development team. Will get back to you once we have any update from them[Internal Tracking Id - DOCXLS-8679]

  Also, please provide a small stripped sample for the workaround that you followed. We are unable to replicate this at our end. This will help us for the investigation.

  Regards,
  
  Nitin

• 

  jonhenning

  • Post Options:
  • Link
    Copy

  Posted 8 August 2023, 1:28 am EST

  Here is a simple xlsx template with the date workaround.

  By the way I also tried using conditional formatting thinking that may work, but found it had the same issue.

• 

  nitin.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 8 August 2023, 5:51 pm EST

  Hi,

  As per the development team, If you have set a custom format: m/d/yyyy hh:mm:ss AM/PM. So the datasource should be an OADate instead of a string.

  You can pass OADate and it will be converted to the exact formatted date.
  
  For e.g., 43762.4252314815 is equivalent to 10/24/2019 10:12:20 AM.

  Please refer to the attached sample for the same: JsonFile_csharp.zip

  Best Regards,
  
  Nitin

• 

  jonhenning

  • Post Options:
  • Link
    Copy

  Posted 11 August 2023, 5:38 am EST

  That gave me an idea. Make an adapter that will check every cell in the used range and detect if it is in ISO8601 format. If it determines it is then convert it ToOADate.
  
  Something like this.

  workbook.ProcessTemplate();
  
  foreach (var ws in workbook.Worksheets)
  {
      var usedRange = ws.GetUsedRange();
      
      for (var r = 0; r < usedRange.Rows.Count; r++) 
      {
          for (var c = 0; c < usedRange.Cells.Count; c++)
          {
              var cell = usedRange.Rows[r].Cells[c];
              var cellValue = cell.Value != null ? cell.Value.ToString() : "";
              DateTime dateTime;
              if (DateTime.TryParse(cellValue.ToString(), null, System.Globalization.DateTimeStyles.RoundtripKind, out dateTime))
              {
                  //verify the date we care about is actually in ISO8601 format
                  var regex = new Regex(@"^\d{4}-\d\d-\d\dT\d\d:\d\d:\d\d(\.\d+)?(([+-]\d\d:\d\d)|Z)?$", RegexOptions.IgnoreCase);
                  if (regex.IsMatch(cellValue.ToString()))
                      cell.Value = dateTime.ToOADate();
              }
          }
      }
  }

• 

  nitin.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 15 August 2023, 4:40 pm EST

  Hi,

  You can try this too. Make sure it accepts OADate. So, that the format will apply correctly.

  Regards,
  
  Nitin