• 设为首页
  • 点击收藏
  • 手机版
    手机扫一扫访问
    迪恩网络手机版
  • 关注官方公众号
    微信扫一扫关注
    公众号

在asp.netmvc中导出Excel文件

原作者: [db:作者] 来自: [db:来源] 收藏 邀请

最近项目中遇到需要导出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));
        }
    }

 


鲜花

握手

雷人

路过

鸡蛋
该文章已有0人参与评论

请发表评论

全部评论

专题导读
上一篇:
Asp.net和Jquery实现用户信息异步验证发布时间:2022-07-10
下一篇:
asp.net的Server.MapPath方法发布时间:2022-07-10
热门推荐
热门话题
阅读排行榜

扫描微信二维码

查看手机版网站

随时了解更新最新资讯

139-2527-9053

在线客服(服务时间 9:00~18:00)

在线QQ客服
地址:深圳市南山区西丽大学城创智工业园
电邮:jeky_zhao#qq.com
移动电话:139-2527-9053

Powered by 互联科技 X3.4© 2001-2213 极客世界.|Sitemap