1 public class ExcelHelper
2 {
3 /// <summary>
4 /// 将Excel文件中的数据读出到DataTable中
5 /// </summary>
6 /// <param name="strFile">文件路径</param>
7 /// <returns>datatable</returns>
8 public static DataTable Excel2DataTable(string strFile, string strSheetName, string strTableName)
9 {
10 DataTable dt = new DataTable();
11 IWorkbook workbook = null;
12 using (FileStream fs = new FileStream(strFile, FileMode.Open, FileAccess.Read))
13 {
14 //office2003 HSSFWorkbook
15 //office2007 XSSFWorkbook初始化
16 workbook = new XSSFWorkbook(fs);
17 }
18 ISheet sheet = workbook.GetSheet(strSheetName);
19 //
20 dt = Export2DataTable(sheet, 0, false);
21 return dt;
22
23 }
24 /// <summary>
25 /// 将指定sheet中的数据导入到datatable中
26 /// </summary>
27 /// <param name="sheet">指定需要导出的sheet</param>
28 /// <param name="HeaderRowIndex">列头所在的行号，-1没有列头</param>
29 /// <param name="needHeader">是否需要列头</param>
30 /// <returns>DataTable</returns>
31 private static DataTable Export2DataTable(ISheet sheet, int HeaderRowIndex, bool needHeader)
32 {
33 DataTable dt = new DataTable();
34 XSSFRow headerRow = null;
35 int cellCount;
36 try
37 {
38 if (HeaderRowIndex < 0 || !needHeader)
39 {
40 headerRow = sheet.GetRow(0) as XSSFRow;
41 cellCount = headerRow.LastCellNum;
42 for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
43 {
44 DataColumn column = new DataColumn(Convert.ToString(i));
45 dt.Columns.Add(column);
46 }
47 }
48 else
49 {
50 headerRow = sheet.GetRow(HeaderRowIndex) as XSSFRow;
51 cellCount = headerRow.LastCellNum;
52 for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
53 {
54 ICell cell = headerRow.GetCell(i);
55 if (cell == null)
56 {
57 break;//到最后 跳出循环
58 }
59 else
60 {
61 //创建列
62 DataColumn column = new DataColumn(headerRow.GetCell(i).ToString());
63 //将列添加到datatable中
64 dt.Columns.Add(column);
65 }
66
67 }
68 }
69 //保存最后一行的索引
70 int intRowCount = sheet.LastRowNum;
71 for (int i = HeaderRowIndex + 1; i <= sheet.LastRowNum; i++)
72 {
73 XSSFRow xSSFRow = null;
74 if (sheet.GetRow(i) == null)
75 {
76 xSSFRow = sheet.CreateRow(i) as XSSFRow;
77 }
78 else
79 {
80 xSSFRow = sheet.GetRow(i) as XSSFRow;
81 }
82 DataRow dtRow = dt.NewRow();
83 for (int j = xSSFRow.FirstCellNum; j <= cellCount; j++)
84 {
85 //j=-1表示没有数据了
86 if (j != -1 && xSSFRow.GetCell(j) != null)
87 {
88 switch (xSSFRow.GetCell(j).CellType)
89 {
90 case CellType.Boolean:
91 dtRow[j] = Convert.ToString(xSSFRow.GetCell(j).BooleanCellValue);
92 break;
93 case CellType.Error:
94 dtRow[j] = ErrorEval.GetText(xSSFRow.GetCell(j).ErrorCellValue);
95 break;
96 case CellType.Formula:
97 switch (xSSFRow.GetCell(j).CachedFormulaResultType)
98 {
99
100 case CellType.Boolean:
101 dtRow[j] = Convert.ToString(xSSFRow.GetCell(j).BooleanCellValue);
102
103 break;
104 case CellType.Error:
105 dtRow[j] = ErrorEval.GetText(xSSFRow.GetCell(j).ErrorCellValue);
106
107 break;
108 case CellType.Numeric:
109 dtRow[j] = Convert.ToString(xSSFRow.GetCell(j).NumericCellValue);
110
111 break;
112 case CellType.String:
113 string strFORMULA = xSSFRow.GetCell(j).StringCellValue;
114 if (strFORMULA != null && strFORMULA.Length > 0)
115 {
116 dtRow[j] = strFORMULA.ToString();
117 }
118 else
119 {
120 dtRow[j] = null;
121 }
122 break;
123 default:
124 dtRow[j] = "";
125 break;
126 }
127 break;
128 case CellType.Numeric:
129 if (DateUtil.IsCellDateFormatted(xSSFRow.GetCell(j)))
130 {
131 dtRow[j] = DateTime.FromOADate(xSSFRow.GetCell(j).NumericCellValue);
132 }
133 else
134 {
135 dtRow[j] = Convert.ToDouble(xSSFRow.GetCell(j).NumericCellValue);
136 }
137 break;
138 case CellType.String:
139 string str = xSSFRow.GetCell(j).StringCellValue;
140 if (!string.IsNullOrEmpty(str))
141 {
142
143 dtRow[j] = Convert.ToString(str);
144 }
145 else
146 {
147 dtRow[j] = null;
148 }
149 break;
150 default:
151 dtRow[j] = "";
152 break;
153 }
154 }
155 else
156 {
157 break;
158 }
159 }
160 //将行加入datatable中
161 dt.Rows.Add(dtRow);
162 }
163 }
164 catch (Exception ex)
165 {
166 throw ex;
167 }
168 return dt;
169 }
170 /// <summary>
171 /// 将DataTable中的数据导入Excel文件中
172 /// </summary>
173 /// <param name="dt"></param>
174 /// <param name="strFile"></param>
175 public static void DataTable2Excel(DataTable dt, string strFile, string strSheetName)
176 {
177 IWorkbook workbook = new XSSFWorkbook();
178 ISheet sheet = workbook.CreateSheet(strSheetName);
179 IRow header = sheet.CreateRow(0);
180 for (int i = 0; i < dt.Columns.Count; i++)
181 {
182 ICell cell = header.CreateCell(i);
183 cell.SetCellValue(dt.Columns[i].ColumnName);
184 }
185 //数据
186 for (int i = 0; i < dt.Rows.Count; i++)
187 {
188 IRow row = sheet.CreateRow(i + 1);
189 for (int j = 0; j < dt.Columns.Count; j++)
190 {
191 ICell cell = row.CreateCell(j);
192 cell.SetCellValue(dt.Rows[i][j].ToString());
193 }
194 }
195 MemoryStream stream = new MemoryStream();
196 workbook.Write(stream);
197 byte[] buffer = stream.ToArray();
198 using (FileStream fs = new FileStream(strFile, FileMode.Create, FileAccess.Write))
199 {
200 fs.Write(buffer, 0, buffer.Length);
201 fs.Flush();
202 }
203 }
204 /// <summary>
205 /// 获取单元格类型
206 /// </summary>
207 /// <param name="xSSFCell">单元格</param>
208 /// <returns>object</returns>
209 private static object GetValueType(XSSFCell xSSFCell)
210 {
211 if (xSSFCell == null)
212 {
213 return null;
214 }
215 switch (xSSFCell.CellType)
216 {
217 case CellType.Blank:
218 return null;
219 case CellType.Boolean:
220 return xSSFCell.BooleanCellValue;
221 case CellType.Error:
222 return xSSFCell.ErrorCellValue;
223
224 case CellType.Numeric:
225 return xSSFCell.NumericCellValue;
226 case CellType.String:
227 return xSSFCell.StringCellValue;
228 case CellType.Formula:
229 default:
230 return "=" + xSSFCell.StringCellValue;
231 }
232 }
233
234 }
ExcelHelper