I think this article will be very much useful if the reader has the above said requirement.
If the data is in an Excel sheet we can easily manipulate it.
Consider that we have an aspx page name main.aspx.
In that we are having one datagrid which holds some data.
Since we are concentrating on Export to Excel concept, i have just added all the data statically.
I did not retrieve that from database.
We will go to the code now.
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles MyBase.Load
'This function will populate the datagrid
CreateDataSource()
'Assign the dataset to the datagrid
dgdTest.DataSource = dsSampleData
dgdTest.DataBind()
End Sub
The following is the code for the user defined function.
Function CreateDataSource()
Dim dt As DataTable
Dim dr As DataRow
Dim i As Integer
'create a DataTable
dt = New DataTable
dt.Columns.Add(New DataColumn("S.No", GetType(Integer)))
dt.Columns.Add(New DataColumn("Employee Name", GetType(String)))
dt.Columns.Add(New DataColumn("Qualification", GetType(String)))
dt.Columns.Add(New DataColumn("Profession", GetType(String)))
'Add some records in the datagrid
dr = dt.NewRow()
dr(0) = 1
dr(1) = "Bala"
dr(2) = "M.C.A"
dr(3) = "System Analyst"
dt.Rows.Add(dr)
dr = dt.NewRow()
dr(0) = 2
dr(1) = "Mala"
dr(2) = "B.E"
dr(3) = "Software Engineer"
dt.Rows.Add(dr)
dr = dt.NewRow()
dr(0) = 3
dr(1) = "Ala"
dr(2) = "M.C.A"
dr(3) = "Project Leader"
dt.Rows.Add(dr)
'create a dataset and assign the datatable to it
dsSampleData.Tables.Add(dt)
'To access the dataset in the ExportToExcel.aspx page
Session("SampleDataset") = dsSampleData
End Function
To access the dataset in the ExportToExcel.aspx page we are storing it in a session.
Place one linkbutton in the HTML source and following is the code for that button's click event.
Private Sub lnkExportToExcel_Click(ByVal sender As System.Object, ByVal e As _
System.EventArgs) Handles lnkExportToExcel.Click
Dim strURL As String = "window.open('ExportToExcel.aspx')"
Response.Write("<script language=javascript>")
Response.Write(strURL)
Response.Write("</script>")
End Sub
The HTML source for this page is as follows.
<asp:datagrid id="dgdTest" runat="server"></asp:datagrid>
<br>
<asp:linkbutton id="lnkExportToExcel" runat="server">Export To Excel</asp:linkbutton>
Now we can see the coding for ExportToExcel.aspx page.
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
Try
Response.ClearContent()
Response.ClearHeaders()
Response.ContentType = "application/vnd.ms-excel"
'Code to dynamically draw the table
Response.Write("<table border=2 runat=server>")
Response.Write("<tr style='color:White;background-color:blue;font-family:Arial;" )
Response.Write("font-size:3;font-weight:bold;height:25px'><td style='width:125px;'>")
Response.Write("S.No</td><td style='width:300px;'>Name</td>")
Response.Write("<td style='width:175px;' >Qualification</td>")
Response.Write("<td style='width:150px;'>Profession</td></tr>")
Dim dsSampleData As New DataSet
Dim drRow As DataRow
dsSampleData = Session("SampleDataset")
For Each drRow In dsSampleData.Tables(0).Rows
Response.Write("<tr><td>")
Response.Write(drRow(0) & "</td><td>")
Response.Write(drRow(1) & "</td><td align=left>")
Response.Write(drRow(2) & "</td><td>")
Response.Write(drRow(3) & "</td><td></tr>")
Next
Response.Write("</table>")
Response.Flush()
Response.Close()
Catch ex As Exception
End Try
End Sub
Placing the above code is enough in the code behind file.
No need to place any controls in the HTML source.
Set main.aspx as the start page and run your application.
Now tha page will load with the datagrid and a Export
To Excel linkbutton.
After clicking the Export To Excel linkbutton,
the Excel sheet which contains the data in the datagrid will open in a browser.You can save it as a Excel Sheet by selecting the Save As option from the File menu of the browser.
|
| Author: Ramesh vittal 11 Apr 2005 | Member Level: Bronze Points : 0 |
IS IT POSSIBLE TO ACCESS DATA FROM EXCEL SHEET TO DATAGRID.
|
| Author: suryaprabha 12 Apr 2005 | Member Level: Silver Points : 0 |
Hi
Its working. Really its a nice article. It is so useful article. Thank u. Keep it up.
|
| Author: Parvathi Gandhi 12 Apr 2005 | Member Level: Silver Points : 0 |
Hi Ramesh
It is possible.We can read the excel sheet's data and bind it to the datagrid.
Please refer the following URL which has an article about this.
http://www.dotnetjohn.com/articles.aspx?articleid=54
Thanks for your feedback.
Parvathi.K
|
| Author: Parvathi Gandhi 12 Apr 2005 | Member Level: Silver Points : 0 |
Hi Suryaprabha
Thanks for your valuable feedback
|
| Author: Brainstorming Guy 15 Apr 2005 | Member Level: Diamond Points : 0 |
Thanks for sharing the code Parvathi. Good Article too.
Lots of people from Aspire System. You, Sadha, Rajkumar...
Good going.
Regards,
Brainstorming Guys
|
| Author: Anil Rajan 15 Apr 2005 | Member Level: Gold Points : 0 |
hi
good ,very nice indeed
just as a suggestion since i hate using session
in the following statement window.open('ExportToExcel.aspx')" be replaced to add 'ExportToExcel.aspx?ids=1"
and in the "'ExportToExcel.aspx" page call the CreateDataSource function and use the ids passed as querystring to retrieve the value from (possibly database ) and do the remaining work.
anyway again tempted to say good work
|
| Author: keerthi kamalapuri 05 May 2005 | Member Level: Bronze Points : 0 |
This article is really useful. Thank a lottttttttttttttttttttttttttttttttttttt.
U rock Honey!!!!!!!!!
|