上传读取Excel文件数据-蒲公英云

上传读取Excel文件数据

朱雀 2021-12-01 15:38 488阅读 0赞

1         /// <summary>
 2         /// 上传读取Excel文件数据
 3         /// 来自http://www.cnblogs.com/cielwater
 4         /// </summary>
 5         /// <param name="form"></param>
 6         /// <returns></returns>
 7         public ActionResult AreaExcelFile(FormCollection form)
 8         {
 9             HttpPostedFileBase fileField = Request.Files["fileField"];
10             string path = Server.MapPath("~/Excel");
11             if (!Directory.Exists(path))
12             {
13                 Directory.CreateDirectory(path);
14             }
15             string flieName = fileField.FileName;
16             string fileExt = Path.GetExtension(flieName).ToLower().Substring(1);
17             //验证是否为Excel文件
18             if (fileExt != "xls" && fileExt != "xlsx")
19             {
20                 ModelState.AddModelError("file", "您选择的不是Excel文件");
21                 return View("ExcelFile");
22             }
23             string FileName = path + flieName.Substring(flieName.LastIndexOf("\\"));
24             fileField.SaveAs(FileName);
25             //读取excel文件，转换成dataset
26             string strConn = "";
27             if (fileExt == "xls")
28             {
29                 strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties=Excel 8.0;";
30             }
31             else if (fileExt == "xlsx")
32             {
33                 strConn = "Provider= Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
34             }
35             DataSet ds = new DataSet();
36             using (OleDbConnection conn = new OleDbConnection(strConn))
37             {
38                 conn.Open();
39                 //获取Excel表结构
40                 System.Data.DataTable sTable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
41                 conn.Dispose();
42                 //遍历Excel文件所有表
43                 foreach (DataRow ExcelTable in sTable.Rows)
44                 {
45                     //读取表
46                     OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + ExcelTable[2] + "]", strConn);
47                     //写入DataSet
48                     oada.Fill(ds);
49                     //后面直接处理ds中的数据则可以
50                 }
51             }
52         }