将excel导入SQL(ASP.NET2.0/C#)
步骤：1、把EXCEL表上传到服务器，2、用OLEDB读取EXCEL到DataSet，3、用SqlCommand执行INSERT INTO插入数据到SQL
1 public partial class datain : System.Web.UI.Page
2 {
3 private static DataSet myds=new DataSet();
4 public DataSet Myds
5 {
6 get { return myds; }
7 set { myds = value; }
8 }
9 protected void Page_Load(object sender, EventArgs e)
10 {
11 }
12 private void CreateDataSource()
13 { //读取xecel.xls数据到DataSet
14 if (myds!=null)
15 {
16 for (int i = 0; i < GridView1.Rows.Count; i++)
17 GridView1.Rows.Cells.Clear();
18 GridView1.Columns.Clear();
19 myds.Tables.Clear();
20 }
21 string strCon;
22 strCon = "Provider=Microsoft.jet.OLEDB.4.0;Data Source=" + Server.MapPath("excel.xls") + "; Extended Properties=Excel 8.0;";
23 OleDbConnection olecon = new OleDbConnection(strCon);
24 OleDbDataAdapter myda = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strCon);
25 myda.Fill(myds);
26 GridView1.DataSource = myds;
27 GridView1.DataBind();
28 }
29 private void Sqlup(string type)
30 {//将Gridview的数据导入数据库
31 int i=0;
32 string consql = ConfigurationManager.ConnectionStrings["wmrmyyConnectionString"].ConnectionString;
33 string name = "temp";
34 string sex = "temp";
35 string doctornum = "temp";
36 string worknum = "temp";
37 string jszc = "temp";
38 string worktype = "temp";
39 string jop = "temp";
40 string sql = "INSERT INTO VALUES(name,sex,doctornum,worknum,zyjb,worktype,jop)";
41 SqlConnection con = new SqlConnection(consql);
42 con.Open();
43 SqlCommand com = new SqlCommand();
44 com.CommandType = CommandType.Text;
45 com.CommandTimeout = 15;
46 com.Connection = con;
47 if (type == "doctor")
48 {
49 for (i = 0; i < myds.Tables[0].Rows.Count; i++)
50 {
51 try
52 {
53 name = myds.Tables[0].Rows[0].ToString();
54 sex = myds.Tables[0].Rows[1].ToString();
55 doctornum = myds.Tables[0].Rows[2].ToString();
56 worknum = myds.Tables[0].Rows[3].ToString();
57 jszc = myds.Tables[0].Rows[4].ToString();
58 worktype = myds.Tables[0].Rows[5].ToString();
59 jop = myds.Tables[0].Rows[6].ToString();
60 sql = "INSERT INTO doctor VALUES(\'" + name + "\',\'" + sex + "\',\'" + doctornum + "\',\'" + worknum + "\',\'" + jszc + "\',\'" + worktype + "\',\'" + jop + "\')";
61 com.CommandText = sql;
62 com.ExecuteNonQuery();
63 }
64 catch
65 {
66 sql = "UPDATE doctor SET name=\'" + name + "\',sex=\'" + sex + "\',jszc=\'" + jszc + "\',worktype=\'" + worktype + "\',jop=\'" + jop + "\' where doctornum=\'" + myds.Tables[0].Rows[2].ToString() + "\'";
67 com.CommandText = sql;
68 com.ExecuteNonQuery();
69 }
70 }
71 for (i = 0; i < GridView1.Rows.Count;i++ )//删除GridView1的行
72 GridView1.Rows.Cells.Clear();
73 GridView1.Columns.Clear();//删除GridView1的列
74 GridView1.DataBind();
75 myds.Clear();
76 con.Close();
77 File.Delete(Server.MapPath("excel.xls"));
78 GridView2.DataSource = bin("doctor", null);
79 GridView2.DataBind();
80 }
81 else
82 {
83 for (i = 0; i < myds.Tables[0].Rows.Count; i++)
84 {
85 name = myds.Tables[0].Rows[0].ToString();
86 sex = myds.Tables[0].Rows[1].ToString();
87 worknum = myds.Tables[0].Rows[2].ToString();
88 jszc = myds.Tables[0].Rows[3].ToString();
89 worktype = myds.Tables[0].Rows[4].ToString();
90 jop = myds.Tables[0].Rows[5].ToString();
91 sql = "INSERT INTO nurse VALUES(\'" + name + "\',\'" + sex + "\',\'" + worknum + "\',\'" + jszc + "\',\'" + worktype + "\',\'" + jop + "\')";
92 try
93 {
94 com.CommandText = sql;
95 com.ExecuteNonQuery();
96 }
97 catch
98 {
99 sql = "UPDATE nurse SET name=\'" + name + "\',sex=\'" + sex + "\',jszc=\'" + jszc + "\',worktype=\'" + worktype + "\',jop=\'" + jop + "\' where worknum=\'" + myds.Tables[0].Rows[2].ToString() + "\'";
100 com.CommandText = sql;
101 com.ExecuteNonQuery();
102 }
103 }
104 for (i = 0; i < GridView1.Rows.Count; i++)
105 GridView1.Rows.Cells.Clear();
106 GridView1.Columns.Clear();
107 GridView1.DataBind();
108 myds.Clear();
109 con.Close();
110 File.Delete(Server.MapPath("excel.xls"));
111 GridView2.DataSource = bin("nurse", null);
112 GridView2.DataBind();
113 }
114 }
115 private DataSet bin(string fr,string wh)
116 {//在Gridview中显示数据
117 string consql = ConfigurationManager.ConnectionStrings["wmrmyyConnectionString"].ConnectionString;
118 string sql = "select * from "+fr;
119 if (wh != null)
120 sql =sql+ " where " + wh;
121 SqlConnection con = new SqlConnection(consql);
122 SqlDataAdapter sda = new SqlDataAdapter(sql,consql);
123 con.Open();
124 DataSet set = new DataSet();
125 sda.Fill(set,"temp");
126 return set;
127 con.Close();
128 sda.Dispose();
129 set.Clear();
130 }
131 protected void up_Click(object sender, EventArgs e)
132 {//上传excel表到服务器病导入数据到GridView
133 File.Delete(Server.MapPath("excel.xls"));//删除服务器上已有的excel.xls
134 if (FileUpload1.FileName == "")
135 {
136 Response.Write("<script>alert('没有可上传的文件！')</script>");
137 return;
138 }
139 else
140 {
141 FileUpload1.PostedFile.SaveAs(Server.MapPath("excel.xls"));
142 if (Server.MapPath("excel.xls") == "")
143 {
144 Response.Write("<script>alert('上传失败！'')</script>");
145 }
146 else
147 {
148 Response.Write("<script>alert('上传成功！')</script>"); }
149 CreateDataSource();//把excel.xls的内容导入Gridview
150 }
151 }
152 protected void Button2_Click(object sender, EventArgs e)
153 {//把Gridview的内容导入数据库，并删除excel.xls文件
154 if (GridView1.Rows.Count==0)
155 {
156 Response.Write("<script>alert('没有数据可导入')</script>");
157 }
158 else
159 {
160 switch (DropDownList1.SelectedValue)
161 {
162 case "医生表":
163 Sqlup("doctor");
164 break;
165 case "护士表":
166 Sqlup("nurse");
167 break;
168 default:
169 Response.Write("<script>alert('请选择要导入到哪个表！')</script>");
170 break;
171 }
172 }
173 }
174 }
浙公网安备 33010602011771号