一、利用NPOI实现的操作Office的类
public class ExcelHelper
{
/************************************************************************/
/*由DataSet/DataTable导出到Excel */
/************************************************************************/
///<summary>
///由DataSet导出Excel
///<summary>
///<param name = "sourceDs">要导出数据的DataTable的集合DataSet</param>
///<param name = "sheetName">工作表名称</param>
///<returns>Excel工作表</returns>
private static Stream ExportDataSetToExcel(DataSet sourceDs, string sheetName)
{
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
string[] sheetNames = sheetName.Split(',');
for (int i = 0; i < sheetNames.Length; i++)
{
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(sheetNames[i]);
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
//handling header
foreach (DataColumn column in sourceDs.Tables[i].Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
}
//handling value
int rowIndex = 1;
foreach (DataRow row in sourceDs.Tables[i].Rows)
{
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in sourceDs.Tables[i].Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
}
rowIndex++;
}
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
workbook = null;
return ms;
}
///<summary>
///由DataSet导出Excel
///<summary>
///<param name =""> </param>
///<param name =""> </param>
///<returns>Excel工作表</returns>
public static void ExportDataSetToExcel(DataSet sourceDs, string fileName, string sheetName)
{
MemoryStream ms = ExportDataSetToExcel(sourceDs, sheetName) as MemoryStream;
HttpContext.Current.Response.AppendHeader("Content-disposition", "attachment;filename=" + fileName);
HttpContext.Current.Response.BinaryWrite(ms.ToArray());
HttpContext.Current.Response.End();
ms.Close();
ms = null;
}
///<summary>
///由DataTable导出Excel
///<summary>
///<param name = "sourceTable">要导出数据的DataTable</param>
///<param name = "sheetName">工作表名称</param>
///<returns>Excel工作表</returns>
private static Stream ExportDataTableToExcel(DataTable sourceTable, string sheetName)
{