1 using (FileStream fs = File.OpenRead(@"D:\成绩管理\名单.xls"))
2 {
3 IWorkbook wk = new HSSFWorkbook(fs);//根据文件流创建workbook
4 if (wk.NumberOfSheets > 0)
5 {
6 ISheet sheet = wk.GetSheetAt(0);//创建工作表
7 //学号 姓名 性别 出生年月 专业
8 for (int r = 1; r <= sheet.LastRowNum; r++)//r=1，从第1行开始读取，而不是第0行，第0行是列名
9 {
10 #region 获取excel数据
11 IRow row = sheet.GetRow(r);
12 int stuid;
13 if (row.GetCell(0).CellType==CellType.BLANK)//主键stuid不允许空，若为空则跳过该行数据
14 {
15 continue;
16 }
17 else
18 {
19 stuid = (int)row.GetCell(0).NumericCellValue;
20 }
21 string name = row.GetCell(1) == null ? null : row.GetCell(1).StringCellValue;
22 string gender = row.GetCell(2)==null ? null: row.GetCell(2).StringCellValue;
23 string birth = row.GetCell(3) == null? null : row.GetCell(3).StringCellValue;
24 string specialty = row.GetCell(4)==null ? null : row.GetCell(4).StringCellValue;
25 #endregion
26
27 string sql = "insert into ceshi values(@stuid,@name,@birth,@gender,@specialty)";
28 SqlParameter[] para = new SqlParameter[]{
29 new SqlParameter("@stuid",stuid),
30 //空值在C#中是null，但通过sql语句向数据库中插入null值应为DBNull.Value
31 new SqlParameter("@name",name==null?DBNull.Value:(object)name),
32 new SqlParameter("@birth",birth==null?DBNull.Value:(object)birth),
33 new SqlParameter("@gender",gender==null?DBNull.Value:(object)gender),
34 new SqlParameter("@specialty",specialty==null?DBNull.Value:(object)specialty),
35 };
36 using (cmd = new SqlCommand(sql, GetConn()))
37 {
38 if (para!=null)
39 {
40 cmd.Parameters.AddRange(para);
41 }
42 cmd.ExecuteNonQuery();
43 }
44 }
45 MessageBox.Show("导入成功");
46 }
47 }
