Export error on Date column.

17 posts, 0 answers
  1. panuwat wanpiam
    panuwat wanpiam avatar
    11 posts
    Member since:
    May 2006

    Posted 29 Jun 2009 Link to this post

    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

  2. Paul
    Paul avatar
    31 posts
    Member since:
    Sep 2012

    Posted 30 Jun 2009 Link to this post

    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.
  3. Martin Vasilev
    Admin
    Martin Vasilev avatar
    1061 posts

    Posted 01 Jul 2009 Link to this post

    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.
  4. panuwat wanpiam
    panuwat wanpiam avatar
    11 posts
    Member since:
    May 2006

    Posted 02 Jul 2009 Link to this post

    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 
  5. Amador
    Amador avatar
    4 posts
    Member since:
    Feb 2009

    Posted 02 Jul 2009 Link to this post

    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.
  6. erwin
    erwin avatar
    401 posts
    Member since:
    Dec 2006

    Posted 02 Jul 2009 Link to this post

    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();  
                    }  
                }  
            }  
        } 
  7. Prashant goyani
    Prashant goyani avatar
    5 posts
    Member since:
    Jul 2009

    Posted 06 Jul 2009 Link to this post

    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
  8. Martin Vasilev
    Admin
    Martin Vasilev avatar
    1061 posts

    Posted 07 Jul 2009 Link to this post

    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.
  9. Prashant goyani
    Prashant goyani avatar
    5 posts
    Member since:
    Jul 2009

    Posted 07 Jul 2009 Link to this post

    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
  10. Martin Vasilev
    Admin
    Martin Vasilev avatar
    1061 posts

    Posted 10 Jul 2009 Link to this post

    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.
  11. Prashant goyani
    Prashant goyani avatar
    5 posts
    Member since:
    Jul 2009

    Posted 10 Jul 2009 Link to this post

    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
  12. Martin Vasilev
    Admin
    Martin Vasilev avatar
    1061 posts

    Posted 16 Jul 2009 Link to this post

    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.
  13. Vidhya
    Vidhya avatar
    2 posts
    Member since:
    Mar 2018

    Posted 13 Mar 2018 Link to this post

    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";

     

     

     

     

  14. Dess | Tech Support Engineer, Sr.
    Admin
    Dess | Tech Support Engineer, Sr.  avatar
    4086 posts

    Posted 15 Mar 2018 Link to this post

    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
    Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
  15. Vidhya
    Vidhya avatar
    41 posts
    Member since:
    Aug 2010

    Posted 19 Mar 2018 in reply to Dess | Tech Support Engineer, Sr. Link to this post

    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.

  16. Dess | Tech Support Engineer, Sr.
    Admin
    Dess | Tech Support Engineer, Sr.  avatar
    4086 posts

    Posted 20 Mar 2018 Link to this post

    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
    Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
  17. Vidhya
    Vidhya avatar
    2 posts
    Member since:
    Mar 2018

    Posted 20 Mar 2018 in reply to Dess | Tech Support Engineer, Sr. Link to this post

    Hi,

    I used  ExportToExcelM.it solved my problem...

    Thank you so much .....

Back to Top