另一个ExcelHelper

有不少园友指点，用NPOI操作Excel会比用ADO.NET 和COM 要好，于是尝试一下用NPOI封装一个ExcelHelper，在使用本类之前，要添加NPOI.dll引用。要添加两个个命名空间 　　

using NPOI.SS.UserModel;

using NPOI.HSSF.UserModel;

类代码如下：

    public class NPOIExcelHelper
    {
        #region 公共方法

        #region 导出

        /// <summary>
        /// 数据导出
        /// </summary>
        /// <param name="fileName">导出到的文件全名</param>
        /// <param name="table">数据表DataTable</param>
        /// <param name="addHeader">是否生产表头</param>
        public static void ExportExcel(string fileName, DataTable table,bool addHeader)
        {
            if (addHeader)
            {
                DataRow row = table.Rows.Add();
                foreach (DataColumn col in table.Columns)
                    row[col] = col.ColumnName;
                table.Rows.Remove(row);
                table.Rows.InsertAt(row, 0);
            }
            EditExcel(fileName, "Sheet1", table, "A1");
        }

        /// <summary>
        /// 数据导出(生产表头)
        /// </summary>
        /// <param name="fileName">导出到的文件全名</param>
        /// <param name="table">数据表DataTable</param>
        public static void ExportExcel(string fileName, DataTable table)
        {
            ExportExcel(fileName, table, true);
        }

        #endregion

        #region 导入

        /// <summary>
        /// 数据导入
        /// </summary>
        /// <param name="fileName">导入的文件全名</param>
        /// <param name="hasHeader">需要生产表头</param>
        /// <returns>导入结果</returns>
        public static DataTable ImportExcel(string fileName,bool hasHeader)
        {
            DataTable table = ReadExcel(fileName, 0);
            if (hasHeader&&table.Rows.Count>0)
            {
                DataRow row = table.Rows[0];
                for (int i = 0; i < table.Columns.Count; i++)
                    table.Columns[i].ColumnName = table.Rows[0][i].ToString();
                table.Rows.Remove(row);
            }
            return table;
        }

        /// <summary>
        /// 数据导入(要生成表头)
        /// </summary>
        /// <param name="fileName">导入的文件全名</param>
        /// <returns>导入结果</returns>
        public static DataTable ImportExcel(string fileName)
        {
            return ImportExcel(fileName, true);
        }

        #endregion

        #region 通用编辑

        /// <summary>
        /// 编辑Excel文档。检查不了文件则不保存，检查不了工作表则新建，覆盖编辑
        /// </summary>
        /// <param name="fileName">文件全名</param>
        /// <param name="sheetName">工作表名</param>
        /// <param name="table">数据表DataTable</param>
        /// <param name="cell">起始的单元格 如 "A1"</param>
        public static void EditExcel(string fileName, string sheetName, DataTable table, string cell)
        {
            IWorkbook workBook = null;
            ISheet sheet = null;
            FileStream fs = null;
            bool exist=false;

            try
            {
                if (IsExistFile(fileName))
                {
                    exist=true;
                    fs = File.Open(fileName, FileMode.Open);
                    workBook = new HSSFWorkbook(fs);
                }
                else
                {
                    exist=false;
                    fs = File.Create(fileName);
                    fs.Close();//2013-10-6创建文件后关闭流，防止占用（有网友指出）
                    fs.Dispose();//2013-10-6创建文件后关闭流，防止占用（有网友指出）
                    workBook = new HSSFWorkbook();
                }
                sheet = workBook.GetSheet(sheetName);
                if (sheet == null)
                    sheet = workBook.CreateSheet(sheetName);

                Tuple<int, int> cellIndex = ConvertCell(cell);

                IRow eRow = null;
                foreach (DataRow row in table.Rows)
                {
                    eRow = sheet.CreateRow(table.Rows.IndexOf(row) + cellIndex.Item1);
                    for (int c = 0; c < table.Columns.Count; c++)
                        eRow.CreateCell(c).SetCellValue(row[c].ToString());
                }

                fs = File.OpenWrite(fileName);
                workBook.Write(fs);
            }
            catch (Exception e)
            {

                throw e;
            }
            finally
            {
                if (fs != null) fs.Close();
            }
        }

        /// <summary>
        /// 编辑Excel文档
        /// </summary>
        /// <param name="fileName">文件全名</param>
        /// <param name="sheetName">工作表名</param>
        /// <param name="table">数据表DataTable</param>
        public static void EditExcel(string fileName, string sheetName, DataTable table)
        {
            EditExcel(fileName, sheetName, table, "A1");
        }

        #endregion

        #region 通用读取

        /// <summary>
        /// 读取Excel文档
        /// </summary>
        /// <param name="fileName">文件全名</param>
        /// <param name="sheetName">工作表名</param>
        /// <param name="startCell">起始单元格 如 "A1"，缺省填 ""或 null</param>
        /// <param name="endCell">终止单元格 如 "A1"，缺省填 ""或 null</param>
        /// <returns>读取结果</returns>
        public static DataTable ReadExcel(string fileName, string sheetName, string startCell,string endCell,bool evaluateAll)
        {
            DataTable table = null;

            if (!File.Exists(fileName))
                throw new Exception("文件不存在");

            IWorkbook workBook = null;
            ISheet sheet = null;
            FileStream fs = null;

            try
            {
                fs=File.OpenRead(fileName);
                workBook = new HSSFWorkbook(fs);
                sheet = workBook.GetSheet(sheetName);

                if (sheet == null)
                    throw new Exception("工作表不存在");
                if (evaluateAll)//2013-9-6 重计算
                {
                    //HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(workBook);
                    //eva.EvaluateAll();
                    EvaluateSheet(workBook, sheet);//2013-10-14 逐个重计算
                }
                table = ReadSheet(sheet, startCell, endCell);
            }
            catch (Exception e)
            {

                throw e;
            }
            finally
            {
                if (fs != null) fs.Close();
            }

            return table;
        }

        /// <summary>
        /// 读取Excel文档
        /// </summary>
        /// <param name="fileName">文件全名</param>
        /// <param name="sheetName">工作表名</param>
        /// <returns>读取结果</returns>
        public static DataTable ReadExcel(string fileName, string sheetName)
        {
            return ReadExcel(fileName, sheetName, "", "",true);
        }

        /// <summary>
        /// 读取Excel文档
        /// </summary>
        /// <param name="fileName">文件全名</param>
        /// <param name="sheetIndex">工作表索引</param>
        /// <param name="startCell">起始单元格 如 "A1"，缺省填 ""或 null</param>
        /// <param name="endCell">终止单元格 如 "A1"，缺省填 ""或 null</param>
        /// <returns>读取结果</returns>
        public static DataTable ReadExcel(string fileName, int sheetIndex, string startCell, string endCell, bool evaluateAll)
        {
            DataTable table = null;

            if (!File.Exists(fileName))
                throw new Exception("文件不存在");

            IWorkbook workBook = null;
            ISheet sheet = null;
            FileStream fs = null;

            try
            {
                fs = File.OpenRead(fileName);
                workBook = new HSSFWorkbook(fs);
                sheet = workBook.GetSheetAt(sheetIndex);

                if (sheet == null)
                    throw new Exception("工作表不存在");
                if (evaluateAll)//2013-9-6 重计算
                {
                    //HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(workBook);
                    //eva.EvaluateAll();
                    EvaluateSheet(workBook, sheet);//2013-10-14 逐个重计算
                }
                table = ReadSheet(sheet, startCell, endCell);
            }
            catch (Exception e)
            {

                throw e;
            }
            finally
            {
                if (fs != null) fs.Close();
            }

            return table;
        }

        /// <summary>
        /// 读取Excel文档
        /// </summary>
        /// <param name="fileName">文件全名</param>
        /// <param name="sheetIndex">工作表索引</param>
        /// <returns>读取结果</returns>
        public static DataTable ReadExcel(string fileName, int sheetIndex)
        {
            return ReadExcel(fileName, sheetIndex,"","",true);
        }

        /// <summary>
        /// 读取Excel文档
        /// </summary>
        /// <param name="fileName">文件全名</param>
        /// <returns>读取结果</returns>
        public static DataTable ReadExcel(string fileName)
        {
            return ReadExcel(fileName, 0);
        }

        #endregion

        #endregion

        #region 内部辅助方法

        /// <summary>
        /// 检查文件是否存在，若不存在则会先确保文件所在的目录存在
        /// </summary>
        /// <param name="fileName">文件名</param>
        /// <returns>检查结果</returns>
        private static bool IsExistFile(string fileName)
        {
            if (File.Exists(fileName)) return true;
            string path = fileName.Substring(0, fileName.LastIndexOf('\\') + 1).Trim('\\');
            if (!Directory.Exists(path))
                Directory.CreateDirectory(path);
            return false;
        }

        /// <summary>
        /// 转换单元格位置
        /// </summary>
        /// <param name="cell">单元格位置</param>
        /// <returns>int二元组</returns>
        private static Tuple<int, int> ConvertCell(string cell)
        {
            Match colM = Regex.Match(cell, @"[a-zA-Z]+");
            if (string.IsNullOrEmpty(colM.Value))
                throw new Exception("单元格格式有误！");
            string colStr = colM.Value.ToUpper();
            int colIndex = 0;
            foreach (char ci in colStr)
                colIndex += ci - 'A';
            //colIndex += 1 + (ci - 'A');

            Match rowM = Regex.Match(cell, @"\d+");
            if (string.IsNullOrEmpty(rowM.Value))
                throw new Exception("单元格格式有误！");
            int rowIndex = Convert.ToInt32(rowM.Value)-1;

            Tuple<int, int> result = new Tuple<int, int>(rowIndex, colIndex);
            return result;
        }

        /// <summary>
        /// 获取工作表指定区域最宽的列数
        /// </summary>
        /// <param name="sheet">ISheet工作表对象</param>
        /// <param name="startRowIndex">开始行数</param>
        /// <param name="toRowIndex">终结行数</param>
        /// <returns>工作表最宽的列数</returns>
        private static int GetLastCell(ISheet sheet, int startRowIndex,int toRowIndex)
        {
            int result = 0;
            for (int i = startRowIndex; i < toRowIndex; i++)
            {
                IRow row = sheet.GetRow(i);
                if (row == null) continue; //2013-9-5防止空引用异常
                int temp = row.Cells.Count;
                if (temp > result) result = temp;
            }

            return result;
        }

        /// <summary>
        /// 读取工作表指定区域的内容
        /// </summary>
        /// <param name="sheet">ISheet工作表对象</param>
        /// <param name="startCell">起始单元格 </param>
        /// <param name="endCell">终止单元格</param>
        /// <returns>读取结果</returns>
        private static DataTable ReadSheet(ISheet sheet, string startCell, string endCell)
        {
            DataTable table = new DataTable();

            Tuple<int, int> sCellIndex;
            if (!string.IsNullOrEmpty(startCell))
                sCellIndex = ConvertCell(startCell);
            else
                sCellIndex = new Tuple<int, int>(0, 0);

            Tuple<int, int> eCellIndex;
            if (!string.IsNullOrEmpty(endCell))
                eCellIndex = ConvertCell(endCell);
            else
            {
                int lastIndex = sheet.LastRowNum;
                eCellIndex = new Tuple<int, int>(lastIndex, GetLastCell(sheet, sCellIndex.Item1, lastIndex));
            }

            IRow row = sheet.GetRow(sCellIndex.Item1);
            int rowIndex = 0;
            //sheet.ForceFormulaRecalculation = true;
            //while (row != null )

            for (; rowIndex <= sheet.LastRowNum;  row = sheet.GetRow(sCellIndex.Item1 + rowIndex+1),rowIndex++) //2013-9-5 rowIndex<sheet.LastRowNum 使其有效范围扩大
            {
                if (row == null)continue; 
                List<ICell> cellList = row.Cells;
                DataRow dtRow = table.Rows.Add();
                for (int i = 0; i < cellList.Count; i++)
                {
                    while (table.Columns.Count < i + 1) //2013-9-6 加一列5够的，要加到够为止
                        table.Columns.Add();

                    dtRow[cellList[ i].ColumnIndex] = TryGetCellValue(cellList[i]);//2013-9-5 获得正确的数据类型的数值
                    //2013-10-6 填到正确的列里面
                }
                //row = sheet.GetRow(sCellIndex.Item1 + rowIndex);
                //rowIndex++;
            }

            return table;
        }


        private static object TryGetCellValue(ICell cell)
        {
            try
            {
                return cell.StringCellValue;
            }
            catch { }
            try
            {
                return cell.RichStringCellValue;
            }
            catch { }
            try
            {
                return cell.NumericCellValue;
            }
            catch { }
            try
            {
                return cell.DateCellValue;
            }
            catch { }
            return cell;
        }

        /// <summary>
        /// 重计算工作表
        /// </summary>
        /// <param name="workBook"></param>
        /// <param name="sheet"></param>
        private static void EvaluateSheet(IWorkbook workBook, ISheet sheet)
        {
            HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(workBook);
            IRow row;
            List<ICell> cellList;
            for (int i = 0; i < sheet.LastRowNum; i++)
            {
                row = sheet.GetRow(i);
                if (row == null) continue;
                cellList = row.Cells;
                foreach (ICell cell in cellList)
                {
                    try
                    {
                        eva.EvaluateInCell(cell);
                    }
                    catch { }
                }
            }
        }

        #endregion
    }

上述代码在经网友指出错误后更改，还有本人在使用过程对功能欠缺的作了一些补充