Grid export to Excel with formatting

[DukeVideo]

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?

[Dimitar Kapitanov]

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

[DukeVideo]

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

[Dimitar Kapitanov]

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

[harra]

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

[Jordan]

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

[Kris]

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.

[erwin]

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();

}

[erwin]

BTW, I've written a protoype for an own application to export to Excel. Send an e-mail to [e-mail] 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)

[Kris]

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.

[erwin]

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.

[Kris]

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.

[erwin]

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          

[Martin Vasilev]

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

[Jacob]

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

[Pramod Goutham]

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.

[Martin Vasilev]

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.

[ganesh shankar]

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.

[Daniel]

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.

[ganesh shankar]

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

[Sebastian]

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.

[ganesh shankar]

sir actually i m using following version of your telrik product.
2008.2.723.35
thanks
Ganesh

[Eric Skaggs]

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

Here's the code for my RadGrid:

	<telerik:RadGrid
	ID="grdView"
	runat="server"
	Skin="Office2007"
	AllowPaging="true"
	PageSize="20"
	OnNeedDataSource="grdView_NeedDataSource"
	OnColumnCreated="grdView_ColumnCreated"
	OnItemCommand="grdView_ItemCommand"
	OnItemDataBound="grdView_ItemDataBound"
	OnPreRender="grdView_PreRender"
	AllowFilteringByColumn="true"
	EnableLinqExpressions="false"
	OnExcelMLExportRowCreated="grdView_ExcelMLExportRowCreated"
	OnExcelMLExportStylesCreated="grdView_ExcelMLExportStylesCreated"
	OnExcelExportCellFormatting="grdView_ExcelExportCellFormatting"
	>

Hope you find a solution!

Eric Skaggs