62,247
社区成员




/// <summary>
/// 由DataSet导出Excel
/// </summary>
/// <param name="sourceTable">要导出数据的DataTable</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 = workbook.CreateSheet(sheetNames[i]);
HSSFRow headerRow = 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 = 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="sourceTable">要导出数据的DataTable</param>
/// <param name="fileName">指定Excel工作表名称</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;
}
//导出Excel
protected void OutputExcel_Click(object sender, EventArgs e)
{
//DataSet ds = purchasebll.GetList1(sSql2);
DataSet ds = ExcelHelper.ExportDataSetToExcel(CommandType.Text, purchasebll.GetList(sSql2));//这句报错,ExportDataSetToExcel”方法没有采用“2”个参数的重载
string filename = "stock.xls";
ExcelHelper.ExportDataSetToExcel(ds,filename,"gap");
}