NPOI 实体列表导出为Excel

最新推荐文章于 2025-06-27 13:14:08 发布

wzt2017l

最新推荐文章于 2025-06-27 13:14:08 发布

阅读量718

点赞数

CC 4.0 BY-SA版权

分类专栏： .net

本文链接：https://blog.youkuaiyun.com/wzt2017l/article/details/79064775

.net 专栏收录该内容

5 篇文章

订阅专栏

本文介绍了一种将泛型列表转换为Excel文件的方法，包括如何设置样式、标题、列宽等，并通过反射来填充数据。适用于.NET环境中快速导出数据到Excel的需求。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

#region List<T>转EXCEL
        /// <summary>
        /// 实体列表转EXCEL
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="EntityList">实体列表</param>
        /// <param name="Titles">标题</param>
        /// <param name="widths">列宽</param>
        /// <param name="sheetName">工作簿名</param>
        /// <param name="book">EXCEL</param>
        /// <returns></returns>
        public XSSFWorkbook EntityToExcel<T>(IEnumerable<T> entityIEnumerable, List<string> Titles,int[] widths,string sheetName,XSSFWorkbook book)
        {
            try
            {
                List<T> entityList= IEnumerableToList<T>(entityIEnumerable);
                ISheet sheet = book.CreateSheet(sheetName);//创建工作簿
                //设置样式                                          //样式
                IFont font = book.CreateFont();
                font.FontName = "微软雅黑";//设置字体
                font.FontHeightInPoints = 10;

                ICellStyle style = book.CreateCellStyle();
                style.SetFont(font);

                ICellStyle styleDouble = book.CreateCellStyle();
                styleDouble.SetFont(font);
                IDataFormat formatDouble = book.CreateDataFormat();
                styleDouble.DataFormat = formatDouble.GetFormat("#,##0.00");

                ICellStyle styleInt = book.CreateCellStyle();
                styleInt.SetFont(font);
                IDataFormat formatInt = book.CreateDataFormat();
                styleInt.DataFormat = formatInt.GetFormat("#,##0");

                ICellStyle stylePercent = book.CreateCellStyle();
                stylePercent.SetFont(font);
                IDataFormat formatPercent = book.CreateDataFormat();
                stylePercent.DataFormat = formatPercent.GetFormat("0.00%");

                IRow titlerow = sheet.CreateRow(0);//标题行
                for (int i = 0; i <Titles.Count; i++)
                {
                    sheet.SetColumnWidth(i, widths[i] * 256);//设置列宽
                    ICell titleCell= titlerow.CreateCell(i);
                    titleCell.SetCellValue(Titles[i]);
                    titleCell.CellStyle = style;
                }
                //反射获取属性信息
                PropertyInfo[] PInfos = null;
                if (entityList.Count>0)
                {
                    PInfos = entityList[0].GetType().GetProperties();//属性
                }

                //内容行
                for (int i = 0; i < entityList.Count; i++)
                {
                    IRow itemRow = sheet.CreateRow(i + 1);//内容行
                    T entity = entityList[i];
                    //细胞
                    for (int j = 0; j < PInfos.Length; j++)//遍历列信息
                    {
                        ICell cellBody = itemRow.CreateCell(j);//创建第i+1行第j列表格
                        PropertyInfo pi = PInfos[j];//当前属性
                        try
                        {
                            if (string.IsNullOrEmpty(pi.GetValue(entity, null).ToString()))//属性值为空
                            {

                                cellBody.SetCellValue(pi.GetValue(entity, null).ToString());
                                continue;
                            }
                        }
                        catch
                        {
                            continue;
                        }

                        switch (pi.PropertyType.ToString())//如果不为空或null
                        {
                            case "System.Int64":
                            case "System.Int32":
                                var vInt = Int64.Parse(pi.GetValue(entity, null).ToString());
                                cellBody.SetCellValue(vInt);
                                if (vInt==0)
                                {
                                    cellBody.CellStyle = style;
                                }
                                else
                                {
                                    cellBody.CellStyle = styleInt;
                                }
                                break;

                            case "System.Double":
                                var vDouble = double.Parse(pi.GetValue(entity, null).ToString());
                                cellBody.SetCellValue(vDouble);//添加数值
                                if (vDouble==0)
                                {
                                    cellBody.CellStyle = style;
                                }
                                else
                                {
                                    cellBody.CellStyle = stylePercent;
                                }
                                break;

                            case "System.Decimal":
                                var vDecimal = double.Parse(pi.GetValue(entity, null).ToString());
                                cellBody.SetCellValue(vDecimal);
                                if (vDecimal==0)
                                {
                                    cellBody.CellStyle = style;
                                }
                                else
                                {
                                    cellBody.CellStyle = styleDouble;
                                }
                                break;

                            case "System.DateTime":
                                var vDate = string.Format("{0:yyyy-MM-dd}", pi.GetValue(entity, null).ToString());
                                cellBody.SetCellValue(vDate);
                                cellBody.CellStyle = style;
                                break;

                            default:
                                string str = pi.GetValue(entity, null).ToString().Replace("<br>", "\n");
                                cellBody.SetCellValue(str);
                                if (str.Contains("\n"))
                                {
                                    style.WrapText = true;
                                    itemRow.GetCell(j).CellStyle = style;
                                }
                                break;
                        }
                    }
                }

            }
            catch
            {
                return book;
            }
            return book;//返回 EXCEL表

        }
        #region 接口转换
        public List<T> IEnumerableToList<T>(IEnumerable<T> ts)
        {
            List<T> tsl = new List<T>();
            foreach (T t in ts)
            {
                tsl.Add(t);
            }
            return tsl;
        }
        #endregion