c# npoi excel 导出

ly188:二话不说上代码

 

    /// <summary>
    /// 描 述：NPOI Excel 操作类
    /// </summary>
    public static class ExcelExport
    {/// <summary>
        /// 根据list导出（表头默认为第一行）
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entitys"></param>
        /// <returns></returns>
        public static byte[] OutputExcel<T>(this List<T> entitys)
            where T : class, new()
        {
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("sheet");
            byte[] buffer = new byte[1024 * 2];

            //// 处理表头,表头默认为第一行
            var headProObj = new List<PropertyInfo>();
            Type entityType = typeof(T);
            PropertyInfo[] entityProperties = entityType.GetProperties();
            IRow titleRow = sheet.CreateRow(0);
            int headCellNum = 0;
            foreach (PropertyInfo property in entityProperties)
            {
                var displayName = property.GetCustomAttribute<DisplayNameAttribute>()?.DisplayName;
                if (!string.IsNullOrWhiteSpace(displayName))
                {
                    titleRow.CreateCell(headCellNum).SetCellValue(displayName);
                    headProObj.Add(property);
                    headCellNum++;
                }
            }

            //// 填充导入数据
            if (entitys.IsNotNullOrEmptyList())
            {
                for (int i = 0; i < entitys.Count; i++)
                {
                    //// excle内容填充
                    IRow contentRow = sheet.CreateRow(i + 1);
                    var entity = entitys[i];
                    int contentCellNum = 0;
                    foreach (var propertyInfo in headProObj)
                    {
                        var sourceData = propertyInfo.GetValue(entity, null)?.ToString();
                        var contentCellRow = contentRow.CreateCell(contentCellNum);
                        //// 给单元格赋值对应格式的数值（方便excle的操作）
                        fillData(contentCellRow, propertyInfo, sourceData);
                        contentCellNum++;
                    }
                }
            }
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                buffer = ms.ToArray();
                ms.Close();
            }

            return buffer;
        }

        /// <summary>
        /// 给单元格赋值对应格式的数值（方便excle的操作）
        /// </summary>
        /// <param name="cell">单元格</param>
        /// <param name="propertyInfo">数据源的属性</param>
        /// <param name="sourceData">值</param>
        private static void fillData(ICell cell, PropertyInfo propertyInfo, string sourceData)
        {
            var infoProTyeo = propertyInfo.PropertyType;
            if (infoProTyeo == typeof(long))
            {
                var value = long.Parse(sourceData);
                cell.SetCellValue(value);
            }
            else if (infoProTyeo == typeof(int))
            {
                var value = int.Parse(sourceData);
                cell.SetCellValue(value);
            }
            else if (infoProTyeo == typeof(decimal) || infoProTyeo == typeof(double))
            {
                var value = double.Parse(sourceData);
                cell.SetCellValue(value);
            }
            else if (infoProTyeo == typeof(DateTime))
            {
                var value = DateTime.Parse(sourceData);
                cell.SetCellValue(value);
            }
            else
            {
                cell.SetCellValue(sourceData);
            }

        }
    }

 

 

 

        /// <summary>
        ///  xxx
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        [HttpGet]
        [Route("downloadInfo")]
        [CreateBy("Conlin.Lin")]
        [AllowAnonymous]
        public async Task<IActionResult> downloadInfo()
        {
            var list = new List<bb>()
            {
                new bb{ name="张三",age=18},
                new bb{ name="李四",age=19},
                new bb{ name="王五",age=20},
            };


            byte[] buffer = list.OutputExcel();

            return File(buffer, "application/ms-excel", string.Format("测试_{0}.xlsx", DateTime.Now.Ticks));
        }

        public class bb
        {
            [DisplayName("姓名")]
            public string name { get; set; }

            [DisplayName("年龄")]
            public int age { get; set; }
        }