View a live sample!
This page provides a simple example of the how to query an EXCEL spreadsheet
From the ASP.net page using either C # or vb.net. Check it out!
This code is written in response to a message posted on one of
Charles Carroll ' s asp.net lists. Can sign up for one or all
of the lists here.
C # Code
---------------------------------------------
<%@ Page language= "C #"%>
<%@ Import namespace= "System.Data"%>
<%@ Import namespace= "System.Data.ADO"%>
<script language= "C #" runat= "Server" >
protected void Page_Load (Object Src, EventArgs E)
{
String strconn;
strconn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=c:\\exceltest.xls;" +
"Extended Properties=excel 8.0;";
' You must use the ' $ after the ' object for your reference in the spreadsheet
Adodatasetcommand mycommand = new Adodatasetcommand ("SELECT * from [sheet1$]", strconn);
DataSet myDataSet = new DataSet ();
Mycommand.filldataset (myDataSet, "excelinfo");
DataGrid1.DataSource = mydataset.tables["Excelinfo"]. DefaultView;
Datagrid1.databind ();
}
</script>
<p><asp:label id=label1 runat= "Server" >spreadsheet contents:</asp:label></p>
<asp:datagrid id=datagrid1 runat= "Server"/>
VB.net Code
----------------------------------------------
<%@ Page language= "VB"%>
<%@ Import namespace= "System.Data"%>
<%@ Import namespace= "System.Data.ADO"%>
<script language= "VB" runat= "Server" >
Sub Page_Load (sender as Object, E as EventArgs)
Dim myDataSet as New DataSet ()
' can also use the Excel ODBC driver I believe-didn ' t try though
Dim strconn as String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\exceltest.xls;" & _
"Extended properties=" "Excel 8.0;" ""
' You must use the ' $ after the ' object for your reference in the spreadsheet
Dim Myadodatasetcommand as New adodatasetcommand ("select * from [sheet1$]", strconn)
MYADODATASETCOMMAND.TABLEMAPPINGS.ADD ("Table", "Exceltest")
Myadodatasetcommand.filldataset (myDataSet)
DataGrid1.DataSource = mydataset.tables (0). DefaultView
Datagrid1.databind ()
End Sub
</script>
<p><asp:label id=label1 runat= "Server" >spreadsheet contents:</asp:label></p>
<asp:datagrid id=datagrid1 runat= "Server"/>
