C#使用NPOI对EXCle增 删 改 查 方法

最近闲来无事封装了使用NPOI对excle文件增删改查的方法，亲测可用附代码：

   /// <summary>
        /// 根据路径和表名得到Dictionary数据字典
        /// </summary>
        /// <param name="excelPath"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public Dictionary<int, Dictionary<string, string>> GetDictionaryBySheetName(string excelPath, string sheetName)
        {
            Dictionary<int, Dictionary<string, string>> sheetDic = new Dictionary<int, Dictionary<string, string>>();
            DataTable dt = GetDataTableBySheetName(excelPath, sheetName);
            if (dt.Rows.Count <= 0) return sheetDic;
            //1 获取表头
            int index = 0;
            foreach (DataRow itemRow in dt.Rows)
            {
                Dictionary<string, string> rowDic = new Dictionary<string, string>();
                for (int columnsI = 0; columnsI < dt.Columns.Count; columnsI++)
                {
                    rowDic.Add(dt.Columns[columnsI].ToString(), itemRow[columnsI].ToString());
                }
                sheetDic.Add(index, rowDic);
                index++;
            }
            return sheetDic;
        }

        /// <summary>
        /// 根据路径和表名得到datatabel
        /// </summary>
        /// <param name="excelPath"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public DataTable GetDataTableBySheetName(string excelPath, string sheetName)
        {
            DataTable dt = new DataTable();
            dt.TableName = sheetName;
            IWorkbook workbook = GetOpenWorkbookByExtension(excelPath);
            ISheet sheet;
            if (!TryPassWorkbook(workbook, sheetName, out sheet)) return dt;
            //1 读取Excel 第一行创建列头
            IRow headRow = sheet.GetRow(0);    //如果需要指定列头  可以在此处传入参数（修改该方法）
            ICell cell = null;
            string tempStr = string.Empty;
            for (int columnI = 0; columnI < headRow.LastCellNum; columnI++)
            {
                cell = headRow.GetCell(columnI);
                tempStr = cell == null ? columnI.ToString() : cell.ToString();
                if (dt.Columns.Contains(tempStr))
                {
                    throw new Exception(string.Format("EXCLE表{0}存在相同列属性【{1}】", sheet.SheetName, tempStr));
                    //处理excel列值相同问题 
                }
                else
                {
                    dt.Columns.Add(tempStr, typeof(string));
                }

            }
            IRow row = null;
            //2 填充数据
            for (int rowI = 1; rowI <= sheet.LastRowNum; rowI++)
            {
                row = sheet.GetRow(rowI);
                DataRow dtRow = dt.NewRow();
                if (null == row) continue;
                //3 遍历单元格
                for (int columJ = 0; columJ < dt.Columns.Count; columJ++)
                {
                    cell = row.GetCell(columJ);
                    if (0 == columJ && (null == cell || "##" == cell.ToString())) break;
                    if (null == cell) continue;
                    tempStr = cell.ToString();
                    dtRow[columJ] = tempStr;
                }
                dt.Rows.Add(dtRow);

            }

            return dt;
        }

        /// <summary>
        /// 移除工作薄中Sheet
        /// </summary>
        /// <param name="excelPath">文件路径</param>
        /// <param name="sheetName">表名</param>
        /// <returns>返回结果</returns>
        public bool RemoveSheetByName(string excelPath, string sheetName)
        {

            //1 得到工作簿
            IWorkbook workbook = GetOpenWorkbookByExtension(excelPath);
            if (null == workbook) return false;
            int sheetIndex = workbook.GetSheetIndex(sheetName);
            if (sheetIndex < 0) return false;
            workbook.RemoveSheetAt(sheetIndex);
            return SaveExcelFile(excelPath, workbook);

        }
        public bool RemoveSheetByName(string excelPath, int sheetIndex)
        {
            //1 得到工作簿
            IWorkbook workbook = GetOpenWorkbookByExtension(excelPath);
            if (null == workbook) return false;
            if (workbook.NumberOfSheets < sheetIndex) return false;
            workbook.RemoveSheetAt(sheetIndex);
            return SaveExcelFile(excelPath, workbook);
        }

有需要可以下载（添加现有项就能用）：https://download.csdn.net/download/weixin_43542114/12424263