现在经常能碰到客户要求打印报表的问题，我们知道B/S架构下，打印一般只有两种，一种用水晶报表，一种就是将数据倒入到Excel中进行打印，相比于第一种来说，Excel的优势还是很明显的！首先，水晶报表的价钱非常昂贵，对于一个中小型软件开发公司来说，不可把那么多资金投入到购买相关的开发工具产品当中，如果选用vs.net 自带的报表，又经常难以满足客户的真正需要。如果用倒入Excel的办法，一来可以节省不小的资金，二来也可以减短开发的周期，那我们何乐而不为呢！！！
下面我举个实际的例子，以下的代码测试已通过
//在.cs 里
using System.Configuration; //须在Config中指定连接的数据库
using common_function; //声明 namespace
using System.Data .SqlClient;
private void Page_Load(object sender, System.EventArgs e)
{
if(!Page.IsPostBack)
{
BindGrid();
}
}
private void BindGrid()
{
string sql_str="select * from repair_now where repair_sort_id=''";
MyDataGrid.DataSource =Public_Class.Get_Select_Table(sql_str); //调用公共类返回datatable
MyDataGrid.DataBind();
//导入Excel表时，先求数量
string sql_count="select count(*) from repair_now ";
string temp=Public_Class.Judge_Repeat(sql_count);
ViewState["count"]=temp;
}
private string Table_Name()
{
string tn="To_Excel";
return tn;
}
private void ToExcel(System.Web.UI.Control ctl,string Table_Name)
{
HttpContext.Current.Response.AppendHeader("Content- Disposition","attachment;filename="+""+Table_Name+".xls");
HttpContext.Current.Response.Charset ="gb-2312";
HttpContext.Current.Response.ContentEncoding =System.Text.Encoding.Default ;
HttpContext.Current.Response.ContentType ="application/ms-excel/ms- word";//image/JPEG;text/HTML;image/GIF;vnd.ms-excel/msword
ctl.Page.EnableViewState =true;
System.IO.StringWriter tw = new System.IO.StringWriter() ;
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter (tw);
ctl.RenderControl(hw);
HttpContext.Current.Response.Write(tw.ToString());
HttpContext.Current.Response.End();
}
private void Button_Excel_Click(object sender, System.Web.UI.ImageClickEventArgs e)//倒入Excel按钮事件
{
MyDataGrid.PageSize=Convert.ToInt32(ViewState["count"].ToString());
BindGrid();
MyDataGrid.PagerStyle.Visible =false;
ToExcel(MyDataGrid,Table_Name());
}
以下是被调用公共类 Public_Class.cs
using System.Data .SqlClient ;
using System.Configuration;
public class Public_Class
{
private static string ConnectString = ConfigurationSettings.AppSettings["ConnectString"];
public static DataTable Get_Select_Table(string sql_str) //执行查询，返回DataTable
{
SqlConnection myConnection;
myConnection = new SqlConnection( ConnectString);
SqlDataAdapter myCommand = new SqlDataAdapter(sql_str, myConnection);
DataSet ds = new DataSet();
myCommand.Fill(ds, "t1");
return ds.Tables ["t1"];
}
public static string Judge_Repeat(string sql_str)
{
SqlConnection myConnection = new SqlConnection(ConnectString);
SqlCommand myCommand = new SqlCommand (sql_str,myConnection);
myCommand.Connection.Open();
SqlDataReader Dr;
Dr=myCommand.ExecuteReader();
try
{
Dr.Read();
return Dr[0].ToString ();
}
catch(Exception err)
{
return "";
}
finally
{
Dr.Close();
myCommand.Connection.Close();
}
}
}
下面我举个实际的例子，以下的代码测试已通过
//在.cs 里
using System.Configuration; //须在Config中指定连接的数据库
using common_function; //声明 namespace
using System.Data .SqlClient;
private void Page_Load(object sender, System.EventArgs e)
{
if(!Page.IsPostBack)
{
BindGrid();
}
}
private void BindGrid()
{
string sql_str="select * from repair_now where repair_sort_id=''";
MyDataGrid.DataSource =Public_Class.Get_Select_Table(sql_str); //调用公共类返回datatable
MyDataGrid.DataBind();
//导入Excel表时，先求数量
string sql_count="select count(*) from repair_now ";
string temp=Public_Class.Judge_Repeat(sql_count);
ViewState["count"]=temp;
}
private string Table_Name()
{
string tn="To_Excel";
return tn;
}
private void ToExcel(System.Web.UI.Control ctl,string Table_Name)
{
HttpContext.Current.Response.AppendHeader("Content- Disposition","attachment;filename="+""+Table_Name+".xls");
HttpContext.Current.Response.Charset ="gb-2312";
HttpContext.Current.Response.ContentEncoding =System.Text.Encoding.Default ;
HttpContext.Current.Response.ContentType ="application/ms-excel/ms- word";//image/JPEG;text/HTML;image/GIF;vnd.ms-excel/msword
ctl.Page.EnableViewState =true;
System.IO.StringWriter tw = new System.IO.StringWriter() ;
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter (tw);
ctl.RenderControl(hw);
HttpContext.Current.Response.Write(tw.ToString());
HttpContext.Current.Response.End();
}
private void Button_Excel_Click(object sender, System.Web.UI.ImageClickEventArgs e)//倒入Excel按钮事件
{
MyDataGrid.PageSize=Convert.ToInt32(ViewState["count"].ToString());
BindGrid();
MyDataGrid.PagerStyle.Visible =false;
ToExcel(MyDataGrid,Table_Name());
}
以下是被调用公共类 Public_Class.cs
using System.Data .SqlClient ;
using System.Configuration;
public class Public_Class
{
private static string ConnectString = ConfigurationSettings.AppSettings["ConnectString"];
public static DataTable Get_Select_Table(string sql_str) //执行查询，返回DataTable
{
SqlConnection myConnection;
myConnection = new SqlConnection( ConnectString);
SqlDataAdapter myCommand = new SqlDataAdapter(sql_str, myConnection);
DataSet ds = new DataSet();
myCommand.Fill(ds, "t1");
return ds.Tables ["t1"];
}
public static string Judge_Repeat(string sql_str)
{
SqlConnection myConnection = new SqlConnection(ConnectString);
SqlCommand myCommand = new SqlCommand (sql_str,myConnection);
myCommand.Connection.Open();
SqlDataReader Dr;
Dr=myCommand.ExecuteReader();
try
{
Dr.Read();
return Dr[0].ToString ();
}
catch(Exception err)
{
return "";
}
finally
{
Dr.Close();
myCommand.Connection.Close();
}
}
}
