Default. aspx
Copy codeThe Code is as follows:
<% @ Page Language = "C #" AutoEventWireup = "true" CodeFile = "Default. aspx. cs" Inherits = "_ Default" %>
<! DOCTYPE html PUBLIC "-// W3C // dtd xhtml 1.0 Transitional // EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<Html xmlns = "http://www.w3.org/1999/xhtml">
<Head id = "Head1" runat = "server">
<Title> No title page </title>
<Style type = "text/css">
. Style1
{
Height: 16px;
}
. Style3
{
Height: 23px;
}
</Style>
</Head>
<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 into the 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" scrolling = "yes" src = "student id .xls"
Style = "WIDTH: 407px; HEIGHT: 280px"> </iframe>
</Td>
<Td style = "WIDTH: pixel PX; 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"/>
<HeaderStyle BackColor = "#990000" Font-Bold = "True" ForeColor = "White"/>
<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 = "button#click"
Text = "Writing Excel Data to the Access Database"/>
<Asp: Label ID = "Label1" runat = "server" Text = "Label" Visible = "False"
Style = "font-size: x-small"> </asp: Label>
</Td>
<Td style = "WIDTH: pixel PX; HEIGHT: 23px; BACKGROUND-COLOR: # ff9900; TEXT-ALIGN: center">
<Asp: Button ID = "Button2" runat = "server" Font-Size = "9pt" onclick = "Button2_Click"
Text = "show Excel data in the database"/>
</Td>
</Tr>
<Tr>
<Td>
</Td>
</Tr>
</Table>
</Form>
</Body>
</Html>
Default. aspx. cs
Copy codeThe Code is as follows:
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 button#click (object sender, EventArgs e)
{
// Define the Excel list
String StyleSheet = "Sheet1 ";
// Call the custom LoadData method to read data from the Excel file to the ASPNET page
LoadData (StyleSheet );
// Define the query SQL statement
String SQL = "select ID, user name, examination paper, Score, test time from Score ";
// Create an Oledb database connection
OleDbConnection con = CreateCon ();
Con. Open (); // Open the database connection
OleDbCommand com = new OleDbCommand (SQL, con );
// Start the transaction
OleDbTransaction tran = con. BeginTransaction ();
Com. Transaction = tran;
// Create an adapter
OleDbDataAdapter da = new OleDbDataAdapter (com );
OleDbCommandBuilder cb = new OleDbCommandBuilder (da );
// Create a DataSet
DataSet ds = new DataSet ();
// Fill the dataset
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 whether the Excel file has been imported into the Access database
If (ExScalar (selsql)> 0)
{
Label1.Visible = true;
Label1.Text = "<script language = javascript> alert ('the data in this Excle has been imported into the database! '); Location = 'default. aspx'; </script> ";
}
Else
{
// Read the data in the Excel file cyclically and add it to the database table created in advance by Access
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); // execute the insert operation.
Tran. Commit (); // transaction Commit
Label1.Visible = true;
Label1.Text = "<script language = javascript> alert ('data imported successfully! '); 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 (); // Close the 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 ('the database has no data information. Please import it first and then query it! '); 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 ID .xls ");
String strcon = "Provider = Microsoft. jet. OLEDB.4.0; "+" Data Source = "+ strExcelFileName +"; "+" Extended Properties = 'excel 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 the database connection string m
String strCon = "Provider = Microsoft. Jet. OLEDB.4.0; Data Source =" + Server. MapPath ("student ID .xls") + "; Extended Properties = Excel 8.0 ";
// Create a database connection
OleDbConnection myConn = new OleDbConnection (strCon );
// Open the data link to obtain a dataset
MyConn. Open ();
// Create a DataSet object
DataSet myDataSet = new DataSet ();
// Define the query SQL statement
String StrSql = "select * from [" + StyleSheet + "$]";
// Create a database Adapter
OleDbDataAdapter myCommand = new OleDbDataAdapter (StrSql, myConn );
// Fill in the data in the dataset
MyCommand. Fill (myDataSet, "[" + StyleSheet + "$]");
// Release occupied Resources
MyCommand. Dispose ();
// Close the 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
