ASP.NET 利用MD.DLL 转EXCEL

1、建立无CS文件的DownExcel.aspx 文件

<%@ Page Language="C#" %>

<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<%@ import Namespace="MD" %>
<script runat="server">     
  string tableName = "";
    string procName ="";   
    private string selectSql( string selstr )
    {
     string sp =selstr + " WHERE";
     int iwhere;
         iwhere=sp.IndexOf("WHERE");
        iwhere=iwhere+7;
        string sall = Server.UrlDecode(Request.QueryString.ToString());
        string[] sparams;
        sparams=sall.Split('&');
        int i=0;
        if (sparams.Length>1){
            while (i<sparams.Length){
                if (!(sparams[i].StartsWith("table"))){
                    if ((sparams[i].StartsWith("str") )){
                        sp=sp+" and " + sparams[i].Replace("=","='").Substring(3) + "'";
                    }
                    if ((sparams[i].StartsWith("num") ))
                    {
                        sp=sp+" and " + sparams[i].Substring(3) + "";
                    }
                }
             i++;
            }
        }
        if (sp.IndexOf("and") >0 ){
        sp = (sp.Substring(0,sp.IndexOf("and")) + sp.Substring(sp.IndexOf("and")+3));
        }
        //sp=sp.Replace("=","='");
        if (sp.Length<iwhere) {
        sp=sp.Substring(0,(iwhere-8));
        }
        return sp;
    }

    private string selectProc( string selstr )
    {
        string sp =selstr + " ";
       
        string sall = Server.UrlDecode(Request.QueryString.ToString());
        //Server.UrlDecode(Request.QueryString.ToString());
        string[] sparams;
        sparams=sall.Split('&');
        int i=0;
        if (sparams.Length>1)
        {
            while (i<sparams.Length)
            {
                if (!(sparams[i].StartsWith("procedure")))
                {
                    if ((sparams[i].StartsWith("str") ))
                    {
                        sp=sp + "'" + sparams[i].Substring( sparams[i].IndexOf("=")+1) + "',";
                    }
                    if ((sparams[i].StartsWith("num") ))
                    {
                        sp=sp + sparams[i].Substring( sparams[i].IndexOf("=")+1) + ",";
                    }
                }
                i++;
            }
        }
        if (sp.EndsWith(",")){
        sp=sp.Substring(0, (sp.Length -1));
        }
        return sp;
    }

    private void Page_Load(object sender, System.EventArgs e)
    {   
        // setup connection
        //Response.Write(selectSql("start test!"));  
        string conn = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;  /// System.Configuration.ConfigurationSettings.AppSettings["connectionString"];
        if (Request.QueryString["table"]== null && Request.QueryString["procedure"]==null)
        {
            this.Response.Write("not supply correct parameters!");
            this.Response.End();
            return; 
        }
        DataSet ds = new DataSet();
        ds.Locale = new System.Globalization.CultureInfo("zh-CN");
        //OleDbDataAdapter adapter=new OleDbDataAdapter();
        if (!(Request.QueryString["table"]== null ) )
        {  
            /*string test1=selectSql(("SELECT * from " + Request.QueryString["table"]));
            this.Response.Write(test1);
            this.Response.End();
            return;*/
            tableName=Request.QueryString["table"];
                           
            MD.SqlHelper.FillDataset(conn,System.Data.CommandType.Text ,selectSql(("SELECT * from " + tableName)),ds,new string[] {"down"});
        }
        if (!(Request.QueryString["procedure"]== null ) )
        {
            /*string test2=selectProc(("exec " + Request.QueryString["procedure"]));
            this.Response.Write(test2);
            this.Response.End();
            return;*/
            procName=Request.QueryString["procedure"];                       
        MD.SqlHelper.FillDataset(conn,System.Data.CommandType.Text ,selectProc(("exec " + procName)),ds,new string[] {"down"});
        }
        if (ds.Tables[0].Rows.Count==0){
            this.Response.Write("条件不符,查询没有任何资料!");
            return;
            }
        string downRes="";
        if (procName=="")
        {
            downRes=tableName;
        }
        else
        {
        downRes=procName;
        }
        //OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * from " + tableName, conn);
                //Response.Write(selectSql("SELECT * from " + tableName));  
        //return;
            // open the Database and get the results
   
            this.DataGridDown.DataSource=ds;
            this.DataGridDown.DataBind();

            this.Response.Clear();
            this.Response.Buffer =true;
            this.Response.Charset="utf-8";
            this.Response.ContentType="application/ms-excel";
            this.Response.AppendHeader("content-Disposition","attachment;filename="+downRes+".xls");
            this.Response.ContentEncoding =System.Text.Encoding.GetEncoding("utf-8");
            //Response.ContentEncoding = System.Text.Encoding.utf-8;
            this.EnableViewState =false;
            System.IO.StringWriter OStringWriter = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter OHtmlTextWriter = new System.Web.UI.HtmlTextWriter(OStringWriter);
            this.DataGridDown.RenderControl(OHtmlTextWriter);
            this.Response.Write(OStringWriter.ToString());
            this.Response.End();
   
            // if the action is update, well, we update our DB

    }

</script>
<html>
<head>   
    <meta http-equiv="content-type" content="application/x-excel; charset=UTF-8"/>

    <!-- <meta http-equiv="Content-Type" content="application/x-msexcel; charset=iso-8859-1" />  -->
</head>
<body>
    <form runat="server">
        <asp:DataGrid id="DataGridDown" style="Z-INDEX: 100; POSITION: absolute" runat="server" Height="373px" Width="674px" >
          
        </asp:DataGrid>
        <!-- Insert content here -->
    </form>
</body>
</html>

2、调用方法：

https://localhost:13042/report/downexcel.aspx?procedure=P_PP_SPC_FindCount&strWorkCenterNum=0&strStatus=全部&strPartno=

注解：

P_PP_SPC_FindCoun:存储过程

WorkcenterNum:参数

在每个参数前都要加上‘Str’表示该参数是字符串型

所以参数要写成StrWorkcenterNum