1 using System;
2 using System.Collections.Generic;
3 using System.Data;
4 using System.IO;
5 using System.Linq;
6 using System.Text;
7 using NPOI.HSSF.UserModel;
8 using NPOI.SS.UserModel;
9 using NPOI.XSSF.UserModel;
10
11 namespace DMEnterpriseAdministration
12 {
13 public class Excel
14 {
15 public static MemoryStream WriteDataToExcel(DataSet ds)
16 {
17 MemoryStream memoryStream = new MemoryStream();
18
19 try
20 {
21 IWorkbook workbook = new HSSFWorkbook();
22
23 foreach (DataTable table in ds.Tables)
24 {
25 ISheet sheet = workbook.CreateSheet(table.TableName);
26 IRow headerRow = sheet.CreateRow(0);
27
28 foreach (DataColumn column in table.Columns)
29 {
30 headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
31 }
32
33 int rowIndex = 1;
34
35 foreach (DataRow row in table.Rows)
36 {
37 IRow dataRow = sheet.CreateRow(rowIndex);
38
39 foreach (DataColumn column in table.Columns)
40 {
41 dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
42 }
43
44 rowIndex++;
45 }
46
47 sheet = null;
48 headerRow = null;
49 }
50
51 workbook.Write(memoryStream);
52
53 workbook = null;
54 }
55 catch (Exception exception)
56 {
57 throw exception;
58 }
59
60 return memoryStream;
61 }
62
63 public static DataSet ExcelToDataSet(string excelPath)
64 {
65 return ExcelToDataSet(excelPath, true);
66 }
67
68 public static DataSet ExcelToDataSet(string excelPath, bool firstRowAsHeader)
69 {
70 int sheetCount;
71 return ExcelToDataSet(excelPath, firstRowAsHeader, out sheetCount);
72 }
73
74 public static DataSet ExcelToDataSet(string excelPath, bool firstRowAsHeader, out int sheetCount)
75 {
76 using (DataSet ds = new DataSet())
77 {
78 using (FileStream fileStream = new FileStream(excelPath, FileMode.Open, FileAccess.Read))
79 {
80 IWorkbook workbook = new HSSFWorkbook(fileStream);
81
82 HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(workbook);
83
84 sheetCount = workbook.NumberOfSheets;
85
86 for (int i = 0; i < sheetCount; ++i)
87 {
88 ISheet sheet = workbook.GetSheetAt(i) as HSSFSheet;
89 DataTable dt = ExcelToDataTable(sheet, evaluator, firstRowAsHeader);
90 ds.Tables.Add(dt);
91 }
92
93 return ds;
94 }
95 }
96 }
97
98 public static DataTable ExcelToDataTable(string excelPath, string sheetName)
99 {
100 return ExcelToDataTable(excelPath, sheetName, true);
101 }
102
103 public static DataTable ExcelToDataTable(string excelPath, string sheetName, bool firstRowAsHeader)
104 {
105 using (FileStream fileStream = new FileStream(excelPath, FileMode.Open, FileAccess.Read))
106 {
107 IWorkbook workbook = null;
108 IFormulaEvaluator evaluator = null;
109 ISheet sheet = null;
110 if (excelPath.EndsWith(".xls"))
111 {
112 workbook = new HSSFWorkbook(fileStream);
113 evaluator = new HSSFFormulaEvaluator(workbook);
114 sheet = workbook.GetSheet(sheetName) as HSSFSheet;
115 if (sheet == null)
116 {
117 sheet = workbook.GetSheetAt(0);
118 }
119 }
120 else
121 {
122 workbook = new XSSFWorkbook(fileStream);
123 evaluator = new XSSFFormulaEvaluator(workbook);
124 sheet = workbook.GetSheet(sheetName) as XSSFSheet;
125 if (sheet == null)
126 {
127 sheet = workbook.GetSheetAt(0);
128 }
129 }
130
131 return ExcelToDataTable(sheet, evaluator, firstRowAsHeader);
132 }
133 }
134
135 private static DataTable ExcelToDataTable(ISheet sheet, IFormulaEvaluator evaluator, bool firstRowAsHeader)
136 {
137 if (firstRowAsHeader)
138 {
139 return ExcelToDataTableFirstRowAsHeader(sheet, evaluator);
140 }
141 else
142 {
143 return ExcelToDataTable(sheet, evaluator);
144 }
145 }
146
147 private static DataTable ExcelToDataTableFirstRowAsHeader(ISheet sheet, IFormulaEvaluator evaluator)
148 {
149 using (DataTable dt = new DataTable())
150 {
151 IRow firstRow = sheet.GetRow(0) as IRow;
152 int cellCount = GetCellCount(sheet);
153
154 for (int i = 0; i < cellCount; i++)
155 {
156 if (firstRow.GetCell(i) != null)
157 {
158 dt.Columns.Add(firstRow.GetCell(i).StringCellValue ?? string.Format("F{0}", i + 1), typeof(string));
159 }
160 else
161 {
162 dt.Columns.Add(string.Format("F{0}", i + 1), typeof(string));
163 }
164 }
165
166 for (int i = 1; i <= sheet.LastRowNum; i++)
167 {
168 IRow row = sheet.GetRow(i) as IRow;
169 DataRow dr = dt.NewRow();
170 FillDataRowByHSSFRow(row, evaluator, ref dr);
171 dt.Rows.Add(dr);
172 }
173
174 dt.TableName = sheet.SheetName;
175 return dt;
176 }
177 }
178
179 private static DataTable ExcelToDataTable(ISheet sheet, IFormulaEvaluator evaluator)
180 {
181 using (DataTable dt = new DataTable())
182 {
183 if (sheet.LastRowNum != 0)
184 {
185 int cellCount = GetCellCount(sheet);
186
187 for (int i = 0; i < cellCount; i++)
188 {
189 dt.Columns.Add(string.Format("F{0}", i), typeof(string));
190 }
191
192 for (int i = 0; i < sheet.FirstRowNum; ++i)
193 {
194 DataRow dr = dt.NewRow();
195 dt.Rows.Add(dr);
196 }
197
198 for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
199 {
200 IRow row = sheet.GetRow(i) as IRow;
201 DataRow dr = dt.NewRow();
202 FillDataRowByHSSFRow(row, evaluator, ref dr);
203 dt.Rows.Add(dr);
204 }
205 }
206
207 dt.TableName = sheet.SheetName;
208 return dt;
209 }
210 }
211
212 private static void FillDataRowByHSSFRow(IRow row, IFormulaEvaluator evaluator, ref DataRow dr)
213 {
214 if (row != null)
215 {
216 for (int j = 0; j < dr.Table.Columns.Count; j++)
217 {
218 ICell cell = row.GetCell(j) as ICell;
219
220 if (cell != null)
221 {
222 switch (cell.CellType)
223 {
224 case CellType.Blank:
225 dr[j] = DBNull.Value;
226 break;
227 case CellType.Boolean:
228 dr[j] = cell.BooleanCellValue;
229 break;
230 case CellType.Numeric:
231 if (DateUtil.IsCellDateFormatted(cell))
232 {
233 dr[j] = cell.DateCellValue;
234 }
235 else
236 {
237 dr[j] = cell.NumericCellValue;
238 }
239 break;
240 case CellType.String:
241 dr[j] = cell.StringCellValue;
242 break;
243 case CellType.Error:
244 dr[j] = cell.ErrorCellValue;
245 break;
246 case CellType.Formula:
247 cell = evaluator.EvaluateInCell(cell) as ICell;
248 dr[j] = cell.ToString();
249 break;
250 default:
251 throw new NotSupportedException(string.Format("Catched unhandle CellType[{0}]", cell.CellType));
252 }
253 }
254 }
255 }
256 }
257
258 private static int GetCellCount(ISheet sheet)
259 {
260 int firstRowNum = sheet.FirstRowNum;
261
262 int cellCount = 0;
263
264 for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; ++i)
265 {
266 IRow row = sheet.GetRow(i) as IRow;
267
268 if (row != null && row.LastCellNum > cellCount)
269 {
270 cellCount = row.LastCellNum;
271 }
272 }
273
274 return cellCount;
275 }
276 }
277 }
浙公网安备 33010602011771号