ExcellHelper 帮助类 2007和2003 任意转换

最近的一些excel的，命名不规范。多选类型
fileDialog.Filter = "所有文档(*.doc;*.docx)|*.doc;*.docx|所有文件(*.*)|*.*";
所以改进了一下这个类
  1 using NPOI.HSSF.UserModel;
  2 using NPOI.SS.UserModel;
  3 using NPOI.XSSF.UserModel;
  4 using System;
  5 using System.Collections.Generic;
  6 using System.Data;
  7 using System.IO;
  8 using System.Linq;
  9 using System.Text;
 10 
 11 namespace NPOI.EXCELL
 12 {
 13     public class Excell
 14     {
 15         public DataTable ExcelsToDataTable(string filepath, string sheetname, bool isFirstRowColumn)
 16         {
 17            return ExcelToDataTable( filepath,  sheetname,  isFirstRowColumn);
 18         }
 19         public int DataTableToExcels(DataTable data, string filepath, string sheename, bool iscolumwrite)
 20         {
 21             return DataTableToExcel(data,filepath, sheename, iscolumwrite);
 22         }
 23         /// <summary>
 24         /// 将Excel导入DataTable
 25         /// </summary>
 26         /// <param name="filepath">导入的文件路径（包括文件名）</param>
 27         /// <param name="sheetname">工作表名称</param>
 28         /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
 29         /// <returns>DataTable</returns>
 30         public DataTable ExcelToDataTable(string filepath, string sheetname, bool isFirstRowColumn)
 31         {
 32             ISheet sheet = null;//工作表
 33             DataTable data = new DataTable();
 34             FileStream fs;
 35             IWorkbook workbook = null;
 36             var startrow = 0;
 37             using (fs = new FileStream(filepath, FileMode.Open, FileAccess.Read))
 38             {
 39                 try
 40                 {
 41              
 42                  
 43                     try
 44                     {
 45                         if (filepath.IndexOf(".xlsx") > 0) // 2007版本
 46                             workbook = new XSSFWorkbook(fs);
 47                         else if (filepath.IndexOf(".xls") > 0) // 2003版本
 48                             workbook = new HSSFWorkbook(fs);
 49                     }
 50                     catch
 51                     {
 52                     }
 53 
 54                     try
 55                     {
 56                         if (workbook == null)
 57                         {
 58                             using (fs = new FileStream(filepath, FileMode.Open, FileAccess.Read)) { 
 59                                 workbook = new XSSFWorkbook(fs);
 60                             }
 61                         }
 62                     }
 63                     catch { 
 64                     }
 65                     try
 66                     {
 67                         if (workbook == null)
 68                         {
 69                             using (fs = new FileStream(filepath, FileMode.Open, FileAccess.Read))
 70                             {
 71                                 workbook = new HSSFWorkbook(fs);
 72                             }
 73                         }
 74                     }
 75                     catch {
 76                     }
 77 
 78 
 79                     if (sheetname != null)
 80                     {
 81                         sheet = workbook.GetSheet(sheetname);
 82                         if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
 83                         {
 84                             sheet = workbook.GetSheetAt(0);
 85                         }
 86                     }
 87                     else
 88                     {
 89                         sheet = workbook.GetSheetAt(0);
 90                     }
 91                     if (sheet != null)
 92                     {
 93                         IRow firstrow = sheet.GetRow(0);
 94                         int cellCount = firstrow.LastCellNum; //行最后一个cell的编号 即总的列数
 95                         if (isFirstRowColumn)
 96                         {
 97                             for (int i = firstrow.FirstCellNum; i < cellCount; i++)
 98                             {
 99                                 ICell cell = firstrow.GetCell(i);
100                                 if (cell != null)
101                                 {
102                                     string cellvalue = cell.StringCellValue;
103                                     if (cellvalue != null)
104                                     {
105                                         DataColumn column = new DataColumn(cellvalue);
106                                         data.Columns.Add(column);
107                                     }
108                                 }
109                             }
110                             startrow = sheet.FirstRowNum + 1;
111                         }
112                         else
113                         {
114                             startrow = sheet.FirstRowNum;
115                         }
116                         //读数据行
117                         int rowcount = sheet.LastRowNum;
118                         for (int i = startrow; i < rowcount; i++)
119                         {
120                             IRow row = sheet.GetRow(i);
121                             if (row == null)
122                             {
123                                 continue; //没有数据的行默认是null
124                             }
125                             DataRow datarow = data.NewRow();//具有相同架构的行
126                             for (int j = row.FirstCellNum; j < cellCount; j++)
127                             {
128                                 if (row.GetCell(j) != null)
129                                 {
130                                     datarow[j] = row.GetCell(j).ToString();
131                                 }
132                             }
133                             data.Rows.Add(datarow);
134                         }
135                     }
136                     return data;
137                 }
138                 catch (Exception ex)
139                 {
140                     Console.WriteLine("Exception: " + ex.Message);
141                     return null;
142                 }
143                 finally { fs.Close(); fs.Dispose(); }
144             }
145         }
146 
147         /// <summary>
148         /// 将DataTable导入到Excel
149         /// </summary>
150         /// <param name="data">要导入的数据</param>
151         /// <param name="filepath">导入的文件路径（包含文件名称）</param>
152         /// <param name="sheename">要导入的表名</param>
153         /// <param name="iscolumwrite">是否写入列名</param>
154         /// <returns>导入Excel的行数</returns>
155         public int DataTableToExcel(DataTable data, string filepath, string sheename, bool iscolumwrite)
156         {
157             int i = 0;
158             int j = 0;
159             int count = 0;
160             FileStream fs;
161             IWorkbook workbook=null;
162             ISheet sheet = null;
163             using (fs = new FileStream(filepath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
164             {
165                 //根据Excel不同版本实例不同工作铺
166                 if (filepath.IndexOf(".xlsx") > 0) // 2007版本
167                 {
168                     workbook = new XSSFWorkbook();
169                 }
170                 else if (filepath.IndexOf(".xls") > 0) // 2003版本
171                     workbook = new HSSFWorkbook();
172 
173                 try
174                 {
175                     if (workbook != null)
176                     {
177                         sheet = workbook.CreateSheet(sheename);
178                     }
179                     else
180                     {
181                         return -1;
182                     }
183 
184                     if (iscolumwrite == true) //写入DataTable的列名
185                     {
186                         IRow row = sheet.CreateRow(0);
187                         for (j = 0; j < data.Columns.Count; ++j)
188                         {
189                             row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
190                         }
191                         count = 1;
192                     }
193                     else
194                     {
195                         count = 0;
196                     }
197 
198                     for (i = 0; i < data.Rows.Count; ++i)
199                     {
200                         IRow row = sheet.CreateRow(count);
201                         for (j = 0; j < data.Columns.Count; ++j)
202                         {
203                             row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
204                         }
205                         count++;
206                     }
207                     workbook.Write(fs); //写入到excel
208                     return count;
209                 }
210                 catch (Exception ex)
211                 {
212                     Console.WriteLine("Exception: " + ex.Message);
213                     return -1;
214                 }
215                 finally { fs.Close(); fs.Dispose(); }
216             }
217         }
218 
219 
220     }
221 }
View Code
Excell h = new Excell();
DataTable de = h.ExcelsToDataTable(@"a0016.xls", "Sheet1", true);
//a0016.xls 这个实际是 2007 的导致了代码报错