.NET WEBAPI 导出EXCEL

 /// <summary>
    /// Excel帮助类
    /// </summary>
    public class ExcelHelper
    {
        /// <summary>
        /// 生成Excel  已知类型
        /// </summary>
        /// <typeparam name="T">数据库查询实体得类型</typeparam>
        /// <param name="sheetName">sheet名</param>
        /// <param name="lists">数据源</param>
        public static string GenerateExcel<T>(List<T> lists) where T : class
        {
            //如果当前得泛型集合为0 说明数据库中没有数据 直接返回

            //获取随机文件名
            string fileName = $"{Path.GetRandomFileName()}.xlsx";
            var path = $@"{AppDomain.CurrentDomain.BaseDirectory}TempExcel\{fileName}";
            //MiniExcel.SaveAs(path, lists);

            if (lists.Count == 0)
            {
                MiniExcel.SaveAs(path, null, true, "data", ExcelType.XLSX);
                return path;
            }

            //判断可以写几个工作表 按一个65536行算
            int count = lists.Count;
            var page = Math.Ceiling((double)count / 65536);
            var sheets = new Dictionary<string, object>();

            //生成工作表和数据得字典
            for (int i = 0; i < page; i++)
            {
                var data = lists.GetRange(i * 65535, 65535 - 1 * (i + 1));
                sheets.Add($"sheet{i}", data);
            }



            //循环便利生成字典
            var dictionarys = new List<Dictionary<string, object>>();
            foreach (var item in lists)
            {


                Type type = item.GetType();
                //获取T得所有字段名
                var properties = type.GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);


                var dictionary = new Dictionary<string, object>();
                //循环 properties 拿出他得字段名和value
                foreach (var property in properties)
                {
                    //当前得属性名
                    var name = property.Name;
                    //根据名字获取到当前循环得item(属性名) 得值
                    var vao = property.GetValue(item);

                    dictionary.Add(name, vao);
                }
                dictionarys.Add(dictionary);
            }
            //生成EXCEL
            var config = new OpenXmlConfiguration()
            {
                TableStyles = TableStyles.Default
            };
            MiniExcel.SaveAsAsync(path, sheets, configuration: config);
            return path;





        }

        /// <summary>
        /// 生成备份模板 已知类型
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public static string GenerateExcelTemp<T>() where T : class
        {
            string fileName = $"{Path.GetRandomFileName()}.xlsx";
            var path = $@"{AppDomain.CurrentDomain.BaseDirectory}TempExcel\{fileName}";
            Type type = typeof(T);

            var properties = type.GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);

            var dictionary = new Dictionary<string, object>();
            foreach (var property in properties)
            {
                var name = property.Name;
                dictionary.Add(name, "");

            }
            var dictionarys = new List<Dictionary<string, object>>();
            dictionarys.Add(dictionary);
            MiniExcel.SaveAs(path, dictionarys, true, "sheet1", ExcelType.XLSX);

            return path;
        }

        /// <summary>
        /// 不落地生成Excel模板
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public static MemoryStream GenerateExcelTempStream<T>() where T : class
        {
            Type type = typeof(T);

            var properties = type.GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);

            var dictionary = new Dictionary<string, object>();
            foreach (var property in properties)
            {
                var name = property.Name;
                dictionary.Add(name, "由此往下填充即可");

            }
            var dictionarys = new List<Dictionary<string, object>>();
            dictionarys.Add(dictionary);

            var memoryStream = new MemoryStream();
            memoryStream.SaveAs(dictionarys, sheetName: "temp");
            memoryStream.Seek(0, SeekOrigin.Begin);
            return memoryStream;

        }
        /// <summary>
        /// 不落地生成Excel备份文件
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public static MemoryStream GenerateExcelStream<T>(List<T> lists) where T : class
        {
            var type = typeof(T);
            var properties = type.GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);
            var memoryStream = new MemoryStream();
            if (lists.Count == 0)
            {
                var _dictionarys = new List<Dictionary<string, object>>();
                var dictionary = new Dictionary<string, object>();
                foreach (var property in properties)
                {
                    //当前得属性名
                    var name = property.Name;
                    //根据名字获取到当前循环得item(属性名) 得值


                    dictionary.Add(name, "无数据");
                }
                _dictionarys.Add(dictionary);


                memoryStream.SaveAs(_dictionarys, sheetName: "data0");
                memoryStream.Seek(0, SeekOrigin.Begin);
                return memoryStream;
            }

            //判断可以写几个工作表 按一个65536行算
            int count = lists.Count;
            var page = Math.Ceiling((double)count / 65536);
            var sheets = new Dictionary<string, object>();

            //生成工作表和数据得字典
            for (int i = 0; i < page; i++)
            {
                if (count >= 65535 - 1 * (i + 1))
                {
                    var data = lists.GetRange(i * 65535, 65535 - 1 * (i + 1));
                    sheets.Add($"sheet{i}", data);
                }
                else
                {
                    var data = lists.GetRange(i * 65535, count );
                    sheets.Add($"sheet{i}", data);
                }
                
            }
            //循环便利生成字典
            var dictionarys = new List<Dictionary<string, object>>();
            foreach (var item in lists)
            {

                //获取T得所有字段名

                var dictionary = new Dictionary<string, object>();
                //循环 properties 拿出他得字段名和value
                foreach (var property in properties)
                {
                    //当前得属性名
                    var name = property.Name;
                    //根据名字获取到当前循环得item(属性名) 得值
                    var vao = property.GetValue(item);

                    dictionary.Add(name, vao);
                }
                dictionarys.Add(dictionary);
            }
            //生成EXCEL
            var config = new OpenXmlConfiguration()
            {
                TableStyles = TableStyles.Default
            };
            //MiniExcel.SaveAsAsync(path, sheets, configuration: config);

            memoryStream.SaveAs(sheets, configuration: config);
            memoryStream.Seek(0, SeekOrigin.Begin);
            return memoryStream;
        }
        /// <summary>
        /// 根据type 生成Excel 未知类型
        /// </summary>
        /// <param name="type"></param>
        /// <param name="sheetName"></param>
        /// <param name="list">必须是集合类型</param>
        /// <returns></returns>
        public static string GenerateExcel(Type type, string sheetName, List<object> lists)
        {
            //获取随机文件名
            string fileName = $"{Path.GetRandomFileName()}.xlsx";
            var path = $@"{AppDomain.CurrentDomain.BaseDirectory}TempExcel\{fileName}";
            //MiniExcel.SaveAs(path, lists);

            if (lists.Count < 0)
            {
                MiniExcel.SaveAs(path, null, true, sheetName, ExcelType.XLSX);
                return path;
            }
            //循环便利生成字典
            var dictionarys = new List<Dictionary<string, object>>();
            foreach (var item in lists)
            {
                //获取T得所有字段名
                var properties = type.GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);


                var dictionary = new Dictionary<string, object>();
                //循环 properties 拿出他得字段名和value
                foreach (var property in properties)
                {
                    //当前得属性名
                    var name = property.Name;
                    var typeValue = (dynamic)item;

                    // dynamic.InvokeGet(typeValue, name);
                    var vao = Microsoft.VisualBasic.CompilerServices.Versioned.CallByName(typeValue, name, CallType.Get);

                    //根据名字获取到当前循环得item(属性名) 得值

                    dictionary.Add(name, vao);
                }
                dictionarys.Add(dictionary);
            }
            //生成EXCEL
            MiniExcel.SaveAs(path, dictionarys, true, sheetName, ExcelType.XLSX);
            return path;


        }

        /// <summary>
        /// 根据type 生成Excel 未知类型
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        public static string GenerateExcelTemp(Type type)
        {

            string fileName = $"{Path.GetRandomFileName()}.xlsx";
            var path = $@"{AppDomain.CurrentDomain.BaseDirectory}TempExcel\{fileName}";


            var properties = type.GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);

            var dictionary = new Dictionary<string, object>();
            foreach (var property in properties)
            {
                var name = property.Name;
                dictionary.Add(name, "");

            }
            var dictionarys = new List<Dictionary<string, object>>();
            dictionarys.Add(dictionary);
            MiniExcel.SaveAs(path, dictionarys, true, "sheet1", ExcelType.XLSX);

            return path;
        }

        /// <summary>
        /// 读取excel中得全部数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="path"></param>
        /// <returns></returns>
        public static List<T> QueryExcelData<T>(string path) where T : class
        {
            var rows = MiniExcel.Query(path).ToList();
            var data = rows as List<T>;
            return data;

        }
    }

        /// <summary>
        /// 备份
        /// </summary>
        /// <returns></returns>
        [HttpGet("UserBackUp")]
        public async Task<IActionResult> UserBackUp()
        {
            using (var conn = _connectFactory.GetOpenConn())
            {
                var data = await conn.Connection.SqlQueryable<UsersInfo>($"select * from UsersInfo").ToListAsync();
                
                var result = ExcelHelper.GenerateExcelStream<UsersInfo>(data);
                this.HttpContext.Response.Headers.Add("Content-Length", result.Length.ToString());
                this.HttpContext.Response.Headers.Add("Content-Type", "charset=UTF-8");
                return File(result, "application/octet-stream;charset=UTF-8", Path.GetRandomFileName()+".xlsx");
            }
        }

        /// <summary>
        /// 下载备份模板
        /// </summary>
        /// <returns></returns>
        [HttpGet("DownloadTemp")]
        public IActionResult DownloadTemp()
        {
            var result = ExcelHelper.GenerateExcelTempStream<UsersInfo>();
            //FileStream fs = new FileStream(result, FileMode.Open);
            this.HttpContext.Response.Headers.Add("Content-Length", result.Length.ToString());
            this.HttpContext.Response.Headers.Add("Content-Type", "charset=UTF-8");
            return File(result, "application/octet-stream;charset=UTF-8", Path.GetFileName(Path.GetRandomFileName()+".xlsx"));
        }