1. Functional analysis
The microsoft.jet.oledb.4.0 approach enables you to use Ado.net to access Excel, as the following example code is a string that connects Excel data:
Copy Code code as follows:
String Strodbccon = @ "Provider=Microsoft.Jet.OLEDB.4.0; Persist Security info=false;data source=d:\2010 Year Book sales. xls; Extended Properties=excel 8.0 ";
2. Implementation methods
Program Development Steps:
(1) Create a new Web site, named 25, whose homepage defaults to Default.aspx.
(2) Add a table table to the Default.aspx page to lay out the page, and then add an IFRAME frame, two button controls, and a GridView control to the table table, where The IFRAME framework is used to display data from the original Excel datasheet, which is used to import the datasheet in the specified Excel into the SQL Server database and to bind the Excel data in the imported SQL Server database to the GridView control ; The GridView control is used to display Excel data in an imported SQL Server database.
(3) The main code of the program is as follows.
Default.aspx page, you first customize a LoadData method, which is no return value type method, and is used primarily to import data from an Excel datasheet into a SQL Server database. The LoadData method implements the code as follows:
Copy Code code as follows:
public void LoadData (string StyleSheet)
{
String Strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" + Server.MapPath
("Usersdb.xls") + "; Extended Properties=excel 8.0 ";
OleDbConnection myconn = new OleDbConnection (Strcon);
MyConn.Open (); Open a data link and get a dataset
DataSet myDataSet = new DataSet (); Creating a DataSet Object
String strSQL = "SELECT * FROM [" + StyleSheet + "$]";
OleDbDataAdapter mycommand = new OleDbDataAdapter (strSQL, myconn);
Mycommand.fill (myDataSet, "[" + StyleSheet + "$]");
Mycommand.dispose ();
DataTable DT = mydataset.tables["[" + StyleSheet + "$]"];
Myconn.close ();
Mycommand.dispose ();
String strconn = "Data source= (local);D atabase=usersdb; Uid=sa; Pwd= ";
SqlConnection conn = new SqlConnection (strconn);
Conn. Open ();
for (int j = 0; J < DT.) Rows.Count; J + +)
{
String UserID = DT. Rows[j][0]. ToString ();
String emailaddress = DT. ROWS[J][1]. ToString ();
String FirstName = DT. ROWS[J][2]. ToString ();
String LastName = DT. ROWS[J][3]. ToString ();
String Address1 = DT. ROWS[J][4]. ToString ();
String Address2 = DT. ROWS[J][5]. ToString ();
String city = DT. ROWS[J][6]. ToString ();
String strSQL = "INSERT into Usersdb (Emailaddress,firstname,
lastname,address1,address2,city) ";
strSQL = strSQL + "VALUES (' + EmailAddress +" ', ' "+ FirstName +",
"+ LastName +" ', ' "+ Address1 +" ', ' "+ Address2 +" ', ' "+ City +" ') ";
SqlCommand comm = new SqlCommand (strSQL, conn);
Comm. ExecuteNonQuery ();
if (j = = DT. ROWS.COUNT-1)
{
Label1.visible = true;
}
Else
{
Label1.visible = false;
}
}
Conn. Close ();
}
Click the Excel data to write to Database button, define a variable of type string that is used to pass parameters to LoadData, and then call the LoadData custom method to import the datasheet in the specified Excel into the SQL Server database. The Click event code for the Excel data write to Database button is as follows:
Copy Code code as follows:
protected void Button1_Click (object sender, EventArgs e)
{
String StyleSheet = "Sheet1";
LoadData (StyleSheet);
}
Click the show Excel data for imported SQL button to bind the Excel data in the imported SQL Server database to the GridView control, which is displayed in the Web page. The Click event code for the "Show Excel data for imported SQL" button is as follows:
Copy Code code as follows:
protected void button2_click (object sender, EventArgs e)
{
String strconn = "Data source= (local);D atabase=usersdb; Uid=sa; Pwd= ";
String sqlstr= "select * from Usersdb";
SqlConnection conn = new SqlConnection (strconn);
SqlDataAdapter Myda = new SqlDataAdapter (Sqlstr,conn);
DataSet ds = new DataSet ();
Conn. Open ();
Myda. Fill (ds, "Usersdb");
Gridview1.datasource = ds;
Gridview1.databind ();
Conn. Close ();
}
Note: When you perform operations related to Excel and SQL Server databases in your program, you first need to add the System.Data.OleDb and System.Data.SqlClient namespaces separately.
3. Supplementary notes
In addition to importing data from Excel into the SQL Server database, you can convert it to a. txt text file format, or import it into a database such as Access or Oracle.
