Simple Way To Export Formatted Data Grid To Excel Sheet

• Download source files - 44 Kb
• Download demo project - 41 Kb

Introduction

Exporting datagrid to excel is very common practice and is mostly used for reporting purposes so the exported datagrid should be fully formatted. Here I have tried a bit for this that the exported grid should be formatted enough for presentation.

How it Works

I have developed a ddl for this and refer that ddl to web project you can make changes in this ddl and can do the same but if your are not comfortable you can use the same code in your desired application. If you want to use the ddl you just need to pass some parameters your desired excel sheet will be populated.

Code

Here I will mention the code used in the ddl.

Imports System.Data.OleDb
Imports System.Web.HttpResponse
Public Class DataGrid
    'if u develop ddl you need to inhertit this

    Inherits System.Web.UI.Page
    'this is the main function to export data to sheet

    'here query is your desired SQL query

    'report_title the title of the report if you want any

    'Connection_String is the database connection, you can use any kind of connection like access, sql server, oracle

    Public Sub Excel_View(ByVal Query As String, ByVal Report_Title As String, ByVal Connection_String As String)
        'defin and open new database connection

         Dim conn As New OleDbConnection(Connection_String)
        conn.Open()
         'new datagrid control

        Dim dg As New System.Web.UI.WebControls.DataGrid()
        Dim da As New OleDbDataAdapter(Query, conn)
        Dim ds As DataSet
        ds = New DataSet()
        'fill datagrid

         da.Fill(ds)
        dg.DataSource = ds
        dg.DataBind()
         'datagrid formatting

        dg.Font.Name = "Verdana"
        dg.BorderStyle = System.Web.UI.WebControls.BorderStyle.Solid
        dg.HeaderStyle.BackColor = System.Drawing.Color.Blue
        dg.HeaderStyle.ForeColor = System.Drawing.Color.White
        dg.HeaderStyle.HorizontalAlign = System.Web.UI.WebControls.HorizontalAlign.Center
        dg.HeaderStyle.Wrap = False
        dg.HeaderStyle.Font.Bold = True
 
         'export to excel

        context.Response.Buffer = True
        context.Response.ClearContent()
        context.Response.ClearHeaders()
        context.Response.ContentType = "application/vnd.ms-excel"
        EnableViewState = True
        Dim tw As New System.IO.StringWriter()
        Dim hw As New System.Web.UI.HtmlTextWriter(tw)
        dg.RenderControl(hw)
        context.Response.Write(Now)
        context.Response.Write("<b><center><font size=3 face=Verdana color=#0000FF>" & Report_Title & "</font></center></b>")
        Context.Response.Write(tw.ToString())
        Context.Response.Flush()
        context.Response.Close()
        context.Response.End()
        da.Dispose()
        da = Nothing
        ds.Dispose()
        ds = Nothing
        conn.Close()
        conn.Dispose()
        conn = Nothing
    End Sub
End Class

Wokring With Demo Project

if you want to run the attached demo then extract the zip file and place it in your localhost folder, Then create a virtual directory ExcelTest and run this in browser as http://localhost/exceltest

Working With Code

If you want to use the same ddl then download the demo project copy ddl to your project and use the following code.

"TEXT-ALIGN: justify">    'create new object

    Dim abc As New GridToExcel.DataGrid
    'call function to populate data

    abc.Excel_View(query,report_title,connection_String)

but if you want to use the code you can use it according to your needs and can accordingly modify it.

Point of Interest

• Using this ddl you do not need to write the code again and again
• As you pass connection string so you donot need to be worried about the database, you can use every kind of database and pass the required database string.
• Even you can customize the report according to your desired format.