Skip to main content

Excel export date format not coming based on OS short date

Ronny
  • Edited

Hi Team,

I am scheduling a report to export the report data in excel to be send as an email attachment.

In my report code, I am doing this

<ExcelColumnFormat  ExcelColumnWidth="Auto" DataType="Date" ExcelFormat="Short Date"  ExcelAutoFitRow="True"/>

The report shows date on browser-based on my local language short date which is fine.

But when I schedule the report to send report data as excel attachment in an email to someone and when that person opens the excel from email, his local language short date is not picked.

Also in excel for date cell column the format if we check then its Custom category instead of Date category.  

So How this thing can be achieved 

 

 

1

Comments

6 comments

Date Votes
  • Official comment
    Natan Cohen

    Hi Ronny,

    By default, the user culture is obtained from the setting in the client browser.  I'm not sure if Excel exports adhere to the client's browser setting.  I would suggest asserting more control over this behavior instead of leaving it "to chance", so to speak.  I suggest you store the user's culture string as an account setting and then save that culture string to session memory.  You could then have a date format assigned as follows - "MM/dd/yyyy" for US and "dd/MM/yyyy" for Europe - in your startup process.  I would then tokenize Globalization -> UserCulture and ExcelColumnFormat -> ExcelFormat attributes.  You'll get more consistent results this way and the user can change their culture setting if they wish.

    Thanks,
    Natan  

  • Ronny

    Hi Natan, 

    This solution will work if user is downloading excel on his machine. But the issue that we have here is that we are scheduling an excel generated from logi application and then sending the generated excel as an attachment in an email. 

    So now when the user downloads it from the received email. What we have is the format in excel changes to custom and not to date data type

     


    0
  • Natan Cohen

    Hi Ronny,

    The solution there would be to ensure that the scheduler is impersonating the user when generating the report to send out.  You could take advantage of the SchedulerRunAs attribute to let the scheduler know whom to impersonate.  You could also set the SchedulerSessionVars attribute to pass in the user's culture string.  Both of these attributes are found on the Procedure.SchedulerCreateTask element in a Process type definition.  I would avoid the SchedulerUsername attribute on the Security element, as setting this will cause every scheduled task to impersonate the same user.

    Thanks,
    Natan
    Logi Support

    0
  • Ronny

    Hi Natan, 

    I think your suggestion is to store the user culture and then use that culture the time we are scheduling the report. But this again is not the case. 

    Let me explain more about this. Suppose scheduler is in the USA. Now even if we get the US culture to be used the time we are scheduling the report to be sent as an email, the culture will stick to the USA Culture for this report. 

    Now, the time user (located in Australia) to whom we have sent an email using scheduler from the USA, will open the attachment from the received email should get the date column in the attached excel based on Australian date culture, not based on the USA date culture, according to my requirement.

    I hope I was able to make you understand the problem in more detail.

    So is there any solution for this or its a Logi limitation.

    1
  • Natan Cohen
    • Edited

    Hi Ronny,

    I will investigate to see if Excel exports honor the date/time format set.  Perhaps there's an issue there.  But just to clarify, are you looking for time zone conversion as well as date/time format, or are you simply looking to display dates in Excel according to the user's culture setting?  Two very different things.  Also, I will look into why Excel thinks that they data type is "Custom" instead of "Date".

    Thanks,
    Natan

    0
  • Natan Cohen

    Hi Ronny,

    I was able to confirm that the Excel export adheres to the culture string set in the UserCulture attribute on the Globalization element in the Settings definition.  I tested with "en-us" and "fr-fr".  I also confirmed that when the culture string is not set to "en-us", the data type is "Custom" instead of "Date".  I will open a ticket for Engineering to investigate this further, but you should be able to get your scheduled exports to use the correct date format by leveraging the user's culture string as an @Session token.

    Thanks,
    Natan

    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post