ASP.NET 엑셀 다운로드하기- DB결과 엑셀로

▶ DB에서 실행된 쿼리의 결과를 엑셀로 출력하기

1. 이미지 버튼에 onclick 이벤트를 사용하여, 이벤트내에서 함수코딩한다.

.aspx
<asp:ImageButton ID="ExcelDownload" src="/Images/Btns/btn_excel_down_small.gif" runat="server" onclick="ExcelDownload_Click" />

 이미지를 클릭하면 .cs의 ExcelDownload_Click로 이동


2. DB의 프로시져 결과데이터를 가져온다.(HttpResponse를 사용하여 응답정보를 캡슐화.)
csv의 ','는 엑셀파일에 저장할때 각각의 칸으로 인식한다. 그래서 아래에서는 for문을 이용하여 컬럼의 사이마다 ','을 찍어줬다.

.aspx.cs
protected void ExcelDownload_Click(object sender, ImageClickEventArgs e)
{
        DataSet ds = new DataSet();
        ds = _Collage.CollageAssign_List(_sYear, _sMonth, _sStep, _colIdx, Page.Response);

        System.Web.HttpResponse objResponse = System.Web.HttpContext.Current.Response;

        objResponse.ClearContent();
        objResponse.ClearHeaders();
objResponse.ContentType = "application/vnd.msexcel"; -- 출력스트림의 HTTP MINE형식, 기본은 "text/html"
objResponse.AddHeader("content-disposition", "attachment; filename=" + HttpUtility.UrlEncode(파일이름) + ".csv");
        objResponse.Charset = "euc-kr";
        objResponse.ContentEncoding = Encoding.GetEncoding(949);


        string sep = "";
        if (ds.Tables[0].Rows.Count > 0)
        {
            sep = "";
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                DataRow rowTable = ds.Tables[0].Rows[i];
                if(rowTable != null)
                {
                    Response.Write(sep + (i+1).ToString());
                    sep = ",";
                    Response.Write(sep+rowTable["dName"].ToString());
                    sep = ",";
                    Response.Write(sep + rowTable["mID"].ToString());
                    sep = ",";
                    Response.Write(sep + rowTable["mName"].ToString());
                    sep = ",";
                    Response.Write(sep + rowTable["mJumin1"].ToString());
                    sep = ",";
                    Response.Write(sep + rowTable["mJumin2"].ToString());
                    sep = ",";
                    Response.Write(sep + "'" + rowTable["mJumin"].ToString());
                    sep = ",";
                    Response.Write(sep + "'" + rowTable["mMobile"].ToString());
                    sep = ",";
                    Response.Write(sep + "'" + rowTable["mPhone"].ToString());
                    sep = ",";
                    Response.Write(sep + rowTable["rtnStr"].ToString());
                }
                sep ="\n";
            }
        }
        Response.End();

        objResponse.Flush();
      objResponse.Close();
      objResponse.End();
}