How to export DataGridView Data to Excel


In this topic we will learn how to export your DataGridView data to Excel without using any excel component or Microsoft Office Excel. Simply add this class in your application.

#Region "   MBExport Class Header"
'********************************************************************************
'  Module Name :  MBExport.vb
'  Project     :  Export DataGridView To Excel
'  Created by  :  Manoj Kishor Bhoir
'  Created on  :  06/02/2014  04:25
'  
'  Copyright © Manoj Kishor Bhoir
'  
'  <Description of the file>
'  All other rights reserved.
'  
'  THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, 
'  EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED 
'  WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
'*********************************************************************************
#End Region

#Region "   Imports"
Imports System.Windows.Forms
Imports System.IO
#End Region

#Region "   MBExport Class"
Namespace Helper
    '''<summary>
    ''' Represents all activities required for Export Data in different formats.
    ''' </summary>
    ''' <remarks></remarks>
    Public Class MBExport
        '''<summary>
        ''' Export Data from data grid to Excel file.
        ''' </summary>
        ''' <param name="FilePath">Excel file name with path as String</param>
        ''' <param name="DataGrid">Data Grid to export in Excel</param>
        ''' <remarks></remarks>
        Public Shared Sub ExportToExcel(ByVal FilePath As String, ByVal DataGrid As DataGridView)
            ExportToExcel(FilePath, DataGrid, "Sheet1")
        End Sub
        '''<summary>
        ''' Export Data from data grid to Excel file.
        ''' </summary>
        ''' <param name="FilePath">Excel file name with path as String</param>
        ''' <param name="DataGrid">Data Grid to export in Excel</param>
        ''' <param name="SheetName">Excel Sheet Name as String</param>
        ''' <remarks></remarks>
        Public Shared Sub ExportToExcel(ByVal FilePath As String, ByVal DataGrid As DataGridView, ByVal SheetName As String)
            Try
                Using _mFileStream As StreamWriter = New StreamWriter(FilePath, False)
                    _mFileStream.WriteLine("<?xml version=""1.0""?>")
                    _mFileStream.WriteLine("<?mso-application progid=""Excel.Sheet""?>")
                    _mFileStream.WriteLine("<ss:Workbook xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet"">")
                    _mFileStream.WriteLine("    <ss:Styles>")
                    _mFileStream.WriteLine("        <ss:Style ss:ID=""1"">")
                    _mFileStream.WriteLine("           <ss:Font ss:Bold=""1""/>")
                    _mFileStream.WriteLine("           <ss:FontName=""Courier New""/>")
                    _mFileStream.WriteLine("        </ss:Style>")
                    _mFileStream.WriteLine("    </ss:Styles>")
                    _mFileStream.WriteLine("    <ss:Worksheet ss:Name=""" & SheetName & """>")
                    _mFileStream.WriteLine("        <ss:Table>")
                    For x As Integer = 0 To DataGrid.Columns.Count - 1
                        _mFileStream.WriteLine("            <ss:Column ss:Width=""{0}""/>", DataGrid.Columns.Item(x).Width)
                    Next
                    _mFileStream.WriteLine("            <ss:Row ss:StyleID=""1"">")
                    For i As Integer = 0 To DataGrid.Columns.Count - 1
                        _mFileStream.WriteLine("                <ss:Cell>")
                        _mFileStream.WriteLine(String.Format("                   <ss:Data ss:Type=""String"">{0}</ss:Data>", DataGrid.Columns.Item(i).HeaderText))
                        _mFileStream.WriteLine("</ss:Cell>")
                    Next
                    _mFileStream.WriteLine("            </ss:Row>")
                    For intRow As Integer = 0 To DataGrid.RowCount - 2
                        _mFileStream.WriteLine(String.Format("            <ss:Row ss:Height =""{0}"">", DataGrid.Rows(intRow).Height))
                        For intCol As Integer = 0 To DataGrid.Columns.Count - 1
                            _mFileStream.WriteLine("                <ss:Cell>")
                            _mFileStream.WriteLine(String.Format("                   <ss:Data ss:Type=""String"">{0}</ss:Data>", DataGrid.Item(intCol, intRow).Value.ToString))
                            _mFileStream.WriteLine("                </ss:Cell>")
                        Next
                        _mFileStream.WriteLine("            </ss:Row>")
                    Next
                    _mFileStream.WriteLine("        </ss:Table>")
                    _mFileStream.WriteLine("    </ss:Worksheet>")
                    _mFileStream.WriteLine("</ss:Workbook>")
                    _mFileStream.Close()
                    _mFileStream.Dispose()
                End Using
            Catch ex As Exception

            End Try
        End Sub
    End Class
End Namespace
#End Region

How to use :

MBExport.ExportToExcel("File Path to Save Excel", "DataGridViewControl")
'OR
MBExport.ExportToExcel("File Path to Save Excel", "DataGridViewControl", "SheetName")
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s