Our ASP page will be on the remote server to read our desktop Excel file. First, we have to upload it to the remote server and then retrive the data. So, we first design a table and upload it to the server. We have to retrive the data from the file again, so we will rename Excel and then upload.
Copy Code code as follows:
<%@ Page language= "VB" autoeventwireup= "false" codefile= "Default.aspx.vb" inherits= "_default"%>
<! DOCTYPE HTML PUBLIC "-//W3C//DTD XHTML 1.0 transitional//en" "Http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd ">
<title>read and Display Data from a Excel File (. xsl or. xlsx) in asp.net</title>
<style type= "Text/css" >
Tr.sectiontableentry1 TD,
Tr.sectiontableentry2 TD {
padding:4px;
}
Tr.sectiontableentry1 TD {
PADDING:8PX 5px;
Background:url (hline.gif) Repeat-x bottom;
}
Tr.sectiontableentry2 TD {
PADDING:8PX 5px;
Background:url (hline.gif) repeat-x bottom #F2F2F2;
}
</style>
<body>
<form id= "Form1" runat= "Server" >
<div>
<table style= "PADDING:5PX; font-size:11px "align=" center "border=" 0 ">
<tbody>
<tr>
<td>
<strong>please Select Excel file containing job details...</strong>
</td>
</tr>
<tr>
<td>
<div style= "Background:url (hline.gif) repeat-x bottom #F2F2F2;p adding:8px 5px;border-bottom:1px solid #ccc;" >
<asp:fileupload id= "Txtfilepath" runat= "Server" ></asp:FileUpload>
<asp:button id= "Btnupload" runat= "Server" text= "Upload"/><br/>
<asp:label id= "lblmessage" runat= "Server" visible= "False" font-bold= "True"
Forecolor= "#009933" ></asp:Label>
</div>
</td>
</tr>
<tr>
<td>
<asp:gridview id= "dtgjobs" runat= "Server" >
<rowstyle cssclass= "Sectiontableentry2"/>
<alternatingrowstyle cssclass= "Sectiontableentry1"/>
</asp:GridView>
</td>
</tr>
</tbody>
</table>
</div>
</form>
</body>
Connect Excel Jet provided by using Microsoft OLE DB
Provides an OLE DB interface for Jet (the Federated Engine Technology Station is a database engine) in Microsoft OLE DB, Microsoft Access database, and allows SQL Server 2005 and higher distributed queries to query Access databases and Excel spreadsheets. We will connect to the Microsoft Excel workbook with the Microsoft OLE DB for Jet 4.0,
Reads the data and then displays the data in the GridView.
Xlsx (Excel 2007) contains provider microsoft.ace.oledb.12.0. This is the OLE DB driver for the new Access database engine and the ability to read Excel 2003. We'll use it to read the xlsx (Excel 2007) data.
We have an Excel file with the contents shown below. Note: This table name must be the same, meaning that if you want to read the SHEET1 data. You must be careful at the same time the written SQL query, since the selection * from [Sheet1 $] and select * FROM [Sheet1 $] is two different queries.
Copy Code code as follows:
Protected Sub Btnupload_click (ByVal sender as Object, ByVal e as System.EventArgs) Handles Btnupload.click
If (Txtfilepath.hasfile) Then
Dim Conn as OleDbConnection
Dim cmd as OleDbCommand
Dim da as OleDbDataAdapter
Dim DS as DataSet
Dim Query as String
Dim connstring as String = ""
Dim strFileName as String = DateTime.Now.ToString ("Ddmmyyyy_hhmmss")
Dim Strfiletype as String = System.IO.Path.GetExtension (txtfilepath.filename). ToString (). ToLower ()
' Check file type
If Strfiletype.trim = ". xls" Or Strfiletype.trim = ". xlsx" Then
Txtfilepath.saveas (Server.MapPath ("~/uploadedexcel/" & strFileName & Strfiletype))
Else
Lblmessage.text = "Only Excel files allowed"
Lblmessage.forecolor = Drawing.Color.Red
Lblmessage.visible = True
Exit Sub
End If
Dim strNewPath as String = Server.MapPath ("~/uploadedexcel/" & strFileName & Strfiletype)
' Connection String to Excel workbook
If Strfiletype.trim = ". xls" Then
connstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" & strNewPath &; Extended properties= "" Excel 8.0; Hdr=yes;imex=2 "" "
ElseIf Strfiletype.trim = ". xlsx" Then
connstring = "Provider=microsoft.ace.oledb.12.0;data source=" & strNewPath &; Extended properties= "" Excel 12.0; Hdr=yes;imex=2 "" "
End If
query = "SELECT * FROM [sheet1$]"
' Create the Connection object '
conn = New OleDbConnection (connstring)
' Open connection
If Conn. State = connectionstate.closed Then Conn. Open ()
' Create ' Command object
cmd = New OleDbCommand (query, conn)
da = New OleDbDataAdapter (cmd)
ds = New DataSet ()
Da. Fill (DS)
Grvexceldata.datasource = ds. Tables (0)
Grvexceldata.databind ()
Da. Dispose ()
Conn. Close ()
Conn. Dispose ()
Else
Lblmessage.text = "Please select a Excel file"
Lblmessage.forecolor = Drawing.Color.Red
Lblmessage.visible = True
End If
End Sub
C#.net Code
Copy Code code as follows:
protected void Btnupload_click (object sender, EventArgs e)
{
if ((Txtfilepath.hasfile))
{
OleDbConnection conn = new OleDbConnection ();
OleDbCommand cmd = new OleDbCommand ();
OleDbDataAdapter da = new OleDbDataAdapter ();
DataSet ds = new DataSet ();
string query = null;
String connstring = "";
String strFileName = DateTime.Now.ToString ("Ddmmyyyy_hhmmss");
String strfiletype = System.IO.Path.GetExtension (txtfilepath.filename). ToString (). ToLower ();
Check file type
if (Strfiletype = = ". xls" | | strfiletype = = ". xlsx")
{
Txtfilepath.saveas (Server.MapPath ("~/uploadedexcel/" + strFileName + strfiletype));
}
Else
{
Lblmessage.text = "Only Excel files allowed";
Lblmessage.forecolor = System.Drawing.Color.Red;
Lblmessage.visible = true;
Return
}
String strNewPath = Server.MapPath ("~/uploadedexcel/" + strFileName + strfiletype);
Connection String to Excel workbook
if (strfiletype.trim () = = ". xls")
{
connstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + strNewPath + "; Extended properties=\ "Excel 8.0; Hdr=yes;imex=2\ "";
}
else if (Strfiletype.trim () = ". xlsx")
{
connstring = "Provider=microsoft.ace.oledb.12.0;data source=" + strNewPath + "; Extended properties=\ "Excel 12.0; Hdr=yes;imex=2\ "";
}
query = "SELECT * FROM [sheet1$]";
query = "SELECT [Country],[capital] from [sheet1$] WHERE [currency]= ' Rupee '"
query = "SELECT [Country],[capital] from [sheet1$]"
Create the Connection object
conn = new OleDbConnection (connstring);
Open Connection
IF (Conn. state = = connectionstate.closed) Conn. Open ();
Create the Command object
cmd = new OleDbCommand (query, conn);
da = new OleDbDataAdapter (cmd);
ds = new DataSet ();
Da. Fill (DS);
Grvexceldata.datasource = ds. Tables[0];
Grvexceldata.databind ();
Lblmessage.text = "Data retrieved successfully! Total Records: "+ ds." Tables[0]. Rows.Count;
Lblmessage.forecolor = System.Drawing.Color.Green;
Lblmessage.visible = true;
Da. Dispose ();
Conn. Close ();
Conn. Dispose ();
}
Else
{
Lblmessage.text = "Please select a Excel file";
Lblmessage.forecolor = System.Drawing.Color.Red;
Lblmessage.visible = true;
}
}
Using the code above to test, the results are as follows:
The above is using ASP.net to read and display Excel data
