This is a migrated thread and some comments may be shown as answers.

Export in Excel keeping columns' format the same as a grid

12 Answers 369 Views
GridView
This is a migrated thread and some comments may be shown as answers.
Victoria F
Top achievements
Rank 1
Victoria F asked on 12 Oct 2011, 08:15 PM
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.

12 Answers, 1 is accepted

Sort by
0
Emanuel Varga
Top achievements
Rank 1
answered on 13 Oct 2011, 06:57 AM
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
0
Victoria F
Top achievements
Rank 1
answered on 13 Oct 2011, 02:36 PM
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.
0
Martin Vasilev
Telerik team
answered on 17 Oct 2011, 04:01 PM
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.

0
Victoria F
Top achievements
Rank 1
answered on 26 Oct 2011, 06:08 PM
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.
0
Martin Vasilev
Telerik team
answered on 31 Oct 2011, 11:30 AM
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.

0
Srinivas
Top achievements
Rank 1
answered on 12 Mar 2014, 01:17 AM
Cell data is jumping to next row if data  is  more 
0
Srinivas
Top achievements
Rank 1
answered on 12 Mar 2014, 05:00 AM
Can  u please reply to above post ,How to remove the cell wrap to only one Column
0
Anton
Telerik team
answered on 14 Mar 2014, 05:14 PM
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.

 
0
Sumila
Top achievements
Rank 1
answered on 10 Oct 2018, 11:17 AM

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

0
Hristo
Telerik team
answered on 11 Oct 2018, 12:32 PM
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.
0
Sumila
Top achievements
Rank 1
answered on 11 Oct 2018, 12:50 PM
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. 
0
Hristo
Telerik team
answered on 11 Oct 2018, 03:23 PM
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.
Tags
GridView
Asked by
Victoria F
Top achievements
Rank 1
Answers by
Emanuel Varga
Top achievements
Rank 1
Victoria F
Top achievements
Rank 1
Martin Vasilev
Telerik team
Srinivas
Top achievements
Rank 1
Anton
Telerik team
Sumila
Top achievements
Rank 1
Hristo
Telerik team
Share this question
or