ASP.NET MVC - NPOI读取Excel

引入：

using  System ;
using  System . Data ;
using  System . IO ;
using  NPOI . SS . UserModel ;
using  NPOI . XSSF . UserModel ;
using  NPOI . HSSF . UserModel ;

namespace  NPOIOprateExcel
{
     public  class  ExcelUtility
     {
         ///   < summary >
         ///  将excel导入到datatable
         ///   </ summary >
         ///   < param  name = " filePath " > excel路径 </ param >
         ///   < param  name = " isColumnName " > 第一行是否是列名 </ param >
         ///   < returns > 返回datatable </ returns >
         public  static  DataTable  ExcelToDataTable ( string  filePath ,  bool  isColumnName )
         {
             DataTable  dataTable  =  null ;
             FileStream  fs  =  null ;
             DataColumn  column  =  null ;
             DataRow  dataRow  =  null ;
             IWorkbook  workbook  =  null ;
             ISheet  sheet  =  null ;
             IRow  row  =  null ;
             ICell  cell  =  null ;
             int  startRow  =  0 ;
             try
             {
                 using  ( fs  =  File . OpenRead ( filePath ))
                 {
                     // 2007版本
                     if  ( filePath . IndexOf ( ".xlsx" )  >  0 )
                         workbook  =  new  XSSFWorkbook ( fs );
                     // 2003版本
                     else  if  ( filePath . IndexOf ( ".xls" )  >  0 )
                         workbook  =  new  HSSFWorkbook ( fs );

                     if  ( workbook  !=  null )
                     {
                         sheet  =  workbook . GetSheetAt ( 0 ); //读取第一个sheet，也能循环读取每个sheet
                         dataTable  =  new  DataTable ();
                         if  ( sheet  !=  null )
                         {
                             int  rowCount  =  sheet . LastRowNum + 1 ; //+1 包括列名的行，总行数
                             if  ( rowCount  >  0 )
                             {
                                 IRow  firstRow  =  sheet . GetRow ( 0 ); //第一行
                                 int  cellCount  =  firstRow . LastCellNum ; //列数

                                 //构建datatable的列
                                 if  ( isColumnName )
                                 {
                                     startRow  =  0 ; //要读取列名则=0 否则=1
                                     for  ( int  i  =  firstRow . FirstCellNum ;  i  <  cellCount ;  ++ i )
                                     {
                                         cell  =  firstRow . GetCell ( i );
                                         if  ( cell  !=  null )
                                         {
                                             if  ( cell . StringCellValue  !=  null )
                                             {
                                                 column  =  new  DataColumn ( cell . StringCellValue );
                                                 dataTable . Columns . Add ( column );
                                             }
                                         }
                                     }
                                 }
                                 else
                                 {
                                     for  ( int  i  =  firstRow . FirstCellNum ;  i  <  cellCount ;  ++ i )
                                     {
                                         column  =  new  DataColumn ( "column"  +  ( i  +  1 ));
                                         dataTable . Columns . Add ( column );
                                     }
                                 }

                                 //填充行
                                 for  ( int  i  =  startRow ;  i  <  rowCount ;  ++ i )  //要读取列名则i<rowCount 否则i <= rowCount
                                 {
                                     row  =  sheet . GetRow ( i );
                                     if  ( row  ==  null )  continue ;

                                     dataRow  =  dataTable . NewRow ();
                                     for  ( int  j  =  row . FirstCellNum ;  j  <  cellCount ;  ++ j )
                                     {
                                         cell  =  row . GetCell ( j );
                                         if  ( cell  ==  null )
                                         {
                                             dataRow [ j ]  =  "" ;
                                         }
                                         else
                                         {
                                             //CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)
                                             switch  ( cell . CellType )
                                             {
                                                 case  CellType . Blank :
                                                     dataRow [ j ]  =  "" ;
                                                     break ;
                                                 case  CellType . Numeric :
                                                     short  format  =  cell . CellStyle . DataFormat ;
                                                     //对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理
                                                     if  ( format  ==  14  ||  format  ==  31  ||  format  ==  57  ||  format  ==  58 )
                                                         dataRow [ j ]  =  cell . DateCellValue ;
                                                     else
                                                         dataRow [ j ]  =  cell . NumericCellValue ;
                                                     break ;
                                                 case  CellType . String :
                                                     dataRow [ j ]  =  cell . StringCellValue ;
                                                     break ;
                                             }
                                         }
                                     }
                                     dataTable . Rows . Add ( dataRow );
                                 }
                             }
                         }
                     }
                 }
                 return  dataTable ;
             }
             catch  ( Exception )
             {
                 if  ( fs  !=  null )
                 {
                     fs . Close ();
                 }
                 return  null ;
             }
         }
     }
}

转载于:https://www.cnblogs.com/myrocknroll/p/8118290.html