基于MVC3的razor视图引擎下的导出功能-致新手(老者请斧正)

内容如题,废话不说,直接上代码

#region 首页/导出EXCEL
 public ActionResult Index(string customerName, string belongsSolicitor, string customerLevel, string ExportExcel, int pageIndex = 1)
        {
            if (User.Identity.IsAuthenticated == true)
            {
                #region 权限验证/数据权限 - BUTTON级别
                ViewBag.LayoutRole = AuthorizeHelper.GetRoleActionStr(User.Identity.Name);
                string dataAuthorize = AuthorizeHelper.GetDataAuthorize(User.Identity.Name);
                #endregion

                string error = string.Empty;
                var dataClass = ClassManager.GetClassListByStr(pageIndex, 15, customerName, belongsSolicitor, customerLevel, MvcApplication.OnelevelList, MvcApplication.DepartList, out error);
                var count = ClassManager.ClassCount(customerName, belongsSolicitor, customerLevel, MvcApplication.OnelevelList, MvcApplication.DepartList, out error);

                InitClass(true);

                #region 导出EXCEL
                if (ExportExcel == "true")
                {
                    var query = ClassManager.GetClassImportList(customerName, belongsSolicitor, customerLevel, MvcApplication.OnelevelList, MvcApplication.DepartList, out error);

                    string fileName = "公司等级信息" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";
                    var sbHtml = new StringBuilder();
                    sbHtml.Append("<table border='1' cellspacing='0' cellpadding='0'>");
                    sbHtml.Append("<tr>");
                    var listTitle = new List<string> { "客户名称", "客户等级", "所属公司", "所属业务员", "客户状态", "客户大类", "业务大类" };
                    foreach (var item in listTitle)
                    {
                        sbHtml.AppendFormat("<td style='font-size: 14px;text-align:center;background-color: #DCE0E2; font-weight:bold;' height='25'>{0}</td>", item);
                    }
                    sbHtml.Append("</tr>");
                    if (query.Count > 0)
                    {
                        for (int i = 0; i < query.Count; i++)
                        {
                            #region 显示转换
                            if (query[i].CustomerLevel == "6") { query[i].CustomerLevel = "铜牌"; }
                            else if (query[i].CustomerLevel == "7") { query[i].CustomerLevel = "银牌"; }
                            else if (query[i].CustomerLevel == "8") { query[i].CustomerLevel = "金牌"; }
                            else if (query[i].CustomerLevel == "9") { query[i].CustomerLevel = "VIP"; }
                            else { query[i].CustomerLevel = "-"; }

                            if (query[i].OnelevelDepartId == "D00000000002") { query[i].OnelevelDepartId = "********集团"; }
                            else if (query[i].OnelevelDepartId == "D00000000003") { query[i].OnelevelDepartId = "物流服务"; }
                            else if (query[i].OnelevelDepartId == "D00000000004") { query[i].OnelevelDepartId = "在线服务"; }
                            else { query[i].OnelevelDepartId = "-"; }

                            if (query[i].CustomerState == "1") { query[i].CustomerState = "临时"; }
                            else if (query[i].CustomerState == "2") { query[i].CustomerState = "待转正"; }
                            else if (query[i].CustomerState == "3") { query[i].CustomerState = "正式"; }
                            else { query[i].CustomerState = "-"; }

                            if (query[i].CustomerType == "1") { query[i].CustomerType = "直客"; }
                            else if (query[i].CustomerType == "2") { query[i].CustomerType = "同行"; }
                            else if (query[i].CustomerType == "3") { query[i].CustomerType = "私人"; }
                            else if (query[i].CustomerType == "4") { query[i].CustomerType = "分支"; }
                            else if (query[i].CustomerType == "5") { query[i].CustomerType = "其他"; }
                            else { query[i].CustomerType = "-"; }

                            if (query[i].Blank4 == "1") { query[i].Blank4 = "海运集装箱"; }
                            else if (query[i].Blank4 == "2") { query[i].Blank4 = "海运散杂"; }
                            else if (query[i].Blank4 == "3") { query[i].Blank4 = "空运"; }
                            else if (query[i].Blank4 == "4") { query[i].Blank4 = "快递"; }
                            else if (query[i].Blank4 == "5") { query[i].Blank4 = "陆运"; }
                            else if (query[i].Blank4 == "6") { query[i].Blank4 = "铁路"; }
                            else if (query[i].Blank4 == "7") { query[i].Blank4 = "报关"; }
                            else if (query[i].Blank4 == "8") { query[i].Blank4 = "保险"; }
                            else if (query[i].Blank4 == "9") { query[i].Blank4 = "多式联运"; }
                            else if (query[i].Blank4 == "11") { query[i].Blank4 = "其他"; }
                            else { query[i].Blank4 = "-"; }
                            #endregion

                            sbHtml.Append("<tr>");
                            sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", query[i].CustomerName);
                            sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", query[i].CustomerLevel);
                            sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", query[i].OnelevelDepartId);
                            sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", query[i].BelongsSolicitor);
                            sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", query[i].CustomerState);
                            sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", query[i].CustomerType);
                            sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", query[i].Blank4);
                            sbHtml.Append("</tr>");
                        }
                    }
                    sbHtml.Append("</table>");

                    byte[] fileContents = Encoding.UTF8.GetBytes(sbHtml.ToString());
                    return File(fileContents, "application/ms-excel", fileName);
                }
                #endregion
                else
                {
                    if (count < 15) { return View(new PagedList<Class>(dataClass, pageIndex, 15)); }
                    else { return View(new PagedList<Class>(dataClass, pageIndex, 15, count)); }
                }
            }
            else
            {
                return Redirect("~/Account/Login");
            }
        }
        #endregion

看了代码后,用过MVC3/razor的老码农们或会嘴角轻扬或会心里默念作者的无知与稚嫩,但稚嫩的作者想说,这是给新手们领路的一段代码,因为本姑娘自己曾经碰壁无数,是在项目的进行中学习相关知识的.网上的知识纷乱复杂,大多是测试版本,而本姑娘上面贴出的代码也是在确认了保密性合格后亮出的项目中原版代码(此项目已上线).

    首先,进入正题,MVC3下导出Excel的重点只在一句话: return File(fileContents, "application/ms-excel", fileName);,你只需在后台数据库中查出你所要导出的数据的LIST(本文中对应var query = ClassManager.GetClassImportList(customerName, belongsSolicitor, customerLevel, MvcApplication.OnelevelList, MvcApplication.DepartList, out error);),把文件名改成你自己需要的(本文中对应:string fileName = "公司等级信息" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";),Excel表头改成你得字段含义(本文中对应:var listTitle = new List<string> { "客户名称", "客户等级", "所属公司", "所属业务员", "客户状态", "客户大类", "业务大类" };),每行数据改成你自己从数据库中load出的内容(query[i].*****),显示转换看你的需求,是否有key和value需要转换一下,其他部分你就copy到你的IDE中运行即可.当然,复杂Excel的格式如果用本篇中提及的方法不免太过于冗杂,但是一般的导出是可以满足的,且是最有效最简单的方式(本姑娘最崇拜的就是那种,自己能用最简单的代码满足需求,而不是一遇到问题就去上网搜各种未知风险的插件来蒙混过关逗自己玩).此时不免又有人会抛出一大块砖头直冲本姑娘脑门大叫一声,这样导出的内容其实不是Excel啊,你就是用html强转的啊,"----------------老公说此段太嚣张,那就改了--------------",对于这个问题,我只能淑女的说"我这里面讲的是简单格式MVC3下Excel的导出,而不是Excel的格式,大家自己替换成自己喜欢的就好".

    其次,说一点代码里其它部分.InitClass()方法可以直接注释掉,这个是我首页上加载combox列表的方法.有关权限验证部分也可以直接注释掉,里面调用了一些我自己写的一些方法,如果你在项目中需要一些权限验证的技术,可以给我留言,以后有机会我也会整理一篇关于权限验证的文章(仅限本人使用过的,有效的方法);代码中dataClass和count不用理它,这是我自己传给我首页的值.你需要关注的只是  if (ExportExcel == "true")这个判断之内的部分.

    最后,IDE:VisualStudio,语言C#,视图引擎Razor.

    希望能给正在寻路的你一点点启发.