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= http://www.mamicode.com/null;"";146                            break;147                        default: cellValue = "http://www.mamicode.com/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列的值。起始点为1161        /// </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);//这里没有考虑数据格式转换，会出现bug190            }191            return list.ToArray();192        }193 194        /// <summary>195        /// 第一个Sheet数据，转换为DataTable196        /// </summary>197        /// <returns></returns>198        public DataTable ExportExcelToDataTable() 199        {200            return ExportToDataTable(_IWorkbook.GetSheetAt(0));201        }202 203        /// <summary>204        /// 第sheetIndex表数据，转换为DataTable205        /// </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 }