p {
display: block;
margin: 3px 0 0 0;
}
-->
.NET架构基础方法—DataTableToExcel通用方法（NPOI）
今天封装DataTaleToExcel通用方法，也是大家开发中特别常用的。首先去下载NPOI，链接http://npoi.codeplex.com/ ，使用包中的net4.0版本的dll，全部引用。官网中已经给了足够的示例，我只拿来异步分，给类命名为ExcelTools.cs 。下面上代码
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NPOI.HPSF;
using NPOI.HSSF.UserModel; namespace CommonUtilities
{
public static class ExcelTools
{
public static HSSFWorkbook workbook; public static void InitializeWorkbook()
{
////create a entry of DocumentSummaryInformation
if (workbook == null)
workbook = new HSSFWorkbook();
}
#region
/// <summary>
/// 将DataTable转成Stream输出.
/// </summary>
/// <param name="SourceTable">The source table.</param>
/// <returns></returns>
public static Stream RenderDataTableToExcel(DataTable SourceTable)
{
workbook = new HSSFWorkbook();
InitializeWorkbook();
MemoryStream ms = new MemoryStream();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(); // handling header.
foreach (DataColumn column in SourceTable.Columns)
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); // handling value.
int rowIndex = ; foreach (DataRow row in SourceTable.Rows)
{
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex); foreach (DataColumn column in SourceTable.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
} rowIndex++;
} workbook.Write(ms);
ms.Flush();
ms.Position = ; sheet = null;
headerRow = null;
workbook = null; return ms;
} public static void WriteSteamToFile(MemoryStream ms, string FileName)
{
FileStream fs = new FileStream(FileName, FileMode.Create, FileAccess.Write);
byte[] data = ms.ToArray(); fs.Write(data, , data.Length);
fs.Flush();
fs.Close(); data = null;
ms = null;
fs = null;
}
public static void WriteSteamToFile(byte[] data, string FileName)
{
FileStream fs = new FileStream(FileName, FileMode.Create, FileAccess.Write);
fs.Write(data, , data.Length);
fs.Flush();
fs.Close();
data = null;
fs = null;
}
public static Stream WorkBookToStream(HSSFWorkbook InputWorkBook)
{
MemoryStream ms = new MemoryStream();
InputWorkBook.Write(ms);
ms.Flush();
ms.Position = ;
return ms;
}
public static HSSFWorkbook StreamToWorkBook(Stream InputStream)
{
HSSFWorkbook WorkBook = new HSSFWorkbook(InputStream);
return WorkBook;
}
public static HSSFWorkbook MemoryStreamToWorkBook(MemoryStream InputStream)
{
HSSFWorkbook WorkBook = new HSSFWorkbook(InputStream as Stream);
return WorkBook;
}
public static MemoryStream WorkBookToMemoryStream(HSSFWorkbook InputStream)
{
//Write the stream data of workbook to the root directory
MemoryStream file = new MemoryStream();
InputStream.Write(file);
return file;
}
public static Stream FileToStream(string FileName)
{
FileInfo fi = new FileInfo(FileName);
if (fi.Exists == true)
{
FileStream fs = new FileStream(FileName, FileMode.Open, FileAccess.Read);
return fs;
}
else return null;
}
public static Stream MemoryStreamToStream(MemoryStream ms)
{
return ms as Stream;
}
#endregion
#region /// <summary>
/// 将DataTable转成Workbook(自定资料形态)输出.
/// </summary>
/// <param name="SourceTable">The source table.</param>
/// <returns></returns>
public static HSSFWorkbook RenderDataTableToWorkBook(DataTable SourceTable)
{
workbook = new HSSFWorkbook();
InitializeWorkbook();
MemoryStream ms = new MemoryStream();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(); // handling header.
foreach (DataColumn column in SourceTable.Columns)
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); // handling value.
int rowIndex = ; foreach (DataRow row in SourceTable.Rows)
{
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex); foreach (DataColumn column in SourceTable.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
} rowIndex++;
}
return workbook;
} /// <summary>
/// 将DataTable资料输出成Excel.
/// </summary>
/// <param name="SourceTable">The source table.</param>
/// <param name="FileName">Name of the file.</param>
public static void RenderDataTableToExcel(DataTable SourceTable, string FileName)
{
MemoryStream ms = RenderDataTableToExcel(SourceTable) as MemoryStream;
WriteSteamToFile(ms, FileName);
} /// <summary>
/// 從位元读取取资料到DataTable.
/// </summary>
/// <param name="ExcelFileStream">The excel file stream.</param>
/// <param name="SheetName">Name of the sheet.</param>
/// <param name="HeaderRowIndex">Index of the header row.</param>
/// <param name="HaveHeader">if set to <c>true</c> [have header].</param>
/// <returns></returns>
public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, string SheetName, int HeaderRowIndex, bool HaveHeader)
{
workbook = new HSSFWorkbook(ExcelFileStream);
InitializeWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.GetSheet(SheetName); DataTable table = new DataTable(); HSSFRow headerRow = (HSSFRow)sheet.GetRow(HeaderRowIndex);
int cellCount = headerRow.LastCellNum; for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
string ColumnName = (HaveHeader == true) ? headerRow.GetCell(i).StringCellValue : "f" + i.ToString();
DataColumn column = new DataColumn(ColumnName);
table.Columns.Add(column);
} int rowCount = sheet.LastRowNum;
int RowStart = (HaveHeader == true) ? sheet.FirstRowNum + : sheet.FirstRowNum;
for (int i = RowStart; i <= sheet.LastRowNum; i++)
{
HSSFRow row = (HSSFRow)sheet.GetRow(i);
DataRow dataRow = table.NewRow(); for (int j = row.FirstCellNum; j < cellCount; j++)
dataRow[j] = row.GetCell(j).ToString();
} ExcelFileStream.Close();
workbook = null;
sheet = null;
return table;
} /// <summary>
/// 從位元流读取资料到DataTable.
/// </summary>
/// <param name="ExcelFileStream">The excel file stream.</param>
/// <param name="SheetIndex">Index of the sheet.</param>
/// <param name="HeaderRowIndex">Index of the header row.</param>
/// <param name="HaveHeader">if set to <c>true</c> [have header].</param>
/// <returns></returns>
public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, int SheetIndex, int HeaderRowIndex, bool HaveHeader)
{
workbook = new HSSFWorkbook(ExcelFileStream);
InitializeWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(SheetIndex); DataTable table = new DataTable(); HSSFRow headerRow = (HSSFRow)sheet.GetRow(HeaderRowIndex);
int cellCount = headerRow.LastCellNum; for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
string ColumnName = (HaveHeader == true) ? headerRow.GetCell(i).StringCellValue : "f" + i.ToString();
DataColumn column = new DataColumn(ColumnName);
table.Columns.Add(column);
} int rowCount = sheet.LastRowNum;
int RowStart = (HaveHeader == true) ? sheet.FirstRowNum + : sheet.FirstRowNum;
for (int i = RowStart; i <= sheet.LastRowNum; i++)
{
HSSFRow row = (HSSFRow)sheet.GetRow(i);
DataRow dataRow = table.NewRow(); for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
dataRow[j] = row.GetCell(j).ToString();
} table.Rows.Add(dataRow);
} ExcelFileStream.Close();
workbook = null;
sheet = null;
return table;
}
#endregion
}
}
有了这段代码之后，我们希望进一步封装，新建一个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, , buffer.Length);
fs.Flush();
}
}
}
就这样简单的方便了你今后的开发。