1 /// <summary>
2 /// 从Excel文件中导入数据
3 /// </summary>
4 /// <param name="sender"></param>
5 /// <param name="e"></param>
6 protected void BtnExeclInput_ServerClick(object sender, EventArgs e)
7 {
8 try
9 {
10 DynamicAccountInfo daInfo = new DynamicAccountInfo();
11 string filename = FileUpload1.FileName;//获取文件名
12
13 bool IsSucc = false;//向数据库插入数据是否成功
14 bool IsTrue = true;//初始化输入数据是正确的
15 bool Istrue = true;
16
17 if (filename == "")
18 {
19 SetInfo("请选择文件进行导入!");
20 }
21 else
22 {
23
24 FileUpload1.SaveAs(Server.MapPath("../Excel文件/") + filename);
25 string _strfilename = Server.MapPath("../Excel文件/") + filename;
26
27 DataTable dtExcel = daDB.ExeclToDataTable(_strfilename, "Sheet1");
28
29 //对Excel文件的设备信息进行验证
30 for (int i = 0; i < dtExcel.Rows.Count; i++)
31 {
32 string DeviceID = dtExcel.Rows[i][0].ToString();
33 string UserMonth = dtExcel.Rows[i][1].ToString();
34 string state = dtExcel.Rows[i][4].ToString();
35 string deptname = dtExcel.Rows[i][5].ToString();
36 string FaceNo = dtExcel.Rows[i][7].ToString();
37 if (DeviceID=="")
38 {
39 IsTrue = false;
40 SetInfo("第"+(i+1)+"行的设备编号为空了!");
41 }
42 else if (Convert.ToDecimal(dtExcel.Rows[i][3].ToString())<0)
43 {
44 IsTrue = false;
45 SetInfo("第" + (i + 1) + "行的资产净值不能小于0 !");
46 }
47 else if (deptname=="")
48 {
49 IsTrue = false;
50 SetInfo("第" + (i + 1) + "行的使用部门不能为空 !");
51 }
52 else if (FaceNo=="")
53 {
54 daInfo.FaceID = 0;
55 }
56 else if (state != "待用" && state != "在用" && state != "在修" && state != "报废")//判断设备状态:0待用;1在用;2在修;3报废;
57 {
58 IsTrue = false;
59 SetInfo("您输入的第" + (i + 1) + "行的使用状态不正确!");
60 }
61 else
62 {
63 IsTrue = VerifyInfo(DeviceID, UserMonth, deptname, FaceNo);
64 Istrue = VerifyTime(state, dtExcel.Rows[i][9].ToString(), dtExcel.Rows[i][10].ToString());
65 }
66
67 }//end of for
68
69 if (IsTrue && Istrue)//所有数据都正确时,开始添加数据
70 {
71 for (int i = 0; i < dtExcel.Rows.Count; i++)
72 {
73 daInfo.DeviceID = dtExcel.Rows[i][0].ToString();
74 daInfo.UseMonth = int.Parse(dtExcel.Rows[i][1].ToString());
75 daInfo.AccuAmort = Convert.ToDecimal(dtExcel.Rows[i][2].ToString());
76 daInfo.NetWorth = Convert.ToDecimal(dtExcel.Rows[i][3].ToString());
77
78 switch (dtExcel.Rows[i][4].ToString())
79 {
80 case "待用": daInfo.State = 0; break;
81 case "在用": daInfo.State = 1; break;
82 case "在修": daInfo.State = 2; break;
83 case "报废": daInfo.State = 3; break;
84 }
85 string deptname = dtExcel.Rows[i][5].ToString();
86
87 daInfo.DeptID = daDB.GetDeptIDFromDB(deptname);
88
89 daInfo.RepairPlace = dtExcel.Rows[i][6].ToString();
90 string faceNo = dtExcel.Rows[i][7].ToString();
91 if (faceNo == "")
92 {
93 daInfo.FaceID = 0;
94 }
95 else
96 {
97 daInfo.FaceID = daDB.GetFaceIDFromDB(faceNo);
98 }
99
100
101 daInfo.DeviceSource = dtExcel.Rows[i][8].ToString();
102 daInfo.SDate = Convert.ToDateTime(dtExcel.Rows[i][9].ToString());
103 if (dtExcel.Rows[i][10].ToString() == "")
104 {
105 daInfo.EDate = Convert.ToDateTime("1900-01-01");
106 }
107 else
108 {
109 daInfo.EDate = Convert.ToDateTime(dtExcel.Rows[i][10].ToString());
110 }
111 daInfo.Remark1 = dtExcel.Rows[i][11].ToString();
112 daInfo.Guid = Guid.NewGuid().ToString();
113
114 IsSucc = daDB.AddDynamicAccountInfo(daInfo);
115 }
116 File.Delete(_strfilename);//删除服务器的临时文件
117 if (IsSucc)
118 {
119 string tempScript = "<script language='javascript' type='text/javascript'> alert('添加数据成功 !');window.close();</script> ";
120 ClientScript.RegisterStartupScript(this.GetType(), "", tempScript);
121 }
122 else
123 {
124 SetInfo("添加数据失败!");
125 }
126
127 }
128 else
129 {
130 File.Delete(_strfilename);//删除服务器的临时文件
131
132 }
133 }
134 }
135 catch (Exception ex)
136 {
137 SetInfo(ex.Message);
138 }
139
140 }
141
142
首先在项目文件夹中创建一个Excel文件2 /// 从Excel文件中导入数据
3 /// </summary>
4 /// <param name="sender"></param>
5 /// <param name="e"></param>
6 protected void BtnExeclInput_ServerClick(object sender, EventArgs e)
7 {
8 try
9 {
10 DynamicAccountInfo daInfo = new DynamicAccountInfo();
11 string filename = FileUpload1.FileName;//获取文件名
12
13 bool IsSucc = false;//向数据库插入数据是否成功
14 bool IsTrue = true;//初始化输入数据是正确的
15 bool Istrue = true;
16
17 if (filename == "")
18 {
19 SetInfo("请选择文件进行导入!");
20 }
21 else
22 {
23
24 FileUpload1.SaveAs(Server.MapPath("../Excel文件/") + filename);
25 string _strfilename = Server.MapPath("../Excel文件/") + filename;
26
27 DataTable dtExcel = daDB.ExeclToDataTable(_strfilename, "Sheet1");
28
29 //对Excel文件的设备信息进行验证
30 for (int i = 0; i < dtExcel.Rows.Count; i++)
31 {
32 string DeviceID = dtExcel.Rows[i][0].ToString();
33 string UserMonth = dtExcel.Rows[i][1].ToString();
34 string state = dtExcel.Rows[i][4].ToString();
35 string deptname = dtExcel.Rows[i][5].ToString();
36 string FaceNo = dtExcel.Rows[i][7].ToString();
37 if (DeviceID=="")
38 {
39 IsTrue = false;
40 SetInfo("第"+(i+1)+"行的设备编号为空了!");
41 }
42 else if (Convert.ToDecimal(dtExcel.Rows[i][3].ToString())<0)
43 {
44 IsTrue = false;
45 SetInfo("第" + (i + 1) + "行的资产净值不能小于0 !");
46 }
47 else if (deptname=="")
48 {
49 IsTrue = false;
50 SetInfo("第" + (i + 1) + "行的使用部门不能为空 !");
51 }
52 else if (FaceNo=="")
53 {
54 daInfo.FaceID = 0;
55 }
56 else if (state != "待用" && state != "在用" && state != "在修" && state != "报废")//判断设备状态:0待用;1在用;2在修;3报废;
57 {
58 IsTrue = false;
59 SetInfo("您输入的第" + (i + 1) + "行的使用状态不正确!");
60 }
61 else
62 {
63 IsTrue = VerifyInfo(DeviceID, UserMonth, deptname, FaceNo);
64 Istrue = VerifyTime(state, dtExcel.Rows[i][9].ToString(), dtExcel.Rows[i][10].ToString());
65 }
66
67 }//end of for
68
69 if (IsTrue && Istrue)//所有数据都正确时,开始添加数据
70 {
71 for (int i = 0; i < dtExcel.Rows.Count; i++)
72 {
73 daInfo.DeviceID = dtExcel.Rows[i][0].ToString();
74 daInfo.UseMonth = int.Parse(dtExcel.Rows[i][1].ToString());
75 daInfo.AccuAmort = Convert.ToDecimal(dtExcel.Rows[i][2].ToString());
76 daInfo.NetWorth = Convert.ToDecimal(dtExcel.Rows[i][3].ToString());
77
78 switch (dtExcel.Rows[i][4].ToString())
79 {
80 case "待用": daInfo.State = 0; break;
81 case "在用": daInfo.State = 1; break;
82 case "在修": daInfo.State = 2; break;
83 case "报废": daInfo.State = 3; break;
84 }
85 string deptname = dtExcel.Rows[i][5].ToString();
86
87 daInfo.DeptID = daDB.GetDeptIDFromDB(deptname);
88
89 daInfo.RepairPlace = dtExcel.Rows[i][6].ToString();
90 string faceNo = dtExcel.Rows[i][7].ToString();
91 if (faceNo == "")
92 {
93 daInfo.FaceID = 0;
94 }
95 else
96 {
97 daInfo.FaceID = daDB.GetFaceIDFromDB(faceNo);
98 }
99
100
101 daInfo.DeviceSource = dtExcel.Rows[i][8].ToString();
102 daInfo.SDate = Convert.ToDateTime(dtExcel.Rows[i][9].ToString());
103 if (dtExcel.Rows[i][10].ToString() == "")
104 {
105 daInfo.EDate = Convert.ToDateTime("1900-01-01");
106 }
107 else
108 {
109 daInfo.EDate = Convert.ToDateTime(dtExcel.Rows[i][10].ToString());
110 }
111 daInfo.Remark1 = dtExcel.Rows[i][11].ToString();
112 daInfo.Guid = Guid.NewGuid().ToString();
113
114 IsSucc = daDB.AddDynamicAccountInfo(daInfo);
115 }
116 File.Delete(_strfilename);//删除服务器的临时文件
117 if (IsSucc)
118 {
119 string tempScript = "<script language='javascript' type='text/javascript'> alert('添加数据成功 !');window.close();</script> ";
120 ClientScript.RegisterStartupScript(this.GetType(), "", tempScript);
121 }
122 else
123 {
124 SetInfo("添加数据失败!");
125 }
126
127 }
128 else
129 {
130 File.Delete(_strfilename);//删除服务器的临时文件
131
132 }
133 }
134 }
135 catch (Exception ex)
136 {
137 SetInfo(ex.Message);
138 }
139
140 }
141
142
daDB.ExeclToDataTable(_strfilename, "Sheet1");
1 public DataTable ExeclToDataTable(string strfilename, string strSheetName)
2 {
3 string strconn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strfilename + ";" + "Extended Properties=Excel 5.0;";
4
5 string strExcel = string.Format("select * from [{0}$]", strSheetName);
6 DataSet ds = new DataSet();
7
8 using (OleDbConnection conn = new OleDbConnection(strconn))
9 {
10 conn.Open();
11 OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strconn);
12 adapter.Fill(ds, strSheetName);
13 conn.Close();
14 }
15
16 return ds.Tables[strSheetName];
17 }
daDB.AddDynamicAccountInfo(daInfo);2 {
3 string strconn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strfilename + ";" + "Extended Properties=Excel 5.0;";
4
5 string strExcel = string.Format("select * from [{0}$]", strSheetName);
6 DataSet ds = new DataSet();
7
8 using (OleDbConnection conn = new OleDbConnection(strconn))
9 {
10 conn.Open();
11 OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strconn);
12 adapter.Fill(ds, strSheetName);
13 conn.Close();
14 }
15
16 return ds.Tables[strSheetName];
17 }
是一个插入语句。