ASP.NET使用NPOI类库导出Excel（收藏）

using  System;
using  System.Collections.Generic;
3   using  System.Data;
4   using  System.IO;
5   using  System.Linq;
6   using  System.Web;
7   using  NPOI;
8   using  NPOI.HPSF;
9   using  NPOI.HSSF;
10   using  NPOI.HSSF.UserModel;
11   using  NPOI.POIFS;
12   using  NPOI.Util;
13  
14   public   class  DataTableRenderToExcel
15   {
16   public   static  Stream RenderDataTableToExcel(DataTable SourceTable)
17   {
18   HSSFWorkbook workbook  =   new  HSSFWorkbook();
19   MemoryStream ms  =   new  MemoryStream();
20   HSSFSheet sheet  =  workbook.CreateSheet();
21   HSSFRow headerRow  =  sheet.CreateRow( 0 );
22  
23   //  handling header.
24   foreach  (DataColumn column  in  SourceTable.Columns)
25   headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
26  
27   //  handling value.
28   int  rowIndex  =   1 ;
29  
30   foreach  (DataRow row  in  SourceTable.Rows)
31   {
32   HSSFRow dataRow  =  sheet.CreateRow(rowIndex);
33  
34   foreach  (DataColumn column  in  SourceTable.Columns)
35   {
36   dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
37   }
38  
39   rowIndex ++ ;
40   }
41  
42   workbook.Write(ms);
43   ms.Flush();
44   ms.Position  =   0 ;
45  
46   sheet  =   null ;
47   headerRow  =   null ;
48   workbook  =   null ;
49  
50   return  ms;
51   }
52  
53   public   static   void  RenderDataTableToExcel(DataTable SourceTable,  string  FileName)
54   {
55   MemoryStream ms  =  RenderDataTableToExcel(SourceTable)  as  MemoryStream;
56   FileStream fs  =   new  FileStream(FileName, FileMode.Create, FileAccess.Write);
57   byte [] data  =  ms.ToArray();
58  
59   fs.Write(data,  0 , data.Length);
60   fs.Flush();
61   fs.Close();
62  
63   data  =   null ;
64   ms  =   null ;
65   fs  =   null ;
66   }
67  
68   public   static  DataTable RenderDataTableFromExcel(Stream ExcelFileStream,  string  SheetName,  int  HeaderRowIndex)
69   {
70   HSSFWorkbook workbook  =   new  HSSFWorkbook(ExcelFileStream);
71   HSSFSheet sheet  =  workbook.GetSheet(SheetName);
72  
73   DataTable table  =   new  DataTable();
74  
75   HSSFRow headerRow  =  sheet.GetRow(HeaderRowIndex);
76   int  cellCount  =  headerRow.LastCellNum;
77  
78   for  ( int  i  =  headerRow.FirstCellNum; i  <  cellCount; i ++ )
79   {
80   DataColumn column  =   new  DataColumn(headerRow.GetCell(i).StringCellValue);
81   table.Columns.Add(column);
82   }
83  
84   int  rowCount  =  sheet.LastRowNum;
85  
86   for  ( int  i  =  (sheet.FirstRowNum  +   1 ); i  <  sheet.LastRowNum; i ++ )
87   {
88   HSSFRow row  =  sheet.GetRow(i);
89   DataRow dataRow  =  table.NewRow();
90  
91   for  ( int  j  =  row.FirstCellNum; j  <  cellCount; j ++ )
92   dataRow[j]  =  row.GetCell(j).ToString();
93   }
94  
95   ExcelFileStream.Close();
96   workbook  =   null ;
97   sheet  =   null ;
98   return  table;
99   }
100  
101   public   static  DataTable RenderDataTableFromExcel(Stream ExcelFileStream,  int  SheetIndex,  int  HeaderRowIndex)
102   {
103   HSSFWorkbook workbook  =   new  HSSFWorkbook(ExcelFileStream);
104   HSSFSheet sheet  =  workbook.GetSheetAt(SheetIndex);
105  
106   DataTable table  =   new  DataTable();
107  
108   HSSFRow headerRow  =  sheet.GetRow(HeaderRowIndex);
109   int  cellCount  =  headerRow.LastCellNum;
110  
111   for  ( int  i  =  headerRow.FirstCellNum; i  <  cellCount; i ++ )
112   {
113   DataColumn column  =   new  DataColumn(headerRow.GetCell(i).StringCellValue);
114   table.Columns.Add(column);
115   }
116  
117   int  rowCount  =  sheet.LastRowNum;
118  
119   for  ( int  i  =  (sheet.FirstRowNum  +   1 ); i  <  sheet.LastRowNum; i ++ )
120   {
121   HSSFRow row  =  sheet.GetRow(i);
122   DataRow dataRow  =  table.NewRow();
123  
124   for  ( int  j  =  row.FirstCellNum; j  <  cellCount; j ++ )
125   {
126   if  (row.GetCell(j)  !=   null )
127   dataRow[j]  =  row.GetCell(j).ToString();
128   }
129  
130   table.Rows.Add(dataRow);
131   }
132  
133   ExcelFileStream.Close();
134   workbook  =   null ;
135   sheet  =   null ;
136   return  table;
137   }
138  
139   ///   <summary> 读取excel
140   ///  默认第一行为标头
141   ///   </summary>
142   ///   <param name="path"> excel文档路径 </param>
143   ///   <returns></returns>
144   public   static  DataTable RenderDataTableFromExcel( string  path) {
145   DataTable dt  =   new  DataTable();
146  
147   HSSFWorkbook hssfworkbook;
148   using  (FileStream file  =   new  FileStream(path, FileMode.Open, FileAccess.Read))
149   {
150   hssfworkbook  =   new  HSSFWorkbook(file);
151   }
152   HSSFSheet sheet  =  hssfworkbook.GetSheetAt( 0 );
153   System.Collections.IEnumerator rows  =  sheet.GetRowEnumerator();
154  
155   HSSFRow headerRow  =  sheet.GetRow( 0 );
156   int  cellCount  =  headerRow.LastCellNum;
157  
158   for  ( int  j  =   0 ; j  <  cellCount; j ++ )
159   {
160   HSSFCell cell  =  headerRow.GetCell(j);
161   dt.Columns.Add(cell.ToString());
162   }
163  
164   for  ( int  i  =  (sheet.FirstRowNum  +   1 ); i  <=  sheet.LastRowNum; i ++ )
165   {
166   HSSFRow row  =  sheet.GetRow(i);
167   DataRow dataRow  =  dt.NewRow();
168  
169   for  ( int  j  =  row.FirstCellNum; j  <  cellCount; j ++ )
170   {
171   if  (row.GetCell(j)  !=   null )
172   dataRow[j]  =  row.GetCell(j).ToString();
173   }
174  
  dt.Rows.Add(dataRow);
  }

  return  dt;
  }
}