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

  • Live Event Date: October 29, 2014 @ 11:00 a.m. ET / 8:00 a.m. PT Are you interested in building a cognitive application using the power of IBM Watson? Need a platform that provides speed and ease for rapidly deploying this application? Join Chris Madison, Watson Solution Architect, as he walks through the process of building a Watson powered application on IBM Bluemix. Chris will talk about the new Watson Services just released on IBM bluemix, but more importantly he will do a step by step cognitive …

  • Live Event Date: October 28, 2014 @ 1:00 p.m. ET / 10:00 a.m. PT The Financial Services industry is a target today like never before, as attackers leverage different techniques to breach and exploit IT security. In recent months, there have been a number of high profile data breaches and attacks that have impacted the Financial Services industry. By examining the recent attacks, several key trends emerge about what attackers are targeting and how organizations can bolster their defenses. Join us for this …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds