troubleshooting Question

Export Data to Excel

Avatar of lulu50
lulu50Flag for United States of America asked on
XMLC#.NET MVC* ASP .NET MVC 5
5 Comments1 Solution138 ViewsLast Modified:
Hi,

I'm trying to export my data to an excel document.

this is my first time doing it in MVC C# so I need your help please.

Do I need to create a view document for "ExportToExcel " ?

I'm doing the example that is in this URL:
https://www.yogihosting.com/export-excel-asp-net-mvc/


Thank you for your help ;-)

Filtering the data in my model

    public List<RuleDetail> RuleDetailList(CABRSearchRules model)
        {
            List<RuleDetail> modelList = new List<RuleDetail>();

            IEnumerable<CABR_RuleDetail> list = new List<CABR_RuleDetail>();
            IEnumerable<CABR_HistoryRuleDetail> Historylist = new List<CABR_HistoryRuleDetail>();

            if (model.SelectedCurrentOrHistoryID == 1) //Current
            {
                list = _unitOfWorkCABusinessRules.RuleDetailRepo.GetAll().ToList();

                //Filter Status
                if (model.SelectedStatus != null)
                    list = list.Where(x => x.isActive == model.SelectedStatus);

                //Filter Environment
                if (model.SelectedEnvironment != "All")
                    list = list.Where(x => x.Environment == model.SelectedEnvironment);

                //Filter Version
                if (model.SelectedVersion != null)
                    list = list.Where(x => model.SelectedVersion.Any(m => Equals(x.Version, m)));

                //Filter CQ Ticket
                if (model.SelectedCQticket != null)
                    list = list.Where(x => model.SelectedCQticket.Any(m => Equals(x.CQTicket, m)));

                //Filter Commit ID
                if (model.SelectedCommitID != null)
                    list = list.Where(x => model.SelectedCommitID.Any(m => Equals(x.CommitID, m)));

                //Filter Created By
                if (model.SelectedCreatedBy != null)
                    list = list.Where(x => model.SelectedCreatedBy.Any(m => Equals(x.CreatedBy, m)));

                //Filter Rule Set
                if (model.SelectedRuleSet != null)
                    list = list.Where(x => model.SelectedRuleSet.Any(m => Equals(x.RuleSetId, m)));

                //Filter Rule Identifier 
                if (model.SelectedRuleIdentifier != null)
                    list = list.Where(x => model.SelectedRuleIdentifier.Any(m => Equals(x.RuleIdentifierId, m)));

                //Filter Created Date
                if (model.FromCreatedDate.ToString() != "1/1/0001 12:00:00 AM" && model.ToCreatedDate.ToString() != "1/1/0001 12:00:00 AM")
                    list = list.Where(x => x.CreatedDate.Date >= model.FromCreatedDate.Date && x.CreatedDate.Date <= model.ToCreatedDate.Date);

                //Filter Migration Date
                if (model.FromMigrationDate.ToString() != "1/1/0001 12:00:00 AM" && model.ToMigrationDate.ToString() != "1/1/0001 12:00:00 AM")
                    list = list.Where(x => x.MigrationDate.Date >= model.FromMigrationDate.Date && x.MigrationDate.Date <= model.ToMigrationDate.Date);

                //Filter Migration Date
                if (model.RuleDescription != null)
                    list = list.Where(x => x.RuleDescription.Contains(model.RuleDescription));

                //Filter Condition Attributes And Values
                if (model.ConditionAttributesAndValues != null)
                    list = list.Where(x => x.ConditionAttributesAndValues.Contains(model.ConditionAttributesAndValues));

                //Filter Action Attributes And Values
                if (model.ActionAttributesAndValues != null)
                    list = list.Where(x => x.ActionAttributesAndValues.Contains(model.ActionAttributesAndValues));

                //Filter Comment Or Notes
                if (model.CommentOrNotes != null)
                    list = list.Where(x => x.CommentOrNotes.Contains(model.CommentOrNotes));

                //Filter Commit Description
                if (model.CommitDescription != null)
                    list = list.Where(x => x.CommitDescription.Contains(model.CommitDescription));

                //Filter Selected Function
                if (model.SelectedFunction != null)
                    list = list.Where(x => model.SelectedFunction.Any(x.ConditionAttributesAndValues.Contains));

                //Filter FunctionTxt
                if (model.FunctionTxt != null)
                    list = list.Where(x => x.ConditionAttributesAndValues.ToLower().Contains(model.FunctionTxt.ToLower()));

                //Filter Selected Group
                if (model.SelectedGroup != null)
                    list = list.Where(x => model.SelectedGroup.Any(x.ConditionAttributesAndValues.Contains));

                //Filter Group Txt
                if (model.GroupTxt != null)
                    list = list.Where(x => InListOrRange(model.GroupTxt, x.ConditionAttributesAndValues));

            }
            else  //History
            {
                Historylist = _unitOfWorkCABusinessRules.HistoryRuleDetailRepo.GetAll().ToList();

                //Filter Status
                if (model.SelectedStatus != null)
                    Historylist = Historylist.Where(x => x.isActive == model.SelectedStatus);

                //Filter Environment
                if (model.SelectedEnvironment != "All")
                    Historylist = Historylist.Where(x => x.Environment == model.SelectedEnvironment);

                //Filter Version
                if (model.SelectedVersion != null)
                    Historylist = Historylist.Where(x => model.SelectedVersion.Any(m => Equals(x.Version, m)));

                //Filter CQ Ticket
                if (model.SelectedCQticket != null)
                    Historylist = Historylist.Where(x => model.SelectedCQticket.Any(m => Equals(x.CQTicket, m)));

                //Filter Commit ID
                if (model.SelectedCommitID != null)
                    Historylist = Historylist.Where(x => model.SelectedCommitID.Any(m => Equals(x.CommitID, m)));

                //Filter Created By
                if (model.SelectedCreatedBy != null)
                    Historylist = Historylist.Where(x => model.SelectedCreatedBy.Any(m => Equals(x.CreatedBy, m)));

                //Filter Rule Set
                if (model.SelectedRuleSet != null)
                    Historylist = Historylist.Where(x => model.SelectedRuleSet.Any(m => Equals(x.RuleSetId, m)));

                //Filter Rule Identifier 
                if (model.SelectedRuleIdentifier != null)
                    Historylist = Historylist.Where(x => model.SelectedRuleIdentifier.Any(m => Equals(x.RuleIdentifierId, m)));

                //Filter Created Date
                if (model.FromCreatedDate.ToString() != "1/1/0001 12:00:00 AM" && model.ToCreatedDate.ToString() != "1/1/0001 12:00:00 AM")
                    Historylist = Historylist.Where(x => x.CreatedDate.Date >= model.FromCreatedDate.Date && x.CreatedDate.Date <= model.ToCreatedDate.Date);

                //Filter Migration Date
                if (model.FromMigrationDate.ToString() != "1/1/0001 12:00:00 AM" && model.ToMigrationDate.ToString() != "1/1/0001 12:00:00 AM")
                    Historylist = Historylist.Where(x => x.MigrationDate.Date >= model.FromMigrationDate.Date && x.MigrationDate.Date <= model.ToMigrationDate.Date);

                //Filter Migration Date
                if (model.RuleDescription != null)
                    Historylist = Historylist.Where(x => x.RuleDescription.Contains(model.RuleDescription));

                //Filter Condition Attributes And Values
                if (model.ConditionAttributesAndValues != null)
                    Historylist = Historylist.Where(x => x.ConditionAttributesAndValues.Contains(model.ConditionAttributesAndValues));

                //Filter Action Attributes And Values
                if (model.ActionAttributesAndValues != null)
                    Historylist = Historylist.Where(x => x.ActionAttributesAndValues.Contains(model.ActionAttributesAndValues));

                //Filter Comment Or Notes
                if (model.CommentOrNotes != null)
                    Historylist = Historylist.Where(x => x.CommentOrNotes.Contains(model.CommentOrNotes));

                //Filter Commit Description
                if (model.CommitDescription != null)
                    Historylist = Historylist.Where(x => x.CommitDescription.Contains(model.CommitDescription));

                //Filter Selected Function
                if (model.SelectedFunction != null)
                    Historylist = Historylist.Where(x => model.SelectedFunction.Any(x.ConditionAttributesAndValues.Contains));

                //Filter FunctionTxt
                if (model.FunctionTxt != null)
                    Historylist = Historylist.Where(x => x.ConditionAttributesAndValues.ToLower().Contains(model.FunctionTxt.ToLower()));

                //Filter Selected Group
                if (model.SelectedGroup != null)
                    Historylist = Historylist.Where(x => model.SelectedGroup.Any(x.ConditionAttributesAndValues.Contains));

                //Filter Group Txt
                if (model.GroupTxt != null)
                    Historylist = Historylist.Where(x => InListOrRange(model.GroupTxt, x.ConditionAttributesAndValues));
            }

            var GetRuleset = _unitOfWorkCABusinessRules.RuleSetRepo.GetAll()
                .Where(x => x.IsActive == true).ToList();

            var GetRuleIdentifier = _unitOfWorkCABusinessRules.RuleIdentifierRepo.GetAll()
                .Where(x => x.IsActive == true).ToList();

            modelList = list.ToList().Select(x => new RuleDetail
            {
                Status = (Boolean)x.isActive,
                RuleSet = GetRuleset.Where(a => a.RuleSetId == x.RuleSetId).FirstOrDefault().RuleSet,
                RuleIdentifier = GetRuleIdentifier.Where(a => a.RuleIdentifierId == x.RuleIdentifierId).FirstOrDefault().RuleIdentifier,
                Versions = x.Version,
                Environments = x.Environment,
                CommitID = x.CommitID,
                CommitDescription = x.CommitDescription,
                RuleDescription = x.CommitDescription,
                ConditionAttributesAndValues = x.ConditionAttributesAndValues,
                ActionAttributesAndValues = x.ActionAttributesAndValues,
                CommentOrNotes = x.CommentOrNotes,
                MigrationDate = x.MigrationDate,
                CreatedDate = x.CreatedDate,
                SubmitterLastNameFirstName = x.SubmitterLastNameFirstName,
                CQTicket = x.CQTicket
            }).ToList();

            return modelList;
        }

    DataSet dataSet = ??????  (I'm not sure how to do this) my list is in the model.RuleDetailList

  [HttpPost]

        public ActionResult ExportToExcel (CABRSearchRules model)
        {

            model.RuleDetailList = _careAdvanceBusinessRulessService.RuleDetailList(model);

            DataSet dataSet = ??????  (I'm not sure how to do this) my list is in the model.RuleDetailList

            StringBuilder sb = new StringBuilder();

            sb.Append("<table>");

            //LINQ to get Column names
            var columnName = dataSet.Tables[0].Columns.Cast<DataColumn>()
                                 .Select(x => x.ColumnName)
                                 .ToArray();
            sb.Append("<tr>");
            //Looping through the column names
            foreach (var col in columnName)
                sb.Append("<td>" + col + "</td>");
            sb.Append("</tr>");

            //Looping through the records
            foreach (DataRow dr in dataSet.Tables[0].Rows)
            {
                sb.Append("<tr>");
                foreach (DataColumn dc in dataSet.Tables[0].Columns)
                {
                    sb.Append("<td>" + dr[dc] + "</td>");
                }
                sb.Append("</tr>");
            }

            sb.Append("</table>");

            //Writing StringBuilder content to an excel file.
            Response.Clear();
            Response.ClearContent();
            Response.ClearHeaders();
            Response.Charset = "";
            Response.Buffer = true;
            Response.ContentType = "application/vnd.ms-excel";
            Response.AddHeader("content-disposition", "attachment;filename=UserReport.xls");
            Response.Write(sb.ToString());
            Response.Flush();
            Response.Close();



            return View();

        }
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 5 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros