Export error on Date column.

12 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. UI for WinForms is Visual Studio 2017 Ready
  4. 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.
  5. 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 
  6. 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.
  7. erwin
    erwin avatar
    358 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();  
                    }  
                }  
            }  
        } 
  8. 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
  9. 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.
  10. 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
  11. 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.
  12. 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
  13. 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.
Back to Top
UI for WinForms is Visual Studio 2017 Ready