自用ExecelHelper.cs

 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 }