Start Free TrialLog in
Avatar of lulu50
lulu50Flag for United States of America

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

.NET MVC* ASP .NET MVC 5C#XML
Avatar of undefined
Last Comment
lulu50
ASKER CERTIFIED SOLUTION
Avatar of it_saige
it_saige
Flag of United States of America image
Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of lulu50
lulu50
Flag of United States of America image

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

Avatar of it_saige
it_saige
Flag of United States of America image
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-
Avatar of lulu50
lulu50
Flag of United States of America image

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
Avatar of lulu50
lulu50
Flag of United States of America image

ASKER

it_saige Thank you
C#
C#

C# is an object-oriented programming language created in conjunction with Microsoft’s .NET framework. Compilation is usually done into the Microsoft Intermediate Language (MSIL), which is then JIT-compiled to native code (and cached) during execution in the Common Language Runtime (CLR).

98K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts

  • "Invaluable tool for our organization"

    Josh Regalski

  • "Your help has saved me hundreds of hours of internet surfing."

    @fblack61

  • "Just a dang good service!"

    @golferski

  • "Worth every penny."

    Steve Hougom

  • "It’s been a life saver."

    Maria Halt

  • "Best support site I’ve seen!"

    Bob Schneider

  • "I would be lost without them."

    @fblack61

  • "Saved my job multiple times."

    Walt Forbes

  • "I love this site."

    Maria Duwe

  • "Friendly respectful help."

    Andrew Kowtalo

  • "Such top-notch experts."

    @magento

  • "The best money I have ever spent."

    @rwheeler23

  • "Beyond any comprehensible value"

    George Morris

  • "Invaluable tool for our organization"

    Josh Regalski

Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo
© 1996-2023 Experts Exchange, LLC. All rights reserved. Covered by US Patent