How to retain column format when data exported to Excel

540

Narasimha posted over 13 years ago

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:

Column[“Date”].Format = “d”
Column[“Date”].FormatInfo = System.Globalization.CultureInfo.GetSpecificCulture(System.Globalization.CultureInfo.CurrentCulture)
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

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

540

Narasimha posted over 13 years ago in reply to Narasimha

i have addressed the formatting issue in excel by implementing the InitializeColumn Event as suggested

void objUltraGridExcelExporter_InitializeColumn(object sender, InitializeColumnEventArgs e)
        {
            if (e.Column.DataType.ToString() == "System.DateTime" )
            {
                switch (System.Globalization.CultureInfo.CurrentCulture.Name.ToLower())
                {
                    case "en-gb" : // Great Britain
                    case "zh-hk": // Hong Kong
                        e.ExcelFormatStr = "dd/mm/yyyy";
                        break;
                    default:
                        e.ExcelFormatStr = "MM/dd/yyyy";
                        break;
                }
            }
        }

Thanks

Narasimha
Cancel
Reply

Cancel
540

Narasimha posted over 13 years ago in reply to Mike Saltzman

hi Mike,

I have attached a zip file which has data and screen shots of the UI screen. The same data when exported to excel (after changing the Regional settings on my machine to : US / UK / Hong Kong) are represented in different forms.

When a date is exported to excel i agree it is/should be exported as it is, but for UK and HK settings you will notice an aditional time component is shown along with date. I am trying to ensure user irrespective of their location when the data is exported it is shown as it is in the grid (avoid the extra time part). The format of the date that is MM/dd/yyyy or dd/mm/yyyy that will automatically change based on the current machine culture, both in the UI controls and Excel.

User can format the column within excel after exporting , i want to avoid that and handled it within code. I will try out the initialize column and CellExported event.

With regards to the link, i was referring to Ken's last reply in the post where it is mentioned for DateTime formating issue with Excel just changed the version of 8.1 and for custom formating it has been handled through InitializeComponent event.

Thanks,

Narasimha
Cancel
Reply

Cancel
469350

Verified Answer
Offline Mike Saltzman posted over 13 years ago

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.
Cancel
Reply

Reject Answer

Cancel