Default.aspx
<! DOCTYPE HTML PUBLIC "-//W3C//DTD XHTML 1.0 transitional//en" "Http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd ">
<title> Untitled Page </title>
<style type= "Text/css" >
. style1
{
height:16px;
}
. style3
{
height:23px;
}
</style>
<body>
<form id= "Form1" runat= "Server" >
<div>
</div>
<table align= "Center" border= "1" bordercolor= "honeydew" cellpadding= "0"
cellspacing= "0" >
<tr>
<TD style= "FONT-SIZE:9PT; COLOR: #ff0000; Background-color: #ff9933; Text-align:center "
class= "Style1" >
</td>
<TD colspan= "2"
Style= "FONT-SIZE:9PT; COLOR: #ffffff; height:16px; Background-color: #ff9933; Text-align:center ">
Write Excel data to an Access database </td>
</tr>
<tr>
<TD style= "Background-color: #ffffcc; Text-align:center ">
</td>
<TD style= "Background-color: #ffffcc; Text-align:center ">
<iframe id= "I1" name= "I1" "Yes" scrolling= "student scores. xls"
Style= "WIDTH:407PX; height:280px "></iframe>
</td>
<TD style= "width:190px; Background-color: #ffffcc; Text-align:center ">
<asp:gridview id= "GridView1" runat= "Server" cellpadding= "4" font-size= "9pt"
Forecolor= "#333333" gridlines= "None" width= "228px" >
<footerstyle backcolor= "#990000" font-bold= "True" forecolor= "white"/>
<rowstyle backcolor= "#FFFBD6" forecolor= "#333333"/>
<selectedrowstyle backcolor= "#FFCC66" font-bold= "True" forecolor= "Navy"/>
<pagerstyle backcolor= "#FFCC66" forecolor= "#333333" horizontalalign= "Center"/>
<alternatingrowstyle backcolor= "White"/>
</asp:GridView>
</td>
</tr>
<tr>
<TD style= "Background-color: #ff9900; Text-align:center "class=" Style3 ">
</td>
<TD style= "HEIGHT:23PX; Background-color: #ff9900; Text-align:center "
valign= "Top" >
<asp:button id= "Button3" runat= "Server" font-size= "9pt" onclick= "Button1_Click"
Text= "Excel data is written to an Access database"/>
<asp:label id= "Label1" runat= "Server" text= "Label" visible= "False"
Style= "Font-size:x-small" ></asp:Label>
</td>
<TD style= "width:190px; height:23px; Background-color: #ff9900; Text-align:center ">
<asp:button id= "Button2" runat= "Server" font-size= "9pt" onclick= "button2_click"
Text= "Excel data Display in database"/>
</td>
</tr>
<tr>
<td>
</td>
</tr>
</table>
</form>
</body>
Default.aspx.cs
Using System;
Using System.Configuration;
Using System.Data;
Using System.Linq;
Using System.Web;
Using System.Web.Security;
Using System.Web.UI;
Using System.Web.UI.HtmlControls;
Using System.Web.UI.WebControls;
Using System.Web.UI.WebControls.WebParts;
Using System.Xml.Linq;
Using System.Data.OleDb;
public partial class _default:system.web.ui.page
{
protected void Page_Load (object sender, EventArgs e)
{
}
Public OleDbConnection Createcon ()
{
String strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + Server.MapPath ("Userscore.mdb") + "; User id=admin; password=; ";
OleDbConnection ODBC = new OleDbConnection (strconn);
return ODBC;
}
protected void Button1_Click (object sender, EventArgs e)
{
Defining Excel Lists
String StyleSheet = "Sheet1";
Call the custom LoadData method to read the data in the Excel file to the ASPNET page
LoadData (StyleSheet);
To define the SQL statement for a query
String sql = "Select ID, user name, quiz paper, score, test time from Score";
To create an OLE DB database connection
OleDbConnection con = Createcon ();
Con. Open ();//Opening database connection
OleDbCommand com = new OleDbCommand (sql, con);
Start a transaction
OleDbTransaction Tran = con. BeginTransaction ();
Com. Transaction = Tran;
Creating adapters
OleDbDataAdapter da = new OleDbDataAdapter (COM);
OleDbCommandBuilder cb = new OleDbCommandBuilder (DA);
Creating DataSet Datasets
DataSet ds = new DataSet ();
Populating data sets
Da. Fill (DS);
int curindex = 0;
if (ds. Tables[0]. Rows.Count > 0)
{
Curindex = Convert.ToInt32 (ds. Tables[0]. Rows[0][0]);
}
Create a Memory table
DataTable TB = this.getexceldate ();
String selsql = "";
for (int i = 0; i < TB. Rows.Count; i++)
{
String UserName = tb. Rows[i][0]. ToString ();
Selsql = "SELECT count (*) from Score where user name = ' + UserName + '";
}
Determine if an Excel file has been imported into an Access database
if (Exscalar (Selsql) > 0)
{
Label1.visible = true;
Label1.Text = "<script language=javascript>alert (' The data in this excle has already been imported into the database! '); Location= ' default.aspx ';</script> ';
}
Else
{
Iterate through the data in an Excel file and add it to a database table that access has created in advance
for (int i = 0; i < TB. Rows.Count; i++)
{
DataRow dr = ds. Tables[0]. NewRow ();
Dr[0] = ++curindex;
DR[1] = tb. ROWS[I][0];
DR[2] = tb. ROWS[I][1];
DR[3] = tb. ROWS[I][2];
DR[4] = tb. ROWS[I][3];
Ds. Tables[0]. Rows.Add (DR);
}
Try
{
Da. Update (DS);//Perform insert operation
Tran.commit ()//Transaction Submission
Label1.visible = true;
Label1.Text = "<script language=javascript>alert" (' Data import success! '); location= ' default.aspx ';</script> ';
}
Catch
{
Tran. Rollback ()//transaction rollback
Label1.visible = true;
Label1.Text = "<script language=javascript>alert (' Data import failed! '); location= ' default.aspx ';</script> ';
}
Finally
{
Con. Close ();//Shut Down database connection
}
}
}
protected void button2_click (object sender, EventArgs e)
{
String sqlstr = "SELECT * from Score";
OleDbConnection conn = Createcon ();
Conn. Open ();
OleDbCommand mycom = new OleDbCommand (SQLSTR, conn);
OleDbDataReader dr = Mycom. ExecuteReader ();
Dr. Read ();
if (Dr. HasRows)
{
GetDataSet (SQLSTR);
}
Else
{
Label1.visible = true;
Label1.Text = "<script language=javascript>alert (' There is no data in the database, please import and query first!") '); location= ' default.aspx ';</script> ';
}
Dr. Close ();
Conn. Close ();
}
Public DataSet GetDataSet (string sqlstr)
{
OleDbConnection conn = Createcon ();
OleDbDataAdapter Myda = new OleDbDataAdapter (SQLSTR, conn);
DataSet ds = new DataSet ();
Myda. Fill (DS);
Gridview1.datasource = ds;
Gridview1.databind ();
return DS;
}
Public DataTable getexceldate ()
{
String strexcelfilename = Server.MapPath ("Student score. xls");
String Strcon = "Provider=Microsoft.Jet.OLEDB.4.0 +" + "Data source=" + Strexcelfilename + ";" + "Extended properties= ' Exce L 8.0; Hdr=yes;imex=1 '; ';
String sql = "SELECT * FROM [sheet1$]";
OleDbDataAdapter da = new OleDbDataAdapter (sql, Strcon);
DataSet ds = new DataSet ();
Da. Fill (DS);
Return DS. Tables[0];
}
public void LoadData (string StyleSheet)
{
Define database connection string m
String Strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" + Server.MapPath ("Student score. xls") + "; Extended Properties=excel 8.0 ";
Creating a database connection
OleDbConnection myconn = new OleDbConnection (Strcon);
Open a data link and get a dataset
MyConn.Open ();
Creating a DataSet Object
DataSet myDataSet = new DataSet ();
To define the SQL statement for a query
String strSQL = "SELECT * FROM [" + StyleSheet + "$]";
Creating a Database Adapter
OleDbDataAdapter mycommand = new OleDbDataAdapter (strSQL, myconn);
Populating data in a dataset
Mycommand.fill (myDataSet, "[" + StyleSheet + "$]");
Releasing the resources in possession
Mycommand.dispose ();
To close a database connection
Myconn.close ();
}
public int exscalar (String sql)
{
OleDbConnection conn = Createcon ();
Conn. Open ();
OleDbCommand com = new OleDbCommand (SQL, conn);
Return Convert.ToInt32 (COM. ExecuteScalar ());
Conn. Close ();
}
}
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
