How to export file to excel in linq sql using asp.net mvc

Hi Team

I have excel spreedsheet, but the problem is formatting of data is wrong and need some help on my sql query when using linq on my MVC application. The reqirement is that first column(year must start with 2021), second column(week must be 9 etc if there is 10 must continue per year week etc), Day column must be 03/02/2021 if there was a schedule on this day. VW250 this model column be 79 if there was a model produce, VW250/2PA this model column must be 574. Below snipping image is my current output and formatting of it is not what is expecting and need help. Second is what is required for this exercise

// Model class
public class ExtractionViewModel
{
public string Year { get; set; }
public int Week { get; set; }
public DateTime Day { get; set; }
public string VW250 { get; set; }
public string VW270 { get; set; }
public string VW2502PA { get; set; }
public string VW270PA { get; set; }
}

// Controller
public void ExportToExcel()
{
var v = new GridView();
v.DataSource = this.GetExtractionViewModels();
v.DataBind();
Response.ClearContent();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment; filename=ExtractionRecords.xls");
Response.ContentType = "application/ms-excel";
Response.Charset = "";
StringWriter objStringWriter = new StringWriter();
HtmlTextWriter htmlTextWriter = new HtmlTextWriter(objStringWriter);
v.RenderControl(htmlTextWriter);
Response.Output.Write(objStringWriter.ToString());
Response.Flush();
Response.End();
//return View("DataResult");
}
public ActionResult DataResult()
{
return View(this.GetExtractionViewModels());
}
public IList GetExtractionViewModels()
{
var db = new ProductionManagementEntities();
var scheduleList = (from p in db.ProductionDays
from m in db.Models
join w in db.Weeks on p.WeekId equals w.WeekId
orderby w.Year ascending
orderby m.Name ascending
where(m.InActive == true)
select new ExtractionViewModel
{
Year = w.Year,
Week = w.WeekNum,
Day = p.ProductionDate,
VW250 = m.Name,
VW270 = m.Name,
VW2502PA = m.Name,
VW270PA = m.Name
}).ToList();
return scheduleList;
}

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Guest User5y ago
Hi Sachin

The data on the excel seem to be duplicate, eg. 2021(count many times), Week also. What needs to change on the query side? I checked on the front end, if WeekNum count 1, year 2021 there is no more
0
Guest User5y ago
Hi Sachin

The output seem now to work on Year, Week, but Day and Model. I need help there as well. have a look at the output as excel after the initial fix. Should count be use for Model colum(VW250) i know their data type is string, but the requirement they must count as integer, Day be 03/02/2021

// Requirement as output below

0
Sachin Singh5y ago
simply order by year,week,day,vw250,vw270
var scheduleList = (from p in db.ProductionDays
from m in db.Models
join w in db.Weeks on p.WeekId equals w.WeekId
orderby w.year,w.name,p.ProductionDate,m.Name
where(m.InActive == true)
select new ExtractionViewModel
{
Year = w.Year,
Week = w.WeekNum,
Day = p.ProductionDate,
VW250 = m.Name,
VW270 = m.Name,
VW2502PA = m.Name,
VW270PA = m.Name
}).ToList();
0