C # import Excel table data

Protected void btnimport_click (Object sender, eventargs E)

{

Int J = 1;

Datetime str_date;

Dataset Ds;

String isxls = system. Io. Path. getextension (fileupload. filename). tostring (). tolower (); // system. Io. Path. getextension get the file extension

If (fileupload. hasfile = false) // hasfile is used to check whether a specified file exists in fileupload.

{

Uploaderrortxt = "Please select an Excel file! <Br/> ";

}

Else if (isxls! = ". Xls" & isxls! = ". XLSX ")

{

Uploaderrortxt + = "the uploaded file suffix is. xls! <Br/> ";

}

Else

{

String filename = datetime. Now. tostring ("yyyymmddhhmmss") + fileupload. filename;

String savepath = server. mappath (("~ // Upfiles // ") + filename );

Fileupload. saveas (savepath );

Try

{

DS = execleds (savepath, filename );

}

Catch

{

Uploaderrortxt = "Import failed. The Excel worksheet tag name is incorrect. The tag name must be sheet1. Please verify and import it again! ";

Gotoerror ();

Return;

}

Datarow [] DR = Ds. Tables [0]. Select ();

Int rowsnum = Ds. Tables [0]. Rows. count;

If (rowsnum = 0)

{

Uploaderrortxt + = "the Excel table is empty and has no data! <Br/> ";

}

Else

{

For (INT I = 0; I <dr. length; I ++)

{

J = J + 1;

String str_id = Dr [I] ["ID"]. tostring ();

String str_name = Dr [I] ["name"]. tostring ();

Try

{

Str_date = datetime. parse (Dr [I] ["time"]. tostring ());

}

Catch

{

The uploaderrortxt = "" + J + "line time format is incorrect. Stop importing! ";

Gotoerror ();

Return;

}

Bool B = BLL. exists (str_id); // ID does not exist

If (B = false)

{

Try

{

Model. ID = int. parse (str_id ;);

}

Catch

{

Uploaderrortxt = "" + J + "Row ID format error. Stop importing! ";

Gotoerror ();

Return;

}

Model. Date = str_date;

Model. Name = str_name;

Try

{

Bll. Add (model );

Uploadsucceedtxt = "excle table imported successfully! ";

}

Catch (membershipcreateuserexception ex)

{

Uploaderrortxt = "imported content:" + ex. message;

}

}

Else

{

Uploaderrortxt + = "ID:" + str_id + "id duplicate import prohibited! <Br/> ";

}

}

}

}

If (uploaderrortxt! = "")

{

String txtprint = "";

Txtprint + = "<Table width = '000000' border = '0' align = 'center' cellpadding = '0' cellspacing = '1' bgcolor = '# bbc7ce 'class =' tab01 '> ";

Txtprint + = "<tr> ";

Txtprint + = "<TD bgcolor = '# ffff'> ";

Txtprint + = uploaderrortxt;

Txtprint + = "</TD> ";

Txtprint + = "</tr> ";

Txtprint + = "</table> ";

// Output error message

This. laberrortxt. Text = txtprint;

}

Else

{

String txtprint = "";

Txtprint + = "<Table width = '000000' border = '0' align = 'center' cellpadding = '0' cellspacing = '1' bgcolor = '# bbc7ce 'class =' tab01 '> ";

Txtprint + = "<tr> ";

Txtprint + = "<TD bgcolor = '# ffff'> ";

Txtprint + = uploadsucceedtxt;

Txtprint + = "</TD> ";

Txtprint + = "</tr> ";

Txtprint + = "</table> ";

// Output a success prompt

This. laberrortxt. Text = txtprint;

Initdatebind ();

}

}

Void gotoerror ()

{

If (uploaderrortxt! = "")

{

String txtprint = "";

Txtprint + = "<Table width = '000000' border = '0' align = 'center' cellpadding = '0' cellspacing = '1' bgcolor = '# bbc7ce 'class =' tab01 '> ";

Txtprint + = "<tr> ";

Txtprint + = "<TD bgcolor = '# ffff'> ";

Txtprint + = uploaderrortxt;

Txtprint + = "</TD> ";

Txtprint + = "</tr> ";

Txtprint + = "</table> ";

// Output error message

This. laberrortxt. Text = txtprint;

Initdatebind ();

}

}

Public dataset execleds (string filenameurl, string table)

{

Dataset DS = new dataset ();

String strconn = "provider = Microsoft. Ace. oledb.12.0; Data Source =" + filenameurl + "; extended properties = 'excel 12.0; HDR = yes; IMEX = 1 '";

Oledbconnection conn = new oledbconnection (strconn );

Conn. open ();

Oledbdataadapter Odda = new oledbdataadapter ("select * from [sheet1 $]", Conn );

Odda. Fill (DS, table );

Return Ds;

}