Net6Demo_Api/Net6Demo_Api.../Helper/NpoiHelper.cs

using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Globalization;
using System.Reflection;

namespace Net6Demo_Api.Util
{
    public class NpoiHelper
    {
        /// <summary>
        /// 导出excel
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="suffix">excel后缀(.xls/.xlsx)</param>
        /// <param name="list">列表</param>
        /// <param name="cols">列属性</param>
        /// <param name="colnames">列属性名</param>
        /// <returns></returns>
        public static byte[] ExportToExcel<T>(string suffix, List<T> list, string[] cols = null, string[] colnames = null)
        {
            IWorkbook workbook = new HSSFWorkbook();
            if (suffix == ".xlsx")
            {
                workbook = new XSSFWorkbook();
            }

            try
            {
                //表头样式
                ICellStyle HeadercellStyle = workbook.CreateCellStyle();
                HeadercellStyle.BorderBottom = BorderStyle.Thin;
                HeadercellStyle.BorderLeft = BorderStyle.Thin;
                HeadercellStyle.BorderRight = BorderStyle.Thin;
                HeadercellStyle.BorderTop = BorderStyle.Thin;
                HeadercellStyle.Alignment = HorizontalAlignment.Center;
                HeadercellStyle.FillForegroundColor = HSSFColor.SkyBlue.Index;
                HeadercellStyle.FillPattern = FillPattern.SolidForeground;
                HeadercellStyle.FillBackgroundColor = HSSFColor.SkyBlue.Index;
                //字体
                IFont headerfont = workbook.CreateFont();
                headerfont.Boldweight = (short)FontBoldWeight.Bold;
                headerfont.FontHeightInPoints = 12;
                HeadercellStyle.SetFont(headerfont);

                int iRowIndex = 0;
                ISheet sheet = workbook.CreateSheet();
                IRow headerRow = sheet.CreateRow(0);

                var cls = typeof(T).GetProperties();

                if (colnames == null || (colnames != null && colnames.Length == 0))
                {
                    if (cols == null || (cols != null && cols.Length == 0))
                    {
                        colnames = cls.Select(p => p.Name).ToArray();
                    }
                    else
                    {
                        colnames = cols;
                    }
                }
                for (int c = 0; c < colnames.Length; c++)
                {
                    ICell cell = headerRow.CreateCell(c);
                    cell.SetCellValue(colnames[c]);
                    cell.CellStyle = HeadercellStyle;
                }

                if (list.Count > 0)
                {
                    //内容行样式
                    ICellStyle cellStyle = workbook.CreateCellStyle();
                    //为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
                    cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
                    cellStyle.BorderBottom = BorderStyle.Thin;
                    cellStyle.BorderLeft = BorderStyle.Thin;
                    cellStyle.BorderRight = BorderStyle.Thin;
                    cellStyle.BorderTop = BorderStyle.Thin;
                    //字体
                    IFont cellfont = workbook.CreateFont();
                    cellfont.Boldweight = (short)FontBoldWeight.Normal;
                    cellStyle.SetFont(cellfont);

                    for (int i = 0; i < list.Count; i++)
                    {
                        if (suffix != ".xlsx")//excel2003超过60000进行分sheet
                        {
                            if (i % 60000 == 0 && i > 0)
                            {
                                //if (i > 0)
                                //{
                                //    //自适应列宽度
                                //    for (int j = 0; j < icolIndex; j++)
                                //    {
                                //        sheet.AutoSizeColumn(j);
                                //    }
                                //}

                                iRowIndex = 0;

                                sheet = workbook.CreateSheet();
                                headerRow = sheet.CreateRow(0);

                                for (int c = 0; c < colnames.Length; c++)
                                {
                                    ICell cell = headerRow.CreateCell(c);
                                    cell.SetCellValue(colnames[c]);
                                    cell.CellStyle = HeadercellStyle;
                                }
                            }
                        }

                        IRow irow = sheet.CreateRow(iRowIndex + 1);
                        for (int j = 0; j < colnames.Length; j++)
                        {
                            var clsi = cls[j];
                            string strsj = string.Empty;
                            if (cols != null && cols.Length > 0)
                            {
                                clsi = cls.Where(p => p.Name == cols[j]).FirstOrDefault();
                            }

                            strsj = clsi.GetValue(list[i], null)?.ToString() ?? "";
                            ICell cell = irow.CreateCell(j);
                            cell.SetCellValue(strsj);
                            cell.CellStyle = cellStyle;
                        }
                        iRowIndex++;
                    }
                }

                byte[] data = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    workbook.Write(ms);
                    data = ms.GetBuffer();
                    ms.Close();
                }

                return data;
            }
            catch (Exception ex)
            {
                LogHelper.Error("导出失败！", ex);
                return null;
            }
        }

        /// <summary>
        /// 导入excel转换为list
        /// </summary>
        /// <param name="stream">文件流</param>
        /// <param name="suffix">后缀名</param>
        /// <param name="headrow">标题行数（默认0代表第一行）</param>
        /// <returns></returns>
        public static List<T> ExcelToList<T>(Stream stream, string suffix, int headrow=0) where T : new()
        {
            List<T> list = new List<T>();

            IWorkbook workbook = null;

            if (suffix == ".xlsx") // 2007版本
            {
                workbook = new XSSFWorkbook(stream);
            }
            else if (suffix == ".xls") // 2003版本
            {
                workbook = new HSSFWorkbook(stream);
            }

            //获取excel的第一个sheet
            ISheet sheet = workbook.GetSheetAt(0);

            //获取sheet的第一行
            IRow headerRow = sheet.GetRow(headrow);

            //一行最后一个方格的编号 即总的列数
            int cellCount = headerRow.LastCellNum;
            //最后一列的标号  即总的行数
            int rowCount = sheet.LastRowNum;
            //获取泛型对象T的所有属性
            var cls = typeof(T).GetProperties();
            for (int i = (sheet.FirstRowNum + headrow + 1); i <= sheet.LastRowNum; i++)
            {
                var obj = new T();

                IRow row = sheet.GetRow(i);
                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    if (row.GetCell(j) != null)
                    {
                        var value = row.GetCell(j).ToString();
                        string str = cls[j].PropertyType.FullName ?? "";
                        if (str.Contains("System.String") )
                        {
                            cls[j].SetValue(obj, value, null);
                        }
                        else if (str.Contains("System.DateTime"))
                        {
                            DateTime pdt = Convert.ToDateTime(value, CultureInfo.InvariantCulture);
                            cls[j].SetValue(obj, pdt, null);
                        }
                        else if (str.Contains("System.Boolean"))
                        {
                            bool pb = Convert.ToBoolean(value);
                            cls[j].SetValue(obj, pb, null);
                        }
                        else if (str.Contains("System.Int16"))
                        {
                            short pi16 = Convert.ToInt16(value);
                            cls[j].SetValue(obj, pi16, null);
                        }
                        else if (str.Contains("System.Int32"))
                        {
                            int pi32 = Convert.ToInt32(value);
                            cls[j].SetValue(obj, pi32, null);
                        }
                        else if (str.Contains("System.Int64"))
                        {
                            long pi64 = Convert.ToInt64(value);
                            cls[j].SetValue(obj, pi64, null);
                        }
                        else if (str.Contains("System.Byte"))
                        {
                            byte pb = Convert.ToByte(value);
                            cls[j].SetValue(obj, pb, null);
                        }
                        else
                        {
                            cls[j].SetValue(obj, null, null);
                        }
                    }
                }

                list.Add(obj);
            }

            sheet = null;
            workbook = null;

            return list;
        }
    }
}