DataSet 엑셀로 다운로드하기

private void Page_Load(object sender, System.EventArgs e)
{
    DataSet dsResult = GetMemberList();    

 

    ArrayList arrColumn = new ArrayList();
    arrColumn.Add(CreateBoundColumn("MEMID", "아이디"));
    arrColumn.Add(CreateBoundColumn("MEMNAME", "이름"));
    arrColumn.Add(CreateBoundColumn

            ("JOINDATE", "가입일", "{0:yyyy/MM/dd}", HorizontalAlign.Center));
    arrColumn.Add(CreateBoundColumn

            ("MILEAGE", "포인트", "{0:###,##0}", HorizontalAlign.Center));

 

    string fileName = "members_" + DateTime.Now.ToString("yyyyMMdd") + ".xls";

    SaveAsExcel(dsResult, arrColumn, "회원리스트", fileName);
}

 

public void SaveAsExcel(DataSet dsResult, ArrayList columns, string subject, string fileName)
{
    if(fileName.Equals(null) && fileName.Equals(""))
        fileName = DateTime.Now.ToString("yyyyMMdd")+".xls";

 

    System.Web.HttpContext.Current.Response.Buffer = true;

 

    DataGrid dgExcel = new DataGrid();
    dgExcel.ShowHeader = true;

    dgExcel.Caption =  subject;

    dgExcel.AutoGenerateColumns = false;

    foreach(object column in columns)
        dgExcel.Columns.Add((BoundColumn)column);

 

    dgExcel.HeaderStyle.BackColor = Color.FromName("powderblue");
    dgExcel.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
    dgExcel.HeaderStyle.Height = 25;
    dgExcel.HeaderStyle.Font.Bold = true;

 

    dgExcel.DataSource = dsResult;
    dgExcel.DataBind();

 

    System.Web.HttpContext.Current.Response.AddHeader
        ("Content-Disposition", string.Format("attachment;filename={0}", fileName));
    System.Web.HttpContext.Current.Response.ContentType = "application/unknown";
    // System.Web.HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";

 

    /////////////////////////////////////////////////////////////////////////////////////////////////
    ///  한글이 깨지는 경우 web.config의 globalization을 euc-kr로 바꿔주세요.
    /// <globalization requestEncoding="euc-kr" responseEncoding="euc-kr" />
    /////////////////////////////////////////////////////////////////////////////////////////////////

    System.Web.HttpContext.Current.Response.Write
        ("<meta http-equiv=Content-Type content='text/html; charset=ks_c_5601-1987'>");

 

    dgExcel.EnableViewState = false;

    System.IO.StringWriter sWriter = new System.IO.StringWriter();
    System.Web.UI.HtmlTextWriter htmlWriter = new System.Web.UI.HtmlTextWriter(sWriter);

    dgExcel.RenderControl(htmlWriter);  

 

    System.Web.HttpContext.Current.Response.Write(sWriter.ToString());
    System.Web.HttpContext.Current.Response.End();

    dgExcel.Dispose();
}

 

public BoundColumn CreateBoundColumn(string DataFieldValue, string HeaderTextValue)
{
    // Create a BoundColumn.
    BoundColumn column = new BoundColumn();

 

    // Set the properties of the BoundColumn.
    column.DataField = DataFieldValue;
    column.HeaderText = HeaderTextValue;
    return column;
}

 

public BoundColumn CreateBoundColumn

   (string DataFieldValue, string HeaderTextValue, string FormatValue, HorizontalAlign AlignValue)
{
    // Create a BoundColumn using the overloaded CreateBoundColumn method.
    BoundColumn column = CreateBoundColumn(DataFieldValue, HeaderTextValue);

 

    // Set the properties of the BoundColumn.
    column.DataFormatString = FormatValue;
    column.ItemStyle.HorizontalAlign = AlignValue;
    return column;
}