NPOI操作之Excel文件的導入和導出

 1  public static DataTable ExcelToDataTable(string filePath)
 2         {
 3             DataTable dt = new DataTable();
 4 
 5             HSSFWorkbook hssfworkbook;
 6             using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
 7             {
 8                 hssfworkbook = new HSSFWorkbook(file);
 9             }
10             ISheet sheet = hssfworkbook.GetSheetAt(0);
11             System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
12 
13             IRow headerRow = sheet.GetRow(0);
14             int cellCount = headerRow.LastCellNum;
15 
16             for (int j = 0; j < cellCount; j++)
17             {
18                 ICell cell = headerRow.GetCell(j);
19                 dt.Columns.Add(cell.ToString());
20             }
21 
22             for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
23             {
24                 IRow row = sheet.GetRow(i);
25                 DataRow dataRow = dt.NewRow();
26                 if (row == null)
27                 {
28                     break;
29                 }
30                 for (int j = row.FirstCellNum; j < cellCount; j++)
31                 {
32                     if (row.GetCell(j) != null)
33                         dataRow[j] = row.GetCell(j).ToString();
34                 }
35 
36                 dt.Rows.Add(dataRow);
37             }
38             return dt;
39         }

導出到excel

 1 public static MemoryStream DataToExcel(DataTable dt)
 2         {
 3             MemoryStream ms = new MemoryStream();
 4             using (dt)
 5             {
 6                 IWorkbook workbook = new HSSFWorkbook();//創建excel工作簿
 7                 ISheet sheet = workbook.CreateSheet();//在該表中創建工作表
 8                 IRow headerRow = sheet.CreateRow(0); //在表中添加一行
 9                 foreach (DataColumn column in dt.Columns)
10                     headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);
11                 int rowIndex = 1;
12                 foreach (DataRow row in dt.Rows)
13                 {
14                     IRow dataRow = sheet.CreateRow(rowIndex);
15                     foreach (DataColumn column in dt.Columns)
16                     {
17                         dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
18                     }
19                     rowIndex++;
20                 }
21                 workbook.Write(ms);
22                 ms.Flush();
23                 ms.Position = 0;
24             }
25             return ms;
26         }

接著

1 MemoryStream ms = ExcelHelper.DataToExcel(dt);
2 FileStream fs = new FileStream("e:\\2.xls", FileMode.Create);
3 ms.WriteTo(fs);
4 fs.Close();
5 ms.Close();