NPOI導入導出excel支持0307

2019-11-14 13:40:17
字體：大中小
來源：轉載
供稿：網友
因為微軟的office成本太高了，所以開發項目的時候電腦上沒安裝office,而是安裝了wps。但開發語言用的是C#,所以直接調用微軟的office組件是很方便的，但一方面慢，一方面成本高，所以從網上找到了NPOI這個開源的項目。http://npoi.codeplex.com/,引用的dll下載目錄 http://npoi.codeplex.com/downloads/get/1476595
并且封裝了通用的處理Excel 跟DataSet,DataTable的方法。方便調用
以上是代碼（當前項目是.net 2.0 下的，如果需要.net 4.0則到NPOI官網下載相應的dll就可以了）
  1 using NPOI.SS.UserModel;  2 using System;  3 using System.Collections.Generic;  4 using System.Data;  5 using System.IO;  6 using System.Text;  7   8 namespace MrLiu.Tools  9 { 10     public sealed class ExcelHelper 11     { 12         #region Excel導入 13         /// <summary> 14         /// Excel 轉換為DataTable 15         /// </summary> 16         /// <param name="file">文件路徑</param> 17         /// <param name="sheetName">Sheet名稱，如果只有一個sheet可以傳 null</param> 18         /// <returns></returns> 19         public static DataTable ExcelToDataTable(string file, string sheetName) 20         { 21             try 22             { 23                 DataTable dt = new DataTable(); 24                 using (FileStream fs = new FileStream(file, FileMode.Open, Fileaccess.Read, FileShare.Read)) 25                 { 26                     var workbook = NPOI.SS.UserModel.WorkbookFactory.Create(fs); 27                     ISheet sheet = null; 28                     if (sheetName == null) 29                     { 30                         sheet = workbook.GetSheetAt(0); 31                     } 32                     else 33                     { 34                         sheet = workbook.GetSheet(sheetName); 35                     } 36                     //列名 37                     IRow rowHead = sheet.GetRow(sheet.FirstRowNum); 38                     for (int i = 0; i < rowHead.LastCellNum; i++) 39                     { 40                         string fildName = rowHead.GetCell(i).StringCellValue; 41                         dt.Columns.Add(fildName, typeof(String)); 42                     } 43  44                     //數據 45                     for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++) 46                     { 47                         IRow row = sheet.GetRow(i); 48                         DataRow dr = dt.NewRow(); 49                         for (int j = row.FirstCellNum; j < row.LastCellNum; j++) 50                         { 51                             var cell = row.GetCell(j); 52                             dr[j] = GetValueTypeForICell(cell); 53                             if (dr[j] == null) 54                             { 55                                 dr[j] = string.Empty; 56                             } 57                         } 58                         dt.Rows.Add(dr); 59                     } 60                 } 61                 return dt; 62             } 63             catch (Exception ex) 64             { 65                 throw new Exception(ex.Message); 66             } 67         } 68         /// <summary> 69         /// Excel 導入為DataTable 70         /// </summary> 71         /// <param name="file">文件路徑</param> 72         /// <param name="extension">后續名 XLS XLSX</param> 73         /// <returns></returns> 74         public static DataTable ExcelToDataTable(string file) 75         { 76             try 77             { 78                 DataTable dt = new DataTable(); 79                 string extension = Path.GetExtension(file); 80                 if (extension.ToUpper() == ".XLS") 81                 { 82                     dt = ExcelToTableForXLS(file); 83                 } 84                 else if (extension.ToUpper() == ".XLS") 85                 { 86                     dt = ExcelToTableForXLSX(file); 87                 } 88                 else 89                 { 90                     throw new Exception("文件格式不正確"); 91                 } 92                 return dt; 93             } 94             catch (Exception ex) 95             { 96                 throw new Exception(ex.Message); 97             } 98         } 99         /// <summary>100         /// 讀取xls格式的Excel101         /// </summary>102         /// <param name="file">文件全路徑</param>103         /// <returns>返回DaTaTable</returns>104         public static DataTable ExcelToTableForXLS(string file)105         {106             try107             {108                 DataTable dt = new DataTable();109                 using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read, FileShare.Read))110                 {111                     var hssfworkbook = new NPOI.HSSF.UserModel.HSSFWorkbook(fs);112                     ISheet sheet = hssfworkbook.GetSheetAt(0);113 114                     //列名115                     IRow rowHead = sheet.GetRow(sheet.FirstRowNum);116                     for (int i = 0; i < rowHead.LastCellNum; i++)117                     {118                         string fildName = rowHead.GetCell(i).StringCellValue;119                         dt.Columns.Add(fildName, typeof(String));120                     }121 122                     //數據123                     for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)124                     {125                         IRow row = sheet.GetRow(i);126                         DataRow dr = dt.NewRow();127                         for (int j = row.FirstCellNum; j < row.LastCellNum; j++)128                         {129                             NPOI.HSSF.UserModel.HSSFCell cell = row.GetCell(j) as NPOI.HSSF.UserModel.HSSFCell;130                             dr[j] = GetValueTypeForXLS(cell);131                             if (dr[j] == null)132                             {133                                 break;134                             }135                         }136                         dt.Rows.Add(dr);137                     }138                 }139                 return dt;140             }141             catch (Exception ex)142             {143                 throw new Exception(ex.Message);144             }145         }146 147         /// <summary>148         /// 獲取單元格類型149         /// </summary>150         /// <param name="cell"></param>151         /// <returns></returns>152         PRivate static object GetValueTypeForXLS(NPOI.HSSF.UserModel.HSSFCell cell)153         {154             try155             {156                 if (cell == null)157                 {158                     return null;159                 }160                 switch (cell.CellType)161                 {162                     case CellType.Blank: //BLANK:  163                         return null;164                     case CellType.Boolean: //BOOLEAN:  165                         return cell.BooleanCellValue;166                     case CellType.Numeric: //NUMERIC:  167                         return cell.NumericCellValue;168                     case CellType.String: //STRING:  169                         return cell.StringCellValue;170                     case CellType.Error: //ERROR:  171                         return cell.ErrorCellValue;172                     case CellType.Formula: //FORMULA:  173                     default:174                         return "=" + cell.CellFormula;175                 }176             }177             catch (Exception ex)178             {179                 throw new Exception(ex.Message);180             }181         }182 183         /// <summary>184         /// 讀取xlsx格式的Excel185         /// </summary>186         /// <param name="file">文件全路徑</param>187         /// <returns>返回DaTaTable</returns>188         public static DataTable ExcelToTableForXLSX(string file)189         {190             try191             {192                 DataTable dt = new DataTable();193                 using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read, FileShare.Read))194                 {195                     var hssfworkbook = new NPOI.XSSF.UserModel.XSSFWorkbook(fs);196                     ISheet sheet = hssfworkbook.GetSheetAt(0);197 198                     //列名199                     IRow rowHead = sheet.GetRow(sheet.FirstRowNum);200                     for (int i = 0; i < rowHead.LastCellNum; i++)201                     {202                         string fildName = rowHead.GetCell(i).StringCellValue;203                         dt.Columns.Add(fildName, typeof(String));204                     }205 206                     //數據207                     for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)208                     {209                         IRow row = sheet.GetRow(i);210                         DataRow dr = dt.NewRow();211                         for (int j = row.FirstCellNum; j < row.LastCellNum; j++)212                         {213                             NPOI.HSSF.UserModel.HSSFCell cell = row.GetCell(j) as NPOI.HSSF.UserModel.HSSFCell;214                             dr[j] = GetValueTypeForXLS(cell);215                             if (dr[j] == null)216                             {217                                 break;218                             }219                         }220                         dt.Rows.Add(dr);221                     }222                 }223                 return dt;224             }225             catch (Exception ex)226             {227                 throw new Exception(ex.Message);228             }229         }230         /// <summary>  231         /// 獲取單元格類型(xlsx)  232         /// </summary>  233         /// <param name="cell"></param>  234         /// <returns></returns>  235         private static object GetValueTypeForXLSX(NPOI.XSSF.UserModel.XSSFCell cell)236         {237             try238             {239                 if (cell == null)240                 {241                     return null;242                 }243                 switch (cell.CellType)244                 {245                     case CellType.Blank: //BLANK:  246                         return null;247                     case CellType.Boolean: //BOOLEAN:  248                         return cell.BooleanCellValue;249                     case CellType.Numeric: //NUMERIC:  250                         return cell.NumericCellValue;251                     case CellType.String: //STRING:  252                         return cell.StringCellValue;253                     case CellType.Error: //ERROR:  254                         return cell.ErrorCellValue;255                     case CellType.Formula: //FORMULA:  256                     default:257                         return "=" + cell.CellFormula;258                 }259             }260             catch (Exception ex)261             {262                 throw new Exception(ex.Message);263             }264         }265 266         /// <summary>  267         /// 獲取單元格類型不定268         /// </summary>  269         /// <param name="cell"></param>  270         /// <returns></returns>  271         private static object GetValueTypeForICell(ICell cell)272         {273             try274             {275                 if (cell == null)276                 {277                     return null;278                 }279                 switch (cell.CellType)280                 {281                     case CellType.Blank: //BLANK:  282                         return null;283                     case CellType.Boolean: //BOOLEAN:  284                         return cell.BooleanCellValue;285                     case CellType.Numeric: //NUMERIC:  286                         return cell.NumericCellValue;287                     case CellType.String: //STRING:  288                         return cell.StringCellValue;289                     case CellType.Error: //ERROR:  290                         return cell.ErrorCellValue;291                     case CellType.Formula: //FORMULA:  292                     default:293                         return "=" + cell.CellFormula;294                 }295             }296             catch (Exception ex)297             {298                 throw new Exception(ex.Message);299             }300         }301 302         /// <summary>303         /// Excel 轉換為DataSet304         /// </summary>305         /// <param name="fileName">文件名</param>306         /// <returns>DataSet</returns>307         public static DataSet ExcelToDataSet(string fileName)308         {309             try310             {311                 if (!File.Exists(fileName))312                 {313                     throw new Exception("文件不存在");314                 }315                 else316                 {317                     DataSet ds = new DataSet();318                     using (FileStream reader = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite))319                     {320                         IWorkbook book = WorkbookFactory.Create(reader);321                         int cnt = book.NumberOfSheets;322                         if (cnt <= 0)323                         {324                             throw new Exception("文件不是Excel文件");325                         }326 327                         for (int i = 0; i < cnt; i++)328                         {329                             ISheet sheet = book.GetSheetAt(i);330                             DataTable dt = new DataTable(sheet.SheetName);331                             IRow rowHead = sheet.GetRow(sheet.FirstRowNum);332                             for (int j = rowHead.FirstCellNum; j < rowHead.LastCellNum; j++)333                             {334                                 ICell cell = rowHead.GetCell(j);335                                 dt.Columns.Add(cell.StringCellValue);336                             }337                             for (int j = sheet.FirstRowNum + 1; j <= sheet.LastRowNum; j++)338                             {339                                 DataRow dr = dt.NewRow();340                                 IRow row = sheet.GetRow(j);341                                 for (int k = rowHead.FirstCellNum; k < rowHead.LastCellNum; k++)342                                 {343                                     dr[k] = row.GetCell(k).StringCellValue;344                                 }345                                 dt.Rows.Add(dr);346                             }347                             ds.Tables.Add(dt);348                         }349                     }350                     return ds;351                 }352             }353             catch (Exception ex)354             {355                 throw new Exception(ex.Message);356             }357         }358         #endregion Excel導出359 360         #region Excel導出361 362         /// <summary>363         /// Excel導出364         /// </summary>365         /// <param name="dt">虛擬表</param>366         /// <param name="fileName">文件路徑</param>367         /// <param name="sheetName">Sheet路徑為空請傳null</param>368         /// <returns></returns>369         public static bool DataTableToXLS(DataTable dt, string fileName, string sheetName)370         {371             try372             {373                 if (dt == null)374                 {375                     return false;376                 }377                 if (String.IsNullOrEmpty(sheetName))378                 {379                     sheetName = Path.GetFileName(fileName);380                 }381                 var book = new NPOI.HSSF.UserModel.HSSFWorkbook();382                 book.CreateSheet();383                 var sheet = book.CreateSheet(sheetName);384 385                 IRow rowHead = sheet.CreateRow(0);386                 for (int i = 0; i < dt.Columns.Count; i++)387                 {388                     ICell cell = rowHead.CreateCell(i);389                     cell.SetCellValue(dt.Columns[i].ColumnName);390                 }391                 for (int i = 0; i < dt.Rows.Count; i++)392                 {393                     IRow row = sheet.CreateRow(i + 1);394                     for (int j = 0; j < dt.Columns.Count; j++)395                     {396                         ICell cell = row.CreateCell(j);397                         cell.SetCellValue(dt.Rows[i][j].ToString());398                     }399                 }400 401                 using (FileStream fsWriter = new FileStream(fileName, FileMode.Append, FileAccess.Write, FileShare.Write))402                 {403                     book.Write(fsWriter);404                     return true;405                 }406             }407             catch (Exception ex)408             {409                 throw new Exception(ex.Message);410             }411         }412 413        414         /// <summary>415         /// DataSet 導出 到Excel416         /// </summary>417         /// <param name="ds">DataSet 表名默認為sheet名</param>418         /// <param name="fileName">文件路徑</param>419         public static bool DataSetToExcel(DataSet ds, string fileName)420         {421             try422             {423                 String extension = Path.GetExtension(fileName).ToUpper();424                 IWorkbook book = null;425                 if (extension == ".XLS")426                 {427                     book = DataSetToHSSFWordbook(ds);428                 }429                 else if (extension == ".XLSX")430                 {431                     book = DataSetToXSSFWorkbook(ds);432                 }433                 else434                 {435                     throw new Exception("導入格式必須為xls或者xlsx");436                 }437 438                 using (FileStream fsWriter = new FileStream(fileName, FileMode.CreateNew, FileAccess.Write, FileShare.ReadWrite))439                 {440                     book.Write(fsWriter);441                     return true;442                 }443             }444             catch (Exception ex)445             {446                 throw new Exception(ex.Message);447             }448         }449         /// <summary>450         /// DataSet 轉換為 XSSFWorkbook 07451         /// </summary>452         /// <param name="ds"></param>453         /// <returns></returns>454         private static NPOI.XSSF.UserModel.XSSFWorkbook DataSetToXSSFWorkbook(DataSet ds)455         {456             try457             {458                 var book = new NPOI.XSSF.UserModel.XSSFWorkbook();459                 foreach (DataTable dt in ds.Tables)460                 {461                     ISheet sheet = book.CreateSheet(dt.TableName);462                     IRow rowHead = sheet.CreateRow(0);463                     ICellStyle style = book.CreateCellStyle();464                     style.BorderBottom = BorderStyle.Thin;465                     style.BorderTop = BorderStyle.Thin;466                     style.BorderLeft = BorderStyle.Thin;467                     style.BorderRight = BorderStyle.Thin;468                     IFont font = book.CreateFont();469                     font.FontHeightInPoints = 12;470                     font.IsBold = true;471                     style.SetFont(font);472                     for (int i = 0; i < dt.Columns.Count; i++)473                     {474                         ICell cell = rowHead.CreateCell(i);475                         cell.CellStyle = style;476                         cell.SetCellValue(dt.Columns[i].ColumnName);477                     }478                     font.IsBold = false;479                     style.SetFont(font);480                     for (int i = 0; i < dt.Rows.Count; i++)481                     {482                         IRow row = sheet.CreateRow(i + 1);483                         DataRow dr = dt.Rows[i];484                         for (int j = 0; j < dt.Columns.Count; j++)485                         {486                             ICell cell = row.CreateCell(j);487                             cell.CellStyle = style;488                             cell.SetCellValue(dr[j].ToString());489                         }490                     }491                 }492                 return book;493             }494             catch (Exception ex)495             {496                 throw new Exception(ex.Message);497             }498         }499 500         /// <summary>501         /// DataSet 轉換為 HSSFWorkbook 03502         /// </summary>503         /// <param name="ds"></param>504         /// <returns></returns>505         private static NPOI.HSSF.UserModel.HSSFWorkbook DataSetToHSSFWordbook(DataSet ds)506         {507             try508             {509                 var book = new NPOI.HSSF.UserModel.HSSFWorkbook();510                 var dsi = NPOI.HPSF.PropertySetFactory.CreateDocumentSummaryInformation();511                 dsi.Company = "xx軟件股份有限公司";512                 var si = NPOI.HPSF.PropertySetFactory.CreateSummaryInformation();513                 si.Subject = "xx系統自動導出";514                 book.DocumentSummaryInformation = dsi;515                 book.SummaryInformation = si;516 517                 foreach (DataTable dt in ds.Tables)518                 {519                     ISheet sheet = book.CreateSheet(dt.TableName);520                     IRow rowHead = sheet.CreateRow(0);521                     ICellStyle style = book.CreateCellStyle();522                     style.BorderBottom = BorderStyle.Thin;523                     style.BorderTop = BorderStyle.Thin;524                     style.BorderLeft = BorderStyle.Thin;525                     style.BorderRight = BorderStyle.Thin;526                     IFont font = book.CreateFont();527                     font.FontHeightInPoints = 12;528                     font.IsBold = true;529                     style.SetFont(font);530                     for (int i = 0; i < dt.Columns.Count; i++)531                     {532                         ICell cell = rowHead.CreateCell(i);533                         cell.CellStyle = style;534                         cell.SetCellValue(dt.Columns[i].ColumnName);535                     }536                     font.IsBold = false;537                     style.SetFont(font);538                     for (int i = 0; i < dt.Rows.Count; i++)539                     {540                         IRow row = sheet.CreateRow(i + 1);541                         DataRow dr = dt.Rows[i];542                         for (int j = 0; j < dt.Columns.Count; j++)543                         {544                             ICell cell = row.CreateCell(j);545                             cell.CellStyle = style;546                             cell.SetCellValue(dr[j].ToString());547                         }548                     }549                 }550                 return book;551             }552             catch (Exception ex)553             {554                 throw new Exception(ex.Message);555             }556         }557 558         #endregion559     }560 }