今天封装DataTaleToExcel通用方法，也是大家开发中特别常用的。首先去下载NPOI，链接http://npoi.codeplex.com/ ，使用包中的net4.0版本的dll，全部引用。官网中已经给了足够的示例，我只拿来异步分，给类命名为ExcelTools.cs 。下面上代码

  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 System.Threading.Tasks;
  8 using NPOI.HPSF;
  9 using NPOI.HSSF.UserModel;
 10 
 11 namespace CommonUtilities
 12 {
 13     public static class ExcelTools
 14     {
 15         public static HSSFWorkbook workbook;
 16         
 17         public static void InitializeWorkbook()
 18         {
 19             ////create a entry of DocumentSummaryInformation
 20             if (workbook == null)
 21                 workbook = new HSSFWorkbook();
 22         }
 23         #region
 24         /// <summary>
 25         /// 将DataTable转成Stream输出.
 26         /// </summary>
 27         /// <param name="SourceTable">The source table.</param>
 28         /// <returns></returns>
 29         public static Stream RenderDataTableToExcel(DataTable SourceTable)
 30         {
 31             workbook = new HSSFWorkbook();
 32             InitializeWorkbook();
 33             MemoryStream ms = new MemoryStream();
 34             HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
 35             HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
 36 
 37             // handling header.
 38             foreach (DataColumn column in SourceTable.Columns)
 39                 headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
 40 
 41             // handling value.
 42             int rowIndex = 1;
 43 
 44             foreach (DataRow row in SourceTable.Rows)
 45             {
 46                 HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
 47 
 48                 foreach (DataColumn column in SourceTable.Columns)
 49                 {
 50                     dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
 51                 }
 52 
 53                 rowIndex++;
 54             }
 55 
 56             workbook.Write(ms);
 57             ms.Flush();
 58             ms.Position = 0;
 59 
 60             sheet = null;
 61             headerRow = null;
 62             workbook = null;
 63 
 64             return ms;
 65         }
 66 
 67 
 68         public static void WriteSteamToFile(MemoryStream ms, string FileName)
 69         {
 70             FileStream fs = new FileStream(FileName, FileMode.Create, FileAccess.Write);
 71             byte[] data = ms.ToArray();
 72 
 73             fs.Write(data, 0, data.Length);
 74             fs.Flush();
 75             fs.Close();
 76 
 77             data = null;
 78             ms = null;
 79             fs = null;
 80         }
 81         public static void WriteSteamToFile(byte[] data, string FileName)
 82         {
 83             FileStream fs = new FileStream(FileName, FileMode.Create, FileAccess.Write);
 84             fs.Write(data, 0, data.Length);
 85             fs.Flush();
 86             fs.Close();
 87             data = null;
 88             fs = null;
 89         }
 90         public static Stream WorkBookToStream(HSSFWorkbook InputWorkBook)
 91         {
 92             MemoryStream ms = new MemoryStream();
 93             InputWorkBook.Write(ms);
 94             ms.Flush();
 95             ms.Position = 0;
 96             return ms;
 97         }
 98         public static HSSFWorkbook StreamToWorkBook(Stream InputStream)
 99         {
100             HSSFWorkbook WorkBook = new HSSFWorkbook(InputStream);
101             return WorkBook;
102         }
103         public static HSSFWorkbook MemoryStreamToWorkBook(MemoryStream InputStream)
104         {
105             HSSFWorkbook WorkBook = new HSSFWorkbook(InputStream as Stream);
106             return WorkBook;
107         }
108         public static MemoryStream WorkBookToMemoryStream(HSSFWorkbook InputStream)
109         {
110             //Write the stream data of workbook to the root directory
111             MemoryStream file = new MemoryStream();
112             InputStream.Write(file);
113             return file;
114         }
115         public static Stream FileToStream(string FileName)
116         {
117             FileInfo fi = new FileInfo(FileName);
118             if (fi.Exists == true)
119             {
120                 FileStream fs = new FileStream(FileName, FileMode.Open, FileAccess.Read);
121                 return fs;
122             }
123             else return null;
124         }
125         public static Stream MemoryStreamToStream(MemoryStream ms)
126         {
127             return ms as Stream;
128         }
129         #endregion
130         #region
131 
132         /// <summary>
133         /// 将DataTable转成Workbook(自定资料形态)输出.
134         /// </summary>
135         /// <param name="SourceTable">The source table.</param>
136         /// <returns></returns>
137         public static HSSFWorkbook RenderDataTableToWorkBook(DataTable SourceTable)
138         {
139             workbook = new HSSFWorkbook();
140             InitializeWorkbook();
141             MemoryStream ms = new MemoryStream();
142             HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
143             HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
144 
145             // handling header.
146             foreach (DataColumn column in SourceTable.Columns)
147                 headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
148 
149             // handling value.
150             int rowIndex = 1;
151 
152             foreach (DataRow row in SourceTable.Rows)
153             {
154                 HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
155 
156                 foreach (DataColumn column in SourceTable.Columns)
157                 {
158                     dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
159                 }
160 
161                 rowIndex++;
162             }
163             return workbook;
164         }
165 
166         /// <summary>
167         /// 将DataTable资料输出成Excel.
168         /// </summary>
169         /// <param name="SourceTable">The source table.</param>
170         /// <param name="FileName">Name of the file.</param>
171         public static void RenderDataTableToExcel(DataTable SourceTable, string FileName)
172         {
173             MemoryStream ms = RenderDataTableToExcel(SourceTable) as MemoryStream;
174             WriteSteamToFile(ms, FileName);
175         }
176 
177         /// <summary>
178         /// 從位元读取取资料到DataTable.
179         /// </summary>
180         /// <param name="ExcelFileStream">The excel file stream.</param>
181         /// <param name="SheetName">Name of the sheet.</param>
182         /// <param name="HeaderRowIndex">Index of the header row.</param>
183         /// <param name="HaveHeader">if set to <c>true</c> [have header].</param>
184         /// <returns></returns>
185         public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, string SheetName, int HeaderRowIndex, bool HaveHeader)
186         {
187             workbook = new HSSFWorkbook(ExcelFileStream);
188             InitializeWorkbook();
189             HSSFSheet sheet = (HSSFSheet)workbook.GetSheet(SheetName);
190 
191             DataTable table = new DataTable();
192 
193             HSSFRow headerRow = (HSSFRow)sheet.GetRow(HeaderRowIndex);
194             int cellCount = headerRow.LastCellNum;
195 
196             for (int i = headerRow.FirstCellNum; i < cellCount; i++)
197             {
198                 string ColumnName = (HaveHeader == true) ? headerRow.GetCell(i).StringCellValue : "f" + i.ToString();
199                 DataColumn column = new DataColumn(ColumnName);
200                 table.Columns.Add(column);
201             }
202 
203             int rowCount = sheet.LastRowNum;
204             int RowStart = (HaveHeader == true) ? sheet.FirstRowNum + 1 : sheet.FirstRowNum;
205             for (int i = RowStart; i <= sheet.LastRowNum; i++)
206             {
207                 HSSFRow row = (HSSFRow)sheet.GetRow(i);
208                 DataRow dataRow = table.NewRow();
209 
210                 for (int j = row.FirstCellNum; j < cellCount; j++)
211                     dataRow[j] = row.GetCell(j).ToString();
212             }
213 
214             ExcelFileStream.Close();
215             workbook = null;
216             sheet = null;
217             return table;
218         }
219 
220         /// <summary>
221         /// 從位元流读取资料到DataTable.
222         /// </summary>
223         /// <param name="ExcelFileStream">The excel file stream.</param>
224         /// <param name="SheetIndex">Index of the sheet.</param>
225         /// <param name="HeaderRowIndex">Index of the header row.</param>
226         /// <param name="HaveHeader">if set to <c>true</c> [have header].</param>
227         /// <returns></returns>
228         public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, int SheetIndex, int HeaderRowIndex, bool HaveHeader)
229         {
230             workbook = new HSSFWorkbook(ExcelFileStream);
231             InitializeWorkbook();
232             HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(SheetIndex);
233 
234             DataTable table = new DataTable();
235 
236             HSSFRow headerRow = (HSSFRow)sheet.GetRow(HeaderRowIndex);
237             int cellCount = headerRow.LastCellNum;
238 
239             for (int i = headerRow.FirstCellNum; i < cellCount; i++)
240             {
241                 string ColumnName = (HaveHeader == true) ? headerRow.GetCell(i).StringCellValue : "f" + i.ToString();
242                 DataColumn column = new DataColumn(ColumnName);
243                 table.Columns.Add(column);
244             }
245 
246             int rowCount = sheet.LastRowNum;
247             int RowStart = (HaveHeader == true) ? sheet.FirstRowNum + 1 : sheet.FirstRowNum;
248             for (int i = RowStart; i <= sheet.LastRowNum; i++)
249             {
250                 HSSFRow row = (HSSFRow)sheet.GetRow(i);
251                 DataRow dataRow = table.NewRow();
252 
253                 for (int j = row.FirstCellNum; j < cellCount; j++)
254                 {
255                     if (row.GetCell(j) != null)
256                         dataRow[j] = row.GetCell(j).ToString();
257                 }
258 
259                 table.Rows.Add(dataRow);
260             }
261 
262             ExcelFileStream.Close();
263             workbook = null;
264             sheet = null;
265             return table;
266         }
267         #endregion
268     }
269 }
View Code

 

  有了这段代码之后，我们希望进一步封装，新建一个DataTableRender.cs类，在这个类中，我只封装出一个方法，其他方法都可以通过调用ExcelTools.cs类中的方法来进一步封装。代码如下：

 public static class DataTableRender
    {
        /// <summary>
            /// 
            /// </summary>
            /// <param name="dt">DataTable</param>
            /// <param name="path">当前 FileStream 对象将封装的文件的相对路径或绝对路径。</param>
            public static void DateTableToExcel(DataTable dt, string path)
        {
            MemoryStream stream = (MemoryStream)ExcelTools.RenderDataTableToExcel(dt);
            byte[] buffer = stream.ToArray();
            using (FileStream fs = new FileStream(path, FileMode.Create, FileAccess.Write))
            {
                fs.Write(buffer, 0, buffer.Length);
                fs.Flush();
            }
        }
    }

 就这样简单的方便了你今后的开发。