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 }
Excell h = new Excell();
DataTable de = h.ExcelsToDataTable(@"a0016.xls", "Sheet1", true);
//a0016.xls 这个实际是 2007 的 导致了代码报错
