C# NPOI 操作Excel helper

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Linq;
  4 using System.Text;
  5 using NPOI.SS.UserModel;
  6 using System.IO;
  7 using System.Data;
  8 using NPOI.HSSF.UserModel;
  9 using NPOI.XSSF.UserModel;
 10 
 11 namespace winNpoiExcel
 12 {
 13     public class NPOIHelper : IDisposable
 14     {
 15         private string fileName = null; //文件名
 16         private IWorkbook workbook = null;
 17         private FileStream fs = null;
 18         private bool disposed;
 19 
 20         public NPOIHelper()
 21         { 
 22             disposed = false;
 23         }
 24 
 25         /// <summary>
 26         /// 将DataTable数据导入到excel中
 27         /// </summary>
 28         /// <param name="data">要导入的数据</param>
 29         /// <param name="isColumnWritten">DataTable的列名是否要导入</param>
 30         /// <param name="sheetName">要导入的excel的sheet的名称</param>
 31         /// <returns>导入数据行数(包含列名那一行)</returns>
 32         public int DataTableToExcel(DataTable data, string sheetName, bool isColumnWritten,string fileName)
 33         {
 34             int i = 0;
 35             int j = 0;
 36             int count = 0;
 37             ISheet sheet = null;
 38 
 39             fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
 40             if (fileName.IndexOf(".xlsx") > 0) // 2007版本
 41                 workbook = new XSSFWorkbook();
 42             else if (fileName.IndexOf(".xls") > 0) // 2003版本
 43                 workbook = new HSSFWorkbook();
 44 
 45             try
 46             {
 47                 if (workbook != null)
 48                 {
 49                     sheet = workbook.CreateSheet(sheetName);
 50                 }
 51                 else
 52                 {
 53                     return -1;
 54                 }
 55 
 56                 if (isColumnWritten == true) //写入DataTable的列名
 57                 {
 58                     IRow row = sheet.CreateRow(0);
 59                     for (j = 0; j < data.Columns.Count; ++j)
 60                     {
 61                         row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
 62                     }
 63                     count = 1;
 64                 }
 65                 else
 66                 {
 67                     count = 0;
 68                 }
 69 
 70                 for (i = 0; i < data.Rows.Count; ++i)
 71                 {
 72                     IRow row = sheet.CreateRow(count);
 73                     for (j = 0; j < data.Columns.Count; ++j)
 74                     {
 75                         row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
 76                     }
 77                     ++count;
 78                 }
 79                 workbook.Write(fs); //写入到excel
 80                 return count;
 81             }
 82             catch (Exception ex)
 83             {
 84                 Console.WriteLine("Exception: " + ex.Message);
 85                 return -1;
 86             }
 87         }
 88 
 89         /// <summary>
 90         /// 将excel中的数据导入到DataTable中
 91         /// </summary>
 92         /// <param name="sheetName">excel工作薄sheet的名称</param>
 93         /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
 94         /// <returns>返回的DataTable</returns>
 95         public DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn, string fileName)
 96         {
 97             ISheet sheet = null;
 98             DataTable data = new DataTable();
 99             int startRow = 0;
100             try
101             {
102                 fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
103                 if (fileName.IndexOf(".xlsx") > 0) // 2007版本
104                     workbook = new XSSFWorkbook(fs);
105                 else if (fileName.IndexOf(".xls") > 0) // 2003版本
106                     workbook = new HSSFWorkbook(fs);
107 
108                 if (sheetName != null)
109                 {
110                     sheet = workbook.GetSheet(sheetName);
111                     if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
112                     {
113                         sheet = workbook.GetSheetAt(0);
114                     }
115                 }
116                 else
117                 {
118                     sheet = workbook.GetSheetAt(0);
119                 }
120                 if (sheet != null)
121                 {
122                     IRow firstRow = sheet.GetRow(0);
123                     int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
124 
125                     if (isFirstRowColumn)
126                     {
127                         for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
128                         {
129                             ICell cell = firstRow.GetCell(i);
130                             if (cell != null)
131                             {
132                                 string cellValue = cell.StringCellValue;
133                                 if (cellValue != null)
134                                 {
135                                     DataColumn column = new DataColumn(cellValue);
136                                     data.Columns.Add(column);
137                                 }
138                             }
139                         }
140                         startRow = sheet.FirstRowNum + 1;
141                     }
142                     else
143                     {
144                         startRow = sheet.FirstRowNum;
145                     }
146 
147                     //最后一列的标号
148                     int rowCount = sheet.LastRowNum;
149                     for (int i = startRow; i <= rowCount; ++i)
150                     {
151                         IRow row = sheet.GetRow(i);
152                         if (row == null) continue; //没有数据的行默认是null　　　　　　　
153 
154                         DataRow dataRow = data.NewRow();
155                         for (int j = row.FirstCellNum; j < cellCount; ++j)
156                         {
157                             if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
158                                 dataRow[j] = row.GetCell(j).ToString();
159                         }
160                         data.Rows.Add(dataRow);
161                     }
162                 }
163 
164                 return data;
165             }
166             catch (Exception ex)
167             {
168                 Console.WriteLine("Exception: " + ex.Message);
169                 return null;
170             }
171         }
172 
173         public void Dispose()
174         {
175             Dispose(true);
176             GC.SuppressFinalize(this);
177         }
178 
179         protected virtual void Dispose(bool disposing)
180         {
181             if (!this.disposed)
182             {
183                 if (disposing)
184                 {
185                     if (fs != null)
186                         fs.Close();
187                 }
188 
189                 fs = null;
190                 disposed = true;
191             }
192         }
193     }
194 }

 1 try
 2             {
 3                 DataTable dt;
 4                 string filename = @"E:\1.xls";
 5               //  string filename = @"E:\1.xlsx";
 6                 using (NPOIHelper excelHelper = new NPOIHelper())
 7                 {
 8                     dt = excelHelper.ExcelToDataTable("MySheet", true, filename);
 9 
10                 }
11                 filename = @"E:\2.xls";
12                 using (NPOIHelper excelHelper = new NPOIHelper())
13                 {
14                     int count = excelHelper.DataTableToExcel(dt, "苏上话", true, filename);
15                 }
16             }
17             catch (Exception ex)
18             {
19                 Console.WriteLine("Exception: " + ex.Message);
20             }