C# NPOI 導入與導出Excel文檔兼容xlsx, xls

 這裡使用的NPOI版本為: 2.1.3.1

官方下載地址:  http://npoi.codeplex.com/releases

版本內包含.Net 2.0 與.Net 4.0

.Net 4.0中包含文件

使用時需引用需要引用所有5個dll

使用到的引用

using NPOI.HSSF.UserModel;
 using NPOI.SS.UserModel;
 using NPOI.XSSF.UserModel;

 

還有經過自己整理的導入導出Excel代碼：

        ///  <summary> 
        /// Excel導入成Datable
         ///  </summary> 
        ///  <param name="file">導入路徑(包含文件名與擴展名)</param> 
        ///  <returns></returns> 
        public  static DataTable ExcelToTable( string file)
        {
            DataTable dt = new DataTable();
            IWorkbook workbook;
            string fileExt = Path.GetExtension(file).ToLower();
             using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
            {
                // XSSFWorkbook 適用XLSX格式，HSSFWorkbook 適用XLS格式
                if (fileExt == " .xlsx " ) { workbook = new XSSFWorkbook(fs); } else  if (fileExt == " .xls " ) { workbook = new HSSFWorkbook(fs); } else { workbook = null ; }
                 if (workbook == null ) { return  null ; }
                ISheet sheet = workbook.GetSheetAt( 0 );

                //表頭  
                IRow header = sheet.GetRow(sheet.FirstRowNum);
                List < int > columns = new List< int > ();
                 for ( int i = 0 ; i < header.LastCellNum; i++ )
                {
                    object obj = GetValueType(header.GetCell(i));
                     if (obj == null || obj.ToString() == string .Empty)
                    {
                        dt.Columns.Add( new DataColumn( " Columns " + i.ToString()));
                    }
                    else 
                        dt.Columns.Add( new DataColumn(obj.ToString()));
                    columns.Add(i);
                }
                //數據  
                for ( int i = sheet.FirstRowNum + 1 ; i <= sheet.LastRowNum; i++ )
                {
                    DataRow dr = dt.NewRow();
                     bool hasValue = false ;
                     foreach ( int j in columns)
                    {
                        dr[j] = GetValueType(sheet.GetRow(i).GetCell(j));
                         if (dr[j] != null && dr[j].ToString() != string .Empty)
                        {
                            hasValue = true ;
                        }
                    }
                    if (hasValue)
                    {
                        dt.Rows.Add(dr);
                    }
                }
            }
            return dt;
        }

        ///  <summary> 
        /// Datable導出成Excel
         ///  </summary> 
        ///  <param name="dt"></param> 
        ///  <param name="file">導出路徑(包括文件名與擴展名)</param> 
        public  static  void TableToExcel(DataTable dt, string file)
        {
            IWorkbook workbook;
            string fileExt = Path.GetExtension(file).ToLower();
             if (fileExt == " .xlsx " ) { workbook = new XSSFWorkbook(); } else  if (fileExt == " .xls " ) { workbook = new HSSFWorkbook(); } else { workbook = null ; }
             if (workbook == null ) { return ; }
            ISheet sheet = string .IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet( " Sheet1 " ) : workbook.CreateSheet(dt.TableName);

            //表頭  
            IRow row = sheet.CreateRow( 0 );
             for ( int i = 0 ; i < dt.Columns.Count; i++ )
            {
                ICell cell = row.CreateCell(i);
                cell.SetCellValue(dt.Columns[i].ColumnName);
            }

            //數據  
            for ( int i = 0 ; i < dt.Rows.Count; i++ )
            {
                IRow row1 = sheet.CreateRow(i + 1 );
                 for ( int j = 0 ; j < dt.Columns.Count; j++ )
                {
                    ICell cell = row1.CreateCell(j);
                    cell.SetCellValue(dt.Rows[i][j].ToString());
                }
            }

            //轉為字節數組  
            MemoryStream stream = new MemoryStream();
            workbook.Write(stream);
            var buf = stream.ToArray();

            //保存為Excel文件  
            using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
            {
                fs.Write(buf, 0 , buf.Length);
                fs.Flush();
            }
        }

        ///  <summary> 
        ///獲取單元格類型
        ///  </summary> 
        ///  <param name="cell"></param> 
        ///  <returns></returns> 
        private  static  object GetValueType(ICell cell)
        {
            if (cell == null )
                 return  null ;
             switch (cell.CellType)
            {
                case CellType.Blank: // BLANK:   
                    return  null ;
                 case CellType.Boolean: // BOOLEAN:   
                    return cell.BooleanCellValue;
                 case CellType.Numeric: // NUMERIC:   
                    return cell.NumericCellValue;
                 case CellType.String: // STRING:   
                    return cell.StringCellValue;
                 case CellType.Error: // ERROR:   
                    return cell.ErrorCellValue;
                 case CellType.Formula: //FORMULA:   
                default :
                     return  " = " + cell.CellFormula;
            }
        }