NPOI对Excel的操作（Sheet转DataTable、List<T>）
通过NPOI对Excel进行操作，这里主要是读取的操作。封装到ExcelHelper操作类中。
1 using System.Collections.Generic; 2 using NPOI.HSSF.UserModel; 3 using NPOI.SS.UserModel; 4 using NPOI.XSSF.UserModel; 5 using System.IO; 6 using System.Data; 7 using System; 8 9 namespace CommonHelper 10 { 11 public class ExcelHelper 12 { 13 14 public ExcelHelper() { } 15 16 /// <summary> 17 /// 文件流初始化对象 18 /// </summary> 19 /// <param name="stream"></param> 20 public ExcelHelper(Stream stream) 21 { 22 _IWorkbook = CreateWorkbook(stream); 23 } 24 25 /// <summary> 26 /// 传入文件名 27 /// </summary> 28 /// <param name="fileName"></param> 29 public ExcelHelper(string fileName) 30 { 31 using (FileStream fileStream = new FileStream(fileName, FileMode.Open, FileAccess.Read)) 32 { 33 _IWorkbook = CreateWorkbook(fileStream); 34 } 35 } 36 37 /// <summary> 38 /// 工作薄 39 /// </summary> 40 private IWorkbook _IWorkbook; 41 42 /// <summary> 43 /// 创建工作簿对象 44 /// </summary> 45 /// <param name="stream"></param> 46 /// <returns></returns> 47 private IWorkbook CreateWorkbook(Stream stream) 48 { 49 try 50 { 51 return new XSSFWorkbook(stream); //07 52 } 53 catch 54 { 55 return new HSSFWorkbook(stream); //03 56 } 57 58 } 59 60 /// <summary> 61 /// 把Sheet中的数据转换为DataTable 62 /// </summary> 63 /// <param name="sheet"></param> 64 /// <returns></returns> 65 private DataTable ExportToDataTable(ISheet sheet) 66 { 67 DataTable dt = new DataTable(); 68 69 //默认，第一行是字段 70 IRow headRow = sheet.GetRow(0); 71 72 //设置datatable字段 73 for (int i = headRow.FirstCellNum, len = headRow.LastCellNum; i < len; i++) 74 { 75 dt.Columns.Add(headRow.Cells[i].StringCellValue); 76 } 77 //遍历数据行 78 for (int i = (sheet.FirstRowNum + 1), len = sheet.LastRowNum + 1; i < len; i++) 79 { 80 IRow tempRow = sheet.GetRow(i); 81 DataRow dataRow = dt.NewRow(); 82 83 //遍历一行的每一个单元格 84 for (int r = 0, j = tempRow.FirstCellNum, len2 = tempRow.LastCellNum; j < len2; j++, r++) 85 { 86 87 ICell cell = tempRow.GetCell(j); 88 89 if (cell != null) 90 { 91 switch (cell.CellType) 92 { 93 case CellType.STRING: 94 dataRow[r] = cell.StringCellValue; 95 break; 96 case CellType.NUMERIC: 97 dataRow[r] = cell.NumericCellValue; 98 break; 99 case CellType.BOOLEAN: 100 dataRow[r] = cell.BooleanCellValue; 101 break; 102 default: dataRow[r] = "ERROR"; 103 break; 104 } 105 } 106 } 107 dt.Rows.Add(dataRow); 108 } 109 return dt; 110 } 111 112 /// <summary> 113 /// Sheet中的数据转换为List集合 114 /// </summary> 115 /// <param name="sheet"></param> 116 /// <param name="fields"></param> 117 /// <returns></returns> 118 private IList<T> ExportToList<T>(ISheet sheet,string[] fields) where T:class,new() 119 { 120 IList<T> list = new List<T>(); 121 122 //遍历每一行数据 123 for (int i = sheet.FirstRowNum + 1, len = sheet.LastRowNum + 1; i < len; i++) 124 { 125 T t=new T(); 126 IRow row = sheet.GetRow(i); 127 128 for (int j = 0, len2 = fields.Length; j < len2; j++) 129 { 130 ICell cell=row.GetCell(j); 131 object cellValue= null; 132 133 switch (cell.CellType) 134 { 135 case CellType.STRING: //文本 136 cellValue = cell.StringCellValue; 137 break; 138 case CellType.NUMERIC: //数值 139 cellValue =Convert.ToInt32(cell.NumericCellValue);//Double转换为int 140 break; 141 case CellType.BOOLEAN: //bool 142 cellValue = cell.BooleanCellValue; 143 break; 144 case CellType.BLANK: //空白 145 cellValue = ""; 146 break; 147 default: cellValue = "ERROR"; 148 break; 149 } 150 151 typeof(T).GetProperty(fields[j]).SetValue(t,cellValue,null); 152 } 153 list.Add(t); 154 } 155 156 return list; 157 } 158 159 /// <summary> 160 /// 获取第一个Sheet的第X行，第Y列的值。起始点为1 161 /// </summary> 162 /// <param name="X">行</param> 163 /// <param name="Y">列</param> 164 /// <returns></returns> 165 public string GetCellValue(int X, int Y) 166 { 167 ISheet sheet = _IWorkbook.GetSheetAt(0); 168 169 IRow row = sheet.GetRow(X-1); 170 171 return row.GetCell(Y-1).ToString(); 172 } 173 174 /// <summary> 175 /// 获取一行的所有数据 176 /// </summary> 177 /// <param name="X">第x行</param> 178 /// <returns></returns> 179 public string[] GetCells(int X) 180 { 181 List<string> list = new List<string>(); 182 183 ISheet sheet = _IWorkbook.GetSheetAt(0); 184 185 IRow row = sheet.GetRow(X-1); 186 187 for (int i = 0, len = row.LastCellNum; i < len; i++) 188 { 189 list.Add(row.GetCell(i).StringCellValue);//这里没有考虑数据格式转换，会出现bug 190 } 191 return list.ToArray(); 192 } 193 194 /// <summary> 195 /// 第一个Sheet数据，转换为DataTable 196 /// </summary> 197 /// <returns></returns> 198 public DataTable ExportExcelToDataTable() 199 { 200 return ExportToDataTable(_IWorkbook.GetSheetAt(0)); 201 } 202 203 /// <summary> 204 /// 第sheetIndex表数据，转换为DataTable 205 /// </summary> 206 /// <param name="sheetIndex">第几个Sheet，从1开始</param> 207 /// <returns></returns> 208 public DataTable ExportExcelToDataTable(int sheetIndex) 209 { 210 return ExportToDataTable(_IWorkbook.GetSheetAt(sheetIndex-1)); 211 } 212 213 214 /// <summary> 215 /// Excel中默认第一张Sheet导出到集合 216 /// </summary> 217 /// <param name="fields">Excel各个列，依次要转换成为的对象字段名称</param> 218 /// <returns></returns> 219 public IList<T> ExcelToList<T>(string[] fields) where T:class,new () 220 { 221 return ExportToList<T>(_IWorkbook.GetSheetAt(0),fields); 222 } 223 224 /// <summary> 225 /// Excel中指定的Sheet导出到集合 226 /// </summary> 227 /// <param name="sheetIndex">第几张Sheet,从1开始</param> 228 /// <param name="fields">Excel各个列，依次要转换成为的对象字段名称</param> 229 /// <returns></returns> 230 public IList<T> ExcelToList<T>(int sheetIndex,string[] fields) where T:class,new() 231 { 232 return ExportToList<T>(_IWorkbook.GetSheetAt(sheetIndex-1), fields); 233 } 234 235 } 236 }
