Asp.NET 导入 Excel 2007 到 SQL 2005

.

.

.

.

页面代码：

View Code 
 1     /// <summary>
 2     /// 导入按钮单击事件
 3     /// </summary>
 4     /// <param name="sender"></param>
 5     /// <param name="e"></param>
 6     protected void btnImport_Click(object sender, EventArgs e)
 7     {
 8         if (fileImport.FileName == "")
 9         {
10             this.RegisterStartupScript("infA", "<script>alert('UpLoad is failed!');</script>");
11         }
12         else if (!fileImport.HasFile)
13         {
14             this.RegisterStartupScript("infA", "<script>alert('UpLoad is failed!');</script>");
15         }
16         else
17         {
18             string fileName, uploadPath;
19             string fileType;
20 
21             fileName = fileImport.FileName;
22             fileType = Path.GetExtension(fileName);
23             fileName = fileName + DateTime.Now.ToString("yyyyMMddhhmmss") + fileType;
24             uploadPath = System.Web.HttpRuntime.AppDomainAppPath + "UploadFiles\\ExcelData\\" + fileName;
25 
26             string allowFile = ".XLS.XLSX";
27             if (allowFile.Contains(fileType.ToUpper()))
28             {
29                 //上传文件
30                 fileImport.SaveAs(uploadPath);
31                 
32                 // 支持Excel2003 和 Excel2007 的连接字符串
33                 // "HDR=yes;"是说第一行是列名而不是数据，"HDR=No;"正好与前面的相反。
34                 // 如果列中的数据类型不一致，使用"IMEX=1"可必免数据类型冲突。
35                 string strConn = "provider=Microsoft.ACE.OleDb.12.0; Data Source ='" + uploadPath + "';Extended Properties='Excel 12.0;HDR=yes;IMEX=1';";
36                 OleDbConnection conn = new OleDbConnection(strConn);
37                 conn.Open();
38                 DataTable dtSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
39                 List<string> sheetNameList = new List<string>();
40                 //一个EXCEL文件可能有多个工作表，遍历之
41                 foreach (DataRow dr in dtSchema.Rows)
42                 {
43                     //获取所有 sheet 页的名字
44                     sheetNameList.Add(dr["TABLE_NAME"].ToString());
45                 } 
46                 conn.Close();
47 
48                 int sheetIndex = -1;
49                 //查找需要导入的 sheet 页的名字
50                 for (int i = 0; i < sheetNameList.Count; i++)
51                 {
52                     if (sheetNameList[i].ToUpper().Equals("需要导入的 sheet 页的名字"))
53                     {
54                         sheetIndex = i;
55                         break;
56                     }
57                 }
58                 //判断该 sheet 页是否存在
59                 if (sheetIndex < 0)
60                 {
61                     this.RegisterStartupScript("infA", "<script>alert('没有找到需要导入的 sheet 页!');</script>");
62                     return;
63                 }
64 
65                 UploadBL ubl = new UploadBL();
66                 List<int> errList = ubl.importData(conn, sheetNameList[sheetIndex]);
67                 if (errList.Count > 0)
68                 {
69                     //写日志
70                     string logPath = @"ImportLog\Excel_" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".txt";
71                     FileStream fsErrLog = new FileStream(logPath, FileMode.Create);
72                     StreamWriter swImpErrLog = new StreamWriter(fsErrLog, Encoding.Default);
73                     foreach (int errIndex in errList)
74                     {
75                         swImpErrLog.WriteLine("第" + errIndex.ToString() + "行导入时出现错误。");
76                     }
77                     swImpErrLog.WriteLine("其它行已导入。");
78                     swImpErrLog.Close();
79                     fsErrLog.Close();
80 
81                     this.RegisterStartupScript("infA", "<script>alert('导入时出现错误，请查看日志：'" + logPath + ");</script>");
82                 }
83                 else
84                     this.RegisterStartupScript("infA", "<script>alert('Import is successfully!');</script>");
85             }
86             else
87                 this.RegisterStartupScript("infA", "<script>alert('文件上传失败！只能是 XLS 或 XLSX 文件。');</script>");
88         }
89     }

业务逻辑层：

View Code 
  1         /// <summary>
  2         /// 导入数据
  3         /// </summary>
  4         /// <param name="conn"></param>
  5         /// <param name="sheetName">sheet 页的名字</param>
  6         /// <returns>错误的行号，集合.Count 小于 1 则没有错误</returns>
  7         public List<int> importAop(OleDbConnection conn, string sheetName)
  8         {
  9             DataSet ds = new DataSet();
 10             string strExcel = "SELECT * FROM [" + sheetName + "]";
 11             OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel, conn);
 12             myCommand.Fill(ds);
 13             string a0 = "", a1 = "", a2 = "", a3 = "", a4 = "", a5 = "", a6 = "", a7 = "";
 14             double a8 = 0, a9 = 0, a10 = 0, a11 = 0, a12 = 0, a13 = 0, a14 = 0, a15 = 0, a16 = 0, a17 = 0, a18 = 0, a19 = 0, a20 = 0, a21 = 0, a22 = 0, a23 = 0, a24 = 0, a25 = 0, a26 = 0, a27 = 0, a28 = 0, a29 = 0, a30 = 0, a31 = 0, a32 = 0, a33 = 0;
 15             List<int> errLine = new List<int>();
 16             int i = 0;
 17             foreach (DataRow dr in ds.Tables[0].Rows)
 18             {
 19                 #region
 20                 if (dr[0] is DBNull)
 21                     a0 = "";
 22                 else
 23                     a0 = dr[0].ToString();
 24                 if (dr[1] is DBNull)
 25                     a1 = "";
 26                 else
 27                     a1 = dr[1].ToString();
 28                 if (dr[2] is DBNull)
 29                     a2 = "";
 30                 else
 31                     a2 = dr[2].ToString();
 32                 if (dr[3] is DBNull)
 33                     a3 = "";
 34                 else
 35                     a3 = dr[3].ToString();
 36                 if (dr[4] is DBNull)
 37                     a4 = "";
 38                 else
 39                     a4 = dr[4].ToString();
 40                 if (dr[5] is DBNull)
 41                     a5 = "";
 42                 else
 43                     a5 = dr[5].ToString();
 44                 if (dr[6] is DBNull)
 45                     a6 = "";
 46                 else
 47                     a6 = dr[6].ToString();
 48                 if (dr[7] is DBNull)
 49                     a7 = "";
 50                 else
 51                     a7 = dr[7].ToString();
 52                 if (dr[8] is DBNull)
 53                     a8 = 0;
 54                 else
 55                     a8 = Convert.ToDouble(dr[8]);
 56                 if (dr[9] is DBNull)
 57                     a9 = 0;
 58                 else
 59                     a9 = Convert.ToDouble(dr[9]);
 60                 if (dr[10] is DBNull)
 61                     a10 = 0;
 62                 else
 63                     a10 = Convert.ToDouble(dr[10]);
 64                 if (dr[11] is DBNull)
 65                     a11 = 0;
 66                 else
 67                     a11 = Convert.ToDouble(dr[11]);
 68                 if (dr[12] is DBNull)
 69                     a12 = 0;
 70                 else
 71                     a12 = Convert.ToDouble(dr[12]);
 72                 if (dr[13] is DBNull)
 73                     a13 = 0;
 74                 else
 75                     a13 = Convert.ToDouble(dr[13]);
 76                 if (dr[14] is DBNull)
 77                     a14 = 0;
 78                 else
 79                     a14 = Convert.ToDouble(dr[14]);
 80                 if (dr[15] is DBNull)
 81                     a15 = 0;
 82                 else
 83                     a15 = Convert.ToDouble(dr[15]);
 84                 if (dr[16] is DBNull)
 85                     a16 = 0;
 86                 else
 87                     a16 = Convert.ToDouble(dr[16]);
 88                 if (dr[17] is DBNull)
 89                     a17 = 0;
 90                 else
 91                     a17 = Convert.ToDouble(dr[17]);
 92                 if (dr[18] is DBNull)
 93                     a18 = 0;
 94                 else
 95                     a18 = Convert.ToDouble(dr[18]);
 96                 if (dr[19] is DBNull)
 97                     a19 = 0;
 98                 else
 99                     a19 = Convert.ToDouble(dr[19]);
100                 if (dr[20] is DBNull)
101                     a20 = 0;
102                 else
103                     a20 = Convert.ToDouble(dr[20]);
104                 if (dr[21] is DBNull)
105                     a21 = 0;
106                 else
107                     a21 = Convert.ToDouble(dr[21]);
108                 if (dr[22] is DBNull)
109                     a22 = 0;
110                 else
111                     a22 = Convert.ToDouble(dr[22]);
112                 if (dr[23] is DBNull)
113                     a23 = 0;
114                 else
115                     a23 = Convert.ToDouble(dr[23]);
116                 if (dr[24] is DBNull)
117                     a24 = 0;
118                 else
119                     a24 = Convert.ToDouble(dr[24]);
120                 if (dr[25] is DBNull)
121                     a25 = 0;
122                 else
123                     a25 = Convert.ToDouble(dr[25]);
124                 if (dr[26] is DBNull)
125                     a26 = 0;
126                 else
127                     a26 = Convert.ToDouble(dr[26]);
128                 if (dr[27] is DBNull)
129                     a27 = 0;
130                 else
131                     a27 = Convert.ToDouble(dr[27]);
132                 if (dr[28] is DBNull)
133                     a28 = 0;
134                 else
135                     a28 = Convert.ToDouble(dr[28]);
136                 if (dr[29] is DBNull)
137                     a29 = 0;
138                 else
139                     a29 = Convert.ToDouble(dr[29]);
140                 if (dr[30] is DBNull)
141                     a30 = 0;
142                 else
143                     a30 = Convert.ToDouble(dr[30]);
144                 if (dr[31] is DBNull)
145                     a31 = 0;
146                 else
147                     a31 = Convert.ToDouble(dr[31]);
148                 if (dr[32] is DBNull)
149                     a32 = 0;
150                 else
151                     a32 = Convert.ToDouble(dr[32]);
152                 if (dr[33] is DBNull)
153                     a33 = 0;
154                 else
155                     a33 = Convert.ToDouble(dr[33]);
156                 #endregion
157 
158                 if (uda.insertData(a0, a1, a2, a3, a4, a5, a6, a7, a8, a9, a10, a11, a12, a13, a14, a15, a16, a17, a18, a19, a20, a21, a22, a23, a24, a25, a26, a27, a28, a29, a30, a31, a32, a33) <= 0)
159                     errLine.Add(i);
160                 i++;
161             }
162             return errLine;
163         }

数据访问层直接对数据库执行 Insert 即可。


posted on 2011-10-15 17:23  0xCAFEBABE  阅读(429)  评论(0)  编辑  收藏  举报

导航