4
Answers

how to reduce Excel file downloading execution time in mvc

Kalyani Shevale

Kalyani Shevale
6y
672
1
I have generated code for creating excel file to generating each cell for the row in MVC.
 I have more than 20000 records that time 15 min required for generating one excel file.so i want minimum time for downloading excel file.their is column generated is dynamic.
  1. public ActionResult ExportToExcel(int SurveyId)  
  2.       {  
  3.           var _checlAuth = this.CheckAuthentication();  
  4.           if (_checlAuth != null)  
  5.           {  
  6.   
  7.               return _checlAuth;  
  8.           }  
  9.   
  10.           //string fileName =  System.Web.Configuration.WebConfigurationManager.AppSettings["ExcelPath"].ToString() + "\\SurveyReport.xlsx";  
  11.           string fileName = System.Web.Configuration.WebConfigurationManager.AppSettings["ExcelPath"].ToString() + "\\SurveyReport_" + DateTime.Now.Day + DateTime.Now.Hour + DateTime.Now.Minute + DateTime.Now.Second + DateTime.Now.Millisecond + "_" + SurveyId + ".xlsx";  
  12.           //"D:\\SurveyReport.xlsx";  
  13.   
  14.           // Create a spreadsheet document by supplying the file name.  
  15.   
  16.           SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.  
  17.               Create(fileName, SpreadsheetDocumentType.Workbook);  
  18.   
  19.           // Add a WorkbookPart to the document.  
  20.           WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();  
  21.           workbookpart.Workbook = new Workbook();  
  22.   
  23.           // Add a WorksheetPart to the WorkbookPart.  
  24.           WorksheetPart worksheetPart = workbookpart.AddNewPart();  
  25.           worksheetPart.Worksheet = new Worksheet(new SheetData());  
  26.           // Add Sheets to the Workbook.  
  27.           Sheets sheetsStudentDetails = spreadsheetDocument.WorkbookPart.Workbook.  
  28.               AppendChild(new Sheets());  
  29.           // Append a new worksheet and associate it with the workbook.  
  30.           Sheet sheetStudentDetail = new Sheet()  
  31.           {  
  32.               Id = spreadsheetDocument.WorkbookPart.  
  33.                   GetIdOfPart(worksheetPart),  
  34.               SheetId = 1,  
  35.               Name = "Survey Details",  
  36.           };  
  37.   
  38.           SheetData sheetData = worksheetPart.Worksheet.GetFirstChild();  
  39.           // Add a row to the cell table.  
  40.           Row row = new Row();  
  41.           sheetsStudentDetails.Append(sheetStudentDetail);  
  42.   
  43.   
  44.           var Question = new BussinessLogic.Admin.UserSurvey(new UserSurveyRepository(new CRMContext())).GetAllUserSurvey().Where(m => m.SurveyId == SurveyId);  
  45.   
  46.           //excelWorksheet.Name = "Admission Data";  
  47.           int i = 1;  
  48.           int j = 1;  
  49.           int kk = 2;  
  50.           var countResponse= new BussinessLogic.Answer.Answer(new AnswerRepository(new CRMContext())).GetAllAnswer().Where(a => a.SurveyId == SurveyId).Select(a => a.EachSurveyUniqueNo).Distinct().ToList();  
  51.           var AllQAnswer = new BussinessLogic.Answer.Answer(new AnswerRepository(new CRMContext())).GetAllAnswer();  
  52.           var AllQueAnswerOption = new BussinessLogic.Admin.QuestionAnswer(new QuestionAnswerRepository(new CRMContext())).GetAllQuestionAnswer();  
  53.           row.Append(ConstructCell("Conducted Survey Id", CellValues.String));  
  54.           row.Append(ConstructCell("Survey Name", CellValues.String));  
  55.           row.Append(ConstructCell("Survey Id", CellValues.String));  
  56.          // row.Append(ConstructCell("Submitted Id", CellValues.String));  
  57.           row.Append(ConstructCell("Date & Time", CellValues.String));  
  58.           row.Append(ConstructCell("Submitted By", CellValues.String));  
  59.             
  60.           foreach (var q in Question)  
  61.           {  
  62.               var QuestionRow = new BussinessLogic.Admin.QuestionAnswer(new QuestionAnswerRepository(new CRMContext())).GetAllQuestionAnswer().Where(k => k.QuestionId == q.Id && k.ColumnOption == null);  
  63.               Row rans = new Row();  
  64.               if (q.QuestionTypeId == 1)  
  65.               {  
  66.                   row.Append(ConstructCell(q.QuestionText, CellValues.String));  
  67.               }  
  68.               if (q.QuestionTypeId == 2)  
  69.               {  
  70.                   var optanser = AllQueAnswerOption.Where(a => a.QuestionId == q.Id).ToList();  
  71.                   foreach (var anss in optanser)  
  72.                   {  
  73.                       row.Append(ConstructCell(q.QuestionText + ": " + anss.AnswerOption, CellValues.String));                     
  74.                   }  
  75.               }  
  76.               if (q.QuestionTypeId == 3)  
  77.               {  
  78.                   row.Append(ConstructCell(q.QuestionText, CellValues.String));  
  79.               }  
  80.               if (q.QuestionTypeId == 5)  
  81.               {  
  82.                   var optanser = AllQueAnswerOption.Where(a => a.QuestionId == q.Id).ToList();  
  83.                   foreach (var anss in optanser)  
  84.                   {  
  85.                       row.Append(ConstructCell(q.QuestionText + ": " + anss.AnswerOption, CellValues.String));  
  86.                   }  
  87.               }  
  88.               if (q.QuestionTypeId == 6)  
  89.               {  
  90.                   row.Append(ConstructCell(q.QuestionText, CellValues.String));  
  91.               }  
  92.               if (q.QuestionTypeId == 7)  
  93.               {  
  94.                   var optanser = AllQueAnswerOption.Where(a => a.QuestionId == q.Id).ToList();  
  95.                   foreach (var anss in optanser)  
  96.                   {  
  97.                       row.Append(ConstructCell(q.QuestionText + ": " + anss.AnswerOption, CellValues.String));  
  98.                   }  
  99.               }  
  100.               if (q.QuestionTypeId == 8)  
  101.               {  
  102.                   var optanser = AllQueAnswerOption.Where(a => a.QuestionId == q.Id).ToList();  
  103.                   foreach (var anss in optanser)  
  104.                   {  
  105.                       row.Append(ConstructCell(q.QuestionText + ": " + anss.AnswerOption, CellValues.String));  
  106.                   }  
  107.               }  
  108.               if (q.QuestionTypeId == 9)  
  109.               {  
  110.                   var optanser = AllQueAnswerOption.Where(a => a.QuestionId == q.Id).ToList();  
  111.                   foreach (var anss in optanser)  
  112.                   {  
  113.                       row.Append(ConstructCell(q.QuestionText + ": " + anss.AnswerOption, CellValues.String));  
  114.                   }  
  115.               }  
  116.               if (q.QuestionTypeId == 10)  
  117.               {  
  118.                   var optanser = AllQueAnswerOption.Where(a => a.QuestionId == q.Id && a.ColumnOption==null).ToList();  
  119.                   foreach (var anss in optanser)  
  120.                   {  
  121.                       foreach(var colum in AllQueAnswerOption.Where(a=>a.QuestionId==q.Id && a.AnswerOption==null).ToList())  
  122.                       {  
  123.                           row.Append(ConstructCell(q.QuestionText + ":- " + anss.AnswerOption + " : " + colum.ColumnOption, CellValues.String));  
  124.                       }  
  125.                   }  
  126.               }  
  127.               if (q.QuestionTypeId == 11)  
  128.               {  
  129.                   var optanser = AllQueAnswerOption.Where(a => a.QuestionId == q.Id && a.ColumnOption == null && a.DataOptions==null).ToList();  
  130.                   foreach (var anss in optanser)  
  131.                   {  
  132.                       foreach (var colum in AllQueAnswerOption.Where(a => a.QuestionId == q.Id && a.AnswerOption == null && a.DataOptions==null).ToList())  
  133.                       {  
  134.                           row.Append(ConstructCell(q.QuestionText + ":- " + anss.AnswerOption + " : " + colum.ColumnOption, CellValues.String));  
  135.                       }  
  136.                   }  
  137.               }  
  138.               if (q.QuestionTypeId == 12)  
  139.               {  
  140.                   var optanser = AllQueAnswerOption.Where(a => a.QuestionId == q.Id && a.ColumnOption == null && a.DataOptions==null).ToList();  
  141.                   foreach (var anss in optanser)  
  142.                   {  
  143.                       foreach (var colum in AllQueAnswerOption.Where(a => a.QuestionId == q.Id && a.AnswerOption == null && a.DataOptions==null).ToList())  
  144.                       {  
  145.                           row.Append(ConstructCell(q.QuestionText + ":- " + anss.AnswerOption + " : " + colum.ColumnOption, CellValues.String));  
  146.                       }  
  147.                   }  
  148.               }  
  149.               if (q.QuestionTypeId == 13)  
  150.               {  
  151.                   var optanser = AllQueAnswerOption.Where(a => a.QuestionId == q.Id && a.ColumnOption == null).ToList();  
  152.                   foreach (var anss in optanser)  
  153.                   {  
  154.                       foreach (var colum in AllQueAnswerOption.Where(a => a.QuestionId == q.Id && a.AnswerOption == null).ToList())  
  155.                       {  
  156.                           row.Append(ConstructCell(q.QuestionText + ":- " + anss.AnswerOption + " : " + colum.ColumnOption, CellValues.String));  
  157.                       }  
  158.                   }  
  159.               }  
  160.               if (q.QuestionTypeId == 14)  
  161.               {  
  162.                   var optanser = AllQueAnswerOption.Where(a => a.QuestionId == q.Id && a.ColumnOption == null).ToList();  
  163.                   foreach (var anss in optanser)  
  164.                   {  
  165.                       foreach (var colum in AllQueAnswerOption.Where(a => a.QuestionId == q.Id && a.AnswerOption == null).ToList())  
  166.                       {  
  167.                           row.Append(ConstructCell(q.QuestionText + ":- " + anss.AnswerOption + " : " + colum.ColumnOption, CellValues.String));  
  168.                       }  
  169.                   }  
  170.               }  
  171.               if (q.QuestionTypeId == 15)  
  172.               {  
  173.                   row.Append(ConstructCell(q.QuestionText, CellValues.String));  
  174.               }  
  175.   
  176.           }  
  177.           sheetData.AppendChild(row);  
  178.           int count = 1;  
  179.           var alluser = new BussinessLogic.User.User(new Repository.UserRepository(new CRMContext())).GetAllUsers();  
  180.           var allsurvey = new BussinessLogic.Admin.Survey(new SurveyRepository(new CRMContext())).GetAllSurvey().Where(a=>a.Id==SurveyId).FirstOrDefault();  
  181.           foreach (var co in countResponse)  
  182.               {  
  183.               Row rans1 = new Row();  
  184.               rans1.Append(ConstructCell(co.ToString(), CellValues.String));  
  185.   
  186.               if (allsurvey.SurveyName!=null)  
  187.               {  
  188.                   rans1.Append(ConstructCell(allsurvey.SurveyName, CellValues.String));  
  189.                   rans1.Append(ConstructCell(allsurvey.Id.ToString(), CellValues.String));  
  190.               }  
  191.               else  
  192.               {  
  193.                   rans1.Append(ConstructCell("None", CellValues.String));  
  194.               }  
  195.               var surveyInfo = AllQAnswer.Where(a => a.EachSurveyUniqueNo == co).FirstOrDefault();  
  196.               if(surveyInfo!=null)  
  197.               {  
  198.                   rans1.Append(ConstructCell(surveyInfo.CreatedDate.ToString(), CellValues.String));  
  199.               }  
  200.               else  
  201.               {  
  202.                   rans1.Append(ConstructCell("None", CellValues.String));  
  203.               }  
  204.               var userNm = alluser.Where(a => a.id == surveyInfo.UserId).FirstOrDefault();  
  205.               if(userNm!=null)  
  206.               {  
  207.                   rans1.Append(ConstructCell(userNm.name + " " + userNm.LastName, CellValues.String));  
  208.               }  
  209.               else  
  210.               {  
  211.                   rans1.Append(ConstructCell("None", CellValues.String));  
  212.               }  
  213.               foreach (var qq in Question)  
  214.                   {  
  215.                       var QuestionRow1 = new BussinessLogic.Admin.QuestionAnswer(new QuestionAnswerRepository(new CRMContext())).GetAllQuestionAnswer().Where(k => k.QuestionId == qq.Id && k.ColumnOption == null);  
  216.                         
  217.                           if (qq.QuestionTypeId == 1)  
  218.                           {  
  219.                               var openeded = AllQAnswer.Where(a => a.QuestionId == qq.Id && a.EachSurveyUniqueNo == co).FirstOrDefault();  
  220.                               if (openeded != null)  
  221.                               {  
  222.                                      var ansopt = AllQueAnswerOption.Where(a => a.Id == openeded.SelectedAnswerOptionId && a.QuestionId == openeded.QuestionId).FirstOrDefault();  
  223.                                       if(ansopt.AnswerOption.Equals("Other (Please specify)"))  
  224.                                       {  
  225.                                           rans1.Append(ConstructCell(openeded.isOtherField, CellValues.String));  
  226.                                       }  
  227.                                       else  
  228.                                       {  
  229.                                           rans1.Append(ConstructCell(ansopt.AnswerOption, CellValues.String));  
  230.                                       }  
  231.                                         
  232.                                     
  233.                               }  
  234.                               else  
  235.                               {  
  236.                                   rans1.Append(ConstructCell("NA", CellValues.String));  
  237.                               }  
  238.                         }  
  239.                       if(qq.QuestionTypeId==2)  
  240.                       {  
  241.                               var ans = AllQAnswer.Where(m => m.QuestionId == qq.Id && m.EachSurveyUniqueNo == co).ToList();  
  242.                               if (ans.Count() == 0)  
  243.                               {  
  244.                                   foreach (var da in AllQueAnswerOption.Where(m => m.QuestionId == qq.Id).ToList())  
  245.                                   {  
  246.                                       rans1.Append(ConstructCell("NA", CellValues.String));  
  247.                                   }  
  248.   
  249.                               }  
  250.                               else  
  251.                               {  
  252.                                   foreach (var da in AllQueAnswerOption.Where(m=>m.QuestionId==qq.Id).ToList())  
  253.                                   {  
  254.                                       var Ques = AllQAnswer.Where(a => a.QuestionId == da.QuestionId && a.EachSurveyUniqueNo == co && a.SelectedAnswerOptionId == da.Id).FirstOrDefault();  
  255.                                       if(Ques!=null)  
  256.                                           {  
  257.                                               var ansopt = AllQueAnswerOption.Where(a => a.Id == Ques.SelectedAnswerOptionId && a.QuestionId == Ques.QuestionId).FirstOrDefault();  
  258.                                               if(ansopt.AnswerOption.Equals("Other (Please specify)"))  
  259.                                               {  
  260.                                                   rans1.Append(ConstructCell(Ques.isOtherField, CellValues.String));  
  261.                                               }  
  262.                                               else  
  263.                                               {  
  264.                                                   rans1.Append(ConstructCell(da.AnswerOption, CellValues.String));  
  265.                                               }  
  266.                                                 
  267.                                           }  
  268.                                           else  
  269.                                           {  
  270.                                               rans1.Append(ConstructCell("NA", CellValues.String));  
  271.                                           }                                  
  272.                                   }  
  273.                               }  
  274.                      }  
  275.                   if (qq.QuestionTypeId == 3)  
  276.                   {  
  277.                       var openeded = AllQAnswer.Where(a => a.QuestionId == qq.Id && a.EachSurveyUniqueNo == co).FirstOrDefault();  
  278.                       if (openeded != null)  
  279.                       {  
  280.                           if (openeded.Description == null)  
  281.                           {  
  282.                               rans1.Append(ConstructCell("NA", CellValues.String));  
  283.                           }  
  284.                           else  
  285.                           {  
  286.                               rans1.Append(ConstructCell(openeded.Description, CellValues.String));  
  287.                           }  
  288.                       }  
  289.                       else  
  290.                       {  
  291.                           rans1.Append(ConstructCell("NA", CellValues.String));  
  292.                       }  
  293.                   }  
  294.                   if (qq.QuestionTypeId == 5)  
  295.                   {  
  296.                       var ans = AllQAnswer.Where(m => m.QuestionId == qq.Id && m.EachSurveyUniqueNo == co).ToList();  
  297.                       if (ans.Count() == 0)  
  298.                       {  
  299.                           foreach (var da in AllQueAnswerOption.Where(m => m.QuestionId == qq.Id).ToList())  
  300.                           {  
  301.                               rans1.Append(ConstructCell("0", CellValues.String));  
  302.                           }  
  303.                       }  
  304.                       else  
  305.                       {  
  306.                           foreach (var da in AllQueAnswerOption.Where(m => m.QuestionId == qq.Id).ToList())  
  307.                           {  
  308.                               var Ques = AllQAnswer.Where(a => a.QuestionId == da.QuestionId && a.EachSurveyUniqueNo == co && a.SelectedAnswerOptionId == da.Id).FirstOrDefault();  
  309.                               if (Ques !=null)  
  310.                               {  
  311.                                   rans1.Append(ConstructCell(Ques.Value.ToString(), CellValues.String));  
  312.                               }  
  313.                               else  
  314.                               {  
  315.                                   rans1.Append(ConstructCell("0", CellValues.String));  
  316.                               }  
  317.                           }  
  318.                       }  
  319.                   }  
  320.                   if (qq.QuestionTypeId == 6)  
  321.                   {  
  322.                       var openeded = AllQAnswer.Where(a => a.QuestionId == qq.Id && a.EachSurveyUniqueNo == co).FirstOrDefault();  
  323.                       if (openeded != null)  
  324.                       {  
  325.                           if (openeded.Description == null)  
  326.                           {  
  327.                               rans1.Append(ConstructCell("NA", CellValues.String));  
  328.                           }  
  329.                           else  
  330.                           {  
  331.                               rans1.Append(ConstructCell(openeded.Description, CellValues.String));  
  332.                           }  
  333.                              
  334.                       }  
  335.                       else  
  336.                       {  
  337.                             rans1.Append(ConstructCell("NA", CellValues.String));  
  338.                           
  339.                       }  
  340.                   }  
  341.                   if (qq.QuestionTypeId == 7)  
  342.                   {  
  343.                       var ans = AllQAnswer.Where(m => m.QuestionId == qq.Id && m.EachSurveyUniqueNo == co).ToList();  
  344.                       if (ans.Count() == 0)  
  345.                       {  
  346.                           foreach (var da in AllQueAnswerOption.Where(m => m.QuestionId == qq.Id).ToList())  
  347.                           {  
  348.                               rans1.Append(ConstructCell("NA", CellValues.String));  
  349.                           }  
  350.                       }  
  351.                       else  
  352.                       {  
  353.                           foreach (var da in AllQueAnswerOption.Where(m => m.QuestionId == qq.Id).ToList())  
  354.                           {  
  355.                               var Ques = AllQAnswer.Where(a => a.QuestionId == da.QuestionId && a.EachSurveyUniqueNo == co && a.SelectedAnswerOptionId == da.Id).FirstOrDefault();  
  356.                               if (Ques != null)  
  357.                               {  
  358.                                   rans1.Append(ConstructCell(Ques.Value.ToString(), CellValues.String));  
  359.                               }  
  360.                               else  
  361.                               {  
  362.                                   rans1.Append(ConstructCell("NA", CellValues.String));  
  363.                               }  
  364.                           }  
  365.                       }  
  366.                   }  
  367.                   if (qq.QuestionTypeId == 8)  
  368.                   {  
  369.                       var ans = AllQAnswer.Where(m => m.QuestionId == qq.Id && m.EachSurveyUniqueNo == co).ToList();  
  370.                       if (ans.Count() == 0)  
  371.                       {  
  372.                           foreach (var da in AllQueAnswerOption.Where(m => m.QuestionId == qq.Id).ToList())  
  373.                           {  
  374.                               rans1.Append(ConstructCell("NA", CellValues.String));  
  375.                           }  
  376.                       }  
  377.                       else  
  378.                       {  
  379.                           foreach (var da in AllQueAnswerOption.Where(m => m.QuestionId == qq.Id).ToList())  
  380.                           {  
  381.                               var Ques = AllQAnswer.Where(a => a.QuestionId == da.QuestionId && a.EachSurveyUniqueNo == co && a.SelectedAnswerOptionId == da.Id).FirstOrDefault();  
  382.                               if (Ques != null)  
  383.                               {  
  384.                                   rans1.Append(ConstructCell(Ques.Value.ToString(), CellValues.String));  
  385.                               }  
  386.                               else  
  387.                               {  
  388.                                   rans1.Append(ConstructCell("NA", CellValues.String));  
  389.                               }  
  390.                           }  
  391.                       }  
  392.                   }  
  393.                   if (qq.QuestionTypeId == 9)  
  394.                   {  
  395.                       var ans = AllQAnswer.Where(m => m.QuestionId == qq.Id && m.EachSurveyUniqueNo == co).ToList();  
  396.                       if (ans.Count() == 0)  
  397.                       {  
  398.                           foreach (var da in AllQueAnswerOption.Where(m => m.QuestionId == qq.Id).ToList())  
  399.                           {  
  400.                               rans1.Append(ConstructCell("NA", CellValues.String));  
  401.                           }  
  402.                       }  
  403.                       else  
  404.                       {  
  405.                           foreach (var da in AllQueAnswerOption.Where(m => m.QuestionId == qq.Id).ToList())  
  406.                           {  
  407.                               var Ques = AllQAnswer.Where(a => a.QuestionId == da.QuestionId && a.EachSurveyUniqueNo == co && a.SelectedAnswerOptionId == da.Id).FirstOrDefault();  
  408.                               if (Ques != null)  
  409.                               {  
  410.                                   rans1.Append(ConstructCell(Ques.Value.ToString(), CellValues.String));  
  411.                               }  
  412.                               else  
  413.                               {  
  414.                                   rans1.Append(ConstructCell("NA", CellValues.String));  
  415.                               }  
  416.                           }  
  417.                       }  
  418.                   }  
  419.                   if (qq.QuestionTypeId == 10)  
  420.                   {  
  421.                       var ans = AllQAnswer.Where(m => m.QuestionId == qq.Id && m.EachSurveyUniqueNo == co).ToList();  
  422.                       if(ans==null)  
  423.                       {  
  424.                           foreach (var da in AllQueAnswerOption.Where(m => m.QuestionId == qq.Id && m.ColumnOption == null).ToList())  
  425.                           {  
  426.                               foreach (var col in AllQueAnswerOption.Where(m => m.QuestionId == qq.Id && m.AnswerOption == null).ToList())  
  427.                               {  
  428.                                   rans1.Append(ConstructCell("NA", CellValues.String));  
  429.                               }  
  430.                           }  
  431.                       }  
  432.                       if (ans.Count() == 0)  
  433.                       {  
  434.                           //foreach (var da in AllQueAnswerOption.Where(m => m.QuestionId == qq.Id).ToList())  
  435.                           //{  
  436.                           //    rans1.Append(ConstructCell("NA", CellValues.String));  
  437.                           //}  
  438.                           foreach (var da in AllQueAnswerOption.Where(m => m.QuestionId == qq.Id && m.ColumnOption == null).ToList())  
  439.                           {  
  440.                               foreach (var col in AllQueAnswerOption.Where(m => m.QuestionId == qq.Id && m.AnswerOption == null).ToList())  
  441.                               {  
  442.                                   rans1.Append(ConstructCell("NA", CellValues.String));  
  443.                               }  
  444.                           }  
  445.                       }  
  446.                       else  
  447.                       {  
  448.                           foreach (var da in AllQueAnswerOption.Where(m => m.QuestionId == qq.Id && m.ColumnOption==null).ToList())  
  449.                           {  
  450.                                   foreach(var col in AllQueAnswerOption.Where(m => m.QuestionId == qq.Id && m.AnswerOption == null).ToList())  
  451.                                   {  
  452.                                       var fin = AllQAnswer.Where(a => a.QuestionId == qq.Id && a.RowId ==da.Id  && a.ColumId == col.Id && a.EachSurveyUniqueNo == co).FirstOrDefault();  
  453.                                       if(fin!=null)  
  454.                                       {  
  455.                                           if(fin.Description==null)  
  456.                                       {  
  457.                                           rans1.Append(ConstructCell("NA", CellValues.String));  
  458.                                       }  
  459.                                        else  
  460.                                       {  
  461.                                           rans1.Append(ConstructCell(fin.Description, CellValues.String));  
  462.                                       }  
  463.                                           
  464.                                       }  
  465.                                       else  
  466.                                       {  
  467.                                               rans1.Append(ConstructCell("NA", CellValues.String));  
  468.                                        }  
  469.                                         
  470.                                       }  
  471.                            }  
  472.                       }  
  473.                   }  
  474.                   if (qq.QuestionTypeId == 11)  
  475.                   {  
  476.                       var ans = AllQAnswer.Where(m => m.QuestionId == qq.Id && m.EachSurveyUniqueNo == co).ToList();  
  477.                       if (ans.Count() == 0)  
  478.                       {  
  479.                           //foreach (var da in AllQueAnswerOption.Where(m => m.QuestionId == qq.Id).ToList())  
  480.                           //{  
  481.                           //    rans1.Append(ConstructCell("NA", CellValues.String));  
  482.                           //}  
  483.                           foreach (var da in AllQueAnswerOption.Where(m => m.QuestionId == qq.Id && m.ColumnOption == null && m.DataOptions == null).ToList())  
  484.                           {  
  485.                               foreach (var col in AllQueAnswerOption.Where(m => m.QuestionId == qq.Id && m.AnswerOption == null && m.DataOptions == null).ToList())  
  486.                               {  
  487.                                   if (col != null)  
  488.                                   {  
  489.                                       rans1.Append(ConstructCell("NA", CellValues.String));  
  490.                                   }  
  491.                               }  
  492.                           }  
  493.                       }  
  494.                       else  
  495.                       {  
  496.                           foreach (var da in AllQueAnswerOption.Where(m => m.QuestionId == qq.Id && m.ColumnOption == null && m.DataOptions==null).ToList())  
  497.                           {  
  498.                               foreach (var col in AllQueAnswerOption.Where(m => m.QuestionId == qq.Id && m.AnswerOption == null && m.DataOptions==null).ToList())  
  499.                               {  
  500.                                   var fin = AllQAnswer.Where(a => a.QuestionId == qq.Id && a.RowId == da.Id && a.ColumId == col.Id && a.EachSurveyUniqueNo == co).FirstOrDefault();  
  501.                                   if (fin != null)  
  502.                                   {  
  503.                                       if(fin.Description==null)  
  504.                                       {  
  505.                                           rans1.Append(ConstructCell("NA", CellValues.String));  
  506.                                       }  
  507.                                       else  
  508.                                       {  
  509.                                           rans1.Append(ConstructCell(fin.Description, CellValues.String));  
  510.                                       }  
  511.                                         
  512.                                   }  
  513.                                   else  
  514.                                   {  
  515.                                       rans1.Append(ConstructCell("NA", CellValues.String));  
  516.                                   }  
  517.   
  518.                               }  
  519.                           }  
  520.                       }  
  521.                   }  
  522.                   if (qq.QuestionTypeId == 12)  
  523.                   {  
  524.                       var ans = AllQAnswer.Where(m => m.QuestionId == qq.Id && m.EachSurveyUniqueNo == co).ToList();  
  525.                       if (ans.Count() == 0)  
  526.                       {  
  527.                           //foreach (var da in AllQueAnswerOption.Where(m => m.QuestionId == qq.Id).ToList())  
  528.                           //{  
  529.                           //    rans1.Append(ConstructCell("NA", CellValues.String));  
  530.                           //}  
  531.                           foreach (var da in AllQueAnswerOption.Where(m => m.QuestionId == qq.Id && m.ColumnOption == null && m.DataOptions == null).ToList())  
  532.                           {  
  533.                               foreach (var col in AllQueAnswerOption.Where(m => m.QuestionId == qq.Id && m.AnswerOption == null && m.DataOptions == null).ToList())  
  534.                               {  
  535.                                  // if (col != null)  
  536.                                  // {  
  537.                                       rans1.Append(ConstructCell("NA", CellValues.String));  
  538.                                 //  }  
  539.   
  540.                               }  
  541.                           }  
  542.                       }  
  543.                       else  
  544.                       {  
  545.                           foreach (var da in AllQueAnswerOption.Where(m => m.QuestionId == qq.Id && m.ColumnOption == null && m.DataOptions==null).ToList())  
  546.                           {  
  547.                               foreach (var col in AllQueAnswerOption.Where(m => m.QuestionId == qq.Id && m.AnswerOption == null && m.DataOptions==null).ToList())  
  548.                               {  
  549.                                   var fin = AllQAnswer.Where(a => a.QuestionId == qq.Id && a.RowId == da.Id && a.ColumId == col.Id && a.EachSurveyUniqueNo == co).FirstOrDefault();  
  550.                                   if (fin != null)  
  551.                                   {  
  552.                                       var dataopt = AllQueAnswerOption.Where(a => a.Id == fin.SelectedDataId && a.QuestionId == qq.Id).FirstOrDefault();  
  553.                                       if(dataopt==null)  
  554.                                       {  
  555.                                           rans1.Append(ConstructCell("NA", CellValues.String));  
  556.                                       }  
  557.                                       else  
  558.                                       {  
  559.                                           rans1.Append(ConstructCell(dataopt.DataOptions, CellValues.String));  
  560.                                       }  
  561.                                         
  562.                                   }  
  563.                                   else  
  564.                                   {  
  565.                                       rans1.Append(ConstructCell("NA", CellValues.String));  
  566.                                   }  
  567.   
  568.                               }  
  569.                           }  
  570.                       }  
  571.                   }  
  572.                   if (qq.QuestionTypeId == 13)  
  573.                   {  
  574.                       var ans = AllQAnswer.Where(m => m.QuestionId == qq.Id && m.EachSurveyUniqueNo == co).ToList();  
  575.                       if (ans.Count() == 0)  
  576.                       {  
  577.                           //foreach (var da in AllQueAnswerOption.Where(m => m.QuestionId == qq.Id).ToList())  
  578.                           //{  
  579.                           //    rans1.Append(ConstructCell("NA", CellValues.String));  
  580.                           //}  
  581.                           foreach (var da in AllQueAnswerOption.Where(m => m.QuestionId == qq.Id && m.ColumnOption == null).ToList())  
  582.                           {  
  583.                               foreach (var col in AllQueAnswerOption.Where(m => m.QuestionId == qq.Id && m.AnswerOption == null).ToList())  
  584.                               {  
  585.                                   rans1.Append(ConstructCell("NA", CellValues.String));  
  586.                               }  
  587.                           }  
  588.                       }  
  589.                       else  
  590.                       {  
  591.                           foreach (var da in AllQueAnswerOption.Where(m => m.QuestionId == qq.Id && m.ColumnOption == null).ToList())  
  592.                           {  
  593.                               foreach (var col in AllQueAnswerOption.Where(m => m.QuestionId == qq.Id && m.AnswerOption == null).ToList())  
  594.                               {  
  595.                                   var fin = AllQAnswer.Where(a => a.QuestionId == qq.Id && a.RowId == da.Id && a.ColumId == col.Id && a.EachSurveyUniqueNo == co).FirstOrDefault();  
  596.                                   if (fin != null)  
  597.                                   {  
  598.                                       rans1.Append(ConstructCell(col.ColumnOption, CellValues.String));  
  599.                                   }  
  600.                                   else  
  601.                                   {  
  602.                                       rans1.Append(ConstructCell("NA", CellValues.String));  
  603.                                   }  
  604.   
  605.                               }  
  606.                           }  
  607.                       }  
  608.                   }  
  609.                   if (qq.QuestionTypeId == 14)  
  610.                   {  
  611.                       var ans = AllQAnswer.Where(m => m.QuestionId == qq.Id && m.EachSurveyUniqueNo == co).ToList();  
  612.                       if (ans.Count() == 0)  
  613.                       {  
  614.                           //foreach (var da in AllQueAnswerOption.Where(m => m.QuestionId == qq.Id).ToList())  
  615.                           //{  
  616.                           //    rans1.Append(ConstructCell("NA", CellValues.String));  
  617.                           //}  
  618.                           foreach (var da in AllQueAnswerOption.Where(m => m.QuestionId == qq.Id && m.ColumnOption == null).ToList())  
  619.                           {  
  620.                               foreach (var col in AllQueAnswerOption.Where(m => m.QuestionId == qq.Id && m.AnswerOption == null).ToList())  
  621.                               {  
  622.                                   rans1.Append(ConstructCell("NA", CellValues.String));  
  623.                               }  
  624.                           }  
  625.                       }  
  626.                       else  
  627.                       {  
  628.                           foreach (var da in AllQueAnswerOption.Where(m => m.QuestionId == qq.Id && m.ColumnOption == null).ToList())  
  629.                           {  
  630.                               foreach (var col in AllQueAnswerOption.Where(m => m.QuestionId == qq.Id && m.AnswerOption == null).ToList())  
  631.                               {  
  632.                                   var fin = AllQAnswer.Where(a => a.QuestionId == qq.Id && a.RowId == da.Id && a.ColumId == col.Id && a.EachSurveyUniqueNo == co).FirstOrDefault();  
  633.                                   if (fin != null)  
  634.                                   {  
  635.                                       rans1.Append(ConstructCell(col.ColumnOption, CellValues.String));  
  636.                                   }  
  637.                                   else  
  638.                                   {  
  639.                                       rans1.Append(ConstructCell("NA", CellValues.String));  
  640.                                   }  
  641.   
  642.                               }  
  643.                           }  
  644.                       }  
  645.                   }  
  646.                   if (qq.QuestionTypeId==15)  
  647.                   {  
  648.                       var openeded = AllQAnswer.Where(a => a.QuestionId == qq.Id && a.EachSurveyUniqueNo == co).FirstOrDefault();  
  649.                       if (openeded!=null)  
  650.                       {  
  651.                           rans1.Append(ConstructCell(openeded.Value.ToString(), CellValues.String));  
  652.                       }  
  653.                       else  
  654.                       {  
  655.                           rans1.Append(ConstructCell("NA", CellValues.String));  
  656.                       }  
  657.                   }  
  658.                    
  659.               }  
  660.               count++;  
  661.               sheetData.AppendChild(rans1);  
  662.           }  
  663.            worksheetPart.Worksheet.Save();  
  664.           //sheetData.AppendChild(row);  
  665.           string contentType = "application/excel";  
  666.           spreadsheetDocument.Close();  
  667.             
  668.           return File(fileName, contentType, "surveyExcel_Report"+System.DateTime.Now+".xlsx");  
  669.           //return View();  
  670.       }  
 
 
Answers (4)
Next Recommended Forum