NPOI导出Excel封装

直接上代码

    public class ExcelUtils
    {
        public static ICellStyle CreateStyle(IWorkbook workbook,
            string fontName = "宋体",
            int fontSize = 10,
            bool isBold = false,
            HorizontalAlignment horizontalAlignment = HorizontalAlignment.Left,
            VerticalAlignment verticalAlignment = VerticalAlignment.Center,
            bool wrapText = false,
            BorderStyle left = BorderStyle.Thin,
            BorderStyle right = BorderStyle.Thin,
            BorderStyle top = BorderStyle.Thin,
            BorderStyle bottom = BorderStyle.Thin
            )
        {
            IFont font = workbook.CreateFont();
            font.FontName = fontName;
            font.FontHeightInPoints = (short)fontSize;
            font.IsBold = isBold;
            ICellStyle style = workbook.CreateCellStyle();
            style.SetFont(font);
            style.Alignment = horizontalAlignment;
            style.VerticalAlignment = verticalAlignment;
            style.WrapText = wrapText;
            style.BorderLeft = left;
            style.BorderRight = right;
            style.BorderTop = top;
            style.BorderBottom = bottom;
            return style;
        }
    }

    public class ExcelRow
    {
        /// <summary>
        /// 
        /// </summary>
        public ISheet Sheet { get; set; }
        /// <summary>
        /// 
        /// </summary>
        public IRow Row { get; set; }
        /// <summary>
        /// 
        /// </summary>
        public int RowIndex { get; private set; }

        public int ColumnIndex { get; set; }

        public const int HeightConstant = 20;

        public ExcelRow(ISheet sheet, int height = 20)
        {
            if (sheet.PhysicalNumberOfRows == 0)
            {
                this.RowIndex = 0;
            }
            else
            {
                this.RowIndex = sheet.PhysicalNumberOfRows ;
            }
            this.ColumnIndex = 0;
            this.Sheet = sheet;
            this.Row = this.Sheet.CreateRow(this.RowIndex);
            this.Row.Height = (short)(HeightConstant * height);
        }


    }

    public class ExcelCell
    {
        /// <summary>
        /// 
        /// </summary>
        public ExcelRow Row { get; set; }
        /// <summary>
        /// 
        /// </summary>
        public ICell Cell { get; set; }
        /// <summary>
        /// 
        /// </summary>
        public int ColumnIndex { get; set; }

        public const int WidthConstant = 256;
        public ExcelCell(ExcelRow row, ICellStyle style, int width = 10)
        {
            this.Row = row;
            this.ColumnIndex = this.Row.ColumnIndex++;

            this.Cell = this.Row.Row.CreateCell(this.ColumnIndex, CellType.Blank);
            this.Row.Sheet.SetColumnWidth(this.ColumnIndex, width * WidthConstant);
            this.Cell.CellStyle = style;

        }


        public ExcelCell SetValue(string value)
        {
            this.Cell.SetCellValue(value);
            this.Cell.SetCellType(CellType.String);
            return this;
        }

        public ExcelCell SetValue(bool value)
        {
            this.Cell.SetCellValue(value);
            this.Cell.SetCellType(CellType.Boolean);
            return this;
        }

        public ExcelCell SetValue(double value)
        {
            this.Cell.SetCellValue(value);
            this.Cell.SetCellType(CellType.Numeric);
            return this;
        }

        public ExcelCell SetValue(DateTime value)
        {
            this.Cell.SetCellValue(value);
            return this;
        }

        public ExcelCell SetRowSpan(int rowspan)
        {
            this.Row.Sheet.AddMergedRegion(new CellRangeAddress(this.Row.RowIndex, this.Row.RowIndex + rowspan - 1, this.ColumnIndex, this.ColumnIndex));
            return this;
        }

        public ExcelCell SetColSpan(int colspan)
        {
            this.Row.Sheet.AddMergedRegion(new CellRangeAddress(this.Row.RowIndex, this.Row.RowIndex, this.ColumnIndex, this.ColumnIndex + colspan - 1));
            return this;
        }


    }

调用方法：

  MemoryStream stream = new MemoryStream();
            IWorkbook workbook = new HSSFWorkbook();
            ICellStyle headStyle = ExcelUtils.CreateStyle(workbook, "宋体", 10, true);
            ICellStyle bodyStyle = ExcelUtils.CreateStyle(workbook, "宋体", 10, false);


            ExcelRow tr = null;
            ExcelCell td = null;
      

            ISheet sheet = workbook.CreateSheet("Sheet0");

            tr = new ExcelRow(sheet);
            td = new ExcelCell(tr, headStyle);
            td.SetValue("序号");

            td = new ExcelCell(tr, headStyle);
            td.SetValue("姓名");

            td = new ExcelCell(tr, headStyle);
            td.SetValue("金额");

            for (int i = 0; i < 10; i++)
            {
                tr = new ExcelRow(sheet);
                td = new ExcelCell(tr, bodyStyle);
                td.SetValue(i + 1);

                td = new ExcelCell(tr, bodyStyle);
                td.SetValue("姓名");

                td = new ExcelCell(tr, bodyStyle);
                td.SetValue(new Random().Next(1, 100));
            }

            td.SetColSpan(3);

            //fs.Flush();
            // fs.Close();
            workbook.Write(stream);//将Excel写入流
            stream.Flush();
            stream.Position = 0;


            File.WriteAllBytes("a.xls", stream.GetBuffer());

        
            Console.Read();