How to import data from Excel to the Access Database

Source: Internet
Author: User

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 ();
}
}

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.