首页 > 代码库 > asp.net 输出(导出) excel 文件(实用)

asp.net 输出(导出) excel 文件(实用)

废话不多说直接上代码,因为文中有中间业务处理,用到的朋友需要去整改,原理: 拼写table插入数据,输出数据流即可!

        /// <summary>        /// 商品导出Excel        /// </summary>        /// <returns></returns>       public ActionResult ProjectToExcel()        {            string subjectNo = Request.Params["SNo"] ?? "";            if (!string.IsNullOrEmpty(subjectNo))            {                SWfsSubjectService service = new SWfsSubjectService();                IList<ProductInfo> productList = service.GetProductList(subjectNo.Trim());                List<ProductInfo> list = new List<ProductInfo>();                if (productList != null)                    list = productList.ToList();                //获取当前活动下的所有分组                IList<SWfsSubjectCategory> categoryList = service.GetSWfsSubjectProductList(subjectNo.Trim());                string tempCategoryNo = string.Empty;                if (categoryList != null)                {                    //判断分组情况,单组情况按以前排序                    if (categoryList.Count == 1)                    {                        #region 无分组排序                        tempCategoryNo = categoryList[0].CategoryNo;                        IList<SWfsSubjectProductSort> sortList = service.GetProductSortList(tempCategoryNo);                        List<ProductInfo> tmplList = list;                        if (sortList.Count > 0)                        {                            list = (from l in productList                                    join s in sortList on l.ProductNo equals s.ProductNo                                    orderby s.Sort ascending                                    select l).ToList();                        }                        if (list.Count < tmplList.Count)                        {                            tmplList = (from t in tmplList                                        where !(from b in list select b.ProductNo).Contains(t.ProductNo)                                        select t).ToList();                            list.AddRange(tmplList);                        }                         #endregion                    }                    else   // 分组情况,拆分成单组情况排序,分组顺序依照显示遍历                    {                        #region 分组排序                        //单分组                        List<ProductInfo> singleList = new List<ProductInfo>();                        //聚合全部数据                        List<ProductInfo> ListSum = new List<ProductInfo>();                        //为防止多次访问数据库,直接查出所有分组数据                        IList<SWfsSubjectProductSort> sortList = new List<SWfsSubjectProductSort>();                        foreach (var model in categoryList)                        {                            tempCategoryNo += model.CategoryNo + ",";                            sortList = service.GetProductSortList(tempCategoryNo);                        }                        //循环各组,拆分                        foreach (var model in categoryList)                        {                            IList<SWfsSubjectProductSort> TempSortList = sortList.Where(c => c.SubjectNo == model.CategoryNo).ToList();                            if (TempSortList != null && TempSortList.Count > 0)                            {                                //查出当前分组里面的所有商品                                List<ProductInfo> singleALLList = productList.Where(c => c.CategoryNo == model.CategoryNo).ToList();                                //排序的插入                                singleList = (from l in singleALLList                                              join s in TempSortList on l.ProductNo equals s.ProductNo                                              orderby s.Sort ascending                                              select l).ToList();                                ListSum.AddRange(singleList);                                //判断是否有无排序的,如果有,提取插入                                if (singleList.Count < singleALLList.Count)                                {                                    singleList = (from t in singleALLList                                                  where !(from b in singleList select b.ProductNo).Contains(t.ProductNo)                                                  select t).ToList();                                    ListSum.AddRange(singleList);                                }                            }                            else                            {                                //无分排序直接插入                                ListSum.AddRange(productList.Where(c => c.CategoryNo == model.CategoryNo).ToList());                            }                        }                        list = ListSum;                         #endregion                    }                }                //判断当前商品数据,如果有数据那么可以导出,如果无数据,判断返回                if (list.Count > 0)                {                    byte[] fileContents = Encoding.UTF8.GetBytes(ExcelMsg(subjectNo, list));                    var fileStream = new MemoryStream(fileContents);                    string excelname = "活动:" + subjectNo + "日期:" + DateTime.Now + ".xls";                    return File(fileStream, "application/ms-excel", excelname);                }                else                {                    string TempAlert = string.Format("<script>alert(‘当前分组无商品数据!‘);history.back(-1);</script>");                    return Content(TempAlert, "text/html");                }            }            return View();        }        private string ExcelMsg(string subjectNo, IList<ProductInfo> productList)        {            #region 获取活动名称            SWfsSubjectService service = new SWfsSubjectService();            IList<SWfsSubject> subjectEntity = service.GetSWfsSubjectBySubjectNo(subjectNo);            string sujectName = string.Empty;            if (subjectEntity != null && subjectEntity.Count() > 0)                sujectName = string.IsNullOrEmpty(subjectEntity[0].SubjectName) ? subjectEntity[0].SubjectEnName : subjectEntity[0].SubjectName;            #endregion            StringBuilder sb = new StringBuilder("<table width=\"100%\"><tr><td colspan=\"10\" rowspan=\"2\"><h2 width=\"100%\">活动名称:" + sujectName + "</h2></td></tr></table><h2>活动编号:" + subjectNo + "</h2><h2>活动商品</h2><table cellpadding=\"0\" cellspacing=\"0\" border=\"1\"  width=\"758px\" id=\"AccountListTable\" >");            sb.AppendLine("<tr style=\"background-color:#FFFF00;\">");            sb.AppendLine("<td><span>分组名称</span></td>");            sb.AppendLine("<td><span>商品编号</span></td>");            sb.AppendLine("<td><span>商品名</span></td>");            sb.AppendLine("<td><span>品牌</span></td> ");            sb.AppendLine("</tr>");            foreach (ProductInfo psingle in productList)            {                #region 导出excel格式模板                string brandName = string.IsNullOrEmpty(psingle.BrandEnName) == true ? psingle.BrandCnName : psingle.BrandEnName;                sb.AppendLine("<tr align=\"left\">");                sb.AppendLine(String.Format("<td>{0}</td>", psingle.CategoryName));                sb.AppendLine(String.Format("<td style=\"mso-number-format:\\@;\">{0}</td>", psingle.ProductNo));                sb.AppendLine(String.Format("<td>{0}</td>", psingle.ProductName));                sb.AppendLine(String.Format("<td>{0}</td>", brandName));                sb.AppendLine("</tr>");                #endregion            }            sb.AppendLine("</table>");            return sb.ToString();        }