asked on

Export Data to Excel

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

Open in new window

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();

        }

Open in new window

ASKER CERTIFIED SOLUTION

it_saige

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lulu50

ASKER

Hi it_saige,

I tried it and it works great!!!
but
I have two problems

one I'm getting this error before opening the excel document

"The file format and extension of ‘FileName.xls don’t match. The file could be corrupted or unsafe. Unless you trust its source don’t open it. Do you want to open it anyway? "

the second is not a problem lol but I just want to add a filter to the first row.

Thank you so much for all your help.

 [HttpPost]
        public ActionResult ExportToExcel(CABRSearchRules model, Boolean? StatusListID, int? CurrentOrHistoryListID, string Environment
            , string[] SelectedVersion, string[] CQTicketListID, string[] CommitIDListID, string[] CreatedByListID,
            int[] RuleSetListID, int[] RuleIdentifierListID, string[] GroupList, string[] FunctionList,
            string[] FilterList)
        {
            model.SelectedStatus = StatusListID;
            model.SelectedCurrentOrHistoryID = CurrentOrHistoryListID;
            model.SelectedEnvironment = Environment;
            model.SelectedVersion = SelectedVersion;
            model.SelectedCQticket = CQTicketListID;
            model.SelectedCommitID = CommitIDListID;
            model.SelectedCreatedBy = CreatedByListID;
            model.SelectedRuleSet = RuleSetListID;
            model.SelectedRuleIdentifier = RuleIdentifierListID;
            model.SelectedGroup = GroupList;
            model.GroupList = _careAdvanceBusinessRulessService.GetSelectedGroupList(GroupList);
            model.SelectedFunction = FunctionList;
            model.FunctionList = _careAdvanceBusinessRulessService.GetSelectedFunctionList(FunctionList);
            model.SelectedFilterList = FilterList;

            model.RuleDetailList = _careAdvanceBusinessRulessService.RuleDetailList(model);
            DataSet dataSet = new DataSet();
            dataSet.Tables.Add(model.RuleDetailList.ToDataTable());

            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 style='color:#ffffff;background-color:#5080a9;font-weight:bold;border:1px solid #ddd;'>" + 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 style='border:1px solid #ddd;'>" + 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/ms-excel";
            Response.AddHeader("content-disposition", "attachment;filename=FileName.xls");
            Response.Write(sb.ToString());
            Response.Flush();
            Response.Close();

            return View();
        }

Open in new window

it_saige

For the first issue, try to change the application Content-Type from "application/ms-excel" to "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"

Could you expand on filter?

Thanks,

-saige-

lulu50

ASKER

Hi it_saige,

I want to thank you so much for all your help.

YOU ROCK TARA RARA TARA......

I still get the "The file format and extension" message right before I open the document.

but I'll close this question because you already helped me with it.

I can now create my document and open it.

Thank you so much

YOU ROCK OH YES!!!!
YOU ROCK OH YES!!! LOL LOL

lulu50

ASKER

it_saige Thank you