///// <summary>
///// 功能：由DataSet导出Excel
///// </summary>
///// <param name="sourceDs">导入的DataSet</param>
///// <param name="fileName">文件名称（如果是Web版就 直接文件名称；否则完整路径）</param>
///// <param name="sheetName">工作表名称</param>
///// <param name="needHttpContext">是否需要HttpContext</param>
///// <param name="error">错误信息</param>
///// <returns></returns>
//public static bool DataTableToExcel(DataSet sourceDs, string fileName, string sheetName, bool needHttpContext, ref string error)
//{
// HSSFWorkbook workbook = new HSSFWorkbook();
// try
// {
// using (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.Position = 0;
// if (needHttpContext == true)
// {
// HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
// HttpContext.Current.Response.BinaryWrite(ms.ToArray());
// //少HttpContext.Current.Response.End();
// return true;
// }
// else
// {
// return FileOperate.WriteFile(fileName, ms, ref error);
// }
// }
// }
// catch (Exception ex)
// {
// error = "由DataSet导出Excel(ex)" + ex.ToString();
// throw;
// }
//}
/// <summary>
/// 功能：将表数据导出到网页流中
/// 创建人：孙佳杰 创建时间：2015年9月2日17:02:52
/// </summary>
/// <param name="modelList"></param>
/// <param name="fileName"></param>
/// <param name="error"></param>
public static void DataTableToExeclMoreSheetHttpContext(ExeclModel ExeclModel, string fileName, ref string error)
{
#region HttpContext数据包
var filename = string.Format("{0}.xls", fileName);
System.Web.HttpContext.Current.Response.ClearContent();
System.Web.HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=" + filename + "");
System.Web.HttpContext.Current.Response.ContentType = "application/excel";
System.Web.HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;
#endregion
StringBuilder stringBuilder = new StringBuilder();
//头部html
stringBuilder.Append("<meta http-equiv=\"content-type\" content=\"application/excel; charset=UTF-8\"/>");
stringBuilder.Append("<table cellspacing=\"0\" rules=\"all\" border=\"1\" style=\"border-collapse:collapse;\">");
HSSFWorkbook workbook = new HSSFWorkbook();
foreach (DataColumn column in ExeclModel.dt.Columns)
{
stringBuilder.AppendFormat("<td>{0}</td>", column.ColumnName);
}
foreach (DataRow row in ExeclModel.dt.Rows)
{
stringBuilder.Append("<tr>");
foreach (DataColumn column in ExeclModel.dt.Columns)
{
stringBuilder.AppendFormat("<td>{0}</td>", row[column]);
}
stringBuilder.Append("</tr>");
}
stringBuilder.Append("</table>");
System.Web.HttpContext.Current.Response.Write(stringBuilder);
System.Web.HttpContext.Current.Response.End();
}
///// <summary>
///// 功能：由DataSet导出Excel
///// </summary>
///// <param name="sourceDs">导入的DataSet</param>
///// <param name="fileName">文件名称（如果是Web版就 直接文件名称；否则完整路径）</param>
///// <param name="sheetName">工作表名称</param>
///// <param name="needHttpContext">是否需要HttpContext</param>
///// <param name="error">错误信息</param>
///// <returns></returns>
//public static bool DataTableToExcel(DataSet sourceDs, string fileName, string sheetName, bool needHttpContext, ref string error)
//{
// HSSFWorkbook workbook = new HSSFWorkbook();
// try
// {
// using (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.Position = 0;
// if (needHttpContext == true)
// {
// HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
// HttpContext.Current.Response.BinaryWrite(ms.ToArray());
// //少HttpContext.Current.Response.End();
// return true;
// }
// else
// {
// return FileOperate.WriteFile(fileName, ms, ref error);
// }
// }
// }
// catch (Exception ex)
// {
// error = "由DataSet导出Excel(ex)" + ex.ToString();
// throw;
// }
//}
/// <summary>
/// 功能：将表数据导出到网页流中
/// 创建人：孙佳杰 创建时间：2015年9月2日17:02:52
/// </summary>
/// <param name="modelList"></param>
/// <param name="fileName"></param>
/// <param name="error"></param>
public static void DataTableToExeclMoreSheetHttpContext(ExeclModel ExeclModel, string fileName, ref string error)
{
#region HttpContext数据包
var filename = string.Format("{0}.xls", fileName);
System.Web.HttpContext.Current.Response.ClearContent();
System.Web.HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=" + filename + "");
System.Web.HttpContext.Current.Response.ContentType = "application/excel";
System.Web.HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;
#endregion
StringBuilder stringBuilder = new StringBuilder();
//头部html
stringBuilder.Append("<meta http-equiv=\"content-type\" content=\"application/excel; charset=UTF-8\"/>");
stringBuilder.Append("<table cellspacing=\"0\" rules=\"all\" border=\"1\" style=\"border-collapse:collapse;\">");
HSSFWorkbook workbook = new HSSFWorkbook();
foreach (DataColumn column in ExeclModel.dt.Columns)
{
stringBuilder.AppendFormat("<td>{0}</td>", column.ColumnName);
}
foreach (DataRow row in ExeclModel.dt.Rows)
{
stringBuilder.Append("<tr>");
foreach (DataColumn column in ExeclModel.dt.Columns)
{
stringBuilder.AppendFormat("<td>{0}</td>", row[column]);
}
stringBuilder.Append("</tr>");
}
stringBuilder.Append("</table>");
System.Web.HttpContext.Current.Response.Write(stringBuilder);
System.Web.HttpContext.Current.Response.End();
}