Grid export to Excel with formatting

23 posts, 0 answers
  1. DukeVideo
    DukeVideo avatar
    48 posts
    Member since:
    Jun 2006

    Posted 23 Nov 2007 Link to this post


    Hi,

    I have a grid ...

    http://www.dukevideo.com/images/for_external/23112007/ss.jpg

    ... which I would like to have exported to Excel.

    Is there any nice function to do this? I can't find one, so have started writing an ActiveX thing to do it.

    But ... I would particularly like to reflect the formatting and/or grouping and/or filtering of the data in the grid. (By filtering, I mean only send the data displayed, not to activate the Excel filtering).

    In the absence of a nice "ExportToExcel()" function (which would be an ace idea), is it possible to interrogate the grid for styling, grouping and filtering?

  2. Dimitar Kapitanov
    Admin
    Dimitar Kapitanov avatar
    632 posts

    Posted 27 Nov 2007 Link to this post

    Hi DukeVideo,
    Currently we do have functionality to export to Excel. You can see it in our "Export to Excel" example in the Examples application. While we do export data, we do not export appearance at the moment. We render B&W grid presentation optimized for printing.

    Best wishes,
    Dimitar Kapitanov
    the Telerik team

    Instantly find answers to your questions at the new Telerik Support Center
  3. UI for ASP.NET Ajax is Ready for VS 2017
  4. DukeVideo
    DukeVideo avatar
    48 posts
    Member since:
    Jun 2006

    Posted 27 Nov 2007 Link to this post

    Hi,

    I can't seem to find any evidence of an Export To Excel function in the Examples Application, just three sliding features with no functionality.

    Are there are code resources that can point me that way? Or, even better, if I can get to the formatting myself ....

  5. Dimitar Kapitanov
    Admin
    Dimitar Kapitanov avatar
    632 posts

    Posted 29 Nov 2007 Link to this post

    Hello DukeVideo,
    You can find the example under RadGridView > Export to Excel node of the treeview navigation in our Examples application (I'am attaching a screenshot of the example for easier reference). You must have a reference to TelerikData.dll in your application to have this functionality (the assembly is provided with every release).
    I have included the code of the example also:

    using System;  
    using System.Collections.Generic;  
    using System.ComponentModel;  
    using System.Data;  
    using System.Drawing;  
    using System.Text;  
    using System.Windows.Forms;  
    using System.Data.OleDb;  
    using System.IO;  
    using Telerik.Data;  
    using Telerik.QuickStart.WinControls;  
    using Telerik.WinControls.UI;  
    using Telerik.WinControls;  
     
     
    namespace Telerik.Examples.WinControls.GridView.Export  
    {  
        public partial class Form1 : ExamplesForm  
        {  
            // Fields  
            private RadGridViewExcelExporter exporter;  
            private DataSet dataSet;  
            private System.Windows.Forms.BindingSource employeeBindingSource;  
            private Telerik.Examples.WinControls.DataSources.NwindDataSetTableAdapters.EmployeesTableAdapter employeeTableAdapter;  
     
            public Form1()  
            {  
                InitializeComponent();  
                this.radGridView1.ThemeName = "ControlDefault";  
            }  
     
            private void Form1_Load(object sender, EventArgs e)  
            {  
                this.radGridView1.GridElement.BeginUpdate();  
                this.customersTableAdapter.Fill(this.nwindRadGridView.Customers);  
                this.radGridView1.MasterGridViewTemplate.EnableFiltering = true;  
                this.radGridView1.MasterGridViewTemplate.AutoExpandGroups = true;  
                this.radGridView1.DataSource = customersBindingSource;  
                this.radGridView1.GridElement.EndUpdate();  
            }  
     
            private void export_Click(object sender, EventArgs e)  
            {  
                saveFileDialog.Filter = "Excel (*.xls)|*.xls";  
                if (saveFileDialog.ShowDialog() == DialogResult.OK)  
                {  
                    if (!saveFileDialog.FileName.Equals(String.Empty))  
                    {  
                        FileInfo file = new FileInfo(saveFileDialog.FileName);  
                        if (file.Extension.Equals(".xls"))  
                        {  
                            Export(saveFileDialog.FileName);  
                        }  
                        else 
                        {  
                            MessageBox.Show("Invalid file type");  
                        }  
                    }  
                    else 
                    {  
                        MessageBox.Show("Please enter a file name.");  
                    }  
                }  
            }  
     
            private void Export(String filepath)  
            {  
                this.exportButton.Enabled = false;  
                exporter = new RadGridViewExcelExporter();  
                BackgroundWorker worker = new BackgroundWorker();  
                worker.DoWork += new DoWorkEventHandler(DoWork);  
                worker.RunWorkerCompleted += new RunWorkerCompletedEventHandler(bg_RunWorkerCompleted);  
                worker.RunWorkerAsync(filepath);  
                exporter.Progress += new ProgressHandler(exportProgress);  
            }  
     
            private void DoWork(object sender, DoWorkEventArgs e)  
            {  
                exporter.Export(this.radGridView1, (String)e.Argument, "newSheet1");  
            }  
     
            //Update the progress bar with the export progress  
            private void exportProgress(object sender, ProgressEventArgs e)  
            {  
                // Call InvokeRequired to check if thread needs marshalling, to access properly the UI thread.  
                if (this.InvokeRequired)  
                {  
                    this.Invoke(new EventHandler(  
                        delegate{  
                            if (e.ProgressValue <= 100)  
                            {  
                                radProgressBar1.Value1 = e.ProgressValue;  
                            }  
                            else 
                            {  
                                radProgressBar1.Value1 = 100;  
                            }  
                    }));  
                }  
                else 
                {  
                    if (e.ProgressValue <= 100)  
                    {  
                        radProgressBar1.Value1 = e.ProgressValue;  
                    }  
                    else 
                    {  
                        radProgressBar1.Value1 = 100;  
                    }  
                      
                }  
            }  
     
            // when the worker finishes the export we can do some post processing  
            private void bg_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)  
            {  
                exportButton.Enabled = true;  
                this.radProgressBar1.Value1 = 0;  
                MessageBox.Show("Exporting Finished.");  
            }  
        }  


    Regards,
    Dimitar Kapitanov
    the Telerik team

    Instantly find answers to your questions at the new Telerik Support Center
  6. harra
    harra avatar
    1 posts
    Member since:
    Sep 2006

    Posted 02 Feb 2008 Link to this post

    Hello,

    Ive got a small issue with the export.
    DateTime data from the table I want to convert, becomes a number in the excel cell/column (serialized datetime?)
    I would like to see real dates here..
    How can I do this?

    Barry
  7. Jordan
    Admin
    Jordan avatar
    547 posts

    Posted 06 Feb 2008 Link to this post

    Hello,

    This issue is due to some limitations/bugs in the Excel COM Interop.
    In order to view the dates in a human-readable format you can select the cells that contain the dates and format them to show dates (right click on the selected cells -> Format Cells...-> choose date format).

    We intend a complete rework the Excel exporting functionality later this year.

    Our targets are:
    • greater export speed
    • overcome issues like yours
    Unfortunately I cannot give you specific time frame for this improved Excel export functionality.

    Best wishes,
    Jordan
    the Telerik team

    Instantly find answers to your questions at the new Telerik Support Center
  8. Kris
    Kris avatar
    47 posts
    Member since:
    Feb 2008

    Posted 10 Jun 2008 Link to this post

    I have the same situation. I am exporting a radgridview to excel with multiple datetime columns. Is there a way to easily convert the datetime column to a textbox column, then export?? And also, the only columns exported are the ones visible right?? If that is the case, then I can just hide the datetime columns, show the textbox columns, export, then reverse it after the export. Thanks.


  9. erwin
    erwin avatar
    358 posts
    Member since:
    Dec 2006

    Posted 11 Jun 2008 Link to this post

    Due to the Excel COM Object Model, DateTime must be converted before you send it to Excel using the ToOADate method.
    Also you need to take care of the localization settings of the machine and Office.

    Type celltype = gridCell.ColumnInfo.GetCellType(gridRow);

    if (celltype == typeof(DateTime))
    {

        DateTime val = (DateTime)gridCell.Value;

        _excel.Cells[row, col] = val.ToOADate();

    }

  10. erwin
    erwin avatar
    358 posts
    Member since:
    Dec 2006

    Posted 11 Jun 2008 Link to this post

    BTW, I've written a protoype for an own application to export to Excel. Send an e-mail to info@richardconsulting.ch if you would like the code.

    In addition to the datetime issue, I've also included options to export either all or only the visible columns and all or only the filtered rows.

    Excel interface is very important in my planned app, so I'm probably going to work more on that. Prototype status at the moment.

    (RadControls 2007 Q1SP1, Office XP and later)
  11. Kris
    Kris avatar
    47 posts
    Member since:
    Feb 2008

    Posted 11 Jun 2008 Link to this post

    Thanks for the help. I'm not using the COM Object model though, I'm using the built in export provided by telerik. If I could just add a Textbox column to my already databound grid, copy over the data from the DateTime column, then export, I think that would solve my problem. Too bad they don't have a DateTimeColumnInfo.ToTextboxColumnInfo() function....that would be perfect.
  12. erwin
    erwin avatar
    358 posts
    Member since:
    Dec 2006

    Posted 11 Jun 2008 Link to this post

    IMHO you'd be better off if you'd write the Excel export yourself as a workaround until telerik fix the export functionality. Not that much work really (less than 100 lines of code or so for a simple grid) and gives you complete control.

    I'll send you my code for a start if you want.
  13. Kris
    Kris avatar
    47 posts
    Member since:
    Feb 2008

    Posted 11 Jun 2008 Link to this post

    Yes, that would be a good side project to work on, because excel exporting requests and issues are always popping up around here, but for now, I found an alternate way. I just cast my datetime columns to varchar columns in sql. Thanks for the help Erwin.
  14. erwin
    erwin avatar
    358 posts
    Member since:
    Dec 2006

    Posted 11 Jun 2008 Link to this post

    Depending on the international formatting, you’ll loose the capability to sort correctly in the grid though.

    For example, the correct way to display a date in Germany is

     DD.MM.YYYY     15.03.2008          

  15. Martin Vasilev
    Admin
    Martin Vasilev avatar
    1061 posts

    Posted 13 Jun 2008 Link to this post

    Hello guys,

    We plan to improve the Excel importing mechanism and resolve the DateTime formatting issue. Nevertheless, we highly appreciate any suggestions or examples of export scenarios. So, Erwin, we'd greatly appreciate it if you could send us your code and share your experience.

    In addition, you might want to explore a possibility that's just become available. You can use the print and export capabilities of our Telerik Reporting product. We recently released a class library that provides easy report generation from a RadGridView. Please find more information in the following Code Library article:
    http://www.telerik.com/community/code-library/submission/b311D-bedcch.aspx


    If you have other questions, do not hesitate to contact me again.

    Best wishes,
    Martin Vasilev
    the Telerik team

    Instantly find answers to your questions at the new Telerik Support Center
  16. Jacob
    Jacob avatar
    10 posts
    Member since:
    Sep 2008

    Posted 09 Feb 2009 Link to this post

    I have the same issue with "Harra":

    "DateTime data from the table I want to convert, becomes a number in the excel cell/column (serialized datetime?)
    I would like to see real dates here..
    How can I do this?"


    Is this issue solved already? I wasn't able to export datetime field properly even if i used GridViewDateTimeColumn. What could be a possible solution to this?

    Thank you
  17. Pramod Goutham
    Pramod Goutham avatar
    19 posts
    Member since:
    Aug 2008

    Posted 10 Feb 2009 Link to this post

    Hi I was just checking this feature of export to excel. I would like to know whether there are any options to add some header and footer info along with the grid data while exporting to excel..

    Also, whenever I export to excel using the code discussed here, the grid lines in excel file do not appear. How can this be avoided?

    Any help is welcome.

    Thanks,
    Pramod Goutham.
  18. Martin Vasilev
    Admin
    Martin Vasilev avatar
    1061 posts

    Posted 12 Feb 2009 Link to this post

    Hi all,

    Thank you for writing.

    Since we added Export as ExcelML functionality, most of the issues discussed in this topic have been addressed.  The primary goal of this method is fast exporting of RadGridView with its current visual appearance and data to a format that is compatible with MS Excel. We do not support adding objects as additional rows, cells, footers or headers. Nevertheless, you can work-around this by adding needed data in RadGridView before exporting and removing it after.

    Concerning the questions about Date formatting, we have recently provided ExcelExportType and ExcelExportFormatString (only if the export type is a custom one) properties of GridViewDataColumn. You can use them to specify the exported excel type or string in any particular grid column:

    radGridView1.MasterGridViewTemplate.Columns["Col3"].ExcelExportType = Telerik.WinControls.UI.Export.DisplayFormatType.Custom;  
    radGridView1.MasterGridViewTemplate.Columns["Col3"].ExcelExportFormatString = "MM/dd/yyy";  
     
     
    Please find more resources for RadGridView exporting capabilities at the following links:
    http://www.telerik.com/help/winforms/export-excel.html

    http://blogs.telerik.com/martinvassilev/posts/09-01-07/Export_RadGridView_Data_to_Microsoft_Excel.aspx

    http://blogs.telerik.com/johnkellar/posts/09-01-27/Exporting_data_to_Excel_using_the_RadGridView_for_WinForms.aspx

    http://blogs.telerik.com/martinvassilev/posts/08-06-11/Export_and_Printing_RadGridView_using_Telerik_Reporting.aspx

    Hope this information is helpful. Do not hesitate to write me back if you have additional questions.

    Sincerely yours,
    Martin Vasilev
    the Telerik team

    Check out Telerik Trainer, the state of the art learning tool for Telerik products.
  19. ganesh shankar
    ganesh shankar  avatar
    5 posts
    Member since:
    Feb 2009

    Posted 17 Feb 2009 Link to this post

    sir
    when i m exporting  RadGrid1(Grid)  in excel then datetime column's  align goes to right in Excel.
    what is the solution for my asp.net application.
    pls reply soon.
  20. Daniel
    Admin
    Daniel avatar
    4943 posts

    Posted 17 Feb 2009 Link to this post

    Hello Ganesh,

    Please try the following approach:
    protected void RadGrid1_ExcelExportCellFormatting(object source, ExcelExportCellFormattingEventArgs e) 
        e.Cell.HorizontalAlign = HorizontalAlign.Left; 

    More information is available in our documentation:
    Export to Microsoft Excel/Word/PDF/CSV

    Let us know if you need further assistance.

    Regards,
    Daniel
    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.
  21. ganesh shankar
    ganesh shankar  avatar
    5 posts
    Member since:
    Feb 2009

    Posted 18 Feb 2009 Link to this post

    sir
    i could not find the event handler RadGrid1_ExcelExportCellFormatting(object source, ExcelExportCellFormattingEventArgs)
    of RadGrid1 in my asp.net application.
    sir i have 3 Events For this-
     1-RadGrid1_ExcelMLExportRowCreated
     2-RadGrid1_ExcelMLExportStylesCreated
     3-RadGrid1_GridExporting
    Any alternate solution sir?
    Thanks for Reply
    Ganesh
  22. Sebastian
    Admin
    Sebastian avatar
    9934 posts

    Posted 18 Feb 2009 Link to this post

    Hi ganesh,

    Verify that you are using the latest release 2008.3.1314 of RadControls for ASP.NET AJAX in your project. Upgrade instructions can be found in the KB article linked below:

    http://www.telerik.com/support/kb/aspnet-ajax/general/updating-radcontrols-for-asp-net-to-another-version-or-license.aspx

    Regards,

    Sebastian
    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.
  23. ganesh shankar
    ganesh shankar  avatar
    5 posts
    Member since:
    Feb 2009

    Posted 18 Feb 2009 Link to this post

    sir actually i m using following version of your telrik product.
    2008.2.723.35
    thanks
    Ganesh
  24. Eric Skaggs
    Eric Skaggs avatar
    39 posts
    Member since:
    Nov 2008

    Posted 03 Apr 2009 Link to this post

    Do you have OnExcelExportCellFormatting?  This should be an attribute available to set on the RadGrid control.

    Here's the code for my RadGrid:

    1 <telerik:RadGrid  
    2                 ID="grdView"  
    3                 runat="server" 
    4                 Skin="Office2007" 
    5                 AllowPaging="true" 
    6                 PageSize="20" 
    7                 OnNeedDataSource="grdView_NeedDataSource" 
    8                 OnColumnCreated="grdView_ColumnCreated" 
    9                 OnItemCommand="grdView_ItemCommand" 
    10                 OnItemDataBound="grdView_ItemDataBound" 
    11                 OnPreRender="grdView_PreRender" 
    12                 AllowFilteringByColumn="true" 
    13                 EnableLinqExpressions="false" 
    14                 OnExcelMLExportRowCreated="grdView_ExcelMLExportRowCreated" 
    15                 OnExcelMLExportStylesCreated="grdView_ExcelMLExportStylesCreated" 
    16                 OnExcelExportCellFormatting="grdView_ExcelExportCellFormatting" 
    17 > 

    Hope you find a solution!

    Eric Skaggs
Back to Top
UI for ASP.NET Ajax is Ready for VS 2017