Exporting data to Excel using the RadGridView for WinForms

There usually comes a time for most developers when they need to use the data being displayed in an application, outside of that application. There are obviously a number of ways you can present data, but it is common to leverage Microsoft Excel. It provides a similar look and feel to the grid which means minimal reformatting, plus Excel is widely used and therefore most offices should have a version of the software available. 

How to get your data into Excel?  The RadGridView offers a couple of options to accomplish this task. First, you can use the Primary Interop Assemblies to work with Excel to export your data. I am not going to cover this option, but you can accomplish the task in this way. I will point out that this option does require Excel on the end user system to perform the export 

The second method is to use the ExcelML format which does not require that Excel be installed on the end users system when exporting. Previously Martin Vassilev described why ExcelML is good, so here is a quick example on how to use it. Before I get into the code, I should point out that ExcelML is really just an XML representation of an Excel workbook. You can look at what Excel generates by saving an existing spreadsheet in the XML Spreadsheet format. To demonstrate, I have created a simple Excel workbook with some data.

I choose 'SaveAs' and selected the 'XML Spreadsheet 2003' option and saved the file. 

After the file is saved, I open it in Notepad and you will see that the entire workbook is in XML format. When this file is opened in Excel it will display exactly as I originally created it. 

Back to the question at hand, how do you export your data from the RadGridView to Excel. Get started by adding a new RadGridView and load it with some data.

You will see in my example that I have a two buttons, one for Excel 2007 and which just says Excel.  In Excel 2007 some of the limitations in older versions were increased, specifically the 65536 row limit was raised to 1048576.  In code the difference is simple an argument, but I thought it would make sense to differentiate in the interface so it is more understandable.  The code below will export the grid to a file names 'sample.xml'.  

You can see that the ExcelMaxRows setting dictates that we will be limited to 65536 rows. The final parameter indicates whether visual settings will be exported, here I am setting it to False. Below you see the end result in Excel.

All the data is presented in classic Excel fashion, but it looks kind of bland.

I have made a few changes to the code. The ExcelMaxRows setting is now set to the Excel 2007 maximum row value. I also changed the final parameter to True so we will see our alternating colors. Finally, I have changed the extension of the file from .xml to .xls. This does not change the behavior of the exporting functionality, but it does demonstrate a gotcha. 

Remember, we are exporting an XML representation of an Excel spreadsheet.  If you choose to use the '.xls' extension, your user will see the message above.  Clicking 'Yes" will open the file as expected, but it will be a training issue for the user if you have '.xls' as an extension.   

So the final result looks much better.  The rows are colored as expected, so it looks like the grid in the application. The RadGridView provides a quick and easy way of getting your data into Excel. It is does not require Excel on the users system when exporting and is remarkably fast. When exporting data with the ExcelML format it will be compatible with Microsoft Excel 2002 and newer.