Write irregular Excel file data to the database

This is to write the data in the following Excel file into the database table of the train ticket booking system. Let's take a look at what Excel Data looks like.

There are two worksheets in this file, and the data is arranged by style. Next, let's look at the database table we want to import.

There are three tables, in which the traintype is the train type table and stores the train type, such as regular vehicles and EMUs.

Traintrip stores trains and reachstation stores arrival stations of trains. Because the start stations are all in Fuzhou,

So the table is now designed in this way.

According to the Excel format, reading data from a regular data source is definitely not feasible. You should adopt a more flexible approach,

So we can useProgramTo read the information of each cell. The constant searching on the internet finally allows me to find an open-source read

Excel Format FileCodeNexcel. For more information, visit the official website http://nexcel.sourceforge.net/

 

Let's take a look at our program interface before starting our program code.

This interface first uploads the file and then imports the data in the file into the database. The file path is saved to the session temporarily.

The following is the code:

 If ( This . Isvalid & txtcheckcode. Text. tolower () = Convert . Tostring (session [ "Checkcode" ]). Tolower ()){ Try { Using ( Sqlconnection Conn = New  Sqlconnection ( Sqlhelper . Connectionstring) {conn. open (); Sqldataadapter Sdatraintype = New  Sqldataadapter ("Select * From traintype" , Conn ); Sqldataadapter Sdatraintrip = New  Sqldataadapter ( "Select * From traintrip" , Conn ); Sqldataadapter Sdareachstation = New  Sqldataadapter ( "Select * From reachstation" , Conn ); Sqlcommandbuilder Buildertraintype = New Sqlcommandbuilder (Sdatraintype ); Sqlcommandbuilder Buildertraintrip = New  Sqlcommandbuilder (Sdatraintrip ); Sqlcommandbuilder Builderreachstation = New  Sqlcommandbuilder (Sdareachstation ); Sqlcommand Required traintype = buildertraintype. getinsertcommand (); Sqlcommand Required traintrip = buildertraintrip. getinsertcommand (); Sqlcommand Cmdreachstation = builderreachstation. getinsertcommand (); Dataset DS = New  Dataset (); Sdatraintype. Fill (DS, "Traintype" ); Sdatraintrip. Fill (DS, "Traintrip" ); Sdareachstation. Fill (DS, "Reachstation" ); Datatable Traintype = Ds. Tables [ "Traintype" ]; Datatable Traintrip = Ds. Tables [ "Traintrip" ]; Datatable Reachstation = Ds. Tables [ "Reachstation" ]; // Excel Data Operations  String Filepath = server. mappath (session [ "FILENAME" ]. Tostring ()); Workbook Workbook = Workbook . Getworkbook (filepath ); Int Traintypeid = 1; Int Traintripid = 1; Int Reachstationid = 1;Foreach ( Sheet Sheet In Workbook. Sheets ){ // Add data to traintype  Datarow Traintyperow = traintype. newrow (); traintyperow [ "ID" ] = Traintypeid ++; traintyperow [ "Name" ] = Sheet. Name; traintype. Rows. Add (traintyperow ); For ( Int Irow = 1; irow <sheet. Rows; irow = irow + 3 ){// Add data to traintrip  If (Sheet. getcell (0, irow). Contents! = "" ){ Datarow Traintriprow = traintrip. newrow (); traintriprow [ "ID" ] = Traintripid ++; traintriprow [ "Tripname" ] = Sheet. getcell (0, irow). Contents; traintriprow [ "Starttime" ] = Sheet. getcell (1, irow). Contents; traintriprow [ "Typeid" ] = Traintypeid-1; traintriprow ["Ticketcount" ] = 0; traintrip. Rows. Add (traintriprow );} // Add data to reachstation  For ( Int Icol = 2; icol <sheet. columns; icol ++ ){ If (Sheet. getcell (icol, irow). Contents = "" ){ Break ; // Exit the loop if the arrival station is empty } Datarow Reachstationrow = reachstation. newrow (); reachstationrow [ "ID" ] = Reachstationid ++; reachstationrow [ "Name" ] = Sheet. getcell (icol, irow). Contents; reachstationrow [ "Reachtime" ] = Sheet. getcell (icol, irow + 1). Contents; reachstationrow [ "Hardseatprice" ] = Sheet. getcell (icol, irow + 2). Contents; reachstationrow [ "Advanceprice" ] = Common . Getadvanceprice ( Convert . Todecimal (sheet. getcell (icol, irow + 2). Contents); reachstationrow [ "Tripid" ] = Traintripid-1; reachstationrow [ "Ticketcount" ] = 0; reachstation. Rows. Add (reachstationrow) ;}} sdatraintype. Update (DS, "Traintype" ); Sdatraintrip. Update (DS, "Traintrip" ); Sdareachstation. Update (DS, "Reachstation" ); Workbook. Close (); lblmsg. Text = "Data imported successfully! " ;}} Catch {Lblmsg. Text = "Failed to import data! " ;}} Else {Lblmsg. Text = "Incorrect verification code! " ;}

 
I am exhausted by the need to make the entire system. But now I keep some of my thoughts here.
 

 
First, the design of the database should take into account whether the table ID needs to be converted into non-self-increment if it involves Database Import. Otherwise, it will cause some difficulties for subsequent development.
 

There is still a problem with the time field. If the time is and there is no date, it is related to the Database Export to excel should be set to the string type, not the datetime type.

The system database is designed to delete data from multiple tables. whether to add foreign key constraints should be considered. If foreign key constraints exist, multiple tables may be deleted due to Mutual Association.

It cannot proceed smoothly.

The next step is the program design. Remember not to keep pursuing the most concise code, but to write a few more sentences, do not be afraid of repetition, just as I updated the datatable to the database in the above section.

Code, I always want to use only oneSqldataadapterI want to write a few more data operations.SqldataadapterIt is still much written. The above are purely personal opinions.

 

you are welcome to reprint it. Please specify the source.