在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));        }    }