ASP.NET页面导出为Excel文档

 

using System; using System.Web; using System.Data; using System.Text; using System.Configuration; namespace SystemFramework { /// <summary> /// Summary description for AppExcel. /// </summary> 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<dtData.Columns.Count;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<dtData.Columns.Count;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<dtData.Columns.Count;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(); } /// <summary> /// 通用excel报表生成函数 /// </summary> /// <param name="dtData"></param> /// <param name="FileName"></param> /// <param name="Page"></param> /// <param name="sArrDateCloumn">日期列的列名数组</param> public void getExcelFileCommon(DataTable dtData,string FileName,System.Web.UI.Page Page,string[] sArrDateCloumn) { #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=""; int i=0; //取得数据表各列标题，各标题之间以/t分割，最后一个列标题后加回车符 for(i=0;i<dtData.Columns.Count;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<dtData.Columns.Count;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(); } /// <summary> /// 通用excel报表生成函数,新更正了只输出可视列:Donghongt 2006-6-14 /// </summary> /// <param name="dtData">输出的数据源DataTable</param> /// <param name="FileName">输出的文件名</param> /// <param name="Page">当前页对象</param> /// <param name="sArrDateCloumn">日期列的列名数组</param> /// <param name="intOutPutColumn">输出列的索引号数组</param> 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; } } }