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

前提：
引入md.dll 文件；
下载地址：//www.jb51.net/dll/md.dll.html
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、调用方法：
http://localhost:13042/report/downexcel.aspx?procedure=p_pp_spc_findcount&strworkcenternum=0&strstatus=全部&strpartno=
注解：
p_pp_spc_findcoun:存储过程
workcenternum:参数
在每个参数前都要加上‘str'表示该参数是字符串型
所以参数要写成strworkcenternum