【原创】WinForm操作EXCEL（第三方插件NPOI）

根据上一章的 WinForm导出数据到EXCEL（根据微软的Excel插件） 中说了一些问题，通过微软的Excel插件来导入到Excel，不过在布置时发现服务器必须安装office组件才可以应用，好像并不方便，于是想到不用微软的Excel插件实现上一章的功能，该如何实现呢？

今天我就用第三方插件NPOI来实现其功能

（1）插件的下载

可以到正式官网去上载，我这里给出这些组件[NPOI组件下载]

（2）命名空间引用

using NPOI;
using NPOI.HPSF;
using NPOI.HSSF;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.POIFS;
using NPOI.Util;

（3）代码实现

  1    /// <summary>
  2         /// 导出Excel文件
  3         /// </summary>
  4         /// <param name="dt">要导入到Excel的数据</param>
  5         public static string DataTableToExcel(System.Data.DataTable dt)
  6         {
  7             string msg = "";
  8             string strFilePath = System.AppDomain.CurrentDomain.BaseDirectory + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";
  9             HSSFWorkbook workbook = new HSSFWorkbook();
 10             try
 11             {
 12                 int MaxRowCount = 60000;
 13 
 14 
 15                 int rowCount = dt.Rows.Count;
 16                 int colCount = dt.Columns.Count;
 17 
 18 
 19                 if (rowCount > 0 && rowCount <= MaxRowCount)
 20                 {
 21                     HSSFSheet sheet = workbook.CreateSheet("总信息");
 22 
 23                     HSSFRow dataRowColumn = sheet.CreateRow(0);
 24                     for (int j = 0; j < colCount; j++)
 25                     {
 26                         HSSFCell newCell = dataRowColumn.CreateCell(j);
 27                         newCell.SetCellValue(dt.Columns[j].ColumnName.ToString());
 28                     }
 29 
 30                     for (int j = 0; j < rowCount; j++)
 31                     {
 32                         HSSFRow dataRow = sheet.CreateRow(j + 1);
 33                         for (int k = 0; k < colCount; k++)
 34                         {
 35                             HSSFCell newCell = dataRow.CreateCell(k);
 36                             newCell.SetCellValue(dt.Rows[j][k].ToString());
 37                         }
 38                     }
 39                 }
 40                 else //超过sheet表单的就再创适sheet表单
 41                 {
 42                     int sheetCount = 1; //sheet表单个数
 43                     if (rowCount % MaxRowCount == 0)
 44                     {
 45                         sheetCount = rowCount / MaxRowCount;
 46                     }
 47                     else
 48                     {
 49                         sheetCount = rowCount / MaxRowCount + 1;
 50                     }
 51 
 52                     int Flag = 1;
 53                     for (var m = 0; m < sheetCount; m++)
 54                     {
 55               
 56                         HSSFSheet sheet = workbook.CreateSheet("第" + (m + 1) + "页数据");
 57 
 58                  
 59                         if (Flag == sheetCount && (rowCount % MaxRowCount != 0))
 60                         {
 61                             int newrowCount = rowCount - ((Flag - 1) * MaxRowCount); 
 62 
 63 
 64                             int RowIndex = 0;
 65 
 66                             HSSFRow dataRowColumn = sheet.CreateRow(0);
 67                             for (int j = 0; j < colCount; j++)
 68                             {
 69                                 HSSFCell newCell = dataRowColumn.CreateCell(j);
 70                                 newCell.SetCellValue(dt.Columns[j].ColumnName.ToString());
 71                             }
 72 
 73 
 74 
 75                             int startIndex = (Flag - 1) * MaxRowCount;
 76                             for (int n = startIndex; n < startIndex + newrowCount; n++)
 77                             {
 78                                 HSSFRow dataRow = sheet.CreateRow(RowIndex + 1);
 79                                 for (int t = 0; t < colCount; t++)
 80                                 {
 81                                     HSSFCell newCell = dataRow.CreateCell(t);
 82                                     newCell.SetCellValue(dt.Rows[n][t].ToString());
 83                                 }
 84                                                               
 85 
 86                                 RowIndex++;
 87                             }
 88 
 89                         }
 90                         else
 91                         {
 92                             HSSFRow dataRowColumn = sheet.CreateRow(0);
 93                             for (int j = 0; j < colCount; j++)
 94                             {
 95                                 HSSFCell newCell = dataRowColumn.CreateCell(j);
 96                                 newCell.SetCellValue(dt.Columns[j].ColumnName.ToString());
 97                             }
 98                             int startIndex = (Flag - 1) * MaxRowCount;
 99                             int rowIndex = 0;
100                             for (int n = startIndex; n < startIndex + MaxRowCount; n++)
101                             {
102                                 HSSFRow dataRow = sheet.CreateRow(rowIndex + 1);
103                                 for (int t = 0; t < colCount; t++)
104                                 {
105                                     HSSFCell newCell = dataRow.CreateCell(t);
106                                     newCell.SetCellValue(dt.Rows[n][t].ToString());
107                                 }
108                                 rowIndex++;
109                             }
110 
111                         }
112                         Flag++;
113                     }
114 
115                 }
116                 FileStream file = new FileStream(strFilePath, FileMode.Create);
117                 workbook.Write(file);
118                 msg = "生成成功：请到文件" + strFilePath + "下取得该文件";
119                 file.Close();         
120 
121             }
122             catch
123             {
124                 workbook.Dispose();
125                 GC.Collect();
126                 return "出现异常";
127             }
128             finally
129             {
130 
131                 workbook.Dispose();
132                 GC.Collect();
133             }
134 
135             return msg;
136         }

代码完毕。

关于NPOI的相关知识我不再赘述，网上一大堆。我这里只是简单的用一些它的小知识点。

 转载的请注原创地址，谢谢。

转载于:https://www.cnblogs.com/yxhblog/archive/2012/06/06/2537642.html