基于NPOI的Excel操作类

网上有很多基于NPOI的Excel类，不过都不太适合我原来的代码习惯。我原来习惯是直接将List插入到一个Excel，不管里面的逻辑。用的是微软的控件完成的，需要安装office，而且要考虑office控件版本，通用性不好，于是修改为使用NPOI来完成。
该类分为2层，分别为NPOIExcelEdit类，封装NPOI的一些常用函数打包。NPOIExcelGenerator继承了NPOIExcelEdit类。如果是用微软的office控件完成，只需要将NPOIExcelEdit类的实现改为微软的控件版本即可。

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

namespace myWork
{
    /// <SUMMARY>
    /// ExcelEdit 的摘要说明
    /// </SUMMARY>
    public class NPOIExcelEdit
    {
        public string mFilename;
        public IWorkbook wb;
        public NPOI.SS.UserModel.ISheet ws;
        public NPOIExcelEdit()
        {
            mFilename = "";
            wb = new XSSFWorkbook();
        }

        public void Open(string filename)
        {
            using (FileStream fileStream = new FileStream(filename, FileMode.Open, FileAccess.Read))
            {
                string ext = Path.GetExtension(filename).ToLower();
                if (ext == ".xlsx")
                    wb = new XSSFWorkbook(fileStream);
                else
                {
                    wb = new HSSFWorkbook(fileStream);

                }
            }
            mFilename = filename;
        }
        /// <summary>
        /// 创建一个Excel对象，该对象为可见的
        /// </summary>
        public void Create(string sheetname = "Sheet1")
        {
            ws = wb.CreateSheet(sheetname);

        }

        public ISheet GetSheet(string SheetName)
        //获取一个工作表
        {
            return (ws = wb.GetSheet(SheetName) ?? wb.CreateSheet(SheetName));
        }

        public ISheet AddSheet(string SheetName)
        //添加一个工作表
        {
            ISheet s = wb.CreateSheet(SheetName);
            return s;
        }

        public void DelSheet(string SheetName)//删除一个工作表
        {
            int index = wb.GetNameIndex(SheetName);
            wb.RemoveSheetAt(index);
        }

        public ISheet ReNameSheet(string OldSheetName, string NewSheetName)//重命名一个工作表一
        {
            int index = wb.GetNameIndex(OldSheetName);
            wb.SetSheetName(index, NewSheetName);
            return wb.GetSheetAt(index);
        }

        public void SetCellValue(ISheet ws, int x, int y, object value)
        //ws：要设值的工作表     X行Y列     value   值
        {
            IRow row = ws.GetRow(x)??ws.CreateRow(x);
            ICell cell = row.GetCell(y)??row.CreateCell(y);
            string valuetype = value.GetType().Name.ToLower();
            switch(valuetype)
            {
                case "string"://字符串类型   
                case "system.string":
                case "datetime":
                case "system.datetime"://日期类型  
                case "boolean"://布尔型   
                case "system.boolean"://布尔型    
                    cell.SetCellType(CellType.String);
                    cell.SetCellValue(value.ToString());
                    break;
                case "byte":
                case "int":
                case "int16":
                case "int32":
                case "int64":
                case "system.int16"://整型   
                case "system.int32":
                case "system.int64":
                case "system.byte":
                    cell.SetCellType(CellType.Numeric);
                    cell.SetCellValue(Convert.ToInt32(value));
                    break;
                case "single":
                case "system.single":
                case "double":
                case "system.double":
                case "decimal":
                case "system.decimal":
                    cell.SetCellType(CellType.Numeric);
                    cell.SetCellValue(Convert.ToDouble(value));
                    break;
                case "dbnull"://空值处理   
                case "system.dbnull"://空值处理   
                    cell.SetCellValue("");
                    break;
                default:
                    cell.SetCellValue(value.ToString());
                    break;
            }
        }

        public void SetCellValue(string ws, int x, int y, object value)
        //ws：要设值的工作表的名称 X行Y列 value 值
        {
            ISheet s = GetSheet(ws);
            SetCellValue(s, x, y, value);
        }

        public ICell GetCell(string ws, int x, int y)
        {
            return GetSheet(ws).GetRow(x).Cells[y];
        }

        public ICell GetCell(ISheet ws, int x, int y)
        {
            return ws.GetRow(x).Cells[y];
        }


        public void SetCellProperty(string ws, int Startx, int Starty, int Endx, int Endy, int size, string fontName, IColor color, int fontSize, HorizontalAlignment HorizontalAlignment)
        //设置一个单元格的属性   字体，   大小，颜色   ，对齐方式
        {
            SetCellProperty(GetSheet(ws), Startx, Starty, Endx, Endy, size, fontName, color, fontSize, HorizontalAlignment);
        }

        public void SetCellProperty(ISheet ws, int Startx, int Starty, int Endx, int Endy, int size, string fontName, IColor color, int fontSize, HorizontalAlignment HorizontalAlignment)
        //设置一个单元格的属性   字体，   大小，颜色   ，对齐方式
        {
            ICellStyle style = wb.CreateCellStyle();
            IFont font = wb.CreateFont();
            font.Color = color.Indexed;
            font.FontName = fontName;
            font.FontHeightInPoints = fontSize;
            style.Alignment = HorizontalAlignment;
            style.SetFont(font);
            foreach (ICell cell in GetCellsOfRange(ws, Startx, Starty, Endx, Endy))
            {
                cell.CellStyle = style;
            }
        }

        public IList<ICell> GetCellsOfRange(string ws, int Startx, int Starty, int Endx, int Endy)
        {
            return (GetCellsOfRange(GetSheet(ws), Startx, Starty, Endx, Endy));
        }

        public IList<ICell> GetCellsOfRange(ISheet ws, int Startx, int Starty, int Endx, int Endy)
        {
            IList<ICell> allCell = new List<ICell>();
            for (int i = Startx; i <= Endx; i++)
                for (int j = Starty; j <= Endy; j++)
                {
                    allCell.Add(GetCell(ws, i, j));
                }
            return allCell;
        }

        public void UniteCells(ISheet ws, int firstRow, int lastRow, int firstCol, int lastCol)
        //合并单元格
        {
            //CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
            ws.AddMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
        }

        public void UniteCells(string ws, int firstRow, int lastRow, int firstCol, int lastCol)
        //合并单元格
        {
            UniteCells(GetSheet(ws), firstRow, lastRow, firstCol, lastCol);
        }

        public bool SaveAs(string FileName)
        //文档另存为
        {
            mFilename = FileName;
            try
            {
                using (FileStream fs = new FileStream(FileName, FileMode.Create, FileAccess.Write))
                {
                    wb.Write(fs);
                }
                return true;
            }
            catch
            {
                return false;
            }
        }

        public void Close()
        {
            wb.Close();
        }
    }
}

第2个类

using System;
using System.Collections.Generic;
using System.Reflection;
using System.Threading.Tasks;
using NPOI.SS.UserModel;

namespace myWork
{
    public class NPOIExcelGenerator : NPOIExcelEdit
    {

        public NPOIExcelGenerator()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
        }

        /// <summary>
        /// 将一个泛型类集合数据插入EXCEL表中
        /// </summary>
        /// <typeparam name="T">泛型类</typeparam>
        /// <param name="alldata">泛型类集合</param>
        /// <param name="caption">放在EXCEL第一行的标题数据</param>
        /// <param name="infostr">放在EXCEL第二行开始的附加数据信息，一个数据放一行</param>
        /// <param name="ws">EXCEL表格名称，默认为"Sheet1"</param>
        /// <param name="startX">EXCEL表格数据的起始行数，默认为第1行开始，在EXCEL中用1、2、3、4表示</param>
        /// <param name="startY">EXCEL表格数据的起始列数，默认为第1行开始，在EXCEL中用A、B、C、D表示</param>
        /// <returns>返回所有数据占据的行数</returns>
        public int InsertIListData<T>(IList<T> alldata, String caption, String[] infostr, string ws, int startX, int startY)
        {
            ISheet s = wb.GetSheet(ws);
            if (s == null)
                s = wb.CreateSheet(ws);
            IRow row;
            //使用原始名称，“否”为使用中文，“是”为使用英文
            bool originname = true;

            //返回值为数据占据的行数
            Type t = typeof(T);
            PropertyInfo[] myPropertyInfo = t.GetProperties();
            String classname = t.Name;
            int nowX = 0, nowY = 0;
            if (alldata == null) return 0;
            //这里插入标题
            if (caption.Length > 0)
            {
                SetCellValue(ws, startX + nowX, startY, caption);
            }
            //这里开始插入附加信息infostr
            for (int i = 0; i < infostr.Length; i++)
            {
                SetCellValue(ws, startX + nowX++, startY + nowY, infostr[i]);
            }
            //这里开始插入具体列表信息
            for (int i = 0; i < myPropertyInfo.Length; i++)
            {
                string temp = myPropertyInfo[i].PropertyType.ToString();
                if (temp.Substring(temp.Length - 2) == "[]")
                    continue;
                SetCellValue(ws, startX + nowX, startY + nowY, myPropertyInfo[i].Name);
                for (int j = 0; j < alldata.Count; j++)
                {
                    SetCellValue(ws, startX + j + nowX + 1, startY + nowY, myPropertyInfo[i].GetValue(alldata[j], null));
                }
                nowY++;
            }
            //这里是将标题行合并并且居中
            if (caption.Length > 0)
            {
                UniteCells(ws, startX, startY, startX, startY + nowY - 1);
            }
            //这里将附加信息合并并且居中
            if (infostr.Length > 0)
            {
                for (int i = 0; i < nowX; i++)
                {
                    UniteCells(ws, startX + i, startY, startX + i, startY + nowY - 1);
                }
            }
            return (alldata.Count + nowX + 1);
        }

        /// <summary>
        /// 将一个泛型类集合数据插入EXCEL表中
        /// </summary>
        /// <typeparam name="T">泛型类</typeparam>
        /// <param name="alldata">泛型类集合</param>
        /// <param name="caption">放在EXCEL第一行的标题数据</param>
        /// <param name="ws">EXCEL表格名称，默认为"Sheet1"</param>
        /// <param name="startX">EXCEL表格数据的起始行数，默认为第1行开始，在EXCEL中用1、2、3、4表示</param>
        /// <param name="startY">EXCEL表格数据的起始列数，默认为第1行开始，在EXCEL中用A、B、C、D表示</param>
        /// <returns>返回所有数据占据的行数</returns>
        public int InsertIListData<T>(IList<T> alldata, String caption, string ws, int startX, int startY)
        {
            String[] infostr = { };
            return (InsertIListData(alldata, caption, infostr, ws, startX, startY));
        }

        public int InsertIListData<T>(IList<T> alldata)
        {
            String caption = "", ws = "Sheet1";
            int startX = 1, startY = 1;
            String[] infostr = { };
            return (InsertIListData(alldata, caption, infostr, ws, startX, startY));
        }

        /*public void InsertIListDataAsync<T>(IList<T> alldata)
        {
            InsertIListDataAsy(alldata);
            return ;
        }*/

        public async void InsertDatatableAsync(System.Data.DataTable dt)
        {
            var t = await Task.Run(() => { return (InsertDatatable(dt)); });
            return;
        }


        public int InsertDatatable(System.Data.DataTable dt)
        {
            return InsertDatatable(dt, "Sheet1");
        }

        public int InsertDatatable(System.Data.DataTable dt, string ws)
        {
            if (dt == null || dt.Rows.Count == 0) return 0;
            long totalCount = dt.Rows.Count;
            int x = 0, y = 0;
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                SetCellValue(ws, x, y + i, dt.Columns[i].ColumnName);
            }
            x++;
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                for (int j = 0; j < dt.Rows.Count; j++)
                {
                    SetCellValue(ws, x + j, y + i, dt.Rows[j][i]);
                }
            }
            return dt.Rows.Count + 1;
        }

        public async void InsertIListDataAsync<T>(IList<T> alldata)
        {
            var t = await Task.Run(() => { return (InsertIListData(alldata)); });
            return;
        }

    }
}

轮子这个东西，有现成的就用现成的，没有现成的就用现成的改，适合自己就好