Click here to Skip to main content
Click here to Skip to main content
Go to top

Simple Way To Export Formatted Data Grid To Excel Sheet

, 19 Feb 2007
Rate this:
Please Sign up or sign in to vote.
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.

<p style="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)
</p>

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Malik Nasir
Team Leader ZonG, China Mobile Company
Pakistan Pakistan
I am currently based in Islamabad,Pakistan. I have done my MS Computer Science and I am involved in Microsoft Visual Studio.net Windows/Web application development since 2003 untill that i have been working in VB, ASP and web scripting languages. Currently i am working in ZonG as Team LEad IT Projects and Planing. I am also the administrator of Business Process

Comments and Discussions

 
GeneralAlternative PinmemberFilipKrnjic27-Jul-09 4:51 
GeneralExport data from DataGrid to Excel PinmemberPranitaPS14-Jul-08 5:56 
Questionneed more guidance Pinmembercchhkknnmm27-Apr-08 19:14 
GeneralAbout the Code Pinmembermib151526-Feb-08 21:36 
QuestionError while using with Update Panel PinmemberJaydeep6-Dec-07 20:10 
Questionusing ASP to export data from SQL database to excel (server side) PinmemberIT_GIRL22-Oct-07 13:06 
Questioni have problem in Export data's in Datagrid to Excel PinmemberDeepika Shalini28-Jun-07 0:07 
QuestionExporting Data PinmemberMember #387219627-Feb-07 19:34 
Generalgood but change the code PinmemberMember #380403119-Feb-07 0:35 
GeneralRe: good but change the code PinmemberMalik Nasir19-Feb-07 2:41 
GeneralExcel Export with multiple worksheets Pinmemberumaramiya4-Feb-07 22:26 
Generali need asp page report to excel Pinmemberlaksh_us10-Jul-06 23:42 
GeneralRe: i need asp page report to excel PinmemberMalik Nasir18-Sep-06 19:51 
Generaldoesn't show any data Pinmembermahila28-May-06 22:17 
GeneralRe: doesn't show any data PinmemberMalik Nasir28-May-06 22:52 
GeneralRe: doesn't show any data PinmemberMalik Nasir28-May-06 23:29 
GeneralRe: doesn't show any data Pinmembermahila29-May-06 4:03 
GeneralRe: doesn't show any data PinmemberMalik Nasir29-May-06 4:26 
GeneralRe: doesn't show any data Pinmembermahila29-May-06 19:35 
GeneralRe: doesn't show any data PinmemberMalik Nasir29-May-06 19:46 
GeneralRe: doesn't show any data Pinmembermahila29-May-06 21:03 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140916.1 | Last Updated 19 Feb 2007
Article Copyright 2006 by Malik Nasir
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid