上传读取Excel文件数据

Bertha 。 2021-11-27 06:46 408阅读 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         }

转载于:https://www.cnblogs.com/CielWater/p/3580475.html