ASP. NET uses MD. DLL to convert to EXCEL

Source: Internet
Author: User

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.

 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.