C# EPPlus 导出Excel

• 什么是EPPlus？

    Epplus是一个使用Open Office XML文件格式，能读写Excel的第三方插件。

• 怎么导出Excel？ 

      基于.NET平台使用Epplus组件操作xlsx我们需要添加EPPlus命名空间。如图所示：

  ExcelPackage库 为epplus的核心。

 导出EXcel分为含模板的导出和不含模板的导出：

• 不含模板的导出：

   private static MemoryStream ExportStream(IEnumerable<object> data)
        {
            MemoryStream ms = new MemoryStream();
            using (ExcelPackage package = new ExcelPackage())
            {
                ExcelWorksheet sheet = package.Workbook.Worksheets.Add("sheet1");
                var properties = data.First().GetType().GetProperties();

                for (int c = 0; c < properties.Count(); c++)
                {
                    int r = 0;
                    foreach (var item in data)
                    {
                        var prop = properties[c];
                        if (r == 0)
                            sheet.Cells[1, c + 1].Value = prop.Name;
                        sheet.Cells[r + 2, c + 1].Value = item.GetType().GetProperty(prop.Name).GetValue(item, null);
                        r++;
                    }
                }
                package.SaveAs(ms);
                ms.Seek(0, SeekOrigin.Begin);
            }
            return ms;
        }

• 含模板的导出：

 private static MemoryStream ExportStream(string path, Action<ExcelCells> action)
        {
            MemoryStream ms = new MemoryStream();
            using (ExcelPackage package = new ExcelPackage(new System.IO.FileInfo(path)))
            {
                ExcelWorksheet sheet = package.Workbook.Worksheets.First();
                ExcelCells cells = new ExcelCells(sheet.Cells);
                action(cells);
                package.SaveAs(ms);
                ms.Seek(0, SeekOrigin.Begin);
            }
            return ms;
        }

ExcelCells 类：

  public class ExcelCells
    {
        private ExcelRange Range { get; set; }
        internal ExcelCells(ExcelRange Range) { this.Range = Range; }
        private int Row { get; set; }
        private int Col { get; set; }
        public object Value
        {
            set
            {
                Range[Row, Col].Value = value;
            }
        }
        public ExcelCells this[int Row, int Col]
        {
            get
            {
                this.Col = Col; this.Row = Row;
                return this;
            }
        }
    }

含模板的导出需要给出模板地址。方法中的path。

博主这边是封装成了方法，调用了封装好的方法。调用代码：

 return Units.ExcelHelper.Export(Server.MapPath(@"~\Asstes\ExportTemp\LogPartOut.xlsx"), c =>
            {
                int index = 3;
                foreach (var item in listdata)
                {
                    c[index, 1].Value = item.PartNo;
                    c[index, 2].Value = item.PartDescription;
                    c[index, 3].Value = item.CustomerPartNo;
                    c[index, 4].Value = item.OutCount;
                    c[index, 5].Value = item.OutTime;
                    index++;
                }
            });

  public static System.Web.Mvc.FileStreamResult Export(string path, Action<ExcelCells> action)
        {
            return new System.Web.Mvc.FileStreamResult(ExportStream(path, action), "application/vnd.ms-excel") { FileDownloadName = "workbook.xlsx" };
        }

export的返回值为FileStreamResult。

博主就介绍到这里，有什么不懂的地方，欢迎留言。