| Author: Deepak Agarwal 12 Jun 2004 | Member Level: Bronze | Rating:  Points: 2 |
Hi Priti
I am sending full coding to export the data to the Excel file but it is in vb.net (asp.net). And I am using Data Grid, you can call image insteed of data.So whatever in the datagrid it will be exported to the excel sheet.
In this coding I am using one component and I made all the comments sothat you can easily understand.
Code:-
Exporting data from DataGrid to the Excel Sheet
1. File Name : cmpDataGridToExcel.vb
Public Class cmpDataGridToExcel
Inherits System.ComponentModel.Component
'************* Deleted the Region Part
Public Shared Sub DataGridToExcel(ByVal dgExport As DataGrid, ByVal response As HttpResponse)
'clean up the response.object
response.Clear()
response.Charset = ""
'set the response mime type for excel
response.ContentType = "application/vnd.ms-excel"
'create a string writer
Dim stringWrite As New System.IO.StringWriter()
'create an htmltextwriter which uses the stringwriter
Dim htmlWrite As New System.Web.UI.HtmlTextWriter(stringWrite)
'instantiate a datagrid
Dim dg As New DataGrid()
' just set the input datagrid = to the new dg grid
dg = dgExport
' I want to make sure there are no annoying gridlines
dg.GridLines = GridLines.None
' Make the header text bold
dg.HeaderStyle.Font.Bold = True
' If needed, here's how to change colors/formatting at the component level
'dg.HeaderStyle.ForeColor = System.Drawing.Color.Black
'dg.ItemStyle.ForeColor = System.Drawing.Color.Black
'bind the modified datagrid
dg.DataBind()
'tell the datagrid to render itself to our htmltextwriter
dg.RenderControl(htmlWrite)
'output the html
response.Write(stringWrite.ToString)
response.End()
End Sub
End Class
2. File Name : DataGridExport.aspx.vb
Imports System.Configuration
Public Class DataGridExport
Inherits System.Web.UI.Page
Protected WithEvents SqlSelectCommand1 As System.Data.SqlClient.SqlCommand
Protected WithEvents SqlInsertCommand1 As System.Data.SqlClient.SqlCommand
Protected WithEvents SqlUpdateCommand1 As System.Data.SqlClient.SqlCommand
Protected WithEvents SqlDeleteCommand1 As System.Data.SqlClient.SqlCommand
Protected WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
Protected WithEvents SqlDataAdapter1 As System.Data.SqlClient.SqlDataAdapter
Protected WithEvents dgToExport As System.Web.UI.WebControls.DataGrid
Protected WithEvents btnExport As System.Web.UI.WebControls.Button
Protected WithEvents Form1 As System.Web.UI.HtmlControls.HtmlForm
Protected WithEvents DataTable1 As System.Data.DataTable
Protected WithEvents DataSet1 As System.Data.DataSet
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
BindGrid()
End Sub
Sub BindGrid()
' Fill our dataset
SqlDataAdapter1.Fill(DataSet1, "Employees")
' Assign the dataset to our Datagrid called dgToExport
dgToExport.DataSource = DataSet1.Tables("Employees").DefaultView
' Finally bind the datagrid
dgToExport.DataBind()
End Sub
Function ReturnName(ByVal strLastName, ByVal strFirstName)
' This is the function I'm calling in the aspx page to show the difference
' between exporting a dataset versus exporting a datagrid. This function is
' simply going to combine the first and last names and and return the
' full name to the datagrid template column for "Name".
Dim strReturn As String
strReturn = strFirstName & " " & strLastName
Return strReturn
End Function
Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExport.Click
' One line handles all of the export. We're simply calling the component ‘(cmpDataGridToExcel),then we're using it's only method (DataGridToExcel), and ‘we're passing our DataGrid (dgToExport)and the value reponse. Note: If you're ‘using VS.Net, once you build your solution after creating the component, ‘Intellisense will now include your component. Just remember you have to build it ‘first.
'
' You could also modify your datagrid here before exporting it. For instance in my ' invoice example we had a checkbox in our datagrid. If you have one of those the ' ' export will generate an error so we simply removed the column first like this:
' dgToExport.Columns.Remove(dgToExport.Columns.Item(11))
cmpDataGridToExcel.DataGridToExcel(dgToExport, Response)
End Sub
End Class
3. File Name : DataGridExport.aspx
<%@ Page Language="vb" AutoEventWireup="false" Codebehind="DataGridExport.aspx.vb" Inherits="Deeptest.DataGridExport" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<title>DataGridExport</title>
<meta name="GENERATOR" content="Microsoft Visual Studio.NET 7.0">
<meta name="CODE_LANGUAGE" content="Visual Basic 7.0">
<meta name="vs_defaultClientScript" content="JavaScript">
<meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">
</HEAD>
<body>
<div align="center">
<form id="Form1" method="post" runat="server">
<P>
<asp:DataGrid id="dgToExport" runat="server" BorderColor="#CC9966" BorderStyle="None" BorderWidth="1px" BackColor="White" CellPadding="4" AutoGenerateColumns="False">
<SelectedItemStyle Font-Bold="True" ForeColor="#663399" BackColor="#FFCC66"></SelectedItemStyle>
<ItemStyle ForeColor="#330099" BackColor="White"></ItemStyle>
<HeaderStyle Font-Bold="True" ForeColor="#FFFFCC" BackColor="#990000"></HeaderStyle>
<FooterStyle ForeColor="#330099" BackColor="#FFFFCC"></FooterStyle>
<Columns>
<asp:BoundColumn DataField="EmployeeID" ReadOnly="True" HeaderText="ID"></asp:BoundColumn>
<asp:TemplateColumn HeaderText="Name">
<ItemTemplate>
<%# ReturnName(DataBinder.Eval(Container.DataItem, "LastName"), DataBinder.Eval(Container.DataItem, "FirstName")) %>
</ItemTemplate>
</asp:TemplateColumn>
<asp:BoundColumn DataField="Title" ReadOnly="True" HeaderText="Title"> </asp:BoundColumn>
</Columns>
<PagerStyle HorizontalAlign="Center" ForeColor="#330099" BackColor="#FFFFCC"></PagerStyle>
</asp:DataGrid>
</P>
<P>
<asp:Button id="btnExport" runat="server" Text="Export to Excel"></asp:Button>
</P>
</form>
</div>
</body>
</HTML>
*************** End Code ************************
Hope this will help you
Happy Coding,
Deepak
|
| Author: Chandra Sekhar Meesala 02 Sep 2005 | Member Level: Bronze | Rating: Points: 2 |
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition", "attachment;Filename=Results.xls"
Response.Write "<body>"
Response.Write "<table>"
---------------------------------------------------------------
I tried this but it doesn't seem to work.
%>
<td width="600" height="95" colspan="3" valign="top"><img src="./images/oakwoodw.jpg" width="163" height="69"></td>
<%
---------------------------------------------------------------
Call WriteTableData
Response.Write "</table>"
Response.Write "</body>"
Response.Write "</html>"
|
| Author: julius masilamani 18 Jan 2008 | Member Level: Bronze | Rating: Points: 2 |
<a>http://www.aspspider.com/resources/Resource347.aspx</a>
|