Export DataGridView to Microsoft Excel

The DataGridView is such a useful control for any database application. Sometimes, just having this data in an application is not enough. Many people like to have it available in a common file format. Enter Microsoft Excel. This article should teach you how to export information from a DataGridView to an Excel document.

To begin, remember that Excel files are glorified XML documents. This makes the exporting very simple. The only “tricky” part can be the styling of the document. Take a look.

First, set up the project. All you need is a form with a Button and a DataGridView. You can organize it however you want.

Second, to open our file once it has been written, you need to declare the ShellExecute function in your form’s class. This is not necessary if you plan only to save the file without opening it.

    Private Declare Function ShellEx Lib "shell32.dll" Alias "ShellExecuteA" ( _
        ByVal hWnd As Integer, ByVal lpOperation As String, _
        ByVal lpFile As String, ByVal lpParameters As String, _
        ByVal lpDirectory As String, ByVal nShowCmd As Integer) As Integer

Third, you are going to create a sub that will export the data for you. In short, you will create a blank file and write to it using the StreamWriter class. Start with the creation of the file and the opening lines of the Excel document. Don’t forget; this is just a glorified XML document.

When calling this sub, you will need to supply four parameters: the DataGridView, the desired filename, the Excel extension you want to use, and the path to which you desire to save the file. I have added this functionality for the sake of user customization. Also, note that Excel 2007 uses *.xlsx as a new file extension. If you attempt to open an *.xls file with 2007, you will receive the following message. Simply click “Yes” and it will open fine.

    Private Sub exportExcel(ByVal grdView As DataGridView, ByVal fileName As String, _
        ByVal fileExtension As String, ByVal filePath As String)

        ' Choose the path, name, and extension for the Excel file
        Dim myFile As String = filePath & "\" & fileName & fileExtension

        ' Open the file and write the headers
        Dim fs As New IO.StreamWriter(myFile, False)
        fs.WriteLine("<?xml version=""1.0""?>")
        fs.WriteLine("<?mso-application progid=""Excel.Sheet""?>")
        fs.WriteLine("<ss:Workbook xmlns_ss=""urn:schemas-microsoft-com:office:spreadsheet"">")