Net Core 3.0 EPPlus对Excel导入导出

1:EPPlus.core 弃用的情况下(EPPlus.core 不支持net Core3.0)可使用 EPPlus 包   【因文件超10M,在最下面提供百度网盘地址】

                   

2:EPPlus 引用方式

      A)工具---->NuGet 包管理器--->管理解决方案的NuGet程序包--->搜索安装

      B) 工具---->NuGet 包管理器--->程序包管理器控制台--->Install-Package EPPlus

            

 3:导出 

  a:工具方法

 1         /// <summary>
 2         /// 导出
 3         /// </summary>
 4         /// <param name="ePPlus"></param>
 5         /// <returns></returns>
 6         public static MemoryStream Export(ICollection<EPPlus> ePPlus)
 7         {
 8             MemoryStream stream = new MemoryStream();
 9             ExcelPackage package = new ExcelPackage(stream);
10 
11             package.Workbook.Worksheets.Add("EPPlus");
12             ExcelWorksheet sheet = package.Workbook.Worksheets[0];
13 
14             #region write header
15             sheet.Cells[1, 1].Value = "用户名";
16             sheet.Cells[1, 2].Value = "年龄";
17             sheet.Cells[1, 3].Value = "性别";
18             sheet.Cells[1, 4].Value = "成绩";
19 
20             using (ExcelRange range = sheet.Cells[1, 1, 1, 4])
21             {
22                 range.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
23                 range.Style.Fill.BackgroundColor.SetColor(Color.Gray);
24                 range.Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thick;
25                 range.Style.Border.Bottom.Color.SetColor(Color.Black);
26                 range.AutoFitColumns(4);
27             }
28             #endregion
29 
30             #region write content
31             int pos = 2;
32             foreach (EPPlus s in ePPlus)
33             {
34                 sheet.Cells[pos, 1].Value = s.Name;
35                 sheet.Cells[pos, 2].Value = s.Age;
36                 sheet.Cells[pos, 3].Value = s.Gender;
37                 sheet.Cells[pos, 4].Value = s.Achievement;
38 
39                 using (ExcelRange range = sheet.Cells[pos, 1, pos, 4])
40                 {
41                     range.Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
42                     range.Style.Border.Bottom.Color.SetColor(Color.Black);
43                     range.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left;
44                 }
45 
46                 pos++;
47             }
48             #endregion
49 
50             package.Save();
51 
52             return stream;
53         }
View Code

  b:Controller

 1 private const string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
 2 private const string fileDownloadName = "EPPlusDemo.xlsx";
 3  
 4 /// <summary>
 5 /// 导出
 6 /// </summary>
 7 /// <returns></returns>
 8 public FileResult Export()
 9 {
10    MemoryStream stream = EPPlusTool.Export(EPPlusTool.EPPlus);
11    return File(stream.ToArray(), contentType, fileDownloadName);
12 }
View Code

4:导入以及下载Excel

   a:工具方法

 1         /// <summary>
 2         /// 导入
 3         /// </summary>
 4         /// <param name="package"></param>
 5         /// <param name="worksheet"></param>
 6         /// <returns></returns>
 7         public static ICollection<EPPlus> Import(ExcelPackage package, ExcelWorksheet worksheet)
 8         {
 9             ICollection<EPPlus> ePPlus = new List<EPPlus>();
10 
11             var rowCount = worksheet.Dimension?.Rows;
12             var colCount = worksheet.Dimension?.Columns;
13             #region check excel format
14             if (!rowCount.HasValue || !colCount.HasValue)
15             {
16                 return ePPlus;
17                 //return "文档表头或内容为空,请下载对应文档!";
18             }
19             if(!worksheet.Cells[1,1].Value.Equals("Name") ||
20                 !worksheet.Cells[1, 2].Value.Equals("Age") ||
21                 !worksheet.Cells[1, 3].Value.Equals("Gender") ||
22                 !worksheet.Cells[1, 4].Value.Equals("Achievement") ||
23                 !worksheet.Cells[2, 1].Value.Equals("用户名") ||
24                 !worksheet.Cells[2, 2].Value.Equals("年龄") ||
25                 !worksheet.Cells[2, 3].Value.Equals("性别") ||
26                 !worksheet.Cells[2, 4].Value.Equals("成绩"))
27             {
28                 return ePPlus;
29                 //return "文档表头或内容不对应,请下载对应文档!";
30             }
31             #endregion
32             #region  read datas  若读取数据库,可写事务拼接sql执行,前端重新加载页面即可
33             for(int i=3; i<= rowCount; i++)
34             {
35                 ePPlus.Add(new EPPlus
36                 {
37                     Name = worksheet.Cells[i, 1].Value.ToString(),
38                     Age = Convert.ToInt32(worksheet.Cells[i, 2].Value.ToString()),
39                     Gender = (Gender)Enum.Parse(typeof(Gender), worksheet.Cells[i, 3].Value.ToString()),
40                     Achievement = Convert.ToDouble(worksheet.Cells[i,4].Value.ToString())
41                 }) ;
42             }
43             #endregion
44             return ePPlus;
45         }
View Code

  b:Controller

 1         public async Task<IActionResult>  Import(IFormFile file)
 2         {
 3             if (file == null || file.Length==0)
 4             {
 5                 ViewBag.Message = "The document is empty, please select a file!";
 6             }
 7             else
 8             {
 9                 using(var memoryStream=new MemoryStream())
10                 {
11                     await file.CopyToAsync(memoryStream).ConfigureAwait(false);
12                     using(var package=new ExcelPackage(memoryStream))
13                     {
14                         var worksheet = package.Workbook.Worksheets[0]; // Tip: To access the first worksheet, try index 1, not 0
15                         ICollection<EPPlus> ePPlus = EPPlusTool.Import(package, worksheet);
16                         EPPlusTool.EPPlus = ePPlus;
17                     }
18                 }
19             }
20 
21             ViewBag.EPPlus = EPPlusTool.EPPlus;
22             return View("Index");
23         }
View Code

5:cshtml页面代码

 1 @{
 2     Layout = null;
 3 }
 4 
 5 <!DOCTYPE html>
 6 
 7 <html>
 8 <head>
 9     <meta name="viewport" content="width=device-width" />
10     <title>Index</title>
11     <script src="~/lib/bootstrap/dist/js/bootstrap.js"></script>
12     <link href="~/lib/bootstrap/dist/css/bootstrap.css" rel="stylesheet" />
13     <script src="~/lib/jquery/dist/jquery.min.js"></script>
14     <script src="~/lib/bootstrap/dist/js/bootstrap.min.js"></script>
15     <link href="~/lib/bootstrap/dist/css/bootstrap-reboot.min.css" />
16 </head>
17 <body>
18     <div style="width:80%;margin:0 auto;margin-top:50px;">
19         @Html.ActionLink("导出", "Export", "EPPlus", null, new { @class = "btn btn-primary", @style = "margin-left:86%" })
20         <button type="button" class="btn btn-success" data-toggle="modal" data-target="#myModal">导入</button>
21     </div>
22     <table class="table table-bordered" style="width:80%;margin:0 auto;margin-top:20px">
23         <thead>
24             <tr>
25                 <td>用户名</td>
26                 <td>年龄</td>
27                 <td>性别</td>
28                 <td>成绩</td>
29             </tr>
30         </thead>
31         <tbody>
32             @foreach (EPPlus ePPlus in ViewBag.EPPlus)
33             {
34                 <tr>
35                     <td>@ePPlus.Name</td>
36                     <td>@ePPlus.Age</td>
37                     <td>@ePPlus.Gender</td>
38                     <td>@ePPlus.Achievement</td>
39                 </tr>
40             }
41         </tbody>
42     </table>
43 
44     @*模态框*@
45     <form method="post" asp-action="Import" asp-controller="EPPlus" enctype="multipart/form-data">
46         <div class="modal fade" id="myModal" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">
47             <div class="modal-dialog">
48                 <div class="modal-content" style="height: 100%;width: 450px;margin-top: 20px;">
49                     <div class="modal-header">
50                         <button type="button" class="close" data-dismiss="modal"
51                                 aria-hidden="true">
52                             ×
53                         </button>
54                     </div>
55                     <table class="table table-borderless" style="width:100%">
56                         <tr>
57                             <td><a href="~/EPPlus/EPPlusDemo.xlsx">下载模板</a></td>
58                         </tr>
59                         <tr>
60                             <td>上传</td>
61                         </tr>
62                         <tr>
63                             <td><input type="file" name="file" /> <span style="color:red;">@ViewBag.Message</span></td>
64                         </tr>
65 
66                     </table>
67 
68                     <div style="margin-top:30px;margin-bottom:10px;margin-left:150px">
69                         <button type="submit" class="btn btn-success" style="margin-right:5px" data-dismiss="none" id="btu-Ses">导入</button>
70                         <button type="button" class="btn btn-light" data-dismiss="modal">返回</button>
71 
72                     </div>
73                 </div>
74             </div>
75         </div>
76     </form>
77     <script>
78         //function Chuan() {
79         //    $.ajax({
80         //        url: "/EPPlus/Import",
81         //        type: "post",
82         //        data: {
83         //            c: $(".file")
84         //        },
85         //        success: function (data) {
86         //            $("#btu-Ses").attr("data-dismiss", "modal")
87         //        },
88         //        error: function () {
89         //            alert("不执行");
90         //        }
91         //    })
92         //}
93     </script>
94 </body>
95 </html>
View Code

总结:

链接:https://pan.baidu.com/s/1VuGl26ugdPrYmEnB4bdtuQ 
提取码:77dm

由于本人为菜鸟一枚,有更有效的完指导,望提醒,谢谢。

posted @ 2020-01-10 12:51  Team_Leading  阅读(973)  评论(0编辑  收藏  举报