Gcobani Mkontwana

Gcobani Mkontwana

  • 1k
  • 1.3k
  • 109.5k

How to use session in linq c# export data as excel

Apr 29 2021 10:01 AM
HI Team
 
I need some help, i have a problem. I want to filter records on my excel sheets as data. Now i dont know how sessions works to export data as excel. Below is my logic i have attempted to join tables using linq. Some how the data itself on excel is not filtering correct values. e.g if i search by YEAR(2021) and Week(53) on query get record this. On the excel itself its not corresponding to my query and need help to it via session.
  1. public IList GetExtractionViewModels()      
  2. {      
  3. var db = new ProductionManagementEntities();      
  4. var scheduleList = (from p in db.ProductionDays      
  5. from m in db.Models      
  6. from mx in db.Models      
  7. from mt in db.Models      
  8. from mv in db.Models      
  9. from wk in db.Models      
  10. join w in db.Weeks on p.WeekId equals w.WeekId      
  11. orderby w.Year descending, m.Name descending, p.ProductionDate descending, w.WeekNum descending, mt.Name descending, mx.Name descending, mv.Name descending      
  12. where (mx.InActive == true)      
  13. where (mt.InActive == false)      
  14. where(m.InActive == false)      
  15. where(mv.InActive == false)      
  16. where(w.WeekNum < 53)      
  17. where (mt.Name == "VW270")      
  18. where(mx.Name == "VW250")      
  19. where(m.Name == "VW270PA")      
  20. where(mv.Name == "VW250/2PA")      
  21. select new ExtractionViewModel      
  22. {      
  23. Year = w.Year,      
  24. Day = p.ProductionDate,      
  25. Week = w.WeekNum,      
  26. VW270 = mt.Name,      
  27. VW270PA = m.Name,      
  28. VW250 = mx.Name,      
  29. VW2502PA = mv.Name      
  30. }).ToList();      
  31. return scheduleList;      
  32. }    
  33.     
  34. // model        
  35. public class ExtractionViewModel        
  36. {        
  37.     public string Year { getset; }       
  38.     public int Week { getset; }      
  39.     [DataType(DataType.Date)]        
  40.     public DateTime Day { getset; }      
  41.     public string VW250 { getset; }      
  42.     public string VW270 { getset; }       
  43.     public string VW2502PA { getset; }        
  44.     public int VW270PA { getset; }        
  45. }  
  1. // Sql for Model      
  2. SELECT TOP (1000) [ModelId]      
  3.       ,[Name]      
  4.       ,[Code]      
  5.       ,[CreatedDate]      
  6.       ,[CreatedBy]      
  7.       ,[ModifiedDate]      
  8.       ,[ModifiedBy]      
  9.       ,[InActive]      
  10.   FROM [ProductionManagement].[Schedule].[Model]      
  11.   where Name = 'VW240'      
  12.   order by InActive    
  13.     
  14. //Week table      
  15.       
  16. SELECT TOP (1000) [WeekId]      
  17.       ,[WeekNum]      
  18.       ,[Year]      
  19.       ,[CreatedDate]      
  20.       ,[CreatedBy]      
  21.       ,[ModifiedDate]      
  22.       ,[ModifiedBy]      
  23.       ,[InActive]      
  24.   FROM [ProductionManagement].[Schedule].[Week]      
  25.       
  26. // ProductionDate      
  27. SELECT TOP (1000) [ProductionDayId]      
  28.       ,[WeekId]      
  29.       ,[ProductionDate]      
  30.       ,[DayOfWeek]      
  31.       ,[CreatedDate]      
  32.       ,[CreatedBy]      
  33.       ,[ModifiedDate]      
  34.       ,[ModifiedBy]      
  35.       ,[InActive]      
  36.   FROM [ProductionManagement].[Schedule].[ProductionDay]  
  1.  // Controller for exporting data    
  2.  public ActionResult List()      
  3.  {      
  4.      string constr = ConfigurationManager.AppSettings["connectionString"];      
  5.      var Client = new MongoClient(constr);      
  6.      var db = Client.GetDatabase("ProductionManagementEntities");      
  7.   
  8.      var collection = db.GetCollection<ExactionViewModel>("ExtractionViewModel").Find(new BsonDocument()).ToList();      
  9.   
  10.      return View(collection);      
  11.  }    
  12.   
  13. void DownloadExcel()      
  14. {      
  15.     string constr = ConfigurationManager.AppSettings["connectionString"];      
  16.     var Client = new MongoClient(constr);      
  17.     var db = Client.GetDatabase("Employee");      
  18.   
  19.     var collection = db.GetCollection<ExactionViewModel>("ExtractionViewModel").Find(new BsonDocument()).ToList();      
  20.           
  21.   
  22.     ExcelPackage Ep = new ExcelPackage();      
  23.     ExcelWorksheet Sheet = Ep.Workbook.Worksheets.Add("Report");      
  24.     Sheet.Cells["A1"].Value = "Year";      
  25.     Sheet.Cells["B1"].Value = "Week";      
  26.     Sheet.Cells["C1"].Value = "Day";      
  27.     Sheet.Cells["D1"].Value = "VW250";      
  28.     Sheet.Cells["E1"].Value = "VW270";      
  29.     int row = 2;      
  30.     foreach (var item in collection)      
  31.     {      
  32.               
  33.         Sheet.Cells[string.Format("A{0}", row)].Value = item.Year;      
  34.         Sheet.Cells[string.Format("B{0}", row)].Value = item.Week;      
  35.         Sheet.Cells[string.Format("C{0}", row)].Value = item.Day;      
  36.         Sheet.Cells[string.Format("D{0}", row)].Value = item.VW250;      
  37.         Sheet.Cells[string.Format("E{0}", row)].Value = item.VW270;      
  38.         row++;      
  39.     }      
  40.           
  41.   
  42.     Sheet.Cells["A:AZ"].AutoFitColumns();      
  43.     Response.Clear();      
  44.     Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";      
  45.     Response.AddHeader("content-disposition""attachment: filename=" + "Report.xlsx");      
  46.     Response.BinaryWrite(Ep.GetAsByteArray());      
  47.     Response.End();      
  48. }