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

OStack程序员社区-中国程序员成长平台 › 门户 › 编程› ASP.NET›ASP.NET编程经验

原作者: [db:作者] 来自: [db:来源] 收藏邀请

前提：
引入MD.dll 文件；
下载地址：https://www.ogeek.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