Go homepage(回首页) Upload pictures (上传图片) Write articles (发文字帖)
The author:(作者)delvpublished in(发表于) 2014/1/24 9:08:20 asp.net页面导出为Excel文档_[Asp.Net教程]
using System;using System.Web;using System.Data;using System.Text;using System.Configuration;
namespace SystemFramework{ /// /// Summary description for AppExcel. /// public class AppExcel { public AppExcel() { // // TOD Add constructor logic here // } public void getExcelFile(DataTable dtData,DataTable dtHeader,string FileName,System.Web.UI.Page Page) { HttpResponse resp=Page.Response;
resp.Clear(); resp.Buffer= true; resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); //resp.AppendHeader("Content-Disposition", "attachment;filename=Temp.xls"); resp.AddHeader("Content-disposition","attachment; filename="+HttpUtility.UrlEncode(FileName+".xls",Encoding.UTF8 )); resp.ContentType="application/ms-excel"; string colHeaders= "", ls_item=""; int i=0; //取得数据表各列标题,各标题之间以\t分割,最后一个列标题后加回车符 foreach(DataRow row in dtHeader.Rows) colHeaders+=ReplaceEnter(row[0].ToString())+"\t"; colHeaders +="\n"; //向HTTP输出流中写入取得的数据信息 resp.Write(colHeaders); //逐行处理数据 foreach(DataRow row in dtData.Rows) { //在当前行中,逐列获得数据,数据之间以\t分割,结束时加回车符\n for(i=0;i { if(dtData.Columns[i].ColumnName.Trim().ToLower()=="bookingdate") { ls_item +=" "+DateTime.Parse(ReplaceEnter(row[i].ToString()).Trim()).ToString("yyyy-MM-dd hh:mm:ss")+ "\t"; } else if(dtData.Columns[i].ColumnName.Trim().ToLower()=="cancellationdate") { if(row[i].ToString().Trim().Length>0) ls_item +=" "+DateTime.Parse(ReplaceEnter(row[i].ToString()).Trim()).ToString("yyyy-MM-dd hh:mm:ss")+ "\t"; else ls_item +=" "+"\t"; } else if(dtData.Columns[i].ColumnName.Trim().ToLower()=="arrivaldate" || dtData.Columns[i].ColumnName.Trim().ToLower()=="departuredate") { ls_item +=" "+DateTime.Parse(ReplaceEnter(row[i].ToString()).Trim()).ToString("yyyy-MM-dd")+ "\t"; } else ls_item +=ReplaceEnter(row[i].ToString()) + "\t"; } ls_item +="\n"; //当前行数据写入HTTP输出流,并且置空ls_item以便下行数据 resp.Write(ls_item); ls_item=""; } resp.End(); }
//!!注意:getExcelFile函数是专用函数,要调用通用函数,请使用getExcelFileCommon() public void getExcelFile(DataTable dtData,string FileName,System.Web.UI.Page Page) { HttpResponse resp=Page.Response;
resp.Clear(); resp.Buffer= true; resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); //resp.AppendHeader("Content-Disposition", "attachment;filename=Temp.xls"); resp.AddHeader("Content-disposition","attachment; filename="+HttpUtility.UrlEncode(FileName+".xls",Encoding.UTF8 )); resp.ContentType= "application/ms-excel"; string colHeaders= "", ls_item=""; int i=0;
//取得数据表各列标题,各标题之间以\t分割,最后一个列标题后加回车符 for(i=0;i colHeaders+=ReplaceEnter(dtData.Columns[i].Caption.ToString())+"\t"; colHeaders +="\n"; //向HTTP输出流中写入取得的数据信息 resp.Write(colHeaders); //逐行处理数据 foreach(DataRow row in dtData.Rows) { //在当前行中,逐列获得数据,数据之间以\t分割,结束时加回车符\n for(i=0;i { if(dtData.Columns[i].ColumnName.Trim().ToLower()=="bookingdate") { ls_item +=" "+DateTime.Parse(ReplaceEnter(row[i].ToString()).Trim()).ToString("yyyy-MM-dd hh:mm:ss")+ "\t"; } else if(dtData.Columns[i].ColumnName.Trim().ToLower()=="cancellationdate") { if(row[i].ToString().Trim().Length>0) ls_item +=" "+DateTime.Parse(ReplaceEnter(row[i].ToString()).Trim()).ToString("yyyy-MM-dd hh:mm:ss")+ "\t"; else ls_item +=" "+"\t"; } else if(dtData.Columns[i].ColumnName.Trim().ToLower()=="arrivaldate" || dtData.Columns[i].ColumnName.Trim().ToLower()=="departuredate") { ls_item +=" "+DateTime.Parse(ReplaceEnter(row[i].ToString()).Trim()).ToString("yyyy-MM-dd")+ "\t"; } else ls_item +=ReplaceEnter(row[i].ToString()) + "\t"; } ls_item +="\n"; //当前行数据写入HTTP输出流,并且置空ls_item以便下行数据 resp.Write(ls_item); ls_item=""; } resp.End(); } /// /// 通用excel报表生成函数 /// /// /// /// /// 日期列的列名数组 public void getExcelFileCommon(DataTable dtData,string FileName,System.Web.UI.Page Page,string[] sArrDateCloumn) { #region 固定代码 HttpResponse resp=Page.Response;
//取得数据表各列标题,各标题之间以\t分割,最后一个列标题后加回车符 for(i=0;i colHeaders+=ReplaceEnter(dtData.Columns[i].Caption.ToString())+"\t"; colHeaders +="\n"; //向HTTP输出流中写入取得的数据信息 resp.Write(colHeaders); #endregion
//逐行处理数据 bool bFinded = false; foreach(DataRow row in dtData.Rows) { //在当前行中,逐列获得数据,数据之间以\t分割,结束时加回车符\n for(i=0;i { bFinded = false; if(sArrDateCloumn.Length==0) { ls_item +=ReplaceEnter(row[i].ToString()) + "\t"; } else { foreach(string ss in sArrDateCloumn)//对传入的每个日期列名数组元素 { if(dtData.Columns[i].ColumnName.Trim().ToLower()==ss) { if(row[i].ToString().Trim()!="") { ls_item +=" "+DateTime.Parse(ReplaceEnter(row[i].ToString()).Trim()).ToString("yyyy-MM-dd")+ "\t"; bFinded = true; break; } } } if(!bFinded) { ls_item +=ReplaceEnter(row[i].ToString()) + "\t"; } } } ls_item +="\n"; //当前行数据写入HTTP输出流,并且置空ls_item以便下行数据 resp.Write(ls_item); ls_item=""; } resp.End(); } /// /// 通用excel报表生成函数,新更正了只输出可视列:Donghongt 2006-6-14 /// /// 输出的数据源DataTable /// 输出的文件名 /// 当前页对象 /// 日期列的列名数组 /// 输出列的索引号数组 public void getExcelFileCommon(DataTable dtData,string FileName,System.Web.UI.Page Page,string[] sArrDateCloumn,int[] intOutPutColumn) { #region 固定代码 HttpResponse resp=Page.Response;
resp.Clear(); resp.Buffer= true; resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); //resp.AppendHeader("Content-Disposition", "attachment;filename=Temp.xls"); resp.AddHeader("Content-disposition","attachment; filename="+HttpUtility.UrlEncode(FileName+".xls",Encoding.UTF8 )); resp.ContentType= "application/ms-excel"; string colHeaders= "", ls_item="";
//取得数据表各列标题,各标题之间以\t分割,最后一个列标题后加回车符 foreach(int i in intOutPutColumn) colHeaders+=ReplaceEnter(dtData.Columns[i].Caption.ToString())+"\t"; colHeaders +="\n"; //向HTTP输出流中写入取得的数据信息 resp.Write(colHeaders); #endregion
//逐行处理数据 bool bFinded = false; foreach(DataRow row in dtData.Rows) { //在当前行中,逐列获得数据,数据之间以\t分割,结束时加回车符\n foreach(int iColumn in intOutPutColumn) { bFinded = false; if(sArrDateCloumn.Length==0) { ls_item +=ReplaceEnter(row[iColumn].ToString()) + "\t"; } else { foreach(string ss in sArrDateCloumn)//对传入的每个日期列名数组元素 { if(dtData.Columns[iColumn].ColumnName.Trim().ToLower()==ss) { if(row[iColumn].ToString().Trim()!="") { ls_item +=" "+DateTime.Parse(ReplaceEnter(row[iColumn].ToString()).Trim()).ToString("yyyy-MM-dd")+ "\t"; bFinded = true; break; } } } if(!bFinded) { ls_item +=ReplaceEnter(row[iColumn].ToString()) + "\t"; } } } ls_item +="\n"; //当前行数据写入HTTP输出流,并且置空ls_item以便下行数据 resp.Write(ls_item); ls_item=""; } resp.End(); } // private string ReplaceEnter(string str) { string s; s=str; if (str.Length>0) { s=s.Replace("\r",""); s=s.Replace("\n",""); } return s; }
}}来源:CSDN
赞