The following examples are reproduced: Internet
You first define a method named Execleds in the class that populates the data in the Excel table into a dataset with the following code
Public DataSet execleds (string filenameurl,string table)
2 {
3 String strconn = "provider=microsoft.jet.oledb.4.0;" + "Data source=" +filenameurl+ "; Extended properties= ' Excel 8.0; Hdr=yes; Imex=1 ' ";
4 OleDbConnection conn = new OleDbConnection (strconn);
5
6 OleDbDataAdapter Odda = new OleDbDataAdapter ("SELECT * from [sheet1$]", conn);
7 DataSet ds = new DataSet ();
8 Odda. Fill (ds,table);
9
Ten return DS;
11
12}
Then design the page and make a simple page here
<table style= "WIDTH:395PX; Height:84px "border=" 1 ">
2 <tr>
3 <td style= "width:380px" align= "Center" >
4 Bulk Import of user information </td>
5 </tr>
6 <tr>
7 <td style= "width:100px" >
8 <asp:fileupload id= "FileUpload1" runat= "Server" width= "380px"/></td>
9 </tr>
Ten <tr>
<TD style= "width:380px" align= "Center" >
<asp:button id= "Button1" runat= "Server" text= "Add" onclick= "Button1_Click"/></td>
</tr>
</table>
<asp:label id= "Label1" runat= "Server" width= "466px" ></asp:Label>
When you click the Add button to fire the event, the code is as follows
protected void Button1_Click (object sender, EventArgs e)
{
if (Fileupload1.hasfile = = False)
{
Response.Write ("<script>alert (' Please select Excel file ') </script>");
return;//when there is no file, return
}
String Isxls=system.io.path.getextension (Fileupload1.filename). ToString (). ToLower ();
if (Isxls! = ". xls")
{
Response.Write ("<script>alert (' can only select Excel files ') </script>");
return;//When you select a file other than Excel, return
}
string error = NULL;
Access.class1 ac = new Access.class1 ();
SqlConnection cn = Ac.myconnection ();
cn. Open ();
String strpath = FileUpload1.PostedFile.FileName.ToString (); Get execle file path
string filename = Fileupload1.filename; Get execle file name
DataSet ds = AC. Execleds (Strpath,filename);
datarow[] Dr = ds. Tables[0]. Select (); Define a DataRow array
int rowsnum = ds. Tables[0]. Rows.Count;
if (Rowsnum = = 0)
{
Response.Write ("<script>alert (' Excel table is empty table, no data! ') </script> "); Prompt the user when the Excel table is empty
}
Else
{
for (int i = 0; i < Dr. Length; i++)
{
String YHMC = dr[i]["Yonghumingcheng"]. ToString ();
String yhmm = dr[i]["Yonghumima"]. ToString ();
String DQRQ = dr[i]["Daoqiriqi"]. ToString ();
String ZT = dr[i]["Zhuangtai"]. ToString ();
String TJSJ = dr[i]["Tianjiashijian"]. ToString ();
String JXDM = dr[i]["Jiaxiaodaima"]. ToString ();
String Sqlcheck = "SELECT count (*) from Dc_yonghuliebiao where yonghumingcheng= '" + YHMC + "' and jiaxiaodaima= '" + JXDM + " ‘"; Check if the user exists
bool ch = ac.check (Sqlcheck);
if (ch = = true)
{
String insertstr = "INSERT into Dc_yonghuliebiao (Yonghumingcheng,yonghumima,daoqiriqi,zhuangtai,tianjiashijian, Jiaxiaodaima) VALUES (' "+
YHMC + "', '" + yhmm + "', '" + Dqrq + "', '" + ZT + "', '" + TJSJ + "', '" + JXDM + "')";
SqlCommand cmd = new SqlCommand (INSERTSTR,CN);
Try
{
Cmd. ExecuteNonQuery ();
} catch (MembershipCreateUserException ex)//catch exception
{
Response.Write ("<script>alert (' Create User:" +ex. Message+ "') </script>");
}
}
Else
{
Error + = "<em style= ' color:red;font-sixe:25px ' >" +yhmc+ "</em> user already exists, this row record cannot be inserted! Please modify the user to insert <br>"; If the user exists, the existing user information is typed and prompted that the user cannot insert
Continue
}
}
Response.Write ("<script>alert (' excle table import succeeded! ') </script> ");
Label1.Text = error;
}
cn. Close ();
}
C # about Excel importing databases