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"" " & _
            "ss:WrapText=""1""/>")
        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}""/>", _
            grdView.Columns.Item(i).Width))
        Next
        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>", _
                grdView.Columns.Item(i).HeaderText))
        Next
        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}"">", _
                grdView.Rows(i).Height))
            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>", _
                    cellText.ToString))
            Next
            fs.WriteLine("    </ss:Row>")
        Next

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>")
        fs.WriteLine("</ss:Worksheet>")
        fs.WriteLine("</ss:Workbook>")
        fs.Close()

        ' 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", _
            My.Computer.FileSystem.SpecialDirectories.Desktop)
    End Sub



About the Author

Paul Avery

www.peejstudio.com

Downloads

Comments

  • 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

  • On-demand Event Event Date: September 10, 2014 Modern mobile applications connect systems-of-engagement (mobile apps) with systems-of-record (traditional IT) to deliver new and innovative business value. But the lifecycle for development of mobile apps is also new and different. Emerging trends in mobile development call for faster delivery of incremental features, coupled with feedback from the users of the app "in the wild." This loop of continuous delivery and continuous feedback is how the best mobile …

  • On-demand Event Event Date: July 22, 2014 In this WhatWorks analysis, John Pescatore examines a use case where end users had local administrative rights on their PCs and it had gotten out of hand for this Fortune 500 Energy and Utilities company. The compelling event that prompted the company to reexamine this situation was the migration to Windows 7. In Windows XP, a custom tool that allowed users one of three levels of administrative rights to their workstations would need to be replaced during the Windows …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds