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

[Victoria F]

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]

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]

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]

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

[Victoria F]

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]

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

[Srinivas]

Cell data is jumping to next row if data  is  more 

[Srinivas]

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

[Anton]

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

 

 

[Sumila]

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]

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

[Sumila]

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]

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