C # Import and Export of database data ASP. NET Excel

 

 

In daily projects, it is very common to import data in Excel, Word, txt, and other formats to the database. Here I will make a summary

 

This section describes how to import SQL Server, Oracle database, and WinForm into SQL Server and Oracle database.

 

First look at the interface

 

 

 

The basic idea of implementation:

 

1. Use the FileUpload control fuload to upload the Excel file to a folder on the server.

 

2. Use OleDb to read the Excel files uploaded to the server. Here, the Excel files are read as a database. In the database contact statement, Data Source is the physical path of the file on the server.

 

3. Return the data read in step 2 as a able object.

 

4. traverse the DataTable object and go to the SQL Server database to check whether the data exists. If yes, update or not process the data. If no, insert the data.

 

Note: When traversing the DataTable, dt is used. rows [I] ["Name"]. toString (); Name Is the header of the Name column, so the order of the columns in Excel is irrelevant. Of course, the premise is that you know the names of the headers in the columns in Excel. If the order of the columns in Excel is fixed, you can proceed in the following code.

 

Add reference:

 

 

Using System;

Using System. Data;

Using System. Data. OleDb;

Using System. Data. SqlClient;

Using System. IO;

Using System. Text;

Using System. Web;

Using System. Web. UI;

 

 

Code:

 

 

Private DataTable external data ()

{

If (fuload. FileName = "")

{

Lbmsg. Text = "select a file ";

Return null;

}

String fileExtenSion;

FileExtenSion = Path. GetExtension (fuload. FileName );

If (fileExtenSion. ToLower ()! = ". Xls" & fileExtenSion. ToLower ()! = ". Xlsx ")

{

Lbmsg. Text = "the format of the uploaded file is incorrect ";

Return null;

}

Try

{

String FileName = "App_Data/" + Path. GetFileName (fuload. FileName );

If (File. Exists (Server. MapPath (FileName )))

{

File. Delete (Server. MapPath (FileName ));

}

Fuload. SaveAs (Server. MapPath (FileName ));

// HDR = Yes, which indicates that the first line is the title and is not used as data. If HDR = NO is used, it indicates that the first line is not the title and used as data. The default value is YES.

String connstr2003 = "Provider = Microsoft. jet. OLEDB.4.0; Data Source = "+ Server. mapPath (FileName) + "; Extended Properties = 'excel 8.0; HDR = Yes; IMEX = 1 ;'";

String connstr2007 = "Provider = Microsoft. ACE. OLEDB.12.0; Data Source = "+ Server. mapPath (FileName) + "; Extended Properties = \" Excel 12.0; HDR = YES \"";

OleDbConnection conn;

If (fileExtenSion. ToLower () = ". xls ")

{

Conn = new OleDbConnection (connstr2003 );

}

Else

{

Conn = new OleDbConnection (connstr2007 );

}

Conn. Open ();

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

OleDbCommand cmd = new OleDbCommand (SQL, conn );

DataTable dt = new DataTable ();

OleDbDataReader sdr = cmd. ExecuteReader ();

 

Dt. Load (sdr );

Sdr. Close ();

Conn. Close ();

// Delete the files uploaded on the server

If (File. Exists (Server. MapPath (FileName )))

{

File. Delete (Server. MapPath (FileName ));

}

Return dt;

}

Catch (Exception e)

{

Return null;

}

}

 

Protected void Btn_Export_Excel_To_DB_Click (object sender, EventArgs e)

{

Try {

 

DataTable dt = foreign data ();

 

// DataGridView2.DataSource = ds. Tables [0];

Int errorcount = 0; // Number of error messages recorded

Int insertcount = 0; // number of records inserted successfully

 

Int updatecount = 0; // number of records for updating information

 

String strcon = "server = localhost; database = database1; uid = sa; pwd = sa ";

SqlConnection conn = new SqlConnection (strcon); // link to the database

Conn. Open ();

 

For (int I = 0; I <dt. Rows. Count; I ++)

{

String Name = dt. rows [I] [0]. toString (); // dt. rows [I] ["Name"]. toString (); "Name" is the header of the Name column in Excel

String Sex = dt. Rows [I] [1]. ToString ();

Int Age = Convert. ToInt32 (dt. Rows [I] [2]. ToString ());

String Address = dt. Rows [I] [3]. ToString ();

If (Name! = "" & Sex! = "" & Age! = 0 & Address! = "")

{

SqlCommand selectcmd = new SqlCommand ("select count (*) from users where Name = '"+ Name +"' and Sex = '"+ Sex +"' and Age = '"+ Age +"' and Address = "+ Address, conn );

Int count = Convert. ToInt32 (selectcmd. ExecuteScalar ());

If (count> 0)

{

Updatecount ++;

}

Else

{

SqlCommand insertcmd = new SqlCommand ("insert into users (Name, Sex, Age, Address) values ('" + Name + "', '" + Sex + "', "+ Age +", '"+ Address +"') ", conn );

Insertcmd. ExecuteNonQuery ();

Insertcount ++;

}

}

Else

{

Errorcount ++;

}

}

Response. Write (insertcount + "data entries imported successfully! "+ Updatecount +" duplicate data! "+ Errorcount +" The part of the data is blank and is not imported! "));

}

Catch (Exception ex)

{

 

}

}

Here is the introduction.

 

From the cpcpc Column