你的位置:首页 > ASP.net教程

[ASP.net教程]NPOI 导入导出excel 支持 03 07

因为微软的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 = "上海金仕达卫宁软件股份有限公司";512         var si = NPOI.HPSF.PropertySetFactory.CreateSummaryInformation();513         si.Subject = "区域HIS系统自动导出";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 }