Export error on Date column.

[panuwat wanpiam]

When i n export Gridview to excel column that contain datetime data like '14/06/2009' will export like '30987' value.

Did some know how to solve this?

Thank

[Paul]

Not ideal but you can open the Excel sheet, select the column containing the date, right click and selected format cells. Change the content to Date and it should be ok.  As for doing it programmatically, I'm not sure but it is somethng I will be looking at in the next few weeks so Im keen to see how to do it.

[Martin Vasilev]

Hi guys,

You can specify the exporting format by using ExcelExportType and ExcelExportFormatString properties. Please, review the code-block bellow:
 

this.radGridView1.Columns["Date"].ExcelExportType = DisplayFormatType.Custom;
this.radGridView1.Columns["Date"].ExcelExportFormatString = "dddd, dd.MM.yyyy";

Hope this helps. Do not hesitate to contact me again if you have other questions.
 

Best wishes,
Martin Vasilev
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.

[panuwat wanpiam]

Hi Martin,

Thank for your help. But it still not work. My export code was simple like this.

        Me.Cursor = Cursors.WaitCursor
        Me.RadGrid_total.Columns("invoicedate").ExcelExportType = DisplayFormatType.Custom
        Me.RadGrid_total.Columns("invoicedate").ExcelExportFormatString = "dddd, dd.MM.yyyy"
        If SaveFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
            Dim excelML As New ExportToExcelML
            excelML.RunExport(Me.RadGrid_total, Me.SaveFileDialog1.FileName, ExportToExcelML.ExcelMaxRows._65536, False)
        End If
        Me.Cursor = Cursors.Arrow

[Amador]

I have the same problem, but exporting decimal data. At the Grid I have 50,00 but it exports like 5000,00.

On my expoprting code i wrote this.

 

 

 

this.rgvFacturas.Columns["TOTAL"].ExcelExportType = DisplayFormatType.Currency;

 

 

but still not work.

Thanks and sorry about my English.

[erwin]

didn't you mean 50,0000 instead of 50,00 ?

I could also not get correct formatting with the telerik exporting tools - plus I had issues with large number of columns in the grid using too much memory.

Here is a very simplistic export function that formats dates and currency as I expect it (supports only simple grids).
May give you a starting point. You have to change the ErrorMessage.Show() to your needs.

    public class ExcelXmlExporter
    {
        public bool ExportHiddenColumns { get; set; }
        public bool ExportHiddenRows { get; set; }
        public void Export(RadGridView grid, string filename)
        {

            XmlTextWriter wr = null;

            const string NS_SPREADSHEET = "urn:schemas-microsoft-com:office:spreadsheet";
            const string NS_EXCEL = "urn:schemas-microsoft-com:office:excel";

            try
            {
                wr = new XmlTextWriter(filename, Encoding.Default);
                wr.Formatting = Formatting.Indented;
                wr.Indentation = 4;
                wr.WriteStartDocument();
                wr.WriteStartElement("Workbook", NS_SPREADSHEET);
                wr.WriteAttributeString("xmlns", "ss", null, NS_SPREADSHEET);
                wr.WriteAttributeString("xmlns", "x", null, NS_EXCEL);
                wr.WriteStartElement("Styles", NS_SPREADSHEET);

                wr.WriteStartElement("Style", NS_SPREADSHEET);
                wr.WriteAttributeString("ID",NS_SPREADSHEET,"octopusDateTimeStyle");
                wr.WriteStartElement("NumberFormat",NS_SPREADSHEET);
                wr.WriteAttributeString("Format", NS_SPREADSHEET, "General Date");
                wr.WriteEndElement();
                wr.WriteEndElement();

                wr.WriteStartElement("Style", NS_SPREADSHEET);
                wr.WriteAttributeString("ID", NS_SPREADSHEET, "octopusDecimalStyle");
                wr.WriteStartElement("NumberFormat", NS_SPREADSHEET);
                wr.WriteAttributeString("Format", NS_SPREADSHEET, "Currency");
                wr.WriteEndElement();
                wr.WriteEndElement();

                wr.WriteEndElement();
                wr.WriteStartElement("Worksheet", NS_SPREADSHEET);
                wr.WriteAttributeString("Name", NS_SPREADSHEET, "Sheet");
                wr.WriteStartElement("Table", NS_SPREADSHEET);

                if(grid.Rows.Count>0)
                {
                    foreach(GridViewCellInfo cell in grid.Rows[0].Cells)
                    {
                        if(cell.ColumnInfo.IsVisible||ExportHiddenColumns)
                        {
                            wr.WriteStartElement("Column", NS_SPREADSHEET);
                            wr.WriteAttributeString("Width", NS_SPREADSHEET, "110");

                            if(cell.ColumnInfo.DataType==typeof(DateTime))
                            {
                                wr.WriteAttributeString("StyleID", NS_SPREADSHEET, "octopusDateTimeStyle");
                            }
                            else if(cell.ColumnInfo.DataType==typeof(System.Decimal))
                            {
                                wr.WriteAttributeString("StyleID",NS_SPREADSHEET,"octopusDecimalStyle");
                            }

                            wr.WriteEndElement();
                        }
                    }
                }

                wr.WriteStartElement("Row", NS_SPREADSHEET);
                foreach (GridViewColumn col in grid.Columns)
                {
                    if (col.IsVisible || ExportHiddenColumns)
                    {
                        wr.WriteStartElement("Cell", NS_SPREADSHEET);
                        wr.WriteStartElement("Data", NS_SPREADSHEET);
                        wr.WriteAttributeString("Type", NS_SPREADSHEET, "String");
                        wr.WriteString(col.HeaderText);
                        wr.WriteEndElement();
                        wr.WriteEndElement();
                    }
                }
                wr.WriteEndElement();
                foreach (GridViewRowInfo row in grid.Rows)
                {
                    wr.WriteStartElement("Row",NS_SPREADSHEET);
                    foreach (GridViewCellInfo cell in row.Cells)
                    {
                        if (cell.ColumnInfo.IsVisible || ExportHiddenColumns)
                        {

                            if(cell.ColumnInfo.DataType==typeof(DateTime))
                            {
                                wr.WriteStartElement("Cell", NS_SPREADSHEET);
                                wr.WriteStartElement("Data", NS_SPREADSHEET);
                                wr.WriteAttributeString("Type", NS_SPREADSHEET, "DateTime");
                                DateTime dt = (DateTime)cell.Value;
                                wr.WriteString(dt.ToString("s")+".000");
                                wr.WriteEndElement();
                                wr.WriteEndElement();
                            }
                            else if(cell.ColumnInfo.DataType==typeof(long)||cell.ColumnInfo.DataType==typeof(int)||cell.ColumnInfo.DataType == typeof(System.Decimal))
                            {
                                wr.WriteStartElement("Cell", NS_SPREADSHEET);
                                wr.WriteStartElement("Data", NS_SPREADSHEET);
                                wr.WriteAttributeString("Type", NS_SPREADSHEET, "Number");
                                wr.WriteString(cell.Value.ToString());
                                wr.WriteEndElement();
                                wr.WriteEndElement();
                            }
                            else if (cell.ColumnInfo.DataType == typeof(bool))
                            {
                                wr.WriteStartElement("Cell", NS_SPREADSHEET);
                                wr.WriteStartElement("Data", NS_SPREADSHEET);
                                wr.WriteAttributeString("Type", NS_SPREADSHEET, "Boolean");
                                bool bl = (bool)cell.Value;
                                if(bl)
                                {
                                    wr.WriteString("1");
                                }
                                else
                                {
                                    wr.WriteString("0");
                                }
                                wr.WriteEndElement();
                                wr.WriteEndElement();
                            }
                            else
                            {
                                wr.WriteStartElement("Cell", NS_SPREADSHEET);
                                wr.WriteStartElement("Data", NS_SPREADSHEET);
                                wr.WriteAttributeString("Type", NS_SPREADSHEET, "String");
                                wr.WriteString(cell.Value.ToString());
                                wr.WriteEndElement();
                                wr.WriteEndElement();
                            }
                        }
                    }
                    wr.WriteEndElement();
                }

                wr.WriteEndDocument();
            }
            catch (System.Exception ex)
            {
                ErrorMessage.Show("Error while Exporting", ex);
            }
            finally
            {
                if (wr != null)
                {
                    wr.Close();
                }
            }
        }
    }

[Prashant goyani]

Hi
I could not find ExcelExportType Property of Datacolumn..is there any referance is missing or any this else...in Excel sheet it disply like 39456 insted of date..plz reply ASAP

[Martin Vasilev]

Hello folks,

Thank you all for writing.

In some old versions of RadControls for Winforms, there is a known issue that the specified excel export format does not applying when exporting with visual settings is set to false. Again, if you miss ExcelExportType property, it is because you are using an outdated version.

Fortunately, Q2 2009 release is already released and it addresses those issues. Moreover, the ExportToExcelML class is completely revised and now it performs better. Also you will find additional new useful features like two new events that allows customization of the exported file. You can find mode details in the release notes. I recommend you upgrade to the new version and give it a try.

Write me back if you have any additional questions.

Greetings,
Martin Vasilev
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.

[Prashant goyani]

Hi...

Thanks for your Help..I got that functinality in new version..and but while i export date column to excel i could not disply proper date format..i have allready try below two type but it is still not worling can you help me ASAP>
 

this.radGridView1.Columns["Date"].ExcelExportType = DisplayFormatType.Custom;
this.radGridView1.Columns["Date"].ExcelExportFormatString = "dddd, dd.MM.yyyy";

Thanks
Prashant

[Martin Vasilev]

Hi Prashant goyani,

Thank you for contacting me back.
 
I was unable to reproduce described behavior. You can find a working sample of how to format a datetime column in our example application. Just navigate to GridView >> Export to Excel >> ExcelML with Events. If you still experience any difficulties with Excel formatting, please, open a new support ticket and send me a sample project that demonstrates your scenario. This will help me to investigate your custom case and provide you with further assistance. 

Sincerely yours,
Martin Vasilev
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.

[Prashant goyani]

Hi..

Thanks for your support..now its workign with ExportToExcelEvent. But Is there any way that i can Export images to Excel sheet from Gridview.

Thanks,
Prashant

[Martin Vasilev]

Hi Prashant goyani,

Thank you for the questions.

Unfortunately the ExcelML format does not support images, and this limitations comes from Microsoft.
 

Regards,
Martin Vasilev
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.

[Vidhya]

Hi, i  also tried this code .but no result .

1.save date in toshortdatestring

2.retrive and disply in grid (format dd/MM/yyyy)

3.also export in same Format.but while exporting shows numbers only.

here my code

                DataTable dt = dbo.GetRecordsWithQuery("Select ID,Date,Reason,Amount from Expense");
             
                Grid.DataSource = dt;
           
                Grid.Columns["Date"].FormatString = "{0:dd/MM/yyyy}";
                Grid.Columns["Date"].ExcelExportType = DisplayFormatType.Custom;
                Grid.Columns["Date"].ExcelExportFormatString = "dd/MM/yyyy";

 

 

 

 

[Dess | Tech Support Engineer, Principal]

Hello, Vidhya,

Thank you for writing.  

I have prepared a sample project for your reference demonstrating how to export the DateTime values in specific formats. The obtained result is illustrated in the below screenshot.



Please have a look at the attached project. Does it produce the same export result on your end? Is the grid's setup different? Do I need to perform some changes in order to replicate some undesired behavior?

I am looking forward to your reply. 
Regards,
Dess
Progress Telerik

[Vidhya]

Thanks for the Reply Sir.

But i could not open that file.i am using vs 2005. also i am using  telerik q2 2009 sp1. in that you mentioned GridviewSpreadExport not available.

[Dess | Tech Support Engineer, Principal]

Hello, Vidhya,  

GridViewSpreadExport utilizes our RadSpreadProcessing library to export the content of RadGridView. It requires .NET Framework 4.0 or later. Since you use Visual Studio 2005, for lower .NET Framework versions you can use the ExportToExcelM. Additional information is available in the following help article: https://docs.telerik.com/devtools/winforms/gridview/exporting-data/export-to-excel-via-excelml-format

However, I would recommend you to upgrade to a newer .NET Framework and thus you can benefit from the GridViewSpreadExport which is a more powerful export.

I hope this information helps. If you have any additional questions, please let me know. 

Regards,
Dess
Progress Telerik

[Vidhya]

Hi,

I used  ExportToExcelM.it solved my problem...

Thank you so much .....