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