ASP.NET 使用 MD.dll 將資料匯出為 Excel 完整實作

前置準備

• 專案需參考 MD.dll (下載：MD.dll 下載連結)
• 資料庫連線字串需在 web.config 設定 ConnStr。

建立無 Code-Behind 的 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 BuildSelectSql(string baseSql) {
        string sql = baseSql + " WHERE";
        int whereIdx = sql.IndexOf("WHERE") + 7;
        string query = Server.UrlDecode(Request.QueryString.ToString());
        string[] parts = query.Split('&');
        for (int i = 0; i < parts.Length; i++) {
            if (!parts[i].StartsWith("table")) {
                if (parts[i].StartsWith("str")) {
                    sql += " AND " + parts[i].Replace("=", "='").Substring(3) + "'";
                } else if (parts[i].StartsWith("num")) {
                    sql += " AND " + parts[i].Substring(3);
                }
            }
        }
        if (sql.IndexOf("AND") > 0) {
            sql = sql.Substring(0, sql.IndexOf("AND")) + sql.Substring(sql.IndexOf("AND") + 3);
        }
        if (sql.Length < whereIdx) sql = sql.Substring(0, whereIdx - 8);
        return sql;
    }

    private string BuildExecProc(string baseProc) {
        string sql = baseProc + " ";
        string query = Server.UrlDecode(Request.QueryString.ToString());
        string[] parts = query.Split('&');
        for (int i = 0; i < parts.Length; i++) {
            if (!parts[i].StartsWith("procedure")) {
                if (parts[i].StartsWith("str")) {
                    sql += "'" + parts[i].Substring(parts[i].IndexOf("=") + 1) + "',";
                } else if (parts[i].StartsWith("num")) {
                    sql += parts[i].Substring(parts[i].IndexOf("=") + 1) + ",";
                }
            }
        }
        if (sql.EndsWith(",")) sql = sql.Substring(0, sql.Length - 1);
        return sql;
    }

    private void Page_Load(object sender, EventArgs e) {
        string conn = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
        if (Request.QueryString["table"] == null && Request.QueryString["procedure"] == null) {
            Response.Write("缺少必要參數！"); Response.End(); return;
        }
        DataSet ds = new DataSet();
        ds.Locale = new System.Globalization.CultureInfo("zh-CN");
        if (Request.QueryString["table"] != null) {
            tableName = Request.QueryString["table"];
            MD.SqlHelper.FillDataset(conn, CommandType.Text,
                BuildSelectSql("SELECT * FROM " + tableName), ds, new string[] { "down" });
        }
        if (Request.QueryString["procedure"] != null) {
            procName = Request.QueryString["procedure"];
            MD.SqlHelper.FillDataset(conn, CommandType.Text,
                BuildExecProc("EXEC " + procName), ds, new string[] { "down" });
        }
        if (ds.Tables[0].Rows.Count == 0) {
            Response.Write("查無資料！"); return;
        }
        string fileName = string.IsNullOrEmpty(procName) ? tableName : procName;
        Response.Clear(); Response.Buffer = true; Response.Charset = "utf-8";
        Response.ContentType = "application/ms-excel";
        Response.AppendHeader("content-disposition", "attachment;filename=" + fileName + ".xls");
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
        EnableViewState = false;
        System.IO.StringWriter sw = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(sw);
        DataGridDown.RenderControl(hw);
        Response.Write(sw.ToString());
        Response.End();
    }
</script>
<html><head>
<meta http-equiv="content-type" content="application/x-excel; charset=UTF-8"/>
</head><body>
<form runat="server">
<asp:DataGrid id="DataGridDown" runat="server" />
</form></body></html>

呼叫方式

// 直接查詢資料表
https://localhost:13042/report/downexcel.aspx?table=Products&strCategory=飲料&numPrice=100

// 執行預存程序 (參數前綴 str=字串、num=數值)
https://localhost:13042/report/downexcel.aspx?procedure=P_PP_SPC_FindCount&strWorkCenterNum=0&strStatus=全部&strPartno=

關鍵點說明

• 動態 WHERE 條件組合：BuildSelectSql 解析 QueryString，自動依前綴判斷字串/數值並組合 SQL。
• 預存程序參數處理：BuildExecProc 同理組合 EXEC 參數清單。
• MD.SqlHelper.FillDataset：元件內建方法，簡化 DataAdapter 操作。
• Excel 匯出：利用 DataGrid.RenderControl 將 HTML 表格輸出至 Response，瀏覽器即提示下載 .xls。

安全性提醒

程式碼直接拼接 SQL 字串，存在 SQL Injection 風險。實務上建議改用參數化查詢或 Entity Framework / Dapper 等 ORM。