1 using System;
2 using System.Collections.Generic;
3 using System.Linq;
4 using System.Text;
5 using System.Data;
6 using System.IO;
7 using NPOI.HSSF.UserModel;
8 using NPOI.SS.UserModel;
9
10 namespace PaperTool.DAL
11 {
12 class ExcelHelper
13 {
14 public static DataTable ExcelToDataTable(string excelPath)
15 {
16 DataTable dt = new DataTable();
17 using (Stream stream = File.OpenRead(excelPath))
18 {
19 HSSFWorkbook workbook = new HSSFWorkbook(stream);
20 ISheet sheet = workbook.GetSheetAt(0);
21 IRow rowheader = sheet.GetRow(0);
22 foreach (ICell cell in rowheader)
23 {
24 dt.Columns.Add(cell.ToString());
25 }
26 for (int i = sheet.FirstRowNum + 1; i < =sheet.LastRowNum; i++)
27 {
28 DataRow dr = dt.NewRow();
29 IRow irow = sheet.GetRow(i);
30 for (int j = irow.FirstCellNum; j < irow.LastCellNum; j++)
31 {
32 dr[j] = irow.GetCell(j).ToString();
33 }
34 dt.Rows.Add(dr);
35 }
36 return dt;
37 }
38 }
39
40 public static void WriteExecel(DataTable dt,string path)
41 {
42 HSSFWorkbook workbook = new HSSFWorkbook();
43 ISheet sheet = workbook.CreateSheet();
44 IRow rowheader = sheet.CreateRow(0);
45 for (int i = 0; i < dt.Columns.Count; i++)
46 {
47 rowheader.CreateCell(i, CellType.STRING).SetCellValue(dt.Columns[i].ColumnName);
48 }
49 for (int rowid = 0; rowid < dt.Rows.Count; rowid++)
50 {
51 DataRow row = dt.Rows[rowid];
52 IRow irow = sheet.CreateRow(rowid + 1);
53 for (int columnid = 0; columnid < dt.Columns.Count; columnid++)
54 {
55 irow.CreateCell(columnid, CellType.STRING).SetCellValue(row[columnid].ToString());
56 }
57 }
58
59 using (Stream stream = File.OpenWrite(path))
60 {
61 workbook.Write(stream);
62 }
63 }
64 }
65 }
