Datagrid|excel|web the data from the DataGrid in the ASP. NET WebForm to Microsoft Excel
This article will step you through the DataGrid Web server control on the ASP.net webform and then remit the DataGrid to Microsoft Excel.
Technology
This article will explain two techniques for the DataGrid data:
Use Excel MIME type (or content type)
You can use server-side code to link the DataGrid to data, and then open the data in Excel in the client computer. If you want to perform this operation, please set ContentType as Application/vnd.ms-excel. When you receive a new data stream from the client, the data is displayed in Excel, as you would open it in the Web browser's new website.
Use Excel to automatically
You can use the client's code, take HTML from the DataGrid capture, and then "excel" the HTML in the new workbook. " "excel" will always display data on the outside of the browser in Excel application windows. " One of the advantages of "self-mobility" is that if you want to modify a live page after you have the data, you can control Excel by the program. However, because Excel's code is designed to be unsafe, the user must apply the security setting on the Web browser that allows it to be customized.
Gradually explain
Activate Visual Studio. NET. In the [File] menu, point to [Add], and then click [The case].
Click [Visual Basic Expertise] in the [Specialized type] pane. Click [asp.net Web application] under [Demo]. Name the application as Excelexport, and then click [OK].
WebForm1 will appear in the "Design" video.
Press the right mouse button on [WebForm1.aspx] on the solution tube, and then click [Rename]. To change the file name to Bottom.aspx.
Click [HTML Original] On the Watch menu to add the following DataGrid to the <form> and </form> tags:
<asp:datagrid id= "DATAGRID1" runat= "Server" gridlines= "Vertical" cellpadding= "3" backcolor= "white"
Bordercolor= "#999999" borderwidth= "1px" borderstyle= "None" width= "100%" height= "100%" font-size= "X-small"
font-names= "Verdana" >
<selecteditemstyle font-bold= "True" forecolor= "white" backcolor= "#008A8C" ></SelectedItemStyle>
<alternatingitemstyle backcolor= "Gainsboro" ></AlternatingItemStyle>
<itemstyle borderwidth= "2px" forecolor= "Black" borderstyle= "Solid" bordercolor= "Black" backcolor= "#EEEEEE" > </ItemStyle>
Bordercolor= "Black" backcolor= "#000084" ></HeaderStyle>
<footerstyle forecolor= "Black" backcolor= "#CCCCCC" ></FooterStyle>
<pagerstyle horizontalalign= "Center" forecolor= "Black" backcolor= "#999999" mode= "NumericPages" ></ Pagerstyle>
</asp:datagrid>
Click [Design] on the Watch menu to return to the Design view.
The DataGrid appears on the WebForm.
Click [Code] on the View menu so that you can display the code behind the Web form. Add the following code to the Page_Load event handler:
Note that you must change the User ID <username> and Password=<strong password> to the correct value to perform this code. The user account must have the right permissions to perform this operation on the data library.
' Create a connection and open it.
Dim objconn as New System.Data.SqlClient.SqlConnection ("User id=<username>; Password=<strong password>;initial catalog=northwind;data source=sqlserver; ")
objConn.Open ()
Dim strSQL as String
Dim Objdataset as New DataSet ()
Dim Objadapter as New System.Data.SqlClient.SqlDataAdapter ()
The customers from the USA.
strSQL = "SELECT * FROM Customers where country= ' USA '"
Objadapter.selectcommand = New System.Data.SqlClient.SqlCommand (strSQL, objconn)
' Fill the DataSet.
Objadapter.fill (Objdataset)
' Create a new view.
Dim Oview as New DataView (objdataset.tables (0))
' Set up the ' data grid and bind the data.
DataGrid1.DataSource = Oview
Datagrid1.databind ()
' Verify If the page is displayed in Excel.
If request.querystring ("bexcel") = "1" Then
' Set the content type to Excel.
Response.ContentType = "Application/vnd.ms-excel"
' Remove the charset from the Content-type header.
Response.Charset = ""
' Turn off the view state.
Me.enableviewstate = False
Dim tw as New System.IO.StringWriter ()
Dim HW as New System.Web.UI.HtmlTextWriter (TW)
' Get the HTML for the control.
Datagrid1.rendercontrol (HW)
' Write the HTML back to the browser.
Response.Write (TW. ToString ())
' End the response.
Response.End ()
End If
NOTE: Replace SQL Server in your code with your SQL name. If you cannot access the SQL Server where the Northwind sample repository resides, modify the link string to use the Microsoft Access 2002 sample Northwind Library:
provider=microsoft.jet.oledb.4.0; Data source=c:\program Files\Microsoft Office\Office10\Samples\Northwind.mdb
If you choose this method, modify the aforementioned code to use the Oledbclient namespace (rather than using the SqlClient name space).
On the [Exclusive] menu, click [Add HTML web]. Name the web as Top.htm, and then click [Open].
Top.htm will appear in the "Design" video.
On the Watch menu, click [HTML Original]. Replace the contents of the HTML Raw File window with the following code:
<script language= "VBScript" >
Sub Button1_onclick
Select Case Select1.selectedindex
Case 0 ' use automation.
Dim SHTML
SHTML = Window.parent.frames ("bottom"). Document.forms (0). Children ("DataGrid1"). outerHTML
Dim oXL, obook
Set oXL = CreateObject ("Excel.Application")
Set obook = OXL.Workbooks.Add
OBook.HTMLProject.HTMLProjectItems ("Sheet1"). Text = SHTML
OBook.HTMLProject.RefreshDocument
oXL.Visible = True
Oxl.usercontrol = True
Case 1 "Use MIME Type" (in a New Window).
window.open ("bottom.aspx?bexcel=1")
Case 2 ' use MIME Type (in the Frame).
Window.parent.frames ("Bottom"). Navigate "Bottom.aspx?bexcel=1"
End Select
End Sub
</script>
<body>
Export to Excel Using:
<select id= "Select1" size= "1" name= "Select1" >
<option value= "0" selected>automation</option>
<option value= "1" >mime Type (in a New Window) </OPTION>
<option value= "2" >mime Type (in the Frame) </OPTION>
</SELECT>
<input id= "Button1" type= "button" value= "go!" name= "Button1" >
</body>
On the [Exclusive] menu, click [Add HTML web]. Name the web as frameset.htm, and then click [Open].
Frameset.htm is opened in the Design view.
On the Watch menu, click [HTML Original]. Replace the contents of the HTML Raw File window with the following code:
<frameset rows= "10%,90%" >
<frame noresize= "0" scrolling= "no" name= "Top" src= "top.htm" >
<frame noresize= "0" scrolling= "yes" name= "bottom" src= "bottom.aspx" >
</frameset>
Right-click the mouse on [frameset.htm] on the solution tube and click [Set as Start].
On the Build menu, click [Build Scenario].
Try to see!
Click [Activate but not bug] on the [Bug] menu to execute the application.
After the Web browser opens the framework group, the DataGrid below the frame shows the data for the Northwind library.
Click [Automation] in the Drop-down list, and then press [go].
The DataGrid content is displayed on the outside of the browser on the Microsoft Excel application window.
In the Drop-down list, click [MIME Type (in a New Window)], and then press [go].
The DataGrid content is displayed in Excel in the New Web browser window.
Note: When you are prompted to open or save an Excel file, click [Open].
In the Drop-down list, click [MIME Type (in the Frame)], and then press [go].
The DataGrid content is displayed in the bottom frame of the Web browser Excel framework.
Note: When you are prompted to open or save an Excel file, click [Open].
