1 #region NPOI 操作 Excel 2007  2         /// <summary>    3         /// 将Excel文件中的数据读出到DataTable中(xlsx)    4         /// </summary>    5         /// <param name="file"></param>    6         /// <returns></returns>    7         public static DataTable ExcelToTableForXLSX(string filePath)  8         {  9             DataTable dt = new DataTable(); 10             using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read)) 11             { 12                 XSSFWorkbook xssfworkbook = new XSSFWorkbook(fs); 13                 ISheet sheet = xssfworkbook.GetSheetAt(0); 14  15                 //表头   16                 IRow header = sheet.GetRow(sheet.FirstRowNum); 17                 List<int> columns = new List<int>(); 18                 for (int i = 0; i < header.LastCellNum; i++) 19                 { 20                     object obj = GetValueTypeForXLSX(header.GetCell(i) as XSSFCell); 21                     if (obj == null || obj.ToString() == string.Empty) 22                     { 23                         dt.Columns.Add(new DataColumn("Columns" + i.ToString())); 24                         //continue;   25                     } 26                     else 27                         dt.Columns.Add(new DataColumn(obj.ToString())); 28                     columns.Add(i); 29                 } 30                 //数据   31                 for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++) 32                 { 33                     DataRow dr = dt.NewRow(); 34                     bool hasValue = http://www.mamicode.com/false; 35                     foreach (int j in columns) 36                     { 37                         dr[j] = GetValueTypeForXLSX(sheet.GetRow(i).GetCell(j) as XSSFCell); 38                         if (dr[j] != null && dr[j].ToString() != string.Empty) 39                         { 40                             hasValue = http://www.mamicode.com/true; 41                         } 42                     } 43                     if (hasValue) 44                     { 45                         dt.Rows.Add(dr); 46                     } 47                 } 48             } 49             return dt; 50         } 51  52         /// <summary>   53         /// 将DataTable数据导出到Excel文件中(xlsx)   54         /// </summary>   55         /// <param name="dt"></param>   56         /// <param name="file"></param>   57         public static void TableToExcelForXLSX(DataTable dt, string file) 58         { 59             XSSFWorkbook xssfworkbook = new XSSFWorkbook(); 60             ISheet sheet = xssfworkbook.CreateSheet("Test"); 61  62             //表头   63             IRow row = sheet.CreateRow(0); 64             for (int i = 0; i < dt.Columns.Count; i++) 65             { 66                 ICell cell = row.CreateCell(i); 67                 cell.SetCellValue(dt.Columns[i].ColumnName); 68             } 69  70             //数据   71             for (int i = 0; i < dt.Rows.Count; i++) 72             { 73                 IRow row1 = sheet.CreateRow(i + 1); 74                 for (int j = 0; j < dt.Columns.Count; j++) 75                 { 76                     ICell cell = row1.CreateCell(j); 77                     cell.SetCellValue(dt.Rows[i][j].ToString()); 78                 } 79             } 80  81             //转为字节数组   82             MemoryStream stream = new MemoryStream(); 83             xssfworkbook.Write(stream); 84             var buf = stream.ToArray(); 85  86             //保存为Excel文件   87             using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write)) 88             { 89                 fs.Write(buf, 0, buf.Length); 90                 fs.Flush(); 91             } 92         } 93  94         /// <summary>   95         /// 获取单元格类型(xlsx)   96         /// </summary>   97         /// <param name="cell"></param>   98         /// <returns></returns>   99         private static object GetValueTypeForXLSX(XSSFCell cell)100         {101             if (cell == null)102                 return null;103             switch (cell.CellType)104             {105                 case CellType.Blank: //BLANK:  106                     return null;107                 case CellType.Boolean: //BOOLEAN:  108                     return cell.BooleanCellValue;109                 case CellType.Numeric: //NUMERIC:  110                     return cell.NumericCellValue;111                 case CellType.String: //STRING:  112                     return cell.StringCellValue;113                 case CellType.Error: //ERROR:  114                     return cell.ErrorCellValue;115                 case CellType.Formula: //FORMULA:  116                 default:117                     return "=" + cell.CellFormula;118             }119         }   120         #endregion121 122         #region NPOI 操作 Excel 2003123         /// <summary>  124         /// 将Excel文件中的数据读出到DataTable中(xls)  125         /// </summary>  126         /// <param name="file"></param>  127         /// <returns></returns>  128         /// 129         public static DataTable ExcelToTableForXLS(string filePath)130         {131             DataTable dt = new DataTable();132             using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))133             {134                 HSSFWorkbook hssfworkbook = new HSSFWorkbook(fs);135                 ISheet sheet = hssfworkbook.GetSheetAt(0);136 137                 //表头  138                 IRow header = sheet.GetRow(sheet.FirstRowNum);139                 List<int> columns = new List<int>();140                 for (int i = 0; i < header.LastCellNum; i++)141                 {142                     object obj = GetValueTypeForXLS(header.GetCell(i) as HSSFCell);143                     if (obj == null || obj.ToString() == string.Empty)144                     {145                         dt.Columns.Add(new DataColumn("Columns" + i.ToString()));146                         //continue;  147                     }148                     else149                         dt.Columns.Add(new DataColumn(obj.ToString()));150                     columns.Add(i);151                 }152                 //数据  153                 for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)154                 {155                     DataRow dr = dt.NewRow();156                     bool hasValue = http://www.mamicode.com/false;157                     foreach (int j in columns)158                     {159                         dr[j] = GetValueTypeForXLS(sheet.GetRow(i).GetCell(j) as HSSFCell);160                         if (dr[j] != null && dr[j].ToString() != string.Empty)161                         {162                             hasValue = http://www.mamicode.com/true;163                         }164                     }165                     if (hasValue)166                     {167                         dt.Rows.Add(dr);168                     }169                 }170             }171             return dt;172         }173 174         /// <summary>  175         /// 将DataTable数据导出到Excel文件中(xls)  176         /// </summary>  177         /// <param name="dt"></param>  178         /// <param name="file"></param>  179         public static void TableToExcelForXLS(DataTable dt, string file)180         {181             HSSFWorkbook hssfworkbook = new HSSFWorkbook();182             ISheet sheet = hssfworkbook.CreateSheet("Test");183 184             //表头  185             IRow row = sheet.CreateRow(0);186             for (int i = 0; i < dt.Columns.Count; i++)187             {188                 ICell cell = row.CreateCell(i);189                 cell.SetCellValue(dt.Columns[i].ColumnName);190             }191 192             //数据  193             for (int i = 0; i < dt.Rows.Count; i++)194             {195                 IRow row1 = sheet.CreateRow(i + 1);196                 for (int j = 0; j < dt.Columns.Count; j++)197                 {198                     ICell cell = row1.CreateCell(j);199                     cell.SetCellValue(dt.Rows[i][j].ToString());200                 }201             }202 203             //转为字节数组  204             MemoryStream stream = new MemoryStream();205             hssfworkbook.Write(stream);206             var buf = stream.ToArray();207 208             //保存为Excel文件  209             using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))210             {211                 fs.Write(buf, 0, buf.Length);212                 fs.Flush();213             }214         }215 216         /// <summary>  217         /// 获取单元格类型(xls)  218         /// </summary>  219         /// <param name="cell"></param>  220         /// <returns></returns>  221         private static object GetValueTypeForXLS(HSSFCell cell)222         {223             if (cell == null)224                 return null;225             switch (cell.CellType)226             {227                 case CellType.Blank: //BLANK:  228                     return null;229                 case CellType.Boolean: //BOOLEAN:  230                     return cell.BooleanCellValue;231                 case CellType.Numeric: //NUMERIC:  232                     return cell.NumericCellValue;233                 case CellType.String: //STRING:  234                     return cell.StringCellValue;235                 case CellType.Error: //ERROR:  236                     return cell.ErrorCellValue;237                 case CellType.Formula: //FORMULA:  238                 default:239                     return "=" + cell.CellFormula;240             }241         } 242         #endregion

1 private string MySubString(string s)2         {3             string[] str = s.Split(‘.‘);4             string temp = str[1];5             return temp;6         }