C# 导入excel数据到数据库

 

上传文件

 1         [HttpPost]
 2         public Result Import()
 3         {
 4             var vRequest = HttpContext.Current.Request;
 5             if (vRequest.Files.Count < 1)
 6             {
 7                 throw new BusinessException("必须上传文件!");
 8             }
 9             var file = vRequest.Files[0];
10             //获取文件路径
11             string filePath = vRequest.Files[0].FileName;
12             //获取文件名
13             string fileName = Path.GetFileNameWithoutExtension(vRequest.Files[0].FileName);
14             //获取文件扩展名
15             string fileExtName = filePath.Substring(filePath.LastIndexOf(".") + 1);
16 
17             if (fileExtName.ToUpper() == "XLS" || fileExtName.ToUpper() == "XLSX")
18             {
19                 //上传文件到临时文件夹
20                 var vTemp = Path.Combine(vRequest.MapPath("~/Temp/ESFCJXX"));
21                 var vTempFile = Path.Combine(vRequest.MapPath("~/Temp/ESFCJXX"), string.Format("{0}", Guid.NewGuid().ToString()) + "." + fileExtName);
22                 if (!Directory.Exists(vTemp))
23                     Directory.CreateDirectory(vTemp);
24                 //保存文件
25                 file.SaveAs(vTempFile);
26                 //提取、保存数据
27                 mSPFSJGLService.DoImport(vTempFile);
28             }
29             else
30             {
31                 throw new BusinessException("必须上传excel文件!");
32             }
33             return JsonResult.OK("上传成功！");
34         }

解析excel

 1  public static List<T> Import<T>(string pTempPath)
 2         {
 3             var list = new List<T>();
 4             DataTable dt = new DataTable();
 5             IWorkbook workbook;
 6             string fileExt = Path.GetExtension(pTempPath).ToLower();
 7             try
 8             {
 9                 using (FileStream fs = new FileStream(pTempPath, FileMode.Open, FileAccess.Read))
10                 {
11                     if (fileExt == ".xlsx")
12                     {
13                         workbook = new XSSFWorkbook(fs);
14                     }
15                     else if (fileExt == ".xls")
16                     {
17                         workbook = new HSSFWorkbook(fs);
18                     }
19                     else
20                     {
21                         workbook = null;
22                     }
23                     if (workbook == null)
24                     {
25                         return null;
26                     }
27                     ISheet sheet = workbook.GetSheetAt(0);
28                    
29                     IRow header = sheet.GetRow(sheet.FirstRowNum);
30 
31                     
32                     var plist = new List<PropertyInfo>(typeof(T).GetProperties());
33 
34                   
35                     for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
36                     {
37                         T s = Activator.CreateInstance<T>();
38                         for(int j=0;j<header.LastCellNum;j++)
39                         {
40                             if (header.GetCell(j)!=null) {
41                                 PropertyInfo info = plist.Find(p => p.Name.ToUpper() == header.GetCell(j).ToString().Trim().ToUpper());
42                                 if (info != null)
43                                 {
44                                     object v = sheet.GetRow(i).GetCell(j);
45                                     if (v != null && v.ToString() != string.Empty)
46                                     {
47                                         if (info.PropertyType.ToString().Contains("System.Nullable"))
48                                         {
49                                             v = Convert.ChangeType(v.ToString(), Nullable.GetUnderlyingType(info.PropertyType));
50                                         }
51                                         else
52                                         {
53                                             v = Convert.ChangeType(v.ToString(), info.PropertyType);
54                                         }
55                                         info.SetValue(s, v, null);
56                                     }
57                                 }
58                             }
59                             
60                         }
61                         if (s != null) {
62                             list.Add(s);
63                         }
64                       
65                     }
66                 }
67             }
68             catch (Exception ex)
69             {
70                 throw new BusinessException("上传文件格式不正确！");
71             }
72 
73             return list;
74         }

保存数据

 public void Add(List<ESFCJXXInfo> pESFCJXXList)
        {
            try
            {
                foreach (ESFCJXXInfo ESFCJXX in pESFCJXXList)
                {
                    ESFCJXX.LRSJ = Convert.ToDateTime(DateTime.Now.ToShortDateString());
                    ESFCJXX.SJZT = ((int)SJZTEnum.无效).ToString();
                    mPersistance.Insert<ESFCJXXInfo>(ESFCJXX);
                   
                }
            }
            catch (Exception ex)
            {
                throw new BusinessException("提交文件格式错误！");
            }
        }

 

posted @ 2020-11-17 17:06  时年天真  阅读(414)  评论(0)    收藏  举报