Read data from an Excel file and import it to the database

Read data from an Excel file and import it to the database

The procedure is as follows:
// Upload the file to be imported to the server
String filepath = "", fileextname = "";
// String myfilename; // It cannot be used, but write it as well.
String mypath;
String fullname = ""; // Save the complete file name
If (fileup. postedfile. filename! = "")
{
// Obtain the file path
Filepath = fileup. postedfile. filename;
// Get the file extension
Fileextname = filepath. substring (filepath. lastindexof (".") + 1 );
// Determine whether the file is an Excel file
If (fileextname = "xls ")
{
Try
{
// Obtain the physical path corresponding to the specified virtual path on the Web Server
Mypath = server. mappath ("upfiles /");
// Get the file name
// Myfilename = filepath. substring (filepath. lastindexof ("") + 1 );
// Obtain the current time and name it "time, minute, and second" to avoid repetition
String strdatename = datetime. Now. tostring ("hhmmss ");
// Save the uploaded file to the specified directory
Fullname = mypath + strdatename + "." + fileextname;
Fileup. postedfile. saveas (fullname );
}
Catch (exception ex)
{
Response. Write (ex. Message );
}
}
Else
{
Page. registerstartupscript ("", "<SCRIPT> alert ('file format incorrect '); </SCRIPT> ");
Return;
}
}
// Read the content in Excel
String strconn = "provider = Microsoft. Jet. oledb.4.0; Data Source =" + fullname + "; extended properties = Excel 8.0 ;";
Oledbconnection connxls = new oledbconnection (strconn );
If (connxls. state. tostring () = "closed ")
{
Connxls. open ();
}
String sqlexcel = ("select * from [sheet1 $]");
Oledbdataadapter myda = new oledbdataadapter (sqlexcel, connxls );
Dataset myds = new dataset ();
Myda. Fill (myds );
If (myds. Tables [0]. Rows. Count> 0)
{
/*************** Bind data to the gridview ******************* **********************************/
Try
{

Gridview1.datasource = myds. Tables [0]. defaultview;
Gridview1.databind ();

}
Catch (exception ex)
{
Response. Write ("read error" + ex. Message );
}
********************** **************************************** ****/

/******* Import data to the database. If there is a corresponding sqlserver2000 database, it is required that the fields correspond **********/
String MySQL = "";
// Link to the sqlserver2000 Database
String sqlconn = "Server = (local); database = learning; user id = sa; Pwd = sasasa ";
Sqlconnection con = new sqlconnection (sqlconn );
If (con. state. tostring () = "closed ")
{
Con. open ();
}
Sqlcommand mycmd = new sqlcommand ();
// Write data row by row into the database
For (INT I = 0; I <myds. Tables [0]. Rows. Count; I ++)
{
// Insert three columns because of the three fields in the database
MySQL = ("insert into myinfo (myname, sex, mylike) values ('" + myds. tables [0]. rows [I] [0]. tostring () + "'," +
"'" + Myds. tables [0]. rows [I] [1]. tostring () + "','" + myds. tables [0]. rows [I] [2]. tostring () + "')");
Mycmd. Connection = con;
Mycmd. commandtext = MySQL;
Try
{
Mycmd. executenonquery ();
}
Catch (exception ex)
{
Response. Write ("error occurred when inserting data into the database" + ex. Message );
}
}
Response. Write ("<SCRIPT> alert ('data has been imported to the database! '); </SCRIPT> ");
If (con. state. tostring () = "open ")
{
Con. Close ();
}
*************************** ***************************************/
}
Else
{
Page. registerstartupscript ("", "<SCRIPT> alert ('no data in Excel! '); </SCRIPT> ");
}
If (connxls. state. tostring () = "open ")
{
Connxls. Close ();
}