ASP.NET利用MD.DLL转EXCEL具体实现

<%@ 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>