net core webAPI NOPI 导出Excel封装

使用方法分三步

1.定义导出的实体类模板

2.将需要导入Excel的字段打上 【DisplayName】特性

3.调用方法

注意：有个巨坑，如果你用的swagger调来调去，文件都打不开，不是损坏就是各种理由不给你打开，那么恭喜你，你已入坑，文件下载，常规做法：不能使用AJAX请求，得使用浏览器输入地址，全局下载

封装函数1

优势：

1.适配所有导出模板

2.只需定义好类，打上DisplayName即可完成导入数据

使用技术：泛型，反射，NOPI

        public static MemoryStream ListToExcel<T>(List<T> data)
        {
            // 2007版本
            string sheetName = "sheet";
            bool isColumnWritten = true;
            IWorkbook workbook = new XSSFWorkbook();
            try
            {
                var sheet = workbook.CreateSheet(sheetName);

                ICellStyle s = workbook.CreateCellStyle();
                s.FillForegroundColor = HSSFColor.BlueGrey.Index;
                s.FillPattern = FillPattern.SolidForeground;



                var count = 0;
                var list = new List<string>();
                //标题
                PropertyInfo[] properties = typeof(T).GetProperties();
                if (isColumnWritten)
                {
                    var row = sheet.CreateRow(0);
                    for (int j = 0; j < properties.Count(); j++)
                    {
                        var item = properties[j];
                        var attrs = item.GetCustomAttributes(typeof(DisplayNameAttribute), true);
                        if (attrs != null && attrs.Count() > 0)
                        {
                            var displayName = ((DisplayNameAttribute)attrs[0]).DisplayName;
                            row.CreateCell(list.Count()).SetCellValue(displayName);
                            row.GetCell(list.Count()).CellStyle = s;
                            list.Add(item.Name);
                        }
                    }
                    count = 1;
                }
                else
                {
                    count = 0;
                }
                if (data.Count > 0)
                {
                    //数据
                    for (var i = 0; i < data.Count; ++i)
                    {
                        var itemData = data[i];
                        var row = sheet.CreateRow(count);
                        for (int iCell = 0; iCell < list.Count; iCell++)
                        {
                            var p = list[iCell];
                            var Properties = itemData.GetType().GetProperties().Where(c => c.Name == p).FirstOrDefault();
                            var value = Properties.GetValue(itemData)?.ToString();
                            row.CreateCell(iCell).SetCellValue(value);
                        }
                        ++count;
                    }
                }
                else
                {
                    var row = sheet.CreateRow(count);
                    for (int iCell = 0; iCell < list.Count; iCell++)
                        row.CreateCell(iCell).SetCellValue("");
                }


                for (int columnNum = 0; columnNum <= list.Count; columnNum++)
                {
                    int columnWidth = sheet.GetColumnWidth(columnNum) / 256;//获取当前列宽度
                    for (int rowNum = 0; rowNum <= sheet.LastRowNum; rowNum++)//在这一列上循环行
                    {
                        IRow currentRow = sheet.GetRow(rowNum);
                        ICell currentCell = currentRow.GetCell(columnNum);
                        if (currentCell != null && !currentCell.ToString().IsNullOrEmpty())
                        {
                            int length = Encoding.UTF8.GetBytes(currentCell.ToString()).Length;//获取当前单元格的内容宽度
                            if (columnWidth < length + 1)
                            {
                                columnWidth = length + 1;
                            }//若当前单元格内容宽度大于列宽，则调整列宽为当前单元格宽度，后面的+1是我人为的将宽度增加一个字符
                        }

                    }
                    sheet.SetColumnWidth(columnNum, columnWidth * 256);
                }

                //写入到excel
                var ms = new NpoiMemoryStream();
                ms.AllowClose = false;
                workbook.Write(ms);
                ms.Flush();
                ms.Seek(0, SeekOrigin.Begin);
                ms.AllowClose = true;
                return ms;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.Message);
                return null;
            }
        }

使用教学

1.写好类

    public class A
    {
        /// <summary>
        /// 这个字段会导入Excel中
        /// </summary>
        [DisplayName("我是A.a")]
        public int a { get; set; }
        public int b { get; set; }
        public int c { get; set; }
    }


      

2.调用

net Core下的使用
var list = new List<A>();
var ms = ExcelHelper.ListToExcel<A>(list);
例子：
var ms = ExcelHelper.ListToExcel<CommDataRecordsV2Dto>(result);
return File(ms, "application/vnd.ms-excel", "order.xlsx");


//net Framework下的使用
var ms = Utility.ListToExcel<DeviceSituationExcel>(list);
result.Content = new StreamContent(ms);
result.Content.Headers.ContentType = new MediaTypeHeaderValue("application/vnd.ms-excel");
result.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment");
result.Content.Headers.ContentDisposition.FileName = "设备运行统计" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx";

3.打开你的Excel 

各位大佬，纯粹自己封装，方便下次使用，不喜勿喷哈哈哈！！！