ASP. NET import Excel to database (SQL)

When we develop various application-oriented systems, we often encounter the import and export of Excel. Why do we use it?

Enterprises or organizations are transforming from informatization to informatization.

Before there is no information-based enterprise or organization, Excel is generally used to record the corresponding data for statistical calculation, after enterprises or organizations implement informatization, they will import the original data to the system for storage and analysis. As a programmer, it is bound to face a function of importing data into the database. The following is an overview of this situation.

In the B/S architecture of ASP. NET, import Excel.

For example:

We need to import the employee information table to the database. The Excel format is as follows:

 

After learning about this, we will use the button in ASP. NET to trigger the import event.

Protected void btnChange_Click (object sender, EventArgs e)

{

UserInfoClass tClass = (UserInfoClass) Session ["UserInfo"];

String tLanguageType = tClass. Language;

// Obtain the file path

String filePath = this. file1.PostedFile. FileName;

If (filePath! = "")

{

If (filePath. Contains ("xls") // you can check whether a file exists.

{

InputExcel (filePath );

}

Else

{

MessageBox. Show ("Please check whether the selected file is an Excel file! Thank you! ");

}

}

Else

{

MessageBox. Show ("select the import file before importing! Thank you! ");

}

}

 

Private void InputExcel (string pPath)

{

String conn = "Provider = Microsoft. Jet. OLEDB.4.0; Data Source =" + pPath + "; Extended Properties = 'excel 8.0; HDR = False; IMEX = 1 '";

OleDbConnection oleCon = new OleDbConnection (conn );

OleCon. Open ();

String SQL = "select * from [Sheet1 $]";

OleDbDataAdapter mycommand = new OleDbDataAdapter (SQL, oleCon );

DataSet ds = new DataSet ();

Mycommand. Fill (ds, "[Sheet1 $]");

OleCon. Close ();

Int count = ds. Tables ["[Sheet1 $]"]. Rows. Count;

For (int I = 0; I <count; I ++)

{

String tUserID, tUserName, tDept, tEmail, tLeader, tAngent;

TUserID = ds. Tables ["[Sheet1 $]"]. Rows [I] ["Employee replacement"]. ToString (). Trim ();

TUserName = ds. Tables ["[Sheet1 $]"]. Rows [I] ["employee name"]. ToString (). Trim ();

TDept = ds. Tables ["[Sheet1 $]"]. Rows [I] ["represent the region of the region"]. ToString (). Trim ();

TEmail = ds. Tables ["[Sheet1 $]"]. Rows [I] ["E-Mail Address"]. ToString (). Trim ();

TLeader = ds. Tables ["[Sheet1 $]"]. Rows [I] ["direct supervisor"]. ToString (). Trim ();

TAngent = ds. Tables ["[Sheet1 $]"]. Rows [I] ["agent"]. ToString (). Trim ();

String excelsql = "insert into" + this. userInfo. company + ".. [resak] (resak001, resak002, resak015, resak005, resak013, resak009) values ('"+ tUserID +"', '"+ tUserName + "', '"+ tDept +"', '"+ tEmail +"', '"+ tLeader +"', '"+ tAngent + "')";

DBCommand cmd = DscDBData. GetDataDBCommand ();

Cmd. ExeNonQuery (excelsql );

}

}

Of course, this part of content should be slightly modified, such as the last part of the Insert statement.

The above describes how to import an Excel file to a database in ASP. NET.

Next, If You Want To directly import Excel to the database in SQL statements, the implementation method is as follows:

SELECT * INTO PURTC

From openrowset ('Microsoft. JET. OLEDB.4.0 ', 'excel 5.0; HDR = YES; DATABASE = D: \ 12.xls', sheet1 $)

Note that if the column in Excel matches the column in the table with OK, you can!

 

As for exporting Excel, I have discussed more in my previous articles.