65.9K
CodeProject is changing. Read more.
Home

Simple Way To Export Formatted Data Grid To Excel Sheet

starIconstarIcon
emptyStarIcon
starIcon
emptyStarIconemptyStarIcon

2.50/5 (9 votes)

May 13, 2006

1 min read

viewsIcon

103363

downloadIcon

1559

Exporting datagrid to formatted excel report or presentation

Sample Image - DataGrid_To_Excel.jpg

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.