C#中NPOI操作excel之读取和写入excel数据

一、下载引用

下载需要引用的dll,即:NPOI.dll,NPOI.OOXML.dll,NPOI.OpenXml4Net.dll,ICSharpCode.SharpZipLib.dll(office2007版需要此dll)。

二、excel转datatable类

 

[csharp] view plain copy
  1. using System;  
  2. using System.Data;  
  3. using System.IO;  
  4. using NPOI.SS.UserModel;  
  5. using NPOI.XSSF.UserModel;  
  6. using NPOI.HSSF.UserModel;  
  7.   
  8. namespace NPOIOprateExcel  
  9. {  
  10.     public class ExcelUtility  
  11.     {  
  12.         /// <summary>  
  13.         /// 将excel导入到datatable  
  14.         /// </summary>  
  15.         /// <param name="filePath">excel路径</param>  
  16.         /// <param name="isColumnName">第一行是否是列名</param>  
  17.         /// <returns>返回datatable</returns>  
  18.         public static DataTable ExcelToDataTable(string filePath, bool isColumnName)  
  19.         {  
  20.             DataTable dataTable = null;  
  21.             FileStream fs = null;  
  22.             DataColumn column = null;  
  23.             DataRow dataRow = null;  
  24.             IWorkbook workbook = null;  
  25.             ISheet sheet = null;  
  26.             IRow row = null;  
  27.             ICell cell = null;  
  28.             int startRow = 0;  
  29.             try  
  30.             {  
  31.                 using (fs = File.OpenRead(filePath))  
  32.                 {  
  33.                     // 2007版本  
  34.                     if (filePath.IndexOf(".xlsx") > 0)  
  35.                         workbook = new XSSFWorkbook(fs);  
  36.                     // 2003版本  
  37.                     else if (filePath.IndexOf(".xls") > 0)  
  38.                         workbook = new HSSFWorkbook(fs);  
  39.   
  40.                     if (workbook != null)  
  41.                     {  
  42.                         sheet = workbook.GetSheetAt(0);//读取第一个sheet,当然也可以循环读取每个sheet  
  43.                         dataTable = new DataTable();  
  44.                         if (sheet != null)  
  45.                         {  
  46.                             int rowCount = sheet.LastRowNum;//总行数  
  47.                             if (rowCount > 0)  
  48.                             {  
  49.                                 IRow firstRow = sheet.GetRow(0);//第一行  
  50.                                 int cellCount = firstRow.LastCellNum;//列数  
  51.   
  52.                                 //构建datatable的列  
  53.                                 if (isColumnName)  
  54.                                 {  
  55.                                     startRow = 1;//如果第一行是列名,则从第二行开始读取  
  56.                                     for (int i = firstRow.FirstCellNum; i < cellCount; ++i)  
  57.                                     {  
  58.                                         cell = firstRow.GetCell(i);  
  59.                                         if (cell != null)  
  60.                                         {  
  61.                                             if (cell.StringCellValue != null)  
  62.                                             {  
  63.                                                 column = new DataColumn(cell.StringCellValue);  
  64.                                                 dataTable.Columns.Add(column);  
  65.                                             }  
  66.                                         }  
  67.                                     }  
  68.                                 }  
  69.                                 else  
  70.                                 {  
  71.                                     for (int i = firstRow.FirstCellNum; i < cellCount; ++i)  
  72.                                     {  
  73.                                         column = new DataColumn("column" + (i + 1));  
  74.                                         dataTable.Columns.Add(column);  
  75.                                     }  
  76.                                 }  
  77.   
  78.                                 //填充行  
  79.                                 for (int i = startRow; i <= rowCount; ++i)  
  80.                                 {  
  81.                                     row = sheet.GetRow(i);  
  82.                                     if (row == null) continue;  
  83.   
  84.                                     dataRow = dataTable.NewRow();  
  85.                                     for (int j = row.FirstCellNum; j < cellCount; ++j)  
  86.                                     {  
  87.                                         cell = row.GetCell(j);                                          
  88.                                         if (cell == null)  
  89.                                         {  
  90.                                             dataRow[j] = "";  
  91.                                         }  
  92.                                         else  
  93.                                         {  
  94.                                             //CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)  
  95.                                             switch (cell.CellType)  
  96.                                             {  
  97.                                                 case CellType.Blank:  
  98.                                                     dataRow[j] = "";  
  99.                                                     break;  
  100.                                                 case CellType.Numeric:  
  101.                                                     short format = cell.CellStyle.DataFormat;  
  102.                                                     //对时间格式(2015.12.5、2015/12/5、2015-12-5等)的处理  
  103.                                                     if (format == 14 || format == 31 || format == 57 || format == 58)  
  104.                                                         dataRow[j] = cell.DateCellValue;  
  105.                                                     else  
  106.                                                         dataRow[j] = cell.NumericCellValue;  
  107.                                                     break;  
  108.                                                 case CellType.String:  
  109.                                                     dataRow[j] = cell.StringCellValue;  
  110.                                                     break;  
  111.                                             }  
  112.                                         }  
  113.                                     }  
  114.                                     dataTable.Rows.Add(dataRow);  
  115.                                 }  
  116.                             }  
  117.                         }  
  118.                     }  
  119.                 }  
  120.                 return dataTable;  
  121.             }  
  122.             catch (Exception)  
  123.             {  
  124.                 if (fs != null)  
  125.                 {  
  126.                     fs.Close();  
  127.                 }  
  128.                 return null;  
  129.             }  
  130.         }  
  131.     }  
  132. }  

三、结果

 

 

四、写入excel类

 

[csharp] view plain copy
  1. public static bool DataTableToExcel(DataTable dt)  
  2.         {  
  3.             bool result = false;  
  4.             IWorkbook workbook = null;  
  5.             FileStream fs = null;  
  6.             IRow row = null;  
  7.             ISheet sheet = null;  
  8.             ICell cell = null;  
  9.             try  
  10.             {  
  11.                 if (dt != null && dt.Rows.Count > 0)  
  12.                 {  
  13.                     workbook = new HSSFWorkbook();  
  14.                     sheet = workbook.CreateSheet("Sheet0");//创建一个名称为Sheet0的表  
  15.                     int rowCount = dt.Rows.Count;//行数  
  16.                     int columnCount = dt.Columns.Count;//列数  
  17.   
  18.                     //设置列头  
  19.                     row = sheet.CreateRow(0);//excel第一行设为列头  
  20.                     for (int c = 0; c < columnCount; c++)  
  21.                     {  
  22.                         cell = row.CreateCell(c);  
  23.                         cell.SetCellValue(dt.Columns[c].ColumnName);  
  24.                     }                      
  25.   
  26.                     //设置每行每列的单元格,  
  27.                     for (int i = 0; i <rowCount; i++)  
  28.                     {  
  29.                         row = sheet.CreateRow(i+1);  
  30.                         for (int j = 0; j < columnCount; j++)  
  31.                         {                              
  32.                             cell = row.CreateCell(j);//excel第二行开始写入数据  
  33.                             cell.SetCellValue(dt.Rows[i][j].ToString());                              
  34.                         }  
  35.                     }  
  36.                     using (fs = File.OpenWrite(@"D:/myxls.xls"))   
  37.                     {  
  38.                         workbook.Write(fs);//向打开的这个xls文件中写入数据  
  39.                         result = true;  
  40.                     }  
  41.                 }  
  42.                 return result;  
  43.             }  
  44.             catch (Exception ex)  
  45.             {  
  46.                 if (fs != null)  
  47.                 {  
  48.                     fs.Close();  
  49.                 }  
  50.                 return false;  
  51.             }  
  52.         }  


结果如下:

 

 

源码地址:http://download.csdn.net/detail/coderk2014/9328779

posted @ 2017-09-05 13:29  Mr.石  阅读(54427)  评论(0编辑  收藏  举报