ASP.NET MVC] Response.ContentType을 사용한 엑셀 다운로드

ExcelHelper.cs

    public class ExcelHelper<T>
    {
        protected IQueryable<T> _source = null;

        public ExcelHelper(IQueryable<T> source)
        {
            _source = source;
        }

        public virtual void ExcelExport()
        {
            int viewHiddenCheck = 0;

HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "Book1.xls"));
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
            HttpContext.Current.Response.ContentType = "application/ms-excel";
            HttpContext.Current.Response.HeaderEncoding = System.Text.Encoding.UTF8;

            using (StringWriter sw = new StringWriter())
            {
                using (HtmlTextWriter htw = new HtmlTextWriter(sw))
                {
                    PropertyInfo[] oProps = null;
                    Table table = new Table();
                    TableRow row = new TableRow();
                    string headText = "Field";

                    Type[] genericArgs = _source.GetType().GetGenericArguments();

                    var entity = (genericArgs.Count() == 1 ? genericArgs[0] : null);

                    if (oProps == null)
                    {
                        oProps = entity.GetProperties();

                        foreach (System.Reflection.PropertyInfo pi in oProps)
                        {
                            // 게시판 Top 설정
                            object[] attributes = pi.GetCustomAttributes(true);
                            viewHiddenCheck = 0; // 초기화

                            for (int i = 0; i < attributes.Count(); i++)
                            {
                                if (attributes[i].GetType().Name == "DisplayAttribute")
                                {
                                    headText = pi.GetCustomAttributesData()[i].NamedArguments[0].TypedValue.Value.ToString();
                                }

                                if (attributes[i].GetType().Name == "ViewHiddenAttribute")
                                {
                                    viewHiddenCheck = 1;
                                }
                            }

                            if (viewHiddenCheck == 0)
                            {
                                TableHeaderCell hcell = new TableHeaderCell();
                                hcell.Text = headText;
                                row.Cells.Add(hcell);
                            }

                            table.Rows.Add(row);
                        }

                        foreach (var rec in _source)
                        {
                            object text = null;

                            TableRow row1 = new TableRow();

                            foreach (System.Reflection.PropertyInfo pi in oProps)
                            {
                                viewHiddenCheck = 0;
                                object[] attributes = pi.GetCustomAttributes(true);
                                text = pi.GetValue(rec, null);

                                for (int i = 0; i < attributes.Count(); i++)
                                {
                                    if (attributes[i].GetType().Name == "ViewHiddenAttribute")
                                    {
                                        viewHiddenCheck = 1;
                                    }
                                }

                                if (viewHiddenCheck == 0)
                                {
                                    TableCell cell = new TableCell();
                                    cell.Text = "" + text;
                                    row1.Cells.Add(cell);
                                }
                            }

                            table.Rows.Add(row1);
                        }
                    }

                    table.RenderControl(htw);

                    HttpContext.Current.Response.Write("<meta http-equiv=Content-Type content=\"text/html; charset=utf-8\">");
                    HttpContext.Current.Response.Write(sw.ToString().Replace("<table>", "<table border='1'>"));
                    HttpContext.Current.Response.End();
                }
            }
        }

    }

DTO

ExcelHelper Class의 Constructor에 넘겨줄 DTO Class를 추가한다. 

Diplay Attribute의 Name값으로 엑셀파일 첫번째 라인에 컬럼명이 지정된다. 

    public class DTOUserInfo
    {
        [Display(Name = "ID")]
        public string ID { get; set; }
        [Display(Name = "회원명")]
        public string USER_NAME { get; set; }
        [Display(Name = "소속")]
        public string BRANCH { get; set; }
        [Display(Name = "담당자")]
        public string EMPNAME { get; set; }

    }

 

cshtml

엑셀 다운로드 버튼을 클릭하면, fn_excel_download 함수가 호출되고 

frm_search form input 데이터들이 querystring으로 만들어진다. 

    function fn_excel_download() {

        const formData = new FormData(document.getElementById('frm_search'))
        const asString = new URLSearchParams(formData).toString();
        console.log(asString);

        location.href = '/Member/ExcelDownload?' + asString;
    }
    
    
    <a href="javascript:fn_excel_download();">엑셀 다운로드</a>

 

controller

        public void ExcelDownload(short date_type, string start_date, string end_date, string branch, string emp_name)
        {
            VOTestSearch search = new VOTestSearch();
            search.PAGE = 1;
            search.PAGE_SIZE = int.MaxValue;
            search.DATE_TYPE = date_type;
            search.START_DATE = start_date;
            search.END_DATE = end_date;
            search.BRANCH = branch;
            search.EMP_NAME = emp_name;

            search.DATE_TYPE = Convert.ToInt16(search.DATE_TYPE == 0 ? 1 : search.DATE_TYPE);

            search.START_DATE = string.IsNullOrEmpty(search.START_DATE) ? DateTime.Now.ToShortDateString() : search.START_DATE;
            search.END_DATE = string.IsNullOrEmpty(search.END_DATE) ? DateTime.Now.ToShortDateString() : search.END_DATE;

            int totalCount = 0;
            DataSet ds = MemberDa.User_List(search, out totalCount);

            IList<DTOUserInfo> DTOUserInfoList = new List<DTOUserInfo>();

            foreach (System.Data.DataRow row in ds.Tables[0].Rows)
            {
                DTOUserInfo temp = new DTOUserInfo();
                temp.ID = row["개인아이디"].ToString();
                temp.USER_NAME = row["성명"].ToString();
                temp.BRANCH = row["소속"].ToString();
                temp.EMPNAME = row["담당자"].ToString();

                DTOUserInfoList.Add(temp);
            }


            ExcelHelper<DTOUserInfo> _excelHelper = new ExcelHelper<DTOUserInfo>(DTOUserInfoList.AsQueryable());
            _excelHelper.ExcelExport();

        }