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格式的Excel
101         /// </summary>
102         /// <param name="file">文件全路径</param>
103         /// <returns>返回DaTaTable</returns>
104         public static DataTable ExcelToTableForXLS(string file)
105         {
106             try
107             {
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             try
155             {
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格式的Excel
185         /// </summary>
186         /// <param name="file">文件全路径</param>
187         /// <returns>返回DaTaTable</returns>
188         public static DataTable ExcelToTableForXLSX(string file)
189         {
190             try
191             {
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             try
238             {
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             try
274             {
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 转换为DataSet
304         /// </summary>
305         /// <param name="fileName">文件名</param>
306         /// <returns>DataSet</returns>
307         public static DataSet ExcelToDataSet(string fileName)
308         {
309             try
310             {
311                 if (!File.Exists(fileName))
312                 {
313                     throw new Exception("文件不存在");
314                 }
315                 else
316                 {
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             try
372             {
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 导出 到Excel
416         /// </summary>
417         /// <param name="ds">DataSet 表名默认为sheet名</param>
418         /// <param name="fileName">文件路径</param>
419         public static bool DataSetToExcel(DataSet ds, string fileName)
420         {
421             try
422             {
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                 else
434                 {
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 07
451         /// </summary>
452         /// <param name="ds"></param>
453         /// <returns></returns>
454         private static NPOI.XSSF.UserModel.XSSFWorkbook DataSetToXSSFWorkbook(DataSet ds)
455         {
456             try
457             {
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 03
502         /// </summary>
503         /// <param name="ds"></param>
504         /// <returns></returns>
505         private static NPOI.HSSF.UserModel.HSSFWorkbook DataSetToHSSFWordbook(DataSet ds)
506         {
507             try
508             {
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         #endregion
559     }
560 }
posted @ 2015-12-21 16:44 Langu 阅读(956) 评论(3) 编辑收藏举报
刷新页面返回顶部
NPOI 导入导出excel 支持 03 07

公告
