通过 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
- }
来源: http://www.bubuko.com/infodetail-1857774.html