asp.net|excel| Programs | access | Visit Excel is a software in Microsoft's Office suite that is used primarily for working with spreadsheets. Excel is friendly to the interface, the processing data quickly and so on the advantage obtains the general office staff's welcome. So a lot of documents are saved in Excel form. For program designers, in program design, we often have to access Excel files to obtain data. However, because the Excel file is not a standard database, it is more difficult to access him in the program language.
ASP.net is a product that Microsoft strongly recommends as an important part of the. NET framework framework, primarily for web design. The new design concept and powerful function make ASP.net is being welcomed by more and more programming designers. It is ASP.net's powerful feature that makes access to Excel documents a relatively simple thing to do. Here's an example to illustrate how asp.net is accessing an Excel document.
I. Programming and OPERATING Environment
(1). Windows Professional
(2). Net Framework SDK Beta 2
(3). Microsoft Access Data Component 2.6 (MADC2.6)
Two. The specific design idea
(1). Get the name of the Excel file you want to access
(2). Read the contents of the Excel file
(3). Display in DataGrid format
Here are some key steps to specify.
Three. Key steps in program design
(1). For convenience, we assume that the Excel file is in the root directory of C disk, named Test.xls.
(2). In order to read Excel files, we must understand a namespace (NameSpace)--system.data.oledb. There are three objects in the System.Data.Oledb, respectively, Oledbconnection,oledbcommand,oledbdataadapter. It is through them that we access the Excel file.
The i> OleDbConnection object is primarily a way to provide connectivity.
The ii> OleDbCommand object provides a concrete way to manipulate the target.
The Iii>oledbdataadapter object is the returned dataset after a different operation on an object.
To read the Test.xls of the C disk, you can do so as long as the following statements:
Dim myoledbconnection as OleDbConnection = New
OleDbConnection ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\test.xls;" & _
"Extended properties=" "Excel 8.0;" ""
Dim Myoledbcommand as OleDbCommand = New OleDbCommand ("SELECT *
From [sheet1$] ", myoledbconnection) ' If you want to read the contents of Sheet2
, change the sheet1$ into sheet2$.
Dim MyData as oledbdataadapter= New
OleDbDataAdapter (Myoledbcommand)
(3). After reading the contents of the Excel file, it is necessary to display it in a DataGrid. In order to display, you also need to use a different namespace--system.data. He has an object dataset that he can use to help with the DataGrid to display the data in a DataGrid. The dataset read at this time is not represented by an object dataset, which requires conversion, but the OleDbDataAdapter object provides a method--fill that can complete the conversion. The specific procedures are as follows:
Dim myDataSet as New DataSet ()
Mydata.fill (myDataSet)
' Complete conversion from OleDbDataAdapter object to DataSet
DataGrid1.DataSource = mydataset.tables (0). DefaultView
Datagrid1.databind () ' Complete data help, display data
Four. The specific source code:
<%@ Page language= "VB"%>
<%@ Import namespace= "System.Data"%>
<%@ Import namespace= "System.Data.Oledb"%>
<script language= "VB" runat= "Server"
Sub Page_Load (sender as Object, E as EventArgs)
Dim myDataSet as New DataSet ()
Dim myoledbconnection as OleDbConnection = New OleDbConnection ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\2.xls;" & _
"Extended properties=" "Excel 8.0;" ""
Dim Myoledbcommand as OleDbCommand = New OleDbCommand ("select * from [sheet1$]", myoledbconnection)
Dim MyData as oledbdataadapter= New OleDbDataAdapter (Myoledbcommand)
Mydata.fill (myDataSet)
DataGrid1.DataSource = mydataset.tables (0). DefaultView
Datagrid1.databind ()
End Sub
</script>
<body>
<asp:label id= "L1" runat= "Server" reads the Test.xls file in the C-packing directory and displays it as a DataGrid </asp:label>
<asp:datagrid id=datagrid1 runat= "Server"/>
</body>
Five. Summary
The ASP.net program that reads the Excel file is complete. If your machine is up to the operating environment mentioned above, then create a Web virtual directory that points to this asp.net program, copy an Excel file to the root directory of C disk, and run the program in the browser, you can see the Excel file Let's do it.
