第一种方法(保存在服务器端)
/// <summary>
/// 输出excel报表
/// </summary>
/// <param name="ds">DataSet内存数据</param>
/// <param name="FileName"> 保存文件名</param>
public string CreateExcel(DataSet ds,string FileName)
{
try
{
if(ds.Tables .Count >0)
if(ds.Tables [0].Rows .Count >0)
{
string dir = Server.MapPath("../../DATA/");
if(!System.IO.Directory.Exists(dir))
{
System.IO.Directory.CreateDirectory(dir);
}
string urlName= DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss")+FileName +".xls";
string fileName = dir + urlName;
StringBuilder content=new StringBuilder ();
using(System.IO.FileStream fs = System.IO.File.Create(fileName))
{
for(int j=0;j<ds.Tables [0].Columns.Count;j++)
{
content.Append (ds.Tables [0].Columns[j].ColumnName.ToString()+"/t");
}
content.Append (Environment.NewLine);
for(int i=0;i<ds.Tables [0].Rows.Count;i++)
{
for(int j=0;j<ds.Tables [0].Columns .Count;j++)
{
content.Append (ds.Tables[0] .Rows[i][j].ToString()+"/t");
}
content.Append (Environment.NewLine);
}
byte[] b = System.Text.Encoding.Default.GetBytes(content.ToString ());
fs.Write(b,0,b.Length);
fs.Close();
return "<script language=javascript>alert('导出Excel成功!')</script>";
}
}
return "<script language=javascript>alert('表中没有数据!')</script>";
}
catch
{
return "<script language=javascript>alert('导出Excel失败!')</script>";
}
}
调用方式:
//打印按钮事件
private void cmd_output_Click(object sender, System.EventArgs e)
{
this.Response.Write (this.output.CreateExcel (ds1,"教学班信息")); //ds1为DataSet
this.Response.End ();
}
第二种(保存在客户端,由客户端选择保存路径)
/// <summary>
/// 输出excel报表
/// </summary>
/// <param name="ds">DataSet内存数据</param>
/// <param name="FileName"> 保存文件名</param>
///<param name="dg" >datagrid 数据</param>
public string CreateExcel(DataSet ds,string FileName,DataGrid dg)
try
{
if(ds.Tables .Count >0)
if(ds.Tables [0].Rows .Count >0)
{
string urlName= DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss")+FileName +".xls";
DataGrid dg1 = new DataGrid ();//创建新的datagrid
dg1 = dg;//dg是要导出数据的datagrid
dg1.AllowPaging = false;//不启用分页
dg1.PageSize = ds.Tables [0].Rows.Count ;//设置页面显示大小
dg1.DataSource = ds;//绑定数据
dg1.DataBind ();
HttpContext.Current.Response.Clear();
HttpContext.Current.Response .Buffer =true;
HttpContext.Current.Response.Charset="UTF8";
HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename="+Page.Server.UrlPathEncode (urlName)); //对文件名重新进行编码,否则为乱码
HttpContext.Current.Response.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312");//设置输出流格式
HttpContext.Current.Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
this.EnableViewState = false;
System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN",true);
System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad);
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
dg1.RenderControl(oHtmlTextWriter);
return oStringWriter.ToString();
}
return "<script language=javascript>alert('表中没有数据!')</script>";
}
catch
{
return "<script language=javascript>alert('导出Excel失败!')</script>";
}
调用方式
private void cmd_output_Click(object sender, System.EventArgs e)
{
this.Response.Write (this.output.CreateExcel (ds1,"教学班信息",this.dg_jxb ));//dg_jxb为要导出数据的datagrid
this.Response.End ();
}