Your Privacy Matters: We use our own and third-party cookies to improve your experience on our website. By continuing to use the website we understand that you accept their use. Cookie Policy
540
How to retain column format when data exported to Excel
posted

Hello,

I am using Ultrawingrid (10.1) to display data to the user in our windows application. Facing an issue with how the data is shown when grid data is exported to excel.

Below is a break up of how data is displayed after exporting it to excel under various regional settings (these settings changed on my machine and then ran the export functionality)

Regional & Language Options (Settings)

Grid Display

Excel Display

Standards & Formats: English (United States) Location: United States

Time Zone: Eastern Time (US & Canada)
GMT - 5

05/17/2011

05/17/2011

Standards & Formats: English (United Kingdom) Location: United Kingdom

Time Zone: GMT (London)

17/05/2011

17/05/2011  05:00:00

Standards & Formats: Chinese (Hong Kong S.A.R. )

Location: Hong Kong S.A.R.

Time Zone: GMT + 8

17/05/2011

17/05/2011  12:00:00

Date time value stored in database: 05/17/2011 12:00:00 AM 

 

 

 

How to retain the display format as shown in the grid even after the data is exported to excel?  (At present no format is set for the column)

Different options tried so far to ensure data format is retained in excel:

  1. Column[“Date”].Format = “d”
  2. Column[“Date”].FormatInfo = System.Globalization.CultureInfo.GetSpecificCulture(System.Globalization.CultureInfo.CurrentCulture)
  3. Column[“Date”].Format = “MM/dd/yyyy” 

·        (Drawback of this will be in regions other than US, the date value will be displayed as MM/dd/yyyy and not in the local region fomat. This we want to avoid).  Even after settign this format when exported data is shown with time for UK and HongKong region

  1. Column[“Date”].MaskInput = “{Date}”

In one of the earlier post, it is mentioned the display retain issue has been addressed in 8.1

http://community.infragistics.com/forums/p/3500/18486.aspx#18486

 

Can anyone suggest how to address this scenario? Just need to ensure the data (date) display format is retained in the excel after export.

 

When the machine settings are set to US region the exported data works fine, only when the region is changed the time part is extra.

 

Thanks,

narasimha

Parents
No Data
Reply
  • 469350
    Verified Answer
    Offline posted

    Hi Narasimha,

    I am a bit confused by your question. The link you posted above has the answer to your question. It does not say anything about this being "resolved" in any version. It says you have to handle the  InitializeColumn event and translate the format to a format that Excel understands.

    When you export a date to Excel, the date is exported as a date - there's no inherent formatting applied to it. Are you saying that you want the date to be formatted based on the culture that was applied when you run the application? That doesn't make a lot of sense - the date is probably formatted by Excel to match the current culture on the machine, not the culture of the application that exported it.

    If that is what you want, then you would have to see if Excel has support for culture-specific formatting. I would be very surprised if it has such support.

    Another option would be to handle the CellExported event of the UltraGridExcelExporter and write out a string in whatever format you want.

Children