NPOI | 操作Excel类的常用方法

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;
using System.Text;
namespace ExamKing.Application.Utils.Excel
{
    /// <summary>
    /// NPOIExcel 针对2.5.1.0出的NPOI操作Excel类的常用方法；
    /// </summary>
    public class NPOIExcel
    {
        /// <summary>
        /// 记录打开的Excel的路径
        /// </summary>
        private string npoiFileName;
        /// <summary>
        /// 工作簿，全局变量
        /// </summary>
        protected IWorkbook workbook;
        /// <summary>
        /// 获取工作表或是创建的
        /// </summary>
        private NPOI.SS.UserModel.ISheet sheet;
        /// <summary>
        /// 构造方法
        /// </summary>
        public NPOIExcel()
        {
            npoiFileName = "";
            workbook = new XSSFWorkbook();
        }
        /// <summary>
        /// 打开
        /// </summary>
        /// <param name="filename">excel文件路径</param>
        public void Open(string filename)
        {
            using (FileStream fileStream = new FileStream(filename, FileMode.Open, FileAccess.Read))
            {
                string ext = Path.GetExtension(filename).ToLower();
                if (ext == ".xlsx")
                    workbook = new XSSFWorkbook(fileStream);
                else
                {
                    workbook = new HSSFWorkbook(fileStream);
                }
            }
            npoiFileName = filename;
        }
        /// <summary>
        /// 创建一个Excel对象，该对象为可见的
        /// </summary>
        public void Create(string sheetname = "Sheet1")
        {
            sheet = workbook.CreateSheet(sheetname);
        }
        /// <summary>
        /// 获取一个工作表
        /// </summary>
        /// <param name="SheetName">工作表名称</param>
        /// <returns></returns>
        public ISheet GetSheet(string SheetName)
        {
            return (sheet = workbook.GetSheet(SheetName) ?? workbook.CreateSheet(SheetName));
        }
        /// <summary>
        /// 添加一个工作表
        /// </summary>
        /// <param name="SheetName">工作表名称</param>
        /// <returns></returns>
        public ISheet AddSheet(string SheetName)
        {
            ISheet s = workbook.CreateSheet(SheetName);
            return s;
        }
        /// <summary>
        /// 删除一个工作表
        /// </summary>
        /// <param name="SheetName">工作表名称</param>
        public void DelSheet(string SheetName)
        {
            int index = workbook.GetNameIndex(SheetName);
            workbook.RemoveSheetAt(index);
        }
        /// <summary>
        /// 重命名一个工作表
        /// </summary>
        /// <param name="OldSheetName">老工作表名称</param>
        /// <param name="NewSheetName">新工作表名称</param>
        /// <returns></returns>
        public ISheet ReNameSheet(string OldSheetName, string NewSheetName)
        {
            int index = workbook.GetNameIndex(OldSheetName);
            workbook.SetSheetName(index, NewSheetName);
            return workbook.GetSheetAt(index);
        }
        /// <summary>
        /// 设置单元格的值
        /// </summary>
        /// <param name="sheetName">工作表名称</param>
        /// <param name="row">行</param>
        /// <param name="col">列</param>
        /// <param name="value">设置的值</param>
        private void SetCellValue(ISheet sheetName, int row, int col, object value)
        {
            IRow _row = sheetName.GetRow(row) ?? sheetName.CreateRow(row);
            ICell cell = _row.GetCell(col) ?? _row.CreateCell(col);
            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;
            }
        }
        /// <summary>
        /// 要设值的工作表的名称 X行Y列 value 值
        /// </summary>
        /// <param name="sheetName">工作表名</param>
        /// <param name="row">行</param>
        /// <param name="col">列</param>
        /// <param name="value">插入的值</param>
        public void SetCellValue(string sheetName, int row, int col, object value)
        {
            ISheet s = GetSheet(sheetName);
            SetCellValue(s, row, col, value);
        }
        /// <summary>
        /// 获取单元格值
        /// </summary>
        /// <param name="sheetName">工作表名</param>
        /// <param name="row">行</param>
        /// <param name="col">列</param>
        /// <returns></returns>
        public ICell GetCell(string sheetName, int row, int col)
        {
            return GetSheet(sheetName).GetRow(row).Cells[col];
        }
        /// <summary>
        /// 获取单元格值
        /// </summary>
        /// <param name="sheetName">工作表名</param>
        /// <param name="row">行</param>
        /// <param name="col">列</param>
        /// <returns></returns>
        private ICell GetCell(ISheet sheetName, int row, int col)
        {
            //return ws.GetRow(row).Cells[col];
            return sheetName.GetRow(row).GetCell(col);
        }
        /// <summary>
        /// 设置单元格的属性(字体,大小,颜色,对齐方式)(前提是该行列范围内有值，否则报错)
        /// </summary>
        /// <param name="sheetName"></param>
        /// <param name="startRow">起始行</param>
        /// <param name="startCol">起始列</param>
        /// <param name="endRow">结束行</param>
        /// <param name="endCol">结束列</param>
        /// <param name="fontName">字体</param>
        /// <param name="indexedColors">字体颜色</param>
        /// <param name="fontSize">字体大小</param>
        /// <param name="horizontalAlignment">水平排列</param>
        public void SetCellProperty(string sheetName, int startRow, int startCol, int endRow, int endCol, string fontName, IndexedColors indexedColors, int fontSize, HorizontalAlignment horizontalAlignment)
        {
            SetCellProperty(GetSheet(sheetName), startRow, startCol, endRow, endCol, fontName, indexedColors, fontSize, horizontalAlignment);
        }
        /// <summary>
        /// 设置一个单元格的属性(字体,大小,颜色,对齐方式)
        /// </summary>
        /// <param name="sheetName"></param>
        /// <param name="Startx"></param>
        /// <param name="Starty"></param>
        /// <param name="Endx"></param>
        /// <param name="Endy"></param>
        /// <param name="fontName"></param>
        /// <param name="indexedColors"></param>
        /// <param name="fontSize"></param>
        /// <param name="horizontalAlignment"></param>
        private void SetCellProperty(ISheet sheetName, int startRow, int startCol, int endRow, int endCol, string fontName, IndexedColors indexedColors, int fontSize, HorizontalAlignment horizontalAlignment)
        {
            ICellStyle style = workbook.CreateCellStyle();
            IFont font = workbook.CreateFont();
            font.Color = indexedColors.Index;
            font.FontName = fontName;
            font.FontHeightInPoints = fontSize;
            //font.Boldweight = Boldweight;//字体加粗
            style.Alignment = horizontalAlignment;
            //style.VerticalAlignment = VerticalAlignment.Center; //垂直居中
            style.SetFont(font);
            foreach (ICell cell in GetCellsOfRange(sheetName, startRow, startCol, endRow, endCol))
            {
                cell.CellStyle = style;
            }
        }
        /// <summary>
        /// 获取起止范围内的行列值
        /// </summary>
        /// <param name="sheetName"></param>
        /// <param name="startRow"></param>
        /// <param name="startCol"></param>
        /// <param name="endRow"></param>
        /// <param name="endCol"></param>
        /// <returns></returns>
        public IList<ICell> GetCellsOfRange(string sheetName, int startRow, int startCol, int endRow, int endCol)
        {
            return (GetCellsOfRange(GetSheet(sheetName), startRow, startCol, endRow, endCol));
        }
        /// <summary>
        /// 获取起止范围内的行列值
        /// </summary>
        /// <param name="sheetName"></param>
        /// <param name="startRow"></param>
        /// <param name="startCol"></param>
        /// <param name="endRow"></param>
        /// <param name="endCol"></param>
        /// <returns></returns>
        private IList<ICell> GetCellsOfRange(ISheet sheetName, int startRow, int startCol, int endRow, int endCol)
        {
            IList<ICell> allCell = new List<ICell>();
            for (int i = startRow; i <= endRow; i++)
                for (int j = startCol; j <= endCol; j++)
                {
                    allCell.Add(GetCell(sheetName, i, j));
                }
            return allCell;
        }
        /// <summary>
        /// 合并单元格
        /// </summary>
        /// <param name="sheetName"></param>
        /// <param name="startRow"></param>
        /// <param name="endRow"></param>
        /// <param name="startCol"></param>
        /// <param name="endCol"></param>
        private void MergedCells(ISheet sheetName, int startRow, int endRow, int startCol, int endCol)
        {
            //CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
            sheetName.AddMergedRegion(new CellRangeAddress(startRow, endRow, startCol, endCol));
        }
        /// <summary>
        /// 合并单元格
        /// </summary>
        /// <param name="sheetName"></param>
        /// <param name="startRow"></param>
        /// <param name="startCol"></param>
        /// <param name="endRow"></param>
        /// <param name="endCol"></param>
        public void MergedCells(string sheetName, int startRow, int startCol, int endRow, int endCol)
        {
            MergedCells(GetSheet(sheetName), startRow, endRow, startCol, endCol);
        }
        /// <summary>
        /// 文档另存为
        /// </summary>
        /// <param name="FileName"></param>
        /// <returns></returns>
        public bool SaveAs(string FileName)
        {
            npoiFileName = FileName;
            try
            {
                using (FileStream fs = new FileStream(FileName, FileMode.Create, FileAccess.Write))
                {
                    workbook.Write(fs);
                }
                return true;
            }
            catch
            {
                return false;
            }
        }
        /// <summary>
        /// 关闭
        /// </summary>
        public void Close()
        {
            workbook.Close();
        }
        /// <summary>
        /// 自适应宽度
        /// </summary>
        /// <param name="sheetName">表名</param>
        /// <param name="startCol">起始列</param>
        /// <param name="endCol">结束列</param>
        public void AutoColumnWidth(string sheetName, int startCol, int endCol)
        {
            AutoColumnWidth(GetSheet(sheetName), startCol, endCol);
        }
        /// <summary>
        /// 自适应宽度
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="cols"></param>
        private void AutoColumnWidth(ISheet sheet, int startCol, int endCol)
        {
            for (int col = startCol; col <= endCol; col++)
            {
                sheet.AutoSizeColumn(col);//但是其实还是比实际文本要宽
            }
        }
        /// <summary>
        /// 设置起止范围的行高，单位为磅
        /// </summary>
        /// <param name="sheetName">工作表名称</param>
        /// <param name="startRow">起始行</param>
        /// <param name="endRow">结束行</param>
        /// <param name="heightValue">设置的高值</param>
        public void SetRowsHeight(string sheetName, int startRow, int endRow, int heightValue)
        {
            ISheet sheet = GetSheet(sheetName);
            for (int i = startRow; i <= endRow; i++)
            {
                //sheet.GetRow(i).Height = Height * 20;
                sheet.GetRow(i).HeightInPoints = heightValue;
            }
        }
        /// <summary>
        /// 设置起止列的宽度,单位为字符
        /// </summary>
        /// <param name="sheetName">工作表名称</param>
        /// <param name="startCol">起始列</param>
        /// <param name="endCol">结束列</param>
        /// <param name="widthValue">设置的宽度值</param>
        public void SetColumnsWidth(string sheetName, int startCol, int endCol, int widthValue)
        {
            ISheet sheet = GetSheet(sheetName);
            for (int j = startCol; j <= endCol; j++)
            {
                sheet.SetColumnWidth(3, widthValue * 256);
            }
        }
    }
}