在asp.net mvc中导出Excel文件

最近项目中遇到需要导出excel文件的任务。实现代码如下：

View Code

 public ExcelFileResult ExportFun(string id)
        {

            //todo:survey dal 中调用存储过程返回查询结果,将结果序列化成list对象转成table导成excel文件
            var activityBll = new ActivityBLL();

            var query = activityBll.GetAll();
            //找到所有人的所有工作计划
            if (!string.IsNullOrWhiteSpace(id))
            {
                var userDpBll = new UserDepartmentBLL();
                List<string> wwidList = userDpBll.GetByDepartmentId(id);
                query = query.Where(a => wwidList.Contains(a.WWID)).ToList();
            }
            DataTable dt = GetActivityTable(query);
            DateTime time = DateTime.Now;
            string fileName = string.Format("{0}_{1}_{2}_{3}_{4}_{5}.xls", "ExportActivity_ Id_" + id, time.Year, time.Month, time.Day, time.Hour, time.Minute);

            ExcelFileResult actionResult = new ExcelFileResult(dt) {  FileDownloadName = fileName };
            return actionResult;
        }

其中ExcelFileResult是一个继承了FileResult的类

View Code

 public sealed class ExcelFileResult : FileResult
    {
        private DataTable dt;
        private TableStyle tableStyle;
        private TableItemStyle headerStyle;
        private TableItemStyle itemStyle;

        /// <summary>
        /// Z.Bsp. "Exportdatum: {0}" (Standard-Initialisierung) - wenn leerer String, wird Exportdatum
        /// nicht angegeben.
        /// </summary>
        public string TitleExportDate { get; set; }
        /// <summary>
        /// Titel des Exports, wird im Sheet oben links ausgegeben
        /// </summary>
        public string Title { get; set; }


        /// <summary>
        /// Konstruktor
        /// </summary>
        /// <param name="dt">Die zu exportierende DataTable</param>
        public ExcelFileResult(DataTable dt)
            : this(dt, null, null, null)
        { }

        /// <summary>
        /// Konstruktor
        /// </summary>
        /// <param name="dt">Die zu exportierende DataTable</param>
        /// <param name="tableStyle">Styling für gesamgte Tabelle</param>
        /// <param name="headerStyle">Styling für Kopfzeile</param>
        /// <param name="itemStyle">Styling für die einzelnen Zellen</param>
        public ExcelFileResult(DataTable dt, TableStyle tableStyle, TableItemStyle headerStyle, TableItemStyle itemStyle)
            : base("application/ms-excel")
        {
            this.dt = dt;
            TitleExportDate = "ExportDateTime: {0}";
            this.tableStyle = tableStyle;
            this.headerStyle = headerStyle;
            this.itemStyle = itemStyle;

            // provide defaults
            if (this.tableStyle == null)
            {
                this.tableStyle = new TableStyle();
                this.tableStyle.BorderStyle = BorderStyle.Solid;
                this.tableStyle.BorderColor = Color.Black;
                this.tableStyle.BorderWidth = Unit.Parse("1px");
            }
            if (this.headerStyle == null)
            {
                this.headerStyle = new TableItemStyle();
                this.headerStyle.BackColor = Color.LightGray;
            }
        }


        protected override void WriteFile(HttpResponseBase response)
        {
            // Create HtmlTextWriter
            StringWriter sw = new StringWriter();
            HtmlTextWriter tw = new HtmlTextWriter(sw);

            // Build HTML Table from Items
            if (tableStyle != null)
                tableStyle.AddAttributesToRender(tw);
            tw.RenderBeginTag(HtmlTextWriterTag.Table);

            // Create Title Row
            tw.RenderBeginTag(HtmlTextWriterTag.Tr);
            tw.AddAttribute(HtmlTextWriterAttribute.Colspan, (dt.Columns.Count - 2).ToString());
            tw.RenderBeginTag(HtmlTextWriterTag.Td);
            tw.Write(Title);
            tw.RenderEndTag();
            tw.AddAttribute(HtmlTextWriterAttribute.Colspan, "2");
            tw.RenderBeginTag(HtmlTextWriterTag.Td);
            if (TitleExportDate != string.Empty)
                tw.WriteLineNoTabs(string.Format(TitleExportDate, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")));
            tw.RenderEndTag();

            // Create Header Row
            tw.RenderBeginTag(HtmlTextWriterTag.Tr);
            DataColumn col = null;
            for (Int32 i = 0; i <= dt.Columns.Count - 1; i++)
            {
                col = dt.Columns[i];
                if (headerStyle != null)
                    headerStyle.AddAttributesToRender(tw);
                tw.RenderBeginTag(HtmlTextWriterTag.Th);
                tw.RenderBeginTag(HtmlTextWriterTag.Strong);
                tw.WriteLineNoTabs(col.ColumnName);
                tw.RenderEndTag();
                tw.RenderEndTag();
            }
            tw.RenderEndTag();

            // Create Data Rows
            foreach (DataRow row in dt.Rows)
            {
                tw.RenderBeginTag(HtmlTextWriterTag.Tr);
                for (Int32 i = 0; i <= dt.Columns.Count - 1; i++)
                {
                    if (itemStyle != null)
                        itemStyle.AddAttributesToRender(tw);
                    tw.RenderBeginTag(HtmlTextWriterTag.Td);
                    tw.WriteLineNoTabs(HttpUtility.HtmlEncode(row[i]));
                    tw.RenderEndTag();
                }
                tw.RenderEndTag(); //  /tr
            }

            tw.RenderEndTag(); //  /table

            // Write result to output-stream
            Stream outputStream = response.OutputStream;
            const string meat = "<meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\">";
            //update by hw 2012_04_12 将utf-8 的encoding添加上
            byte[] byteArray = Encoding.UTF8.GetBytes(meat + sw);
            //byte[] byteArray = Encoding.GetEncoding("GB2312").GetBytes(sw.ToString());
            outputStream.Write(byteArray, 0, byteArray.GetLength(0));
        }
    }

 

转载于:https://www.cnblogs.com/Benjamin/archive/2012/12/25/2833160.html