.NET Core使用EPPlus简单操作Excel(简单实现导入导出)

1.前言

  EPPlus是一个使用Open Office XML(xlsx)文件格式,能读写Excel 2007/2010 文件的开源组件,在导出Excel的时候不需要电脑上安装office,它的一个缺点就是不支持导出2003版的Excel(xls)。

2.数据导出

    在此之前,先引入nuget包:EPPlus.Core   

 1        [HttpGet]
 2         public IActionResult Export()
 3         {
 4             string sWebRootFolder = hostingEnv.WebRootPath;
 5             string sFileName = $@"qmhuangtext{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx";
 6             var path = Path.Combine(sWebRootFolder, sFileName);
 7             FileInfo file = new FileInfo(path);
 8             //构建数据
 9             List<Person> list1 = new List<Person>()
10                 {
11                     new Person{Name = "123",Sex=""},
12                     new Person{Name = "234",Sex=""},
13                     new Person{Name = "345",Sex=""}
14                 };
15             if (file.Exists)
16             {
17                 file.Delete();
18                 file = new FileInfo(path);
19             }
20             using (ExcelPackage package = new ExcelPackage(file))
21             {
22                  //创建sheet
23                     ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(“sheetname”);
25                     worksheet.Cells.LoadFromCollection(list1 ,true);
26                     package.Save(); //Save the workbook.
27             }
28             return File(new FileStream(Path.Combine(sWebRootFolder, sFileName), FileMode.Open), "application/octet-stream", $"excel导出测试{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx");
29         }

     在导出时有很多种数据Resource可以选择,比如上面的 LoadFromCollection ,还有 LoadFromDataTable  ,  LoadFromText 等方法

  worksheet.Cells.LoadFromCollection(list1 ,true);   //此方法第二个参数决定是否打印表头,第一行标题栏

2.数据导入

 1         /// <summary>
 2         /// 读取sheet 内的数据进入实体
 3         /// </summary>
 4         /// <param name="worksheet"></param>
 5         /// <returns></returns>
 6         public List<Person> GetSheetValues(string filepath)
 7         {
 8             FileInfo file = new FileInfo(filepath);
 9             if (file != null)
10             {
11                 using (ExcelPackage package = new ExcelPackage(file))
12                 {
13 
14                     ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
15                     //获取表格的列数和行数
16                     int rowCount = worksheet.Dimension.Rows;
17                     int ColCount = worksheet.Dimension.Columns;
18                     var persons = new List<Person>();
19                     for (int row = 1; row <= rowCount; row++)
20                     {
21                         Person person = new Person();
22                         person.Name = worksheet.Cells[row, 1].Value.ToString();
23                         person.Sex = worksheet.Cells[row, 2].Value.ToString();
24                         persons.Add(person);
25                     }
26                     return persons;
27                 }
28             }
29              return null;
30         }

注:你会发现上面在取数据时,遍历worksheet时坐标是从(1,1)开始的。epplus的顶点就是这个位置,如果你试图读取0位置,则会报错

posted @ 2018-01-17 21:40 Degalo 阅读(...) 评论(...) 编辑 收藏