C # about Excel importing databases

Source: Internet
Author: User

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>
&LT;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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.