Npoi 操作excel

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApp
{

    /// <summary>
    /// NPOIExcel 针对2.5.1.0出的NPOI操作Excel类的常用方法；
    /// </summary>
    public class NPOIHelper
    {
        /// <summary>
        /// 记录打开的Excel的路径
        /// </summary>
        private string npoiFileName;
        /// <summary>
        /// 工作簿，全局变量
        /// </summary>
        protected IWorkbook workbook;
        /// <summary>
        /// 获取工作表或是创建的
        /// </summary>
        private NPOI.SS.UserModel.ISheet sheet;
        /// <summary>
        /// 构造方法
        /// </summary>
        public NPOIHelper()
        {
            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>
        /// 获取当前sheet的所有有数据的单元格
        /// </summary>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public List<ICell> GetHasValueCell(string sheetName)
        {
            List<ICell> values = new List<ICell>();
            var sheet = GetSheet(sheetName);
            int maxRow = sheet.LastRowNum + 1;
            int maxCol = MaxCellIndex(sheetName);
            for (int row = 0; row < maxRow; row++)
            {
                for (int col = 0; col < maxCol; col++)
                {
                    try
                    {
                        //这一行查不到值的话会报错
                        ICell cell = sheet.GetRow(row)?.GetCell(col);
                        if (cell != null) values.Add(cell);
                    }
                    catch (Exception)
                    {
                    }
                }
            }
            return values;
        }
        /// <summary>
        /// 得到最大有效数据行索引
        /// </summary>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public int MaxRowIndex(string sheetName)
        {
            var sheet = GetSheet(sheetName);
            return sheet.LastRowNum;
        }
        /// <summary>
        /// 得到最大有效数据列从1开始
        /// </summary>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public int MaxCellIndex(string sheetName)
        {
            int maxCellIndex = -1;
            var sheet = GetSheet(sheetName);
            for (int rowCnt = sheet.FirstRowNum; rowCnt <= sheet.LastRowNum; rowCnt++)//迭代所有行
            {
                IRow row = sheet.GetRow(rowCnt);
                if (row != null && row.LastCellNum > maxCellIndex)
                {
                    maxCellIndex = row.LastCellNum;
                }
            }
            return maxCellIndex;
        }
        /// <summary>
        /// 得到最大行和最大列的索引行从0开始列从0开始
        /// </summary>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public (int, int) GetMaxRowIndexAndCellIndex(string sheetName)
        {
            return (MaxRowIndex(sheetName), MaxCellIndex(sheetName) - 1);
        }
        /// <summary>
        /// 得到最大行和最大列的索引，行从0开始列从1开始
        /// </summary>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public (int, int) GetMaxRowIndexAndCell(string sheetName)
        {
            return (MaxRowIndex(sheetName), MaxCellIndex(sheetName));
        }
        /// <summary>
        /// 清除某行的值和样式
        /// </summary>
        /// <param name="sheetName"></param>
        /// <param name="rowIndex"></param>
        public void ClearRowValue(string sheetName, int rowIndex)
        {
            var sheet = GetSheet(sheetName);
            if (sheet == null) return;
            IRow row = sheet.GetRow(rowIndex);
            if (row == null) return;
            for (int i = 0; i < row.LastCellNum; i++)
            {
                try
                {
                    ICell cell = row.GetCell(i);
                    cell.CellStyle = null;
                    cell.SetCellValue(string.Empty);
                }
                catch (Exception)
                {
                }
            }
        }

        /// <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四个参数为：起始行，结束行，起始列，结束列
            var region = new CellRangeAddress(startRow, endRow, startCol, endCol);
            sheetName.AddMergedRegion(region);
        }


        /// <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(j, widthValue * 256);
            }
        }
        /// <summary>
        /// 插入新行
        /// </summary>
        /// <param name="sheetName">工作表名</param>
        /// <param name="insertRowIndex">插入的行索引位置</param>
        /// <param name="insertRowCount">插入的行数量</param>
        /// <param name="formatRowIndex">获取插入行的参照样式的行索引</param>
        public void InsertRow(string sheetName, int insertRowIndex, int insertRowCount, int formatRowIndex)
        {
            ISheet sheet = GetSheet(sheetName);
            IRow formatRow = sheet.GetRow(formatRowIndex);
            InsertRow(sheet, insertRowIndex, insertRowCount, formatRow);
        }


        /// <summary>
        /// 插入新行
        /// </summary>
        /// <param name="sheetName"></param>
        /// <param name="insertRowIndex"></param>
        /// <param name="insertRowCount"></param>
        /// <param name="formatRow"></param>
        private void InsertRow(ISheet sheetName, int insertRowIndex, int insertRowCount, IRow formatRow)
        {
            sheet.ShiftRows(insertRowIndex, sheet.LastRowNum, insertRowCount, true, false);
            for (int i = insertRowIndex; i < insertRowIndex + insertRowCount; i++)
            {
                IRow targetRow = null;
                ICell sourceCell = null;
                ICell targetCell = null;
                targetRow = sheet.CreateRow(i);
                for (int m = formatRow.FirstCellNum; m < formatRow.LastCellNum; m++)
                {
                    sourceCell = formatRow.GetCell(m);
                    if (sourceCell == null)
                    {
                        continue;
                    }
                    targetCell = targetRow.CreateCell(m);
                    targetCell.CellStyle = sourceCell.CellStyle;
                    targetCell.SetCellType(sourceCell.CellType);

                }
            }

            for (int i = insertRowIndex; i < insertRowIndex + insertRowCount; i++)
            {
                IRow firstTargetRow = sheet.GetRow(i);
                ICell firstSourceCell = null;
                ICell firstTargetCell = null;

                for (int m = formatRow.FirstCellNum; m < formatRow.LastCellNum; m++)
                {
                    firstSourceCell = formatRow.GetCell(m, MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    if (firstSourceCell == null)
                    {
                        continue;
                    }
                    firstTargetCell = firstTargetRow.GetCell(m, MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    firstTargetCell.CellStyle = firstSourceCell.CellStyle;
                    firstTargetCell.SetCellType(firstSourceCell.CellType);
                }
            }
        }


        //--------------------------------------------------------------------新增的方法-------------------------------------

        #region 自定义样式
        /// <summary>
        /// 自定义样式
        /// </summary>
        /// <param name="fontSize"></param>
        /// <param name="fontName"></param>
        /// <param name="horizontalAlignment"></param>
        /// <param name="verticalAlignment"></param>
        /// <returns></returns>
        public ICellStyle CreateCellStyle(int fontSize, string fontName, bool fontBold, HorizontalAlignment horizontal, VerticalAlignment vertical, BorderStyle borderStyle)
        {
            ICellStyle style = workbook.CreateCellStyle();
            IFont font = workbook.CreateFont();
            font.FontName = fontName;
            font.FontHeightInPoints = (short)fontSize;
            if (fontBold) font.Boldweight = (short)FontBoldWeight.Bold;//字体加粗
            style.Alignment = horizontal;
            style.VerticalAlignment = vertical;
            style.SetFont(font);
            style.BorderLeft = borderStyle;
            style.BorderRight = borderStyle;
            style.BorderTop = borderStyle;
            style.BorderBottom = borderStyle;
            return style;
        }
        /// <summary>
        /// 得到数据行常规样式（11 字号+居中对齐）
        /// </summary>
        /// <returns></returns>
        public ICellStyle GetNormalCellStyle()
        {
            return GetNormalCellStyle(11, HorizontalAlignment.Center);
        }
        public ICellStyle GetNormalCellStyle(int fontSize, HorizontalAlignment horizontal)
        {
            return CreateCellStyle(fontSize, "Arial", false, horizontal, VerticalAlignment.Center, BorderStyle.Thin);
        }
        /// <summary>
        /// 得到标题样式(20字号+加粗+居中对齐)
        /// </summary>
        /// <returns></returns>
        public ICellStyle GetHeaderCellStyle()
        {
            return CreateCellStyle(20, "Arial", true, HorizontalAlignment.Center, VerticalAlignment.Center, BorderStyle.Thin);
        }
        #endregion

        #region 设置行列的边框，以及样式
        /// <summary>
        /// 设置指定列，指定范围行的边框 （传从0 开始的索引）
        /// </summary>
        public void SetCellRangeBorder(string sheetName, int cellIndex, int startRow, int endRow, ICellStyle cellStyle)
        {
            SetRengeBorder(sheetName, startRow, cellIndex, endRow, cellIndex, cellStyle);
        }
        /// <summary>
        /// 设置指定行指定范围列的边框（传从0 开始的索引）
        /// </summary>
        public void SetRowRangeBorder(string sheetName, int rowIndex, int startCell, int endCell, ICellStyle cellStyle)
        {
            SetRengeBorder(sheetName, rowIndex, startCell, rowIndex, endCell, cellStyle);
        }
        /// <summary>
        /// 设置指定范围的边框，（传从0 开始的索引）
        /// </summary>
        public void SetRengeBorder(string sheetName, int startRow, int startCell, int endRow, int endCell, ICellStyle cellStyle)
        {
            var sheet = GetSheet(sheetName);
            for (int i = startRow; i <= endRow; i++)
            {
                for (int n = startCell; n <= endCell; n++)
                {
                    var row = sheet.GetRow(i) ?? sheet.CreateRow(i);
                    var cell = row.GetCell(n);
                    if (cell == null)
                    {
                        cell = sheet.GetRow(i).CreateCell(n);
                    }
                    cell.CellStyle = cellStyle;
                }
            }
        }
        #endregion

        #region 合并单元格，带样式 
        /// <summary>
        /// 合并单元格常规样式
        /// </summary>
        /// <param name="sheetName"></param>
        /// <param name="startRow"></param>
        /// <param name="startCol"></param>
        /// <param name="endRow"></param>
        /// <param name="endCol"></param>
        public void MergedCellsStyle(string sheetName, int startRow, int startCol, int endRow, int endCol)
        {
            MergedCellsStyle(sheetName, startRow, startCol, endRow, endCol, GetNormalCellStyle());
        }
        /// <summary>
        /// 合并单元格指定样式
        /// </summary>
        /// <param name="sheetName"></param>
        /// <param name="startRow"></param>
        /// <param name="startCol"></param>
        /// <param name="endRow"></param>
        /// <param name="endCol"></param>
        /// <param name="cellStyle"></param>
        public void MergedCellsStyle(string sheetName, int startRow, int startCol, int endRow, int endCol, ICellStyle cellStyle)
        {
            MergedCells(GetSheet(sheetName), startRow, endRow, startCol, endCol);
            for (int i = startRow; i <= endRow; i++)
            {
                SetRowRangeBorder(sheetName, i, startCol, endCol, cellStyle);
            }
        }
        #endregion

        /// <summary>
        /// 设置表格内有效数据行样式（全部行）(填充完数据后调用)
        /// </summary>
        /// <param name="sheetName"></param>
        /// <param name="cellStyle">样式</param>
        /// <param name="isIgnoreHeader">是否忽略掉标题，前两行</param>
        /// <param name="isAutoW">内容是否自适应宽度</param>
        public void SetValidDataRowStyle(string sheetName, ICellStyle cellStyle, bool isIgnoreHeader = true, bool isAutoW = true)
        {
            var (row, cell) = GetMaxRowIndexAndCellIndex(sheetName);
            int startRow = isIgnoreHeader ? 2 : 0;
            SetRengeBorder(sheetName, startRow, 0, row, cell, cellStyle);
            if (isAutoW) AutoColumnWidth(sheetName, 0, cell);
        }
    }
}