NPOI2.0导出excel之添加样式、边框和表头

 //优化后导出excel
        public System.IO.Stream ExcelStream(string search) //
        {
            var orderBusiniss = Containers.ObjectContainer.CreateObject<IOrderBusiness>();
            var list = orderBusiniss.GetExcel(search);

            NPOI.HSSF.UserModel.HSSFWorkbook hssfworkbook = new NPOI.HSSF.UserModel.HSSFWorkbook();

            NPOI.SS.UserModel.ISheet sheet1 = hssfworkbook.CreateSheet("生产计划");

            //添加标题头
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 13));
            NPOI.SS.UserModel.IRow rowTitle = sheet1.CreateRow(0);
            rowTitle.Height = 30 * 20;//设置行高
            var cellTitel = rowTitle.CreateCell(0);

            //给标题头添加格式
            NPOI.SS.UserModel.ICellStyle styleTitle = hssfworkbook.CreateCellStyle();
            styleTitle.Alignment = HorizontalAlignment.Center;//单元格水平居中
            IFont fontTitle = hssfworkbook.CreateFont();//新建一个字体样式对象
            fontTitle.Boldweight = short.MaxValue;//设置字体加粗样式
            fontTitle.FontHeightInPoints = 16;//设置字体大小
            styleTitle.SetFont(fontTitle);//使用SetFont方法将字体样式添加到单元格样式中
            cellTitel.CellStyle = styleTitle;

            cellTitel.SetCellValue(ApplicationContext.Current.CompanyName + "公司生产计划单");


            //通过反射得到需要导出的属性
            Type orderExcel = typeof(OrderToExcel);
            PropertyInfo[] ps = orderExcel.GetProperties();
            string[] title = new string[] { 
                "序号",
                "生产时间" ,
                "订单号",
                "业务员",
                "工程名称",
                "建筑单位",
                "施工部位",
                "产品信息",
                "特殊要求",
                "方量（M3）",
                "下单时间",
                "联系人",
                "联系电话",
                "备注",
            };
            //添加行头
            NPOI.SS.UserModel.IRow rowHeader = sheet1.CreateRow(1);

            //公共样式：加边框
            NPOI.SS.UserModel.ICellStyle style = hssfworkbook.CreateCellStyle();
            style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            style.Alignment = HorizontalAlignment.Center;

            NPOI.SS.UserModel.ICellStyle styleHeader = hssfworkbook.CreateCellStyle();
            styleHeader.CloneStyleFrom(style);//克隆公共的样式
            styleHeader.Alignment = HorizontalAlignment.Center;//单元格水平居中
            IFont fontHeader = hssfworkbook.CreateFont();//新建一个字体样式对象
            fontHeader.Boldweight = short.MaxValue;//设置字体加粗样式
            styleHeader.SetFont(fontHeader);//使用SetFont方法将字体样式添加到单元格样式中


            for (int i = 0; i < ps.Length + 1; i++)
            {
                var cellHeard = rowHeader.CreateCell(i);
                cellHeard.CellStyle = styleHeader;
                cellHeard.SetCellValue(title[i]);
            }

            if (list != null)
            {
                //通过序列号，得到需要导出的字段
                List<OrderToExcel> listExcel = list.ToObjectList<Order, OrderToExcel>();
                //保存列集合的属性信息数组
                PropertyInfo[] oProps = null;
                for (int i = 0; i < listExcel.Count; i++)
                {
                    NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 2);

                    var cellNum = rowtemp.CreateCell(0, CellType.Numeric);
                    cellNum.CellStyle = style;
                    cellNum.SetCellValue(i + 1);
                    oProps = ((Type)listExcel[i].GetType()).GetProperties();
                    for (int j = 0; j < oProps.Length; j++)
                    {
                        Type colType = oProps[j].PropertyType;//得到属性的类型
                        var value = oProps[j].GetValue(listExcel[i]);//得到属性的值
                        var cell = rowtemp.CreateCell(j + 1);
                        cell.CellStyle = style;
                        cell.SetCellValue(value == null ? "" : value.ToString());
                    }
                }

            }

            //自动调节行宽度
            for (int i = 0; i < 15; i++)
                sheet1.AutoSizeColumn(i);

            System.IO.MemoryStream file = new System.IO.MemoryStream();
            hssfworkbook.Write(file);
            file.Seek(0, System.IO.SeekOrigin.Begin);

            return file;
        }

        public class OrderToExcel
        {
            public Nullable<System.DateTime> ProductionTime { get; set; }
            public string Code { get; set; }
            public string BSalesman { get; set; }
            public string ProjectName { get; set; }
            public string ConstructionUnit { get; set; }
            public string ConstructionSite { get; set; }
            public string ProductName { get; set; }
            public string WP { get; set; }
            public Nullable<decimal> Number { get; set; }
            public Nullable<System.DateTime> CreationTime { get; set; }
            public string ContactPerson { get; set; }
            public string ContactPhone { get; set; }
            public string Remarks { get; set; }
        }

　　

之前做了导出excel功能，没有样式比较难看。最近优化了下，NPOI版本为最新版本2.0.4.1版本

优化添加：

1.添加了标题。

2.通过对象的属性来导出excel，表头添加也比较容易点。

 

转载于:https://www.cnblogs.com/zhuyapeng/p/5008816.html