1 class ExcelHelper : IDisposable  2     {  3         private string fileName = null; //文件名  4         private IWorkbook workbook = null;  5         private FileStream fs = null;  6         private bool disposed;  7   8         public ExcelHelper(string fileName)  9         { 10             this.fileName = fileName; 11             disposed = false; 12         } 13  14         /// <summary> 15         /// 将DataTable数据导入到excel中 16         /// </summary> 17         /// <param name="data">要导入的数据</param> 18         /// <param name="isColumnWritten">DataTable的列名是否要导入</param> 19         /// <param name="sheetName">要导入的excel的sheet的名称</param> 20         /// <returns>导入数据行数(包含列名那一行)</returns> 21         public int DataTableToExcel(DataTable data, string sheetName, bool isColumnWritten) 22         { 23             int i = 0; 24             int j = 0; 25             int count = 0; 26             ISheet sheet = null; 27  28             fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite); 29             if (fileName.IndexOf(".xlsx") > 0) // 2007版本 30                 workbook = new XSSFWorkbook(); 31             else if (fileName.IndexOf(".xls") > 0) // 2003版本 32                 workbook = new HSSFWorkbook(); 33  34             try 35             { 36                 if (workbook != null) 37                 { 38                     sheet = workbook.CreateSheet(sheetName); 39                 } 40                 else 41                 { 42                     return -1; 43                 } 44  45                 if (isColumnWritten == true) //写入DataTable的列名 46                 { 47                     IRow row = sheet.CreateRow(0); 48                     for (j = 0; j < data.Columns.Count; ++j) 49                     { 50                         row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName); 51                     } 52                     count = 1; 53                 } 54                 else 55                 { 56                     count = 0; 57                 } 58  59                 for (i = 0; i < data.Rows.Count; ++i) 60                 { 61                     IRow row = sheet.CreateRow(count); 62                     for (j = 0; j < data.Columns.Count; ++j) 63                     { 64                         row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString()); 65                     } 66                     ++count; 67                 } 68                 workbook.Write(fs); //写入到excel 69                 return count; 70             } 71             catch (Exception ex) 72             { 73                 Console.WriteLine("Exception: " + ex.Message); 74                 return -1; 75             } 76         } 77  78         /// <summary> 79         /// 将excel中的数据导入到DataTable中 80         /// </summary> 81         /// <param name="sheetName">excel工作薄sheet的名称</param> 82         /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param> 83         /// <returns>返回的DataTable</returns> 84         public DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn) 85         { 86             ISheet sheet = null; 87             DataTable data = http://www.mamicode.com/new DataTable(); 88             int startRow = 0; 89             try 90             { 91                 fs = new FileStream(fileName, FileMode.Open, FileAccess.Read); 92                 if (fileName.IndexOf(".xlsx") > 0) // 2007版本 93                     workbook = new XSSFWorkbook(fs); 94                 else if (fileName.IndexOf(".xls") > 0) // 2003版本 95                     workbook = new HSSFWorkbook(fs); 96  97                 if (sheetName != null) 98                 { 99                     sheet = workbook.GetSheet(sheetName);100                 }101                 else102                 {103                     sheet = workbook.GetSheetAt(0);104                 }105                 if (sheet != null)106                 {107                     IRow firstRow = sheet.GetRow(0);108                     int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数109 110                     if (isFirstRowColumn)111                     {112                         for (int i = firstRow.FirstCellNum; i < cellCount; ++i)113                         {114                             DataColumn column = new DataColumn(firstRow.GetCell(i).StringCellValue);115                             data.Columns.Add(column);116                         }117                         startRow = sheet.FirstRowNum + 1;118                     }119                     else120                     {121                         startRow = sheet.FirstRowNum;122                     }123 124                     //最后一列的标号125                     int rowCount = sheet.LastRowNum;126                     for (int i = startRow; i <= rowCount; ++i)127                     {128                         IRow row = sheet.GetRow(i);129                         if (row == null) continue; //没有数据的行默认是null　　　　　　　130 131                         DataRow dataRow = data.NewRow();132                         for (int j = row.FirstCellNum; j < cellCount; ++j)133                         {134                             if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null135                                 dataRow[j] = row.GetCell(j).ToString();136                         }137                         data.Rows.Add(dataRow);138                     }139                 }140 141                 return data;142             }143             catch (Exception ex)144             {145                 Console.WriteLine("Exception: " + ex.Message);146                 return null;147             }148         }149 150         public void Dispose()151         {152             Dispose(true);153             GC.SuppressFinalize(this);154         }155 156         protected virtual void Dispose(bool disposing)157         {158             if (!this.disposed)159             {160                 if (disposing)161                 {162                     if (fs != null)163                         fs.Close();164                 }165 166                 fs = null;167                 disposed = true;168             }169         }170     }