Export in Excel keeping columns' format the same as a grid in UI for WinForms GridView

Victoria F

46 posts

Member since:
May 2010

Posted 12 Oct 2011 Link to this post

Hello guys,

I'm exporting hierarchical grid to Excel.
The problem is that while exporting the grid it's loosing the formatting of the columns.
Is there any property to set that keep format the same as grid has?
I have more then 50 columns with a different format .. do I have to format it twice for the grid view and for the Excel ?

Can I use ExcelCellFormatting event to copy format of the grid cells to Excel?

Thank you ,
Victoria.

Emanuel Varga

1336 posts

Member since:
May 2010

Posted 13 Oct 2011 Link to this post

Hello Victoria,

Yes, you have to use ExcelCellFormatting and you have to perform some kind of mapping of the actual grid formatting to excel style formatting.

Hope this helps, if you have any other questions or comments, please let me know,

Best Regards,
Emanuel Varga

Telerik WinForms MVP

Victoria F

46 posts

Member since:
May 2010

Posted 13 Oct 2011 Link to this post

Hello Emanuel,
Do you have any example of mapping of the formats between grid and Excel file.
For example I have formatted grid with :

radGridView_LayersSummary.Columns["A"].Width = 90;

radGridView_LayersSummary.Columns["A"].FormatString = "{0:N0}";

radGridView_LayersSummary.Columns["B"].FormatString = "{0:N4}";

radGridView_AcctSummary.Columns["C"].FormatString = "{0: M/d/yyyy}";

What should I write in ExcelCellFormatting event ?
Please, give me an example of this event.

Thank you ,
Victoria.

Martin Vasilev

Admin

1061 posts

Posted 17 Oct 2011 Link to this post

Hello Victoria,

Thank you for writing.

To format the exported data in the appropriate way, you can use grid column's properties ExcelExportType and ExportFormatString:

this.radGridView1.Columns["Date"].ExcelExportType = DisplayFormatType.Custom;

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

Column's width, background and foreground colors usually are automatically transfered even if they are applied by grid's CellFormatting event. ExcelCellFormatting event can be used for any additional and specific export formatting. More details you can find in our product documentation.

Hope this helps. Let me know if you have any additional questions.

Greetings,
Martin Vasilev
the Telerik team

Q2’11 SP1 of RadControls for WinForms is available for download (see what's new); also available is the Q3'11 Roadmap for Telerik Windows Forms controls.

Victoria F

46 posts

Member since:
May 2010

Posted 26 Oct 2011 Link to this post

Martin,

Thank you for this example. It works very good for a simple grid.
When I started to use export from the grid I counted that formatting of the grid somehow automatically can be apply to the Excel while exporting..
I have multilevel grid. So if I set column "C" as a Date and my child template suppose to be mapped to column "C" also as a decimal field with a different formatting.
What should I do in this case ? How can I format the Excel file?

Thank you,
Victoria.

Martin Vasilev

Admin

1061 posts

Posted 31 Oct 2011 Link to this post

Hello Victoria F,

Thank you for getting back to me.

I confirm that it is possible to have issues with formatting in export hierarchy scenarios, because MS Excel do not natively support hierarchical data and child rows data can get into columns with different type of data.

You can work-around this limitation by manually specifying the value in ExcelCellFormatting event. For example, you can consider the following code:

void exporter_ExcelCellFormatting(object sender, ExcelCellFormattingEventArgs e)

{

if (e.GridCellInfo.ViewTemplate == this.radGridView1.MasterTemplate.Templates[0]

&& e.GridCellInfo.RowInfo is GridViewDataRowInfo

&& e.GridColumnIndex == 1)

{

e.ExcelCellElement.Data.DataType = DataType.String;

e.ExcelCellElement.Data.DataItem = "my formated value";

}

Do not hesitate to contact me again if you have any other questions.

Greetings,
Martin Vasilev
the Telerik team

Q2’11 SP1 of RadControls for WinForms is available for download (see what's new); also available is the Q3'11 Roadmap for Telerik Windows Forms controls.

Srinivas

2 posts

Member since:
Sep 2013

Posted 11 Mar 2014 Link to this post

Cell data is jumping to next row if data is more

ExcelCell.jpg

Srinivas

2 posts

Member since:
Sep 2013

Posted 12 Mar 2014 in reply to Srinivas Link to this post

Can u please reply to above post ,How to remove the cell wrap to only one Column

Anton

Admin

167 posts

Posted 14 Mar 2014 Link to this post

Hello Srinivas,

Thank you for writing.

To achieve that you should subscribe to the ExelCellFormatting event and to set the WrapText property to false for each cell in desired column.

e.ExcelStyleElement.AlignmentElement.WrapText = false;

You can find more information for exporting to excel in the following documentation article:
http://www.telerik.com/help/winforms/gridview-exporting-data-export-to-excel-via-excelml-format.html

I hope this helps.

Regards,
Anton
Telerik

DevCraft Q1'14 is here! Watch the online conference to see how this release solves your top-5 .NET challenges. Watch on demand now.

Sumila

7 posts

Member since:
Sep 2018

Posted 10 Oct 2018 Link to this post

Hello Team,

I was trying to do something similar. I am trying to export radgrid data to excel. I would like to keep only expanded row have child rows in Excel when created. Following is the code I try but the excel created have all rows childrows expanded even when only one row is expanded in the grid. Below is the code I use.

1.From the usercontrol we calling the common method for excel export

var gridControl = this.FindControl("UserControlsSharedDynamicGrid") as DynamicGrid;
                var dynamicGrid = gridControl.FindControl("gvDynamicGrid") as RadGrid;

                ExcelExportHelper method = new ExcelExportHelper();
                method.ExcelExport(dynamicGrid);

2. Common method for export to excel

       public void ExcelExport(dynamic grid)
        {
            //xls format
            string alternateText = "Html";

            grid.ExportSettings.Excel.Format = (GridExcelExportFormat)Enum.Parse(typeof(GridExcelExportFormat), alternateText);

            grid.ExportSettings.ExportOnlyData = true;

            grid.ExportSettings.IgnorePaging = true;

            grid.ExportSettings.OpenInNewWindow = true;

            grid.MasterTableView.UseAllDataFields = true;
            //if (grid.MasterTableView.DetailTables.Count > 0)
            //{
            //    grid.MasterTableView.HierarchyLoadMode = GridChildLoadMode.Conditional;
            //    grid.MasterTableView.DetailTables[0].HierarchyLoadMode = GridChildLoadMode.Conditional;
            //}
            grid.MasterTableView.ExportToExcel();
        }

Hristo

Admin

1521 posts

Posted 11 Oct 2018 Link to this post

Hello Sumila,

Thank you for writing.

The preferred way to export the grid to Excel is to use the GridViewSpreadExport class. The exporting engine behind it is working natively in the .Xlsx format and it has various advantages. You can refer to the following section of the documentation with detailed information: https://docs.telerik.com/devtools/winforms/gridview/exporting-data/spread-export.

I hope this helps. Let me know if you need further assistance.

Regards,
Hristo
Progress Telerik

Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.

Sumila

7 posts

Member since:
Sep 2018

Posted 11 Oct 2018 in reply to Hristo Link to this post

Thanks for prompt response. Sorry if I dint mention above I'm trying to export data in web application and not windows. Thanks in advance.

Hristo

Admin

1521 posts

Posted 11 Oct 2018 Link to this post

Hello Sumila,

Please post your question in the correct forum for the product you are using: https://www.telerik.com/forums.

Regards,
Hristo
Progress Telerik

Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.