ASP. NET uses MD. DLL to convert to EXCEL

1. Create a DownExcel. aspx file without the CS File

<% @ 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. etettings ["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 ("the condition does not match. The query does not contain any information! ");
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 = "app/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. Call method:

Http: // localhost: 13042/report/downexcel. aspx? Procedure = P_PP_SPC_FindCount & strWorkCenterNum = 0 & strStatus = All & strPartno =

Note:

P_PP_SPC_FindCoun: Stored Procedure

WorkcenterNum: Parameter

Add 'str' to each parameter to indicate that the parameter is string type.

Therefore, the parameter must be written as StrWorkcenterNum.