bootstraptable导出excel独立使用_使用DotNetCore.NPOI写通用的导出导入excel

前言

最近做项目使用上.Net Core，而且又要写一个excel的导出导入功能，以前我曾经写两篇关于http://asp.net上使用NPOI做导出导入功能文章--传送阵。
因为现在使用的框架是.Net core,因此以前引用的dll都要升级，以及写法上也有点点不同，所以就重新再写一文记录。

首先都是引入dll,到Nuget那里搜索DotNetCore.NPOI,然后下载安装即可。

导出篇

先引入这四个命名空间

using NPOI.HSSF.UserModel; 
using NPOI.XSSF.UserModel; 
using NPOI.SS.UserModel; 
using NPOI.HSSF.Util; 

接下就是正式的代码导入代码

public byte[] ExportToExcel<T>(List<T> entities, ExportModel model)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFPalette palette = workbook.GetCustomPalette();
            HSSFColor hssFColor;
            byte red, green, bule;

            if (model.DataFields == null || model.DataFields.Length == 0)
            {
                model.DataFields = model.ColumnNames;
            }

            #region 标题

            // 标题字体
            IFont titleFont = workbook.CreateFont();
            var titleColor = model.TitleRow.CellStyle.Font.Color;
            red = titleColor[0];
            green = titleColor[1];
            bule = titleColor[2];
            palette.SetColorAtIndex(8, red, green, bule);
            hssFColor = palette.FindColor(red, green, bule);
            titleFont.Color = hssFColor.Indexed;
            titleFont.FontHeightInPoints = model.TitleRow.CellStyle.Font.FontHeightInPoints;

            // 标题前景色
            var titleForegroundColor = model.TitleRow.CellStyle.FillForegroundColor;
            red = titleForegroundColor[0];
            green = titleForegroundColor[1];
            bule = titleForegroundColor[2];
            palette.SetColorAtIndex(9, red, green, bule);
            hssFColor = palette.FindColor(red, green, bule);

            // 标题
            ICellStyle titleStyle = workbook.CreateCellStyle();
            titleStyle.SetFont(titleFont);
            titleStyle.FillPattern = FillPattern.SolidForeground;
            titleStyle.FillForegroundColor = hssFColor.Indexed;
            titleStyle.Alignment = HorizontalAlignment.Center;
            titleStyle.VerticalAlignment = VerticalAlignment.Center;

            ISheet sheet = workbook.CreateSheet("Sheet1");
            IRow row = sheet.CreateRow(0);
            row.HeightInPoints = model.DataRow.HeightInPoints;
            ICell cell = null;
            for (int i = 0; i < model.ColumnNames.Length; i++)
            {
                cell = row.CreateCell(i);
                cell.CellStyle = titleStyle;
                cell.SetCellValue(model.ColumnNames[i]);
            }

            #endregion

            if (entities.Count > 0)
            {
                // 数据行
                object cellValue = string.Empty;
                ICellStyle cellStyle = workbook.CreateCellStyle();
                IFont cellFont = workbook.CreateFont();
                cellFont.FontHeightInPoints = model.DataRow.CellStyle.Font.FontHeightInPoints;
                cellStyle.SetFont(cellFont);
                cellStyle.VerticalAlignment = VerticalAlignment.Center;
                for (int i = 0; i < entities.Count; i++)
                {
                    row = sheet.CreateRow(i + 1);
                    row.HeightInPoints = model.DataRow.HeightInPoints;
                    object entity = entities[i];
                    for (int j = 0; j < model.DataFields.Length; j++)
                    {
                        cellValue = entity.GetType().GetProperty(model.DataFields[j]).GetValue(entity);
                        cell = row.CreateCell(j);
                        cell.CellStyle = cellStyle;
                        cell.SetCellValue(Convert.ToString(cellValue));
                    }
                }

                // 调整列宽
                for (int i = 0; i <= entities.Count; i++)
                {
                    sheet.AutoSizeColumn(i);
                }
                for (int columnNum = 0; columnNum <= model.ColumnNames.Length; columnNum++)
                {
                    int columnWidth = sheet.GetColumnWidth(columnNum) / 256;
                    for (int rowNum = 1; rowNum <= sheet.LastRowNum; rowNum++)
                    {
                        IRow currentRow;
                        if (sheet.GetRow(rowNum) == null)
                        {
                            currentRow = sheet.CreateRow(rowNum);
                        }
                        else
                        {
                            currentRow = sheet.GetRow(rowNum);
                        }

                        if (currentRow.GetCell(columnNum) != null)
                        {
                            ICell currentCell = currentRow.GetCell(columnNum);
                            int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;
                            if (columnWidth < length)
                            {
                                columnWidth = length;
                            }
                        }
                    }
                    columnWidth = Math.Min(columnWidth, 255);
                    sheet.SetColumnWidth(columnNum, columnWidth * 256);
                }
            }

            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                return ms.GetBuffer();
            }
        }

这里需要注意的是，我使用的是对象是HSSFWorkbook，因此生成的文档是03版excel即xls类型的文档。如果想生成07版的excel即xlsx类型的文档，则需要使用XSSFWorkbook对象。其他的写法都基本一致。
还有一点就是ExportModel这个对象属于我自己封装的，是一些关于生成excel文档的一些基本配置，后面我给出这个对象代码属性。

调用

 public IActionResult ExportToExcel()
        {
            IExcelService _IExcelService = new ExcelService();
            //var result = _IPostingStatusService.GetExcelData(model).ToList();
            List<string> result = new List<string>();
            ExportModel excelModel = new ExportModel();
            excelModel.DataFields = new string[] { "Department", "JobTitle", "Folio" };
            excelModel.ColumnNames = new string[] { "Department", "Job Title", "Request No." };

            var buffer = _IExcelService.ExportToExcel(result, excelModel);

            return File(buffer, MediaTypeNames.Application.Octet, "111.xls");
        }

ExportMode

 public class ExportModel
    {
        public string[] DataFields { get; set; }

        public string[] ColumnNames { get; set; }

        public RowModel TitleRow { get; set; } = new RowModel()
        {
            HeightInPoints = 22,
            CellStyle = new CellStyleModel
            {
                FillForegroundColor = new byte[] { 0, 74, 134 },
                Font = new FontModel
                {
                    FontHeightInPoints = 12,
                    Color = new byte[] { 255, 255, 255 }
                }
            }
        };

        public RowModel DataRow { get; set; } = new RowModel()
        {
            HeightInPoints = 22,
            CellStyle = new CellStyleModel
            {
                FillForegroundColor = new byte[] { 255, 255, 255 },
                Font = new FontModel
                {
                    FontHeightInPoints = 10,
                    Color = new byte[] { 0, 0, 0 }
                }
            }
        };
    }

public class RowModel
    {
        public CellStyleModel CellStyle { get; set; }

        public float HeightInPoints { get; set; }
    }

 public class CellStyleModel
    {
        public byte[] FillForegroundColor { get; set; }

        public FontModel Font { get; set; }
    }

public class FontModel
    {
        public short FontHeightInPoints { get; set; }

        /// <summary>
        /// R,G,B
        /// </summary>
        public byte[] Color { get; set; }
    }

导入篇

直接上代码

public List<T> ExcelToList<T>(Stream stream, string fileName) where T : class, new()
        {
            IWorkbook workbook = null;
            string _ext = fileName.Substring(fileName.LastIndexOf("."), fileName.Length - fileName.LastIndexOf("."));
            if (_ext == ".xlsx")
            {
                workbook = new XSSFWorkbook(stream);
            }
            else
            {
                workbook = new HSSFWorkbook(stream);
            }

            ISheet sheet = workbook.GetSheetAt(0);
            IRow ITitleRow = sheet.GetRow(0);
            int totalColumn = ITitleRow.LastCellNum;
            int totalRow = sheet.LastRowNum;

            Dictionary<string, int> dic = new Dictionary<string, int>();
            var properties = typeof(T).GetProperties();
            for (int i = 0, len = properties.Length; i < len; i++)
            {
                object[] _attributes = properties[i].GetCustomAttributes(typeof(DescriptionAttribute), false);
                if (_attributes.Length == 0)
                {
                    continue;
                }
                string _description = ((DescriptionAttribute)_attributes[0]).Description;
                if (!string.IsNullOrWhiteSpace(_description))
                {
                    dic.Add(_description, i);
                }
            }

            string _value = string.Empty;
            string _type = string.Empty;
            int index = 0;
            List<T> list = new List<T>();
            for (int i = 1; i <= totalRow; i++)
            {
                IRow row = sheet.GetRow(i);
                if (row == null)
                {
                    continue;
                }
                var obj = new T();
                for (int j = 0; j < totalColumn; j++)
                {
                    if (dic.TryGetValue(ITitleRow.GetCell(j).ToString(), out index) && row.GetCell(j) != null)
                    {
                        _type = (properties[index].PropertyType).FullName;
                        _value = row.GetCell(j).ToString();
                        if (_type == "System.String")
                        {
                            properties[index].SetValue(obj, _value, null);
                        }
                        else if (_type == "System.DateTime")
                        {
                            DateTime pdt = Convert.ToDateTime(_value, CultureInfo.InvariantCulture);
                            properties[index].SetValue(obj, pdt, null);
                        }
                        else if (_type == "System.Boolean")
                        {
                            bool pb = Convert.ToBoolean(_value);
                            properties[index].SetValue(obj, pb, null);
                        }
                        else if (_type == "System.Int16")
                        {
                            short pi16 = Convert.ToInt16(_value);
                            properties[index].SetValue(obj, pi16, null);
                        }
                        else if (_type == "System.Int32")
                        {
                            int pi32 = Convert.ToInt32(_value);
                            properties[index].SetValue(obj, pi32, null);
                        }
                        else if (_type == "System.Int64")
                        {
                            long pi64 = Convert.ToInt64(_value);
                            properties[index].SetValue(obj, pi64, null);
                        }
                        else if (_type == "System.Byte")
                        {
                            byte pb = Convert.ToByte(_value);
                            properties[index].SetValue(obj, pb, null);
                        }
                        else
                        {
                            properties[index].SetValue(obj, null, null);
                        }
                    }
                }
                list.Add(obj);
            }
            return list;
        }

调用

 public IActionResult LoadExcel(IFormFile file)
        {
            IExcelService _IExcelService = new ExcelService();
            try
            {
                var rows = _IExcelService.ExcelToList<ImportExcelModel>(file.OpenReadStream(), file.FileName);


                return Content("导入成功！");
            }
            catch (Exception e) { return Content("导入失败"); }
        }

说明：ImportExcelModel其实也就是自己建的一个保存数据对象，这个对象可以跟你的表对应，这样导入的数据就能与表关联起来再保存到数据库。

最后

上面的代码就是.NetCore使用NPOI做的导出导入功能的通用写法，是可以直接复制到自己代码，稍微更改一下就能直接使用，希望对大家有所帮助。