본문 바로가기

개발 Tip/ASP .Net

[ASP.Net2.0] GridView를 Excel 출력하기

* GridView 데이터를 Excel로 출력하기


   // 엑셀 출력 버튼

    protected void imgBtnExcel_Click(object sender, ImageClickEventArgs e)
    {
        DataSet ds = GetData();
        Boolean check = false;

        if (ds.Tables.Count != 0)
        {
            if (ds.Tables[0].Rows.Count != 0)
            {
                check = true;
                DataView dv = new DataView(ds.Tables[0]);
                SaveDataView2Excel(dv);
            }
            else
            {
                check = false;
            }
        }

        if (!check)
        {
            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            sb.Append("<script type='text/javascript'>alert('검색된 결과가 없습니다.'); </script>");

            this.ClientScript.RegisterClientScriptBlock(this.GetType(), "alert", sb.ToString());
        }
    }


    // 엑셀 출력 함수
    public void SaveDataView2Excel(DataView view)
    {
        DataTable table = new DataTable();
        table = view.Table.Copy();

        string strStyle = @"<style>td { mso-number-format:\@; } </style>";
        string fileName = "OrderForITEM_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
        fileName = HttpUtility.UrlEncode(fileName, new System.Text.UTF8Encoding()).Replace("+", "%20");

        #region html테이블 생성
        string htmlStr = "<div><table class='cell_bg' cellspacing='0' rules='all' border='1' id='ctl00_ContentPlaceHolder_GridView1' style='width:1000px;border-collapse:collapse;'>";
        htmlStr += "<tr align='center' valign='middle' style='background-color:#E9EEEA;height:30px;'>";
        for (int i = 0; i < table.Columns.Count; i++)
        {
            htmlStr += "<th scope='col'>" + table.Columns[i].ToString() + "</th>";
        }

        for (int j = 0; j < table.Rows.Count; j++)
        {
            htmlStr += "</tr><tr align='center' valign='middle' style='color:Black;height:30px;'>";
            for (int i = 0; i < table.Columns.Count; i++)
            {
                htmlStr += "<td>" + table.Rows[j][i].ToString() + "</td>";
            }
            htmlStr += @"</tr>";
        }
        htmlStr += @"</table></div><meta http-equiv='Content-Type' content='text/html; charset=utf-8'>";
        #endregion

        Response.ClearHeaders();

        Response.AddHeader("content-disposition", "attachment;filename=\"" + fileName + "\"");

        Response.Charset = "";
        Response.ContentType = "application/vnd.xls";
        Response.Write(strStyle);
        Response.Write(htmlStr);
        Response.End();

    }