Click here to Skip to main content
12,634,690 members (28,286 online)
Click here to Skip to main content
Add your own
alternative version

Stats

81.5K views
1.5K downloads
32 bookmarked
Posted

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
Program Manager ZonG, China Mobile Company
Pakistan Pakistan
I am working as Manager Enterprise Services where key responsibilities are to support end users on their day to day IT related issues.
The other function I am responsible is IT IPCC Services Support Since September 2013 and my key responsibilities are to maintain IPCC Uptime along with end users support at both CMPak call centers. IPCC enhancements and new feature development is also part of my job role.
The third area that was handed over to me in March 2015 is in-house apps support, where key responsibility is to provide support for all the application developed by IT department and to maintain the availability of these applications.
Previously I was working as Manager Automation & Planning and this is my key area of expertise. My responsibilities were to manage team in Project Planning, System Analysis, Requirements gathering, Preparation of SRS, Presentations to client about the requirements, getting Approvals on SRS from the Client, Database Design, Development Tracking, System Deployments, end user Feedback along with Change Management.

Planning and Defining Scope, Resource Planning, Time Estimating, Creating Charts and Schedules, Risk Analysis
Managing Risks and Issues, Monitoring and Reporting Progress, Team Leadership, Working with Vendors, Scalability were the key area of working along with an efficient problem-solver in professional way with envisions business and technical perspectives to develop workable solutions.

I started my career as developer in 2004 and promoted as team lead IT Automation in 2007 and then as manager so I spend 7 years in Software Development & Project Planning.

You may also be interested in...

Pro

Comments and Discussions

 
Questionhow about import data from excel and show to datagrid view in vb. net and save to sql Pin
Yas_Aje16-Nov-15 5:26
memberYas_Aje16-Nov-15 5:26 
GeneralAlternative Pin
FilipKrnjic27-Jul-09 5:51
memberFilipKrnjic27-Jul-09 5:51 
GeneralExport data from DataGrid to Excel Pin
PranitaPS14-Jul-08 6:56
memberPranitaPS14-Jul-08 6:56 
Questionneed more guidance Pin
cchhkknnmm27-Apr-08 20:14
membercchhkknnmm27-Apr-08 20:14 
GeneralAbout the Code Pin
mib151526-Feb-08 22:36
membermib151526-Feb-08 22:36 
QuestionError while using with Update Panel Pin
Jaydeep6-Dec-07 21:10
memberJaydeep6-Dec-07 21:10 
Questionusing ASP to export data from SQL database to excel (server side) Pin
IT_GIRL22-Oct-07 14:06
memberIT_GIRL22-Oct-07 14:06 
Questioni have problem in Export data's in Datagrid to Excel Pin
Deepika Shalini28-Jun-07 1:07
memberDeepika Shalini28-Jun-07 1:07 
QuestionExporting Data Pin
Member #387219627-Feb-07 20:34
memberMember #387219627-Feb-07 20:34 
Generalgood but change the code Pin
Member #380403119-Feb-07 1:35
memberMember #380403119-Feb-07 1:35 
GeneralRe: good but change the code Pin
Malik Nasir19-Feb-07 3:41
memberMalik Nasir19-Feb-07 3:41 
GeneralExcel Export with multiple worksheets Pin
umaramiya4-Feb-07 23:26
memberumaramiya4-Feb-07 23:26 
Generali need asp page report to excel Pin
laksh_us11-Jul-06 0:42
memberlaksh_us11-Jul-06 0:42 
GeneralRe: i need asp page report to excel Pin
Malik Nasir18-Sep-06 20:51
memberMalik Nasir18-Sep-06 20:51 
Generaldoesn't show any data Pin
mahila28-May-06 23:17
membermahila28-May-06 23:17 
GeneralRe: doesn't show any data Pin
Malik Nasir28-May-06 23:52
memberMalik Nasir28-May-06 23:52 
GeneralRe: doesn't show any data Pin
Malik Nasir29-May-06 0:29
memberMalik Nasir29-May-06 0:29 
GeneralRe: doesn't show any data Pin
mahila29-May-06 5:03
membermahila29-May-06 5:03 
GeneralRe: doesn't show any data Pin
Malik Nasir29-May-06 5:26
memberMalik Nasir29-May-06 5:26 
GeneralRe: doesn't show any data Pin
mahila29-May-06 20:35
membermahila29-May-06 20:35 
GeneralRe: doesn't show any data Pin
Malik Nasir29-May-06 20:46
memberMalik Nasir29-May-06 20:46 
GeneralRe: doesn't show any data Pin
mahila29-May-06 22:03
membermahila29-May-06 22:03 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    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 | Terms of Use | Mobile
Web02 | 2.8.161208.2 | Last Updated 19 Feb 2007
Article Copyright 2006 by Malik Nasir
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid