Cannot Export Excel file. from Ajax Post to Contoller

Question

Hi Guys, i need some help.. what be the problem.. i cannot export the data in the controller .. if theres a another way to export the file.

This my code

$.ajax({

type: "post",

url: "/shift/ajax_getReportsExl2",

data: ({

sdate: sdate,

datas: json_data,

SortBy: sort,

SortHow: sorthow2

}),

async: false,

success: function (data) {

}

});

 

 

 

 

Controller 

 

public FileStreamResult ajax_getReportsExl2(string sDate, string datas, int SortBy = 0, int SortHow = 0 )

{

WebClient client = new WebClient();

SLDocument sl = new SLDocument();

sl.RenameWorksheet(SLDocument.DefaultFirstSheetName, "CSAttendanceReports");

SLStyle style1 = sl.CreateStyle();

style1.SetHorizontalAlignment(HorizontalAlignmentValues.Center);

SLStyle style2 = sl.CreateStyle();

style2.Font.Bold = true;

DateTime date1;

DateTime.TryParse(sDate, out date1);

if (date1 == null)

throw new Exception("<i>Invalid date!</i>");

bool isTL = UserPermission.IsAuthorized(SessionHelper.EmployeeId, PermissionType.VIEW_SHIFT_TL);

var taskTypes = csTaskTrackerDb.TaskTypes;

if (empId == 0)

empId = SessionHelper.EmployeeId;

DateTime dtSDate = Convert.ToDateTime(sDate);

var thisEmp = EmpDb.Employees.Find(empId);

var Dept = EmpDb.Departments.Find(thisEmp.DepartmentID).DepartmentID;

List<AgentAttendance> reports = new List<AgentAttendance>();

List<int> columnTotal = new List<int>();

JObject object_data = JObject.Parse(datas);

DateTime endDate = dtSDate.AddDays(1); //end of date

if (Dept > -1)

{

var employees = EmpDb.Employees.Where(m => (Dept == 0 ? true : m.DepartmentID == Dept) && (m.LeaveDate == null || m.LeaveDate > DateTime.Today)); //and also, comment these...

foreach (var emp in employees)

{

int rowTotal = 0;

AgentAttendance report = new AgentAttendance();

report.empID = emp.EmployeeID;

var currentStatus = csTaskTrackerDb.AgentStatus.Where(m => m.EmployeeId == emp.EmployeeID && m.TimeChanged >= dtSDate.Date).OrderByDescending(m => m.ID).FirstOrDefault();

int status = 0;

if (currentStatus != null)

status = (int)currentStatus.StatusId;

string iconClass = "";

string iconColor = "";

report.empName = emp.Firstname + " " + emp.Lastname; //column for name

int index = 0;

var empTask = (from task in csTaskTrackerDb.CSTasks where (task.EmployeeID == emp.EmployeeID && task.StartDate == dtSDate) join ctask in csTaskTrackerDb.CSTaskTypes on task.TaskID equals ctask.CSTaskId select ctask.CSTaskDescription).FirstOrDefault();

report.task = empTask == null ? "NONE" : empTask; //Task

var thisEmpShift = Helpers.Helper.GetShift(emp.EmployeeID, emp.DepartmentID.GetValueOrDefault(), dtSDate);

var thisEmpAttendance = csTaskTrackerDb.Attendances.Find(empId, dtSDate);

var thisEmpRemarks = csTaskTrackerDb.CSRemarks.Where(x => x.EmployeeID == empId).FirstOrDefault();

DateTime ShiftStartTime = dtSDate.AddHours(Convert.ToDouble(thisEmpShift.StartTime)).AddHours(-3);

DateTime ShiftEndTime = dtSDate.AddHours(Convert.ToDouble(thisEmpShift.EndTime)).AddHours(+3);

var emp_time = object_data["data"].Children()

.Where(e => e["badgeNumber"].ToString() == emp.EmployeeCode && DateTime.Parse(e["time"].ToString()) > DateTime.Parse(ShiftStartTime.ToString())

&& DateTime.Parse(e["time"].ToString()) < DateTime.Parse(ShiftEndTime.ToString())).Select(t => t["time"]).FirstOrDefault();

report.shiftEndTime = ShiftEndTime; // shift end time;

report.shiftStartTime = ShiftStartTime; // shift start time;

report.shiftCode = thisEmpShift == null ? "Off" : thisEmpShift.Code; // Shift Code

report.time_in = emp_time == null ? "---" : Convert.ToDateTime(emp_time.ToString()).ToString("h:mm tt"); //time in

report.status = emp_time == null ? "ABSENT" : "PRESENT"; //status

report.remarks = (thisEmpRemarks == null ? "NONE" : thisEmpRemarks.Remarks); //remarks

report.employeeCode = emp.EmployeeCode;

reports.Add(report);

}

string sortByStr = "";

switch (SortBy)

{

case 1: sortByStr = "empName"; break;

case 2: sortByStr = "task"; break;

case 3: sortByStr = "shiftCode"; break;

case 4: sortByStr = "timeIn"; break;

case 5: sortByStr = "status"; break;

case 6: sortByStr = "remarks"; break;

}

if (SortBy > 0)

{

if (SortHow == 0)

reports = reports.OrderBy(m => m.GetType().GetProperty(sortByStr).GetValue(m, null)).ToList();

else

reports = reports.OrderByDescending(m => m.GetType().GetProperty(sortByStr).GetValue(m, null)).ToList();

}

}

sl.SetCellValue(1, 1, "Name");

sl.SetColumnWidth(1, 1, 30);

sl.SetCellValue(1, 2, "Task");

sl.SetColumnWidth(1, 2, 10);

sl.SetCellValue(1, 3, "Shift");

sl.SetColumnWidth(1, 3, 30);

sl.SetCellValue(1, 4, "Time In");

sl.SetColumnWidth(1, 4, 20);

sl.SetCellValue(1, 5, "Status");

sl.SetColumnWidth(1, 5, 10);

sl.SetCellValue(1, 6, "Remarks");

int c = 3;

int r = 2;

foreach (var rep in reports)

{

bool isFirst = true;

c = 1;

sl.SetCellValue(++r, c, rep.empName);

sl.SetCellValue(r, ++c, rep.task);

sl.SetCellValue(r, ++c, rep.shiftCode);

sl.SetCellValue(r, ++c, rep.time_in);

sl.SetCellValue(r, ++c, rep.status);

sl.SetCellValue(r, ++c, rep.remarks);

}

sl.AddWorksheet("Attendance Data");

int c1 = 2;

int r1 = 1;

foreach(var reps in reports) {

c1 = 1;

var attendance = object_data["data"].Children().Where(t => t["badgeNumber"].ToString() == reps.employeeCode && DateTime.Parse(t["time"].ToString()) > DateTime.Parse(reps.shiftStartTime.ToString())

&& DateTime.Parse(t["time"].ToString()) < DateTime.Parse(reps.shiftEndTime.ToString()));

sl.SetCellValue(1, 1, "Name");

sl.SetColumnWidth(1, 1, 30);

sl.SetCellValue(1, 2, "Employee Number");

sl.SetColumnWidth(1, 2, 10);

sl.SetCellValue(1, 3, "Transtype");

sl.SetColumnWidth(1, 3, 20);

sl.SetCellValue(1, 4, "Time");

sl.SetColumnWidth(1, 4, 30);

foreach(var att in attendance)

{

c1 = 1;

var emplname = att["name"].ToString();

var bagde = att["badgeNumber"].ToString();

var timeIN = Convert.ToDateTime(att["time"].ToString()).ToString("h:mm tt");

var transtype = (att["transtype"].ToString() == "1" ? "Time In" : "Time Out");

sl.SetCellValue(++r1, c1, reps.empName);

sl.SetCellValue(r1, ++c1, bagde);

sl.SetCellValue(r1, ++c1, transtype);

sl.SetCellValue(r1, ++c1, timeIN);

}

}

var ms = new MemoryStream();

 

sl.SaveAs(ms);

ms.Position = 0;

return File(ms, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "CSAttendanceReports.xlsx");

}

}