The program robust control is added for some code snippets on the Internet.
The front-end interface is as follows:
<% @ Page Language = "C #" autoeventwireup = "true" codefile = "default. aspx. cs" inherits = "_ default" %>
<! Doctype HTML public "-// W3C // dtd xhtml 1.0 transitional // en" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<HTML xmlns = "http://www.w3.org/1999/xhtml">
<Head runat = "server">
<Title> No title page </title>
</Head>
<Body>
<Form ID = "form1" runat = "server">
<Div>
<Asp: fileupload id = "fuopen" runat = "server"/>
<Asp: button id = "btnupload" runat = "server" text = "import" onclick = "btnupload_click"/>
<Asp: gridview id = "gridview1" runat = "server">
</ASP: gridview>
</Div>
</Form>
</Body>
</Html>
Background code:
Using system;
Using system. configuration;
Using system. Data;
Using system. Web;
Using system. Web. Security;
Using system. Web. UI;
Using system. Web. UI. htmlcontrols;
Using system. Web. UI. webcontrols;
Using system. Web. UI. webcontrols. webparts;
Using system. Data. oledb;
Public partial class _ default: system. Web. UI. Page
{
Protected void page_load (Object sender, eventargs E)
{
}
Protected void btnupload_click (Object sender, eventargs E)
{
// Make sure that you have selected the file to be imported. Upload the file first, and then complete the import on the server.
If (this. fuopen. postedfile. filename! = "")
{
// Make sure the file is in Excel format
// Response. Write (this. fuopen. filename. substring (this. fuopen. filename. lastindexof ('.')));
If (this. fuopen. filename. substring (this. fuopen. filename. lastindexof ('.') + 1) = "xls ")
{
Random RD = new random (1 );
String filename = datetime. now. date. tostring ("yyyymmdd") + datetime. now. tolongtimestring (). replace (":", "") + Rd. next (1, 9999 ). tostring () + ". xls ";
Try
{
This. fuopen. postedfile. saveas (@ server. mappath ("fileupload/") + filename );
}
Catch (httpexception he)
{
Response. Write ("the File Upload Failed. Check whether the file is too large and whether you have the write permission! ");
Return;
}
# Region -------- Read File Content to server memory ----------
String conn = "provider = Microsoft. jet. oledb.4.0; Data Source = "+ server. mappath ("fileupload") + "/" + filename + "; extended properties = Excel 8.0 ";
Oledbconnection thisconnection = new oledbconnection (conn );
Thisconnection. open ();
// Ensure that the field name is the same as that in the Excel table
String SQL = "select stuname, majorid, cardid from [sheet1 $]";
Oledbdataadapter mycommand = new oledbdataadapter (SQL, thisconnection );
Dataset DS = new dataset ();
Mycommand. Fill (DS, "[sheet1 $]");
Thisconnection. Close ();
This. gridview1.datasource = Ds;
This. gridview1.databind ();
# Endregion
# Region -------- insert to database ---------
String conn1 = "User ID = sa; Data Source = 127.0.0.1 // sqlserver; Password = sa; initial catalog = login; provider = sqloledb.1 ;";
Oledbconnection thisconnection1 = new oledbconnection (conn1 );
Thisconnection1.open ();
Int COUNT = Ds. Tables ["[sheet1 $]"]. Rows. count;
For (INT I = 0; I <count; I ++)
{
String stuname, majorid, cardid;
Stuname = Ds. Tables ["[sheet1 $]"]. Rows [I] ["stuname"]. tostring ();
Majorid = Ds. Tables ["[sheet1 $]"]. Rows [I] ["majorid"]. tostring ();
Cardid = Ds. Tables ["[sheet1 $]"]. Rows [I] ["cardid"]. tostring ();
// Id_3 = Ds. Tables ["[sheet1 $]"]. Rows [I] ["id_3"]. tostring ();
String excelsql = "insert into fee_stu (stuname, majorid, cardid) values ('" + stuname + "', '" + majorid + "', '" + cardid + "') ";
Oledbcommand mycommand1 = new oledbcommand (excelsql, thisconnection1 );
Try
{
Mycommand1.executenonquery ();
}
Catch (oledbexception ODE)
{
Response. Write ("<B> Import failed. Please try again! </B> ");
Return;
}
}
Response. Write ("updated successfully ");
Thisconnection1.close ();
# Endregion
}
Else
{
Response. Write ("the format of the imported file is incorrect! ");
}
}
Else
{
Response. Write ("You have not selected the file to import! ");
}
}
}