日韩黑丝制服一区视频播放|日韩欧美人妻丝袜视频在线观看|九九影院一级蜜桃|亚洲中文在线导航|青草草视频在线观看|婷婷五月色伊人网站|日本一区二区在线|国产AV一二三四区毛片|正在播放久草视频|亚洲色图精品一区

搜索
分享

C#中NPOI操作excel之讀取和寫入excel數(shù)據(jù)

 羊玉wngbx 2019-02-10

 http://blog.csdn.net/coderk2014/article/details/50185163

一、下載引用

下載需要引用的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，當然也可以循環(huán)讀取每個sheet  
  43.                         dataTable = new DataTable();  
  44.                         if (sheet != null)  
  45.                         {  
  46.                             int rowCount = sheet.LastRowNum;//總行數(shù)  
  47.                             if (rowCount > 0)  
  48.                             {  
  49.                                 IRow firstRow = sheet.GetRow(0);//第一行  
  50.                                 int cellCount = firstRow.LastCellNum;//列數(shù)  
  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");//創(chuàng)建一個名稱為Sheet0的表  
  15.                     int rowCount = dt.Rows.Count;//行數(shù)  
  16.                     int columnCount = dt.Columns.Count;//列數(shù)  
  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第二行開始寫入數(shù)據(jù)  
  33.                             cell.SetCellValue(dt.Rows[i][j].ToString());                              
  34.                         }  
  35.                     }  
  36.                     using (fs = File.OpenWrite(@"D:/myxls.xls"))   
  37.                     {  
  38.                         workbook.Write(fs);//向打開的這個xls文件中寫入數(shù)據(jù)  
  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

    本站是提供個人知識管理的網(wǎng)絡存儲空間，所有內(nèi)容均由用戶發(fā)布，不代表本站觀點。請注意甄別內(nèi)容中的聯(lián)系方式、誘導購買等信息，謹防詐騙。如發(fā)現(xiàn)有害或侵權內(nèi)容，請點擊一鍵舉報。
    轉藏 分享 獻花（0）

    0條評論

    發(fā)表

    請遵守用戶 評論公約

    類似文章 更多