Export DataGridView to Microsoft Excel

Monday May 5th 2008 by Paul Avery

Learn how to export information from a DataGridView to a Microsoft Excel file.

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"">")

Fourth, you want to create the styles for the column headers and the regular cells. Microsoft Excel does this at the beginning of the document rather than within each cell. The positive for this method would be a much smaller file size due to predefining the style with a simple ID rather than writing the whole style in each node.

        ' Create the styles for the worksheet
        fs.WriteLine("  <ss:Styles>")
        ' Style for the column headers
        fs.WriteLine("    <ss:Style ss:ID=""1"">")
        fs.WriteLine("      <ss:Font ss:Bold=""1""/>")
        fs.WriteLine("      <ss:Alignment ss:Horizontal=""Center"" ss:Vertical=""Center"" " & _
        fs.WriteLine("      <ss:Interior ss:Color=""#C0C0C0"" ss:Pattern=""Solid""/>")
        fs.WriteLine("    </ss:Style>")
        ' Style for the column information
        fs.WriteLine("    <ss:Style ss:ID=""2"">")
        fs.WriteLine("      <ss:Alignment ss:Vertical=""Center"" ss:WrapText=""1""/>")
        fs.WriteLine("    </ss:Style>")
        fs.WriteLine("  </ss:Styles>")

Fifth, you need to create the actual worksheet. This includes the contents for both the column headers and the general information cells. In the following code, you will see a loop through the columns to get the width, and then another loop through to write the actual text of the column header. After that, it will loop through the rows grabbing each column's text. This is a simple X and Y loop.

        ' Write the worksheet contents
        fs.WriteLine("<ss:Worksheet ss:Name=""Sheet1"">")
        fs.WriteLine("  <ss:Table>")
        For i As Integer = 0 To grdView.Columns.Count - 1
            fs.WriteLine(String.Format("    <ss:Column ss:Width=""{0}""/>", _
        fs.WriteLine("    <ss:Row>")
        For i As Integer = 0 To grdView.Columns.Count - 1
            fs.WriteLine(String.Format("      <ss:Cell ss:StyleID=""1"">" & _
                "<ss:Data ss:Type=""String"">{0}</ss:Data></ss:Cell>", _
        fs.WriteLine("    </ss:Row>")

        ' Check for an empty row at the end due to Adding allowed on the DataGridView
        Dim subtractBy As Integer, cellText As String
        If grdView.AllowUserToAddRows = True Then subtractBy = 2 Else subtractBy = 1
        ' Write contents for each cell
        For i As Integer = 0 To grdView.RowCount - subtractBy
            fs.WriteLine(String.Format("    <ss:Row ss:Height=""{0}"">", _
            For intCol As Integer = 0 To grdView.Columns.Count - 1
                cellText = grdView.Item(intCol, i).Value
                ' Check for null cell and change it to empty to avoid error
                If cellText = vbNullString Then cellText = ""
                fs.WriteLine(String.Format("      <ss:Cell ss:StyleID=""2"">" & _
                    "<ss:Data ss:Type=""String"">{0}</ss:Data></ss:Cell>", _
            fs.WriteLine("    </ss:Row>")

Last, all that is needed is to clean up by closing the proper tags for the document, execute the file so that it opens in Microsoft Excel, and end the sub.

        ' Close up the document
        fs.WriteLine("  </ss:Table>")

        ' Open the file in Microsoft Excel
        ' 10 = SW_SHOWDEFAULT
        ShellEx(Me.Handle, "Open", myFile, "", "", 10)
    End Sub

To export the data, simply call the export sub with the proper parameters. Now, you should have a cleanly exported DataGridView in a Microsoft Excel file. If you want to see the whole project, simply download it at the bottom of the page.

    Private Sub Button1_Click(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles Button1.Click
        ' Call the export sub
        exportExcel(DataGridView1, "exportedData", ".xlsx", _
    End Sub


Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved