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

 

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.

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

C # database data import and export series II Database Export to excel

C # database data import and export series 3 export the database to an Excel file

C # database data import and export series 4 winform Database Import and Export to excel

(Note that the four articles here are only basic methods. If you have higher requirements, refer

Http://www.cnblogs.com/atao/archive/2009/11/15/1603528.html

Http://www.cnblogs.com/tonyqus/category/182110.html

Http://www.yongfa365.com/Item/NPOI-MyXls-DataTable-To-Excel-From-Excel.html. net exports datatable to excel via npoi or myxls

)

 

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 writable data () {If (fuload. filename = "") {lbmsg. TEXT = "select 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, the first line is not the title, 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 (connst R2003);} 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 if (file. exists (server. mappath (filename) {file. delete (server. mappath (filename);} return DT;} catch (excep Tion 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 records int insertcount = 0; // number of records inserted successfully int updatecount = 0; // number of record updates 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.