using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;public partial class User_Query : System.Web.UI.Page
{
    static string sql = "select * from [Equipment]";    protected void Page_Load(object sender, EventArgs e)
    {
        //下面这行是自动换行
        GridView1.Attributes.Add("style", "word-break:break-all;word-wrap:break-word");
        if (!IsPostBack)
        {
            sql = "select * from [Equipment]";            DataAccess da = new DataAccess();
            DataTable dt = da.ExecuteSelect("SELECT [Type] FROM [Catalog]");
                       DataRow newRow = null;            newRow = dt.NewRow();
            newRow["type"] = "请选择";
            dt.Rows.InsertAt(newRow, 0);            CatalogList.DataSource = dt;
            CatalogList.DataTextField = "type";
            CatalogList.DataValueField = "type";
            CatalogList.DataBind();
            CatalogList.SelectedValue = "请选择";            //
            dt = da.ExecuteSelect("SELECT [Name] FROM [Supplier]");
            newRow = dt.NewRow();
            newRow["name"] = "请选择";
            dt.Rows.InsertAt(newRow, 0);            SupplierList.DataSource = dt;
            SupplierList.DataTextField = "name";
            SupplierList.DataValueField = "name";
            SupplierList.DataBind();
            SupplierList.SelectedValue = "请选择";            //
            dt = da.ExecuteSelect("SELECT [Name] FROM [Users] AS u WHERE u.Name!='超级管理员'");
            newRow = dt.NewRow();
            newRow["name"] = "请选择";
            dt.Rows.InsertAt(newRow, 0);            ConfirmerList.DataSource = dt;
            ConfirmerList.DataTextField = "name";
            ConfirmerList.DataValueField = "name";
            ConfirmerList.DataBind();
            ConfirmerList.SelectedValue = "请选择";            dt = da.ExecuteSelect("SELECT [No] FROM [Laboratory]");
            newRow = dt.NewRow();
            newRow["no"] = "请选择";
            dt.Rows.InsertAt(newRow, 0);            LabList.DataSource = dt;
            LabList.DataTextField = "no";
            LabList.DataValueField = "no";
            LabList.DataBind();
            LabList.SelectedValue = "请选择";
        }        SqlDataSource1.ConnectionString = ConfigurationManager.ConnectionStrings["System.Data.SqlClient"].ConnectionString;
        SqlDataSource1.SelectCommand = sql;
    }    protected void GridView1_OnRowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            ((HyperLink)e.Row.Cells[0].Controls[0]).NavigateUrl = "EquipmentDetail.aspx?ID=" + ((HyperLink)e.Row.Cells[0].Controls[0]).Text;
            ((HyperLink)e.Row.Cells[3].Controls[0]).NavigateUrl = "CatalogDetail.aspx?ID=" + ((HyperLink)e.Row.Cells[3].Controls[0]).Text;
            ((HyperLink)e.Row.Cells[4].Controls[0]).NavigateUrl = "UserDetail.aspx?ID=" + ((HyperLink)e.Row.Cells[4].Controls[0]).Text;
            ((HyperLink)e.Row.Cells[5].Controls[0]).NavigateUrl = "LaboratoryDetail.aspx?ID=" + ((HyperLink)e.Row.Cells[5].Controls[0]).Text;
            ((HyperLink)e.Row.Cells[6].Controls[0]).NavigateUrl = "SupplierDetail.aspx?ID=" + ((HyperLink)e.Row.Cells[6].Controls[0]).Text;
        }
    }    protected void QueryBtn_Click(object sender, EventArgs e)
    {
        sql = "SELECT * FROM [Equipment] WHERE [Faults] >= '" + Tbx_faults.Text + "'";        if (!Tbx_no.Text.Equals(""))
        {
            sql += " AND [No]='@No'";
            sql = sql.Replace("@No", Tbx_no.Text);
        }        if (!Tbx_name.Text.Equals(""))
        {
            sql += " AND [Name]='@Name'";
            sql = sql.Replace("@Name", Tbx_name.Text);
        }        if (!CatalogList.SelectedValue.Equals("请选择"))
            
        {
            sql += "AND [Catalog_Type]='@[Catalog_Type]'";
            sql = sql.Replace("@[Catalog_Type]", CatalogList.SelectedValue);
        }        if (!SupplierList.SelectedValue.Equals("请选择"))
        {
            sql += "AND [Supplier_Name]='@[Supplier_Name]'";
            sql = sql.Replace("@[Supplier_Name]", SupplierList.SelectedValue);
        }        if (!ConfirmerList.SelectedValue.Equals("请选择"))
        {
            sql += "AND [Confirmer_Name]='@[Confirmer_Name]'";
            sql = sql.Replace("@[Confirmer_Name]", ConfirmerList.SelectedValue);
        }        if (!LabList.SelectedValue.Equals("请选择"))
        {
            sql += "AND [Lab_No]='@[Lab_No]'";
            sql = sql.Replace("@[Lab_No]", LabList.SelectedValue);
        }        if (!StateList.SelectedValue.Equals("0"))
        {
            sql += "AND [State]='@[State]'";
            sql = sql.Replace("@[State]",StateList.SelectedValue.ToString ());
        }        SqlDataSource1.ConnectionString = ConfigurationManager.ConnectionStrings["System.Data.SqlClient"].ConnectionString;
        SqlDataSource1.SelectCommand = sql;        DataSourceSelectArguments arg = new DataSourceSelectArguments();
        DataView dv = (DataView) SqlDataSource1.Select(arg);
        DataTable result = dv.Table;        if (0 == result.Rows.Count)
        {
            emptyMSG.Visible = true;
        }
        else
        {
            emptyMSG.Visible = false;
        }
    }
    protected void PrintBtn_Click(object sender, System.EventArgs e)
    {    }   
}
PrintBtn_Click函数要如何写才能用excel表格导出查询的内容?

解决方案 »

  1.   


            /// 把DataSet导出到Excel文件,并带表头
            /// </summary>
            /// <param name="title">表头</param>
            /// <param name="_caption">标题</param>
            /// <param name="ds">源数据DataSet</param>
            /// <param name="cols">导出的列</param>
            /// <param name="FileName">导出的文件名</param>
            public static void ExportDataSetToExcelWithTitle(string title,string[] _caption, DataSet ds, string[] cols, string FileName)
            {
                HttpResponse resp;
                resp = HttpContext.Current.Response;
                resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
                resp.AppendHeader("Content-Disposition", "attachment;filename=" + FileName);
                resp.Charset = "UTF-8";            string colHeaders = "", ls_item = "";
                int i = 0;            DataTable dt = ds.Tables[0];
                DataRow[] myRow = dt.Select("");            resp.ContentType = "application/ms-excel";//image/JPEG;text/HTML;image/GIF;vnd.ms-excel/msword            resp.Write(title +"\n");            for (i = 0; i < _caption.Length - 1; i++)
                    colHeaders += _caption[i] + "\t";            colHeaders += _caption[i] + "\n";            resp.Write(colHeaders);            foreach (DataRow row in myRow)
                {
                    for (i = 0; i < cols.Length - 1; i++)
                    {
                        if (dt.Columns.Contains(cols[i]))
                            ls_item += row[cols[i]].ToString().Replace("\t", "").Replace("\n", "").Replace("\r", "") + "\t";
                    }                ls_item += row[cols[i]].ToString().Replace("\t", "").Replace("\n", "").Replace("\r", "") + "\n";
     
                    resp.Write(ls_item);
                    ls_item = "";
                }
              
                resp.End();
            }
    }
      

  2.   


    #region 读取Excel文件内容到DataSet中
             public static DataSet ReadExcel(string xlsPath)
             {
                 // 读取Excel数据,填充DataSet
                 // 连接字符串            
                 string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                                 "Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\";" + // 指定扩展属性为 Microsoft Excel 8.0 (97) 9.0 (2000) 10.0 (2002),并且第一行作为数据返回,且以文本方式读取
                                 "data source=" + xlsPath + ";";
                 string sql_F = "SELECT * FROM [{0}]";             System.Data.OleDb.OleDbConnection conn = null;
                 System.Data.OleDb.OleDbDataAdapter da = null;
                 System.Data.DataTable tblSchema = null;
                 IList<string> tblNames = null;             // 初始化连接,并打开
                 conn = new System.Data.OleDb.OleDbConnection(connStr);
                 try
                 {
                     conn.Open();
                 }
                 catch (Exception ex)
                 {
                     throw ex;
                 }
                 // 获取数据源的表定义元数据                        
                 //tblSchema = conn.GetSchema("Tables");
                 tblSchema = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });             //关闭连接
                 conn.Close();             tblNames = new List<string>();
                 foreach (DataRow row in tblSchema.Rows)
                 {
                     tblNames.Add((string)row["TABLE_NAME"]); // 读取表名
                 }             // 初始化适配器
                 da = new System.Data.OleDb.OleDbDataAdapter();
                 // 准备数据,导入DataSet
                 DataSet ds = new DataSet();             foreach (string tblName in tblNames)
                 {
                     da.SelectCommand = new System.Data.OleDb.OleDbCommand(String.Format(sql_F, tblName), conn);
                     try
                     {
                         da.Fill(ds, tblName);
                     }
                     catch
                     {
                         // 关闭连接
                         if (conn.State == ConnectionState.Open)
                         {
                             conn.Close();
                         }
                         throw;
                     }
                 }             // 关闭连接
                 if (conn.State == ConnectionState.Open)
                 {
                     conn.Close();
                 }
                 return ds;
             }
             #endregion         #region 使用第三方組件NPOI導出EXCEL
             public static void ResponseExcel(System.Data.DataTable table)
             {
                 string strFileName = System.Web.HttpContext.Current.Server.MapPath("~/data/excel.xls");
                 try  
                 {  
                     //文档仅写入一个sheet 
                     //建立一个workbook  
                     HSSFWorkbook workbook =  new   HSSFWorkbook(); 
                     System.Data.DataTable dt = table;  
                     //建立sheet                 
                     HSSFSheet sheet = workbook.CreateSheet( "sheet1" );  
                     //为避免日期格式被Excel自动替换,所以设定 format 为 『@』 表示一率当成text來看  
                     HSSFCellStyle textStyle = workbook.CreateCellStyle();  
                     textStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@"); 
                     //用column name 作为列名 
                     List< string > columns =  new   List< string >();  
                     for   ( int   colIndex = 0; colIndex < dt.Columns.Count; colIndex++)  
                     {  
                         string   name = dt.Columns[colIndex].ColumnName;  
                         HSSFCell cell = sheet.CreateRow(0).CreateCell(colIndex);  
                         cell.SetCellValue(name); 
                         cell.CellStyle = textStyle;  
                         columns.Add(name); 
                     }   
                     //建立内容列  
                     for( int   row = 0; row < dt.Rows.Count; row++)  
                     {  
                         DataRow dr = dt.Rows[row];  
                         for   ( int   col = 0; col < columns.Count; col++)  
                         {  
                             string   data = dr[columns[col]].ToString(); 
                             HSSFCell cell = sheet.CreateRow(row + 1).CreateCell(col); 
                             cell.SetCellValue(data);  
                             cell.CellStyle = textStyle;  
                         }  
                     } 
                     //写Excel  
                     FileStream file =  new   FileStream(strFileName, FileMode.OpenOrCreate); 
                     workbook.Write(file);                   
                     file.Close();
                     //ResponseExcel();                 //直接讀取數據源輸出文件會出錯,但也可以使用
                     HttpContext.Current.Response.ClearContent();    //清除缓冲内容
                     HttpContext.Current.Response.ClearHeaders();    //清除缓冲头
                     HttpContext.Current.Response.ContentType = "application/ms-excel";    //设置显示的contentType
                     HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls", System.Text.Encoding.UTF8));
                     HttpContext.Current.Response.BinaryWrite(workbook.GetBytes());
                     HttpContext.Current.Response.Flush();           //发送缓冲
                     HttpContext.Current.Response.Close();           //关闭输出
                     HttpContext.Current.Response.End();               }  
                 catch(Exception ex)
                 {
                     throw ex;
                 }     
             }
             #endregion
      

  3.   


    #region 导出页面或web控件方法
             /// <summary>
             ///  将Web控件或页面信息导出(不带文件名参数)
             /// </summary>
             /// <param name="source">控件实例</param>        
             /// <param name="DocumentType">导出类型:Excel或Word</param>
             public static void ExportControl(System.Web.UI.Control source, string DocumentType)
             {
                 //设置Http的头信息,编码格式
                 if (DocumentType == "Excel")
                 {
                     //Excel       
                     HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename="+ HttpUtility.UrlEncode("下载文件.xls",System.Text.Encoding.UTF8));                 HttpContext.Current.Response.ContentType = "application/ms-excel";
                 }
                 else if (DocumentType == "Word")
                 {
                     //Word
                     HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename="+ HttpUtility.UrlEncode("下载文件.doc",System.Text.Encoding.UTF8));
                     HttpContext.Current.Response.ContentType = "application/ms-word";                 
                 }
                 HttpContext.Current.Response.Charset = "UTF-8";   
                 HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;      
                 //关闭控件的视图状态
                 source.Page.EnableViewState =false;   
                 //初始化HtmlWriter
                 System.IO.StringWriter writer = new System.IO.StringWriter() ;
                 System.Web.UI.HtmlTextWriter htmlWriter = new System.Web.UI.HtmlTextWriter(writer);
                 source.RenderControl(htmlWriter); 
                 //输出
                 HttpContext.Current.Response.Write(writer.ToString());
                 HttpContext.Current.Response.End();
            }
            /// <summary>
            /// 将Web控件或页面信息导出(带文件名参数)
            /// </summary>
            /// <param name="source">控件实例</param> 
            /// <param name="DocumentType">导出类型:Excel或Word</param>
            /// <param name="filename">保存文件名</param>
             public void ExportControl(System.Web.UI.Control source, string DocumentType, string filename)
             {
                 //设置Http的头信息,编码格式
                 if (DocumentType == "Excel")
                 {
                     //Excel       
                     HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename="+ HttpUtility.UrlEncode(filename+".xls",System.Text.Encoding.UTF8));
                     HttpContext.Current.Response.ContentType = "application/ms-excel";                       }
                 else if (DocumentType == "Word")
                 {
                     //Word
                     HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename="+ HttpUtility.UrlEncode(filename+".doc",System.Text.Encoding.UTF8));
                     HttpContext.Current.Response.ContentType = "application/ms-word";
                 }             
                 HttpContext.Current.Response.Charset = "UTF-8";   
                 HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8; 
                 //关闭控件的视图状态
                 source.Page.EnableViewState =false;    
                 //初始化HtmlWriter
                 System.IO.StringWriter writer = new System.IO.StringWriter() ;
                 System.Web.UI.HtmlTextWriter htmlWriter = new System.Web.UI.HtmlTextWriter(writer);
                 source.RenderControl(htmlWriter); 
                //输出
                 HttpContext.Current.Response.Write(writer.ToString());
                 HttpContext.Current.Response.End();
             }
             //方法ExportControl(System.Web.UI.Control source, string DocumentType,string filename)中
             //第一个参数source表示导出的页面或控件名,当为datagrid或dataList控件时,在导出Excel/word文件时,必须把控件的分页、排序等属性去除并重新绑定,
             //第二个参数DocumentType表示导出的文件类型word或excel
             //第三个参数filename表示需要导出的文件所取的文件名
             //调用方法:
             //ExportData export=new ExportData();
             //export.ExportControl(this, "Word","testfilename");//当为this时表示当前页面
             //这是将整个页面导出为Word,并命名为testfilename
            #endregion             }
      

  4.   

    http://xiedwxy.blog.163.com/blog/static/192916285201242413254382/ 这是我的bolg 你可以去看看!
      

  5.   

    http://msdn.microsoft.com/zh-tw/ee818993.aspx
    NPOI,你的不二选择
      

  6.   

    NPOI,你的不二选择 不错的推荐 用了很久