ASP.NET Core使用EPPlus

原创

用户3829742

修改于 2021-04-22 17:56:33

1.5K0

修改于 2021-04-22 17:56:33

文章被收录于专栏：.NET Core 使用

引言

ASP.NET Core使用EPPlus导出Excel在封装之前，我们需要做一些处理，怎么去根据一个对象(类)获取到其属性和属性的值，提前弄清楚这个东东的话，对我们后续的代码就会好写很多了！因为EPPlus导出Excel是要行列对应的，不能每导出一个列表，我们都要手动去控制这个对象所需多少行，多少列吧，明显太费事也不爱用。所以我们需要进行简单处理下：

一、获取对象(类)的属性和值

1.1 创建一个简单对象

    public class Product
    {
        /// <summary>
        /// 商品编号
        /// </summary>
        public string ProductNo { get; set; }

        /// <summary>
        /// 商品名称
        /// </summary>
        public string ProductName { get; set; }

        /// <summary>
        /// 供应商
        /// </summary>
        public string Provider { get; set; }

        /// <summary>
        /// 价格
        /// </summary>
        public decimal Price { get; set; }
    }

1.2 获取对象(类)属性和值

    class Program
    {
        static void Main(string[] args)
        {
            var product = new Product()
            {
                ProductNo="BT01258",
                ProductName="肥皂",
                Provider="深圳",
                Price=Convert.ToDecimal(3.98)
            };
            PropertyInfo[] properties = product.GetType().GetProperties();
            foreach (var item in properties)
            {
                //属性值
                var value = product.GetType().GetProperty(item.Name).GetValue(product);

                Console.WriteLine(item.Name+" : "+value);
            }
            Console.ReadKey();
        }
    }

二、使用EPPlus导出

2.1 NuGet : EPPlus

2.2 封装EPPlus

public class ExcelHelper<T> where T:class,new()
    {
        /// <summary>
        /// 获取类的属性值
        /// </summary>
        /// <param name="obj">类</param>
        /// <param name="property">属性</param>
        /// <returns></returns>
        private static object GetPropertyValue(object obj, string property)
        {
            return obj.GetType().GetProperty(property).GetValue(obj);
        }

        /// <summary>
        /// 获取类的全部属性
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        private static PropertyInfo[] GetProperties(T t)
        {
            PropertyInfo[] properties = t.GetType().GetProperties();
            return properties;
        }

        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <param name="fileInfo">FileInfo</param>
        /// <param name="tList">数据</param>
        /// <returns></returns>
        public static async Task OutPutExcel(FileInfo fileInfo, List<T> tList)
        {
            //指定EPPlus使用非商业化许可证
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            using (ExcelPackage package = new ExcelPackage(fileInfo))
            {      
                //工作簿
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

                //实体属性
                PropertyInfo[] properties = GetProperties(new T());

                //填充表头
                for (int i = 1; i < properties.Length+1; i++) 
                {
                    worksheet.Cells[1, i].Value = properties[i - 1].Name;
                }

                //填充行(从第二行开始)
                for (int i = 2; i < tList.Count + 2; i++)
                {
                    //填充行内列
                    for (int j = 1; j < properties.Length + 1; j++)
                    {
                        var property = properties[j - 1].Name;
                        worksheet.Cells[i, j].Value = GetPropertyValue(tList[i - 2], property);
                    }
                }

                //列宽自适应
                worksheet.Cells.AutoFitColumns();

                //保存
                await package.SaveAsync();
            }
        }       
    }

2.3 Web层调用导出

        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <returns></returns>
        [HttpGet("out")]
        public async Task<FileResult> OutPut()
        {
            string filePath = Directory.GetCurrentDirectory() + "/excels";
            if (!Directory.Exists(filePath))
            {
                Directory.CreateDirectory(filePath);
            }
            string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
            FileInfo fileInfo = new FileInfo(Path.Combine(filePath, fileName));

            var product = new Product()
            {
                ProductNo = "BT01258",
                ProductName = "肥皂",
                Provider = "深圳",
                Price = Convert.ToDecimal(3.98)
            };
            List<Product> list = new List<Product>();
            list.Add(product);
            
            await ExcelHelper<Product>.OutPutExcel(fileInfo, list);
            
            var stream = System.IO.File.OpenRead(Path.Combine(filePath, fileName));
            var provider = new FileExtensionContentTypeProvider();
            string contentType = provider.Mappings[Path.GetExtension(fileName)];

            return File(stream, contentType, fileName);
        }