封装对NPOIExcel的操作，方便的设置导出Excel的样式

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using NPOI.SS.UserModel;
using System.IO;
using NPOI.XSSF.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.Util;

namespace NPOIExcel
{
    public class Point
    {
        public int X { get; set; }
        public int Y { get; set; }
    }
    public class Range
    {
        public int FIRSTROW { get; set; }
        public int LASTROW { get; set; }
        public int FIRSTCOL { get; set; }
        public int LASTCOL { get; set; }
    }

    public class ExcelManager
    {
        #region 属性

        private string inPutPath { get; set; }
        private string outPutPath { get; set; }
        private IWorkbook workbook { get; set; }
        private ISheet sheet { get; set; }



        #endregion

        #region 初始化
        //public ExcelManager() { }
        /// <summary>
        /// 创建一个用于导入，导出  数据的管理类
        /// </summary>
        /// <param name="Path">文件路径</param>
        /// <param name="type">设置路径为导入或导出</param>
        public ExcelManager(string Path, DealType type)
        {
            //设置导入文件的路径,workBook
            if (type == DealType.importExcel)
            {
                inPutPath = Path;
                using (FileStream file = new FileStream(inPutPath, FileMode.Open, FileAccess.Read))
                {
                    workbook = WorkbookFactory.Create(file);
                }
            }
            if (type == DealType.exportExcel)
            {
                outPutPath = Path;
                workbook = new XSSFWorkbook();
                sheet = workbook.CreateSheet("sheet1");
            }

        }

        /// <summary>
        /// 对Excel的操作类型
        /// </summary>
        public enum DealType
        {
            importExcel,
            exportExcel
        }
        #endregion

        #region "基础样式设置---2016-05-17 fengylc add "
        /// <summary>
        /// 设置范围内的样式
        /// </summary>
        /// <param name="startPoint">开始的单元格：例-A1</param>
        /// <param name="endPoint">结束的单元格：例-B1</param>
        /// <returns>样式</returns>
        public NPOIStyle SetRangeStyle(string startPoint, string endPoint)
        {
            return new NPOIStyle(workbook, sheet, startPoint, endPoint);
        }

        #endregion

        #region 导入
        /// <summary>
        /// 读取Excel到DataTable,默认第一行为列名
        /// </summary>
        /// <param name="headRowCount">表头的行数，从表头下一行开始是数据</param>
        /// <returns>DataTable</returns>
        public DataTable ReadExcelToDataTable(int headRowCount = 1)
        {

            using (FileStream file = new FileStream(inPutPath, FileMode.Open, FileAccess.Read))
            {
                workbook = WorkbookFactory.Create(file);
            }

            sheet = workbook.GetSheetAt(0);
            DataTable dtl = new DataTable(sheet.SheetName.Trim());

            IRow headRow = sheet.GetRow(0);

            int columnCount = headRow.Cells.Count;

            for (int index = 0; index <= columnCount - 1; index++)
            {
                if (headRow.Cells[index].Equals(null))
                    dtl.Columns.Add(string.Empty, typeof (string));
                else
                {
                    if(CellType.Numeric==headRow.Cells[index].CellType)
                        dtl.Columns.Add(headRow.Cells[index].NumericCellValue.ToString().Trim(), typeof(string));
                    else
                    dtl.Columns.Add(headRow.Cells[index].StringCellValue.Trim(), typeof(string));
                }
                   
            }

            for (int x = headRowCount; x <= sheet.LastRowNum; x++)
            {
                IRow contentRow = sheet.GetRow(x);
                if (contentRow.Equals(null))
                    continue;
                else
                {
                    DataRow dr = dtl.NewRow();
                    bool isEmpty = true;

                    for (int y = 0; y <= columnCount - 1; y++)
                    {
                        if (contentRow.GetCell(y).Equals(null))
                        {
                            dr[y] = string.Empty;
                        }
                        else
                        {
                            ICell contentCell = contentRow.GetCell(y);
                            switch (contentCell.CellType)
                            {
                                case CellType.Blank:
                                    dr[y] = string.Empty;
                                    break;
                                case CellType.Boolean:
                                    break;
                                case CellType.Error:
                                    break;
                                case CellType.Formula:
                                    dr[y] = contentCell.StringCellValue.Trim();
                                    break;
                                case CellType.Numeric:
                                    {
                                        if (DateUtil.IsCellDateFormatted(contentCell))
                                            dr[y] = contentCell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss");
                                        else
                                            dr[y] = contentCell.NumericCellValue.ToString().Trim();
                                    }
                                    break;
                                case CellType.String:
                                    dr[y] = contentCell.StringCellValue.Trim();
                                    break;
                                case CellType.Unknown:
                                    break;

                            }
                            isEmpty = string.IsNullOrEmpty(dr[y].ToString().Trim()) && isEmpty;
                        }
                    }

                    //非全空添加行
                    if (!isEmpty) dtl.Rows.Add(dr);
                }
            }
            return dtl;
        }
        #endregion

        #region 导出
        /// <summary>
        /// 设置数据源
        /// </summary>
        /// <param name="dt">数据</param>
        /// <param name="headEmptyLineCount">最上方留出的空行</param>
        public void SetDataSource(DataTable dt, int headEmptyLineCount = 0)
        {


            try
            {
                int TotalNum = dt.Rows.Count + headEmptyLineCount + 1;// （加上 空行 ）

                //添加数据
                addData(TotalNum, headEmptyLineCount + 1, sheet, dt);
                //标题设置
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    sheet.GetRow(headEmptyLineCount).GetCell(i).SetCellValue(dt.Columns[i].ColumnName);
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
        /// <summary>
        /// 导出文件
        /// </summary>
        /// <param name="fileName">文件名，不指定为随机生成</param>
        public void ExportExcelFile(string fileName = "")
        {
            Random ran = new Random();
            if (string.IsNullOrEmpty(fileName))
            {
                fileName = "\" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ran.Next(1000, 9999).ToString() + ".xlsx";
            }
            else
            {
                fileName = "\" + fileName + ".xlsx";
            }

            string CreatePath = outPutPath + fileName;
            FileStream NewFile = new FileStream(CreatePath, FileMode.Create);
            workbook.Write(NewFile);
        }
        private static void addData(int TotalNum, int headCount, ISheet sheet, DataTable dt)
        {
            //添加数据
            for (int i = 0; i < TotalNum; i++)
            {
                IRow row = sheet.CreateRow(i);

                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    row.CreateCell(j);
                    if (i >= headCount)
                    {
                        try
                        {
                            double cellData = double.Parse(dt.Rows[i - headCount][j].ToString());
                            row.CreateCell(j).SetCellValue(cellData);
                        }
                        catch
                        {
                            string cellData = dt.Rows[i - headCount][j].ToString();
                            if (cellData.Trim().Length > 1 && cellData.Substring(0, 1) == "'")
                            { cellData = cellData.Substring(1, cellData.Length - 1); }
                            row.CreateCell(j).SetCellValue(cellData);
                        }
                    }
                }
            }
        }
        #endregion
    }
    /// <summary>
    /// 设置样式
    /// </summary>
    public class NPOIStyle
    {
        public Range range { get; set; }
        public ISheet sheet { get; set; }
        public ICellStyle cellStyle { get; set; }
        public IFont cellFontStyle { get; set; }

        public NPOIStyle(IWorkbook workbook, ISheet sheet, string startPoint, string endPoint)
        {
            cellStyle = workbook.CreateCellStyle();
            cellFontStyle = workbook.CreateFont();
            this.sheet = sheet;
            range = GetRange(startPoint, endPoint);

        }

        public enum Alignment
        {
            Center, Fill, Left, Right, Bottom, Top
        }
        public enum NPOIColor : short
        {
            Black = 8, Brown = 60, OliveGreen = 59, DarkGreen = 58, DarkTeal = 56,
            DarkBlue = 18, Indigo = 62, Grey80Percent = 63, DarkRed = 16, Orange = 53,
            DarkYellow = 19, Green = 17, Teal = 21, Blue = 12, BlueGrey = 54,
            Grey50Percent = 23, Red = 10, LightOrange = 52, Lime = 50, SeaGreen = 57,
            Aqua = 49, LightBlue = 48, Violet = 20, Grey40Percent = 55, Pink = 14,
            Gold = 51, Yellow = 13, BrightGreen = 11, Turquoise = 15, SkyBlue = 40,
            Plum = 61, Grey25Percent = 22, Rose = 45, Tan = 47, LightYellow = 43,
            LightGreen = 42, LightTurquoise = 41, PaleBlue = 44, Lavender = 46,
            White = 9, CornflowerBlue = 24, LemonChiffon = 26, Maroon = 25,
            Orchid = 28, Coral = 29, RoyalBlue = 30, LightCornflowerBlue = 31, Automatic = 64
        }
        /// <summary>
        /// 把Excel列名字母转换为 数字坐标
        /// </summary>
        /// <param name="character">Excel列名</param>
        /// <returns>转换后的数字</returns>
        public static int Asc(string character)
        {
            character = character.ToUpper();
            char[] arry = character.ToCharArray();
            int total = 0;
            for (int i = 0; i < character.Length; i++)
            {
                total += (Convert.ToInt32(arry[i]) - 65) + 26 * i;
            }
            return total;
        }
        /// <summary>
        /// 把‘A1’类型转换成Point类型
        /// </summary>
        /// <param name="point">字符点</param>
        /// <returns>Point</returns>
        public static Point GetPoint(string point)
        {
            Point newPoint = new Point();
            char[] arry = point.ToCharArray();

            int index = 0;
            for (int i = 0; i < point.Length; i++)
            {
                if (Char.IsNumber(arry[i]))
                {
                    index = i;
                    break;
                }
            }
            newPoint.Y = Asc(point.Substring(0, index));
            newPoint.X = int.Parse(point.Substring(index, point.Length - index)) - 1;
            return newPoint;
        }
        /// <summary>
        /// 根据两个点确定一个范围
        /// </summary>
        /// <param name="startPosition">Excel上的开始点</param>
        /// <param name="endPosition">Excel上的结束点</param>
        /// <returns>范围</returns>
        public Range GetRange(string startPosition, string endPosition)
        {
            Range range = new Range();
            Point start = GetPoint(startPosition);
            Point end = GetPoint(endPosition);

            range.FIRSTROW = start.X;
            range.LASTROW = end.X;
            range.FIRSTCOL = start.Y;
            range.LASTCOL = end.Y;

            for (int row = range.FIRSTROW; row <= range.LASTROW; row++)
            {
                if (sheet.GetRow(row) == null) sheet.CreateRow(row);

                for (int col = range.FIRSTCOL; col <= range.LASTCOL; col++)
                {
                    if (sheet.GetRow(row).GetCell(col) == null) sheet.GetRow(row).CreateCell(col);
                }
            }

            return range;
        }
        /// <summary>
        /// 设置样式的必要设置：设置样式的范围
        /// </summary>
        /// <param name="startPoint">范围起始点</param>
        /// <param name="endPoint">范围结束点</param>
        /// <returns>设置样式的范围</returns>
        public NPOIStyle SetRange(string startPoint, string endPoint)
        {
            this.range = GetRange(startPoint, endPoint);
            return this;
        }
        /// <summary>
        /// 合并单元格  
        /// </summary>
        /// <param name="startPosition">合并开始的单元格</param>
        /// <param name="endPosition">合并结束的单元格</param>
        /// <returns></returns>
        public NPOIStyle SetCellMerge()
        {
            //把excel单元格带字母坐标 转换为 数字坐标
            CellRangeAddress cellRangeAddress = new CellRangeAddress(range.FIRSTROW, range.LASTROW, range.FIRSTCOL, range.LASTCOL);
            this.sheet.AddMergedRegion(cellRangeAddress);
            return this;
        }
        /// <summary>
        /// 设置字体大小
        /// </summary>
        /// <param name="size">字体大小:9,10,11...</param>
        /// <returns>Manager</returns>
        public NPOIStyle SetFontSize(short size)
        {
            cellFontStyle.FontHeightInPoints = size;
            return this;
        }
        /// <summary>
        /// 设置sheet页面上列的宽
        /// </summary>
        /// <param name="size"></param>
        /// <returns></returns>
        public NPOIStyle SetSheetColumnWidthSize(short size)
        {
            sheet.DefaultColumnWidth = size;
            return this;
        }
        /// <summary>
        /// 设置字体样式
        /// </summary>
        /// <param name="FontName">字体:宋体,黑体...</param>
        /// <returns>Manager</returns>
        public NPOIStyle SetFontName(string FontName)
        {
            cellFontStyle.FontName = FontName;
            return this;
        }
        /// <summary>
        /// 设置字体加粗
        /// </summary>
        /// <returns>Manager</returns>
        public NPOIStyle SetFontBold()
        {
            cellFontStyle.Boldweight = (short)FontBoldWeight.Bold;
            return this;
        }
        /// <summary>
        /// 设置字体下划线
        /// </summary>
        /// <returns>Manager</returns>
        public NPOIStyle SetFontUnderline()
        {
            cellFontStyle.Underline = FontUnderlineType.Single;
            return this;
        }
        /// <summary>
        /// 设置字体下倾斜
        /// </summary>
        /// <returns>Manager</returns>
        public NPOIStyle SetFontItalic()
        {
            cellFontStyle.IsItalic = true;
            return this;
        }
        /// <summary>
        /// 设置字体水平对齐
        /// </summary>
        /// <returns>Manager</returns>
        public NPOIStyle SetHorizontalAlignment(Alignment alignment)
        {
            switch (alignment)
            {
                case Alignment.Center:
                    cellStyle.Alignment = HorizontalAlignment.Center;
                    break;
                case Alignment.Fill:
                    cellStyle.Alignment = HorizontalAlignment.Fill;
                    break;
                case Alignment.Left:
                    cellStyle.Alignment = HorizontalAlignment.Left;
                    break;
                case Alignment.Right:
                    cellStyle.Alignment = HorizontalAlignment.Right;
                    break;
                default:
                    break;
            }
            return this;
        }
        /// <summary>
        /// 设置字体垂直对齐
        /// </summary>
        /// <returns>Manager</returns>
        public NPOIStyle SetVerticalAlignment(Alignment alignment)
        {
            switch (alignment)
            {
                case Alignment.Center:
                    cellStyle.VerticalAlignment = VerticalAlignment.Center;
                    break;
                case Alignment.Fill:
                    cellStyle.VerticalAlignment = VerticalAlignment.Justify;
                    break;
                case Alignment.Top:
                    cellStyle.VerticalAlignment = VerticalAlignment.Top;
                    break;
                case Alignment.Bottom:
                    cellStyle.VerticalAlignment = VerticalAlignment.Bottom;
                    break;
                default:
                    break;
            }
            return this;
        }
        /// <summary>
        /// 设置字体颜色
        /// </summary>
        /// <param name="FontName">字体:宋体,黑体...</param>
        /// <returns>Manager</returns>
        public NPOIStyle SetFontColor(NPOIColor color)
        {
            cellFontStyle.Color = (short)color;
            return this;
        }
        /// <summary>
        /// 设置单元格背景色
        /// </summary>
        /// <param name="color"></param>
        /// <returns></returns>
        public NPOIStyle SetBackgroundColor(NPOIColor color)
        {
            cellStyle.FillPattern = FillPattern.SolidForeground;
            cellStyle.FillForegroundColor = (short)color;
            return this;
        }
        /// <summary>
        /// 设置单元格的文字
        /// </summary>
        /// <param name="position"></param>
        /// <param name="text"></param>
        public NPOIStyle SetCellText(string text)
        {
            sheet.GetRow(range.FIRSTROW).GetCell(range.FIRSTCOL).SetCellValue(text);
            return this;
        }
        /// <summary>
        /// 设置边框（Range内每个单元格的四边）
        /// </summary>
        /// <returns></returns>
        public NPOIStyle SetBorderAll()
        {
            cellStyle.BorderBottom = BorderStyle.Thin;
            cellStyle.BorderLeft = BorderStyle.Thin;
            cellStyle.BorderRight = BorderStyle.Thin;
            cellStyle.BorderTop = BorderStyle.Thin;
            return this;
        }
        public NPOIStyle SetBorderBottom()
        {
            cellStyle.BorderBottom = BorderStyle.Thin;
            return this;
        }
        public NPOIStyle SetBorderLeft()
        {
            cellStyle.BorderLeft = BorderStyle.Thin;
            return this;
        }
        public NPOIStyle SetBorderRight()
        {
            cellStyle.BorderRight = BorderStyle.Thin;
            return this;
        }
        public NPOIStyle SetBorderTop()
        {
            cellStyle.BorderTop = BorderStyle.Thin;
            return this;
        }

        /// <summary>
        /// 样式设置结束，调用此方法生效
        /// </summary>
        public void SetEnd()
        {
            for (int i = range.FIRSTROW; i <= range.LASTROW; i++)
            {
                for (int j = range.FIRSTCOL; j <= range.LASTCOL; j++)
                {
                    cellStyle.SetFont(cellFontStyle);
                    sheet.GetRow(i).GetCell(j).CellStyle = cellStyle;
                }
            }
        }
    }
}