Gridview export to excel

[Murtaza]

Hi

I am using radgridview (windows) export to excel functionality and I would like to keep the same currency format e.g ({0:€#,#0;(€#,#0);-}).

Is there is any option available there to do that?

Please reply urgently

Thanks

[Emanuel Varga]

Hello Murtaza,

Please take a look at this help article.

It is explained there that by handling the ExcelCellFormatting you can do all the formatting you need on the excel document while exporting.

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

Best Regards,
Emanuel Varga

Telerik WinForms MVP

[Murtaza]

Hi

Thanks for your reply.

I am using ExcelCellFormatting method to do formatting but I don't know which property to use to format number

 

If e.GridRowInfoType Is GetType(GridViewTableHeaderRowInfo) Then

 

 

Dim border As BorderStyles = New BorderStyles()

 

border.Color = Color.Black

border.Weight = 2

border.LineStyle = LineStyle.Continuous

border.PositionType = PositionType.Bottom

e.ExcelStyleElement.Borders.Add(border)

 

ElseIf e.GridRowIndex = 2 AndAlso e.GridColumnIndex = 1 Then

 

e.ExcelStyleElement.InteriorStyle.Color = Color.Yellow

e.ExcelStyleElement.AlignmentElement.WrapText =

True

 

 

ElseIf e.GridColumnIndex = 2 Then

 

 

'''' which property to set

 

 

End If

 

[Emanuel Varga]

Hello again,

You just need to set the ExcelExportFormatString for that column, like so:

grid.Columns["Id"].ExcelExportType = DisplayFormatType.Custom;
grid.Columns["Id"].ExcelExportFormatString = "€#,#0";

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

Best Regards,
Emanuel Varga

Telerik WinForms MVP

[Murtaza]

Thanks,

I works fine.

[Viki]

Hi,

I have two issues with the exporting:

1. When I open the exported excel file (.xls) I always get the following warning:

The file you are trying to open, '...xls', is in a different format than specified by the file extension.
* I tried to use both: ExportFormat.Html and ExportFormat.ExcelML with the same result

* I don't get this warning when I try to open some another excel file (that wasn't exported from the grid view)

2. In the grid view cell I have a data separated by line break ('\n'), but in the exported excel file, the data in the cell is separated by a space (' '). Is there a way to keep the line break format in the cell?

Thanks,
Viki

[Ivan Petrov]

Hello Viki,

Thank you for writing.

1. The reason for the message you get is that the ExportToExcelML exports the grid to an xml format which is not the native format for an .xls. The idea behind this is that Excel is usually not associated by default to open xml files and when you export to Excel, you would expect to get a file that would be opened by Excel. Since Excel can open files with different format than the extension suggests, the message is the price to pay for the Excel icon and association of the exported file.
2. Currently, there is no way to keep the line breaks as all the text is escaped and the line breaks are replaced by an interval. I have added a feature request into our Public Issue Tracking System - PITS for an option that will allow the users to turn off the escaping of special characters. You can use the following link to track the progress of the feature and to add your vote/comment for it - PITS Feature. I have added the "\n" line break to the special characters collection and from our upcoming service pack it will be escaped with a character that Excel uses for line breaks. 

I have also updated your Telerik points for the good suggestion.

I hope this will be useful for you. Should you have further questions I would be glad to help.

Regards,
Ivan Petrov
the Telerik team

[Viki]

Hi, thanks for the replay!

But I get this message even with the ExportFormat.Html.
Is there a way to export to Excel without to get this message?

Thanks,
Viki

[Ivan Petrov]

Hello Viki,

Thank you for writing back.

To remove the warning, you have to change the extension of the exported file. You can do that using the following code snippet for a reference:

ExportToExcelML exporter = new ExportToExcelML(this.radGridView1);
exporter.FileExtension = "xml";

I hope this will help. If you need further assistance, I would be glad to provide it.

Kind regards,
Ivan Petrov
the Telerik team

[Fadi]

Dear Ivan, i have a winForm application contains a radGridView, i am exporting it into excel in this way...
inside a button i have this code.

ExportToExcelML export = new ExportToExcelML(this.radGridView1);
export.ExportVisualSettings = true;
export.RunExport(saveFileDialog1.FileName);

i still need to have borders in the exported excel file, how to do it?
Thanks 

[Ivan Petrov]

Hi Fadi,

Thank you for writing.

Please, find attached an example project which demonstrates how to enable borders in the exported excel file.

I hope this will be useful. Do not hesitate to write back with any further questions.
 
Greetings,
Ivan Petrov
the Telerik team

[Fadi]

Thanks a lot, it worked perfect.
Still have the same table i am exporting it to PDF file. and i am using almost the same method...

ExportToPDF PDF = new ExportToPDF(radGridView1);
PDF.ExportVisualSettings = true;
PDF.RunExport(saveFileDialog1.FileName);

how to export borders as well for a PDF file?
Thanks.

[Ivan Petrov]

Hello Fadi,

Thank you for writing back.

I am glad I was able to help. The ExportToPDF has a property which controls the width of the exported table. Here is an example:

ExportToPDF export = new ExportToPDF(this.radGridView1);
export.TableBorderThickness = 1;

I hope this will help. Should you have further questions, I would be glad to help.
 
Kind regards,
Ivan Petrov
the Telerik team

[Fadi]

Thanks very much

[Acr]

Hello Ivan

 

We are still trying to add a line break inside a Cell, in the Excel exported file, generated by the RadTreeListView, we tried with either <br /> or &#10;

and also setting up the Wrap Text view, still doesn't work.

 

My question about the: adding the line break to the special characters collection, is it available? 

if it is, where can we get a Demo?

 

Thank you.

Best regards,

Carlos

[Hristo]

Hi Carlos,

Thank you for writing.

This thread discussing RadGridView for WinForms. Please check the control and product you are using. In case you are using our RadTreeView control, you can refer to the following documentation article: http://docs.telerik.com/devtools/winforms/treeview/export-data/spread-export.

I hope this helps. Should you have further questions please do not hesitate to write back.

Regards,
Hristo
Telerik by Progress