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

Export DataGridView to Microsoft Excel

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

About the Author

Paul Avery




  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • When individual departments procure cloud service for their own use, they usually don't consider the hazardous organization-wide implications. Read this paper to learn best practices for setting up an internal, IT-based cloud brokerage function that service the entire organization. Find out how this approach enables you to retain top-down visibility and control of network security and manage the impact of cloud traffic on your WAN.

  • Complex hybrid environments can make it difficult to track interdependencies, increasing the risk of disrupting critical business services. In this white paper by EMA, you'll learn how application discovery and dependency mapping can help you: Meet granular targets for availability, cost, and time-to-revenue for cloud services. Accelerate mean time to repair (MTTR) while communicating better with stakeholders. Manage even the most complex hybrid environments more efficiently and effectively Understand the …

Most Popular Programming Stories

More for Developers

RSS Feeds

Thanks for your registration, follow us on our social networks to keep up-to-date