public async Task <IActionResult> OnGetAsync(string username, string month) { HttpContext.Session.SetString("ausername", username); HttpContext.Session.SetString("amonth", month); using (NpgsqlConnection connection = new NpgsqlConnection()) { connection.ConnectionString = "Server=103.118.157.29;Port=5432;Database=attendance;User Id=postgres;Password=princetonhive@123;"; connection.Open(); int year = indianTime.Year; if (month == "01") { monthname = "January"; } else if (month == "02") { monthname = "February"; } else if (month == "03") { monthname = "March"; } else if (month == "04") { monthname = "April"; } else if (month == "05") { monthname = "May"; } else if (month == "06") { monthname = "June"; } else if (month == "07") { monthname = "July"; } else if (month == "08") { monthname = "August"; } else if (month == "09") { monthname = "September"; } else if (month == "10") { monthname = "October"; } else if (month == "11") { monthname = "November"; } else if (month == "12") { monthname = "December"; } string txtdate = "00-" + month + "-" + year; string sql = "select name,timein,timeout,date from employee inner join login on login.username=employee.employeeid where timein IS NOT NULL and SUBSTRING(date,3,8)=SUBSTRING('" + txtdate + "',3,8) and employeeid='" + username + "' order by date"; NpgsqlCommand cmd = new NpgsqlCommand(sql, connection); NpgsqlDataReader dr; dr = cmd.ExecuteReader(); while (dr.Read()) { Employeedata1 p = new Employeedata1(); p.Employeeid = Convert.ToString(dr["name"].ToString()); p.Timein = Convert.ToString(dr["timein"].ToString()); p.Timeout = Convert.ToString(dr["timeout"].ToString()); string date1 = Convert.ToString(dr["date"].ToString()); DateTime tmpDate = DateTime.ParseExact(date1, "dd-MM-yyyy HH:mm", null); p.Date = tmpDate.ToString("dd-MM-yyyy"); // p.Date = date1.ToString("dd-MM-yyyy"); EmployeeData1.Add(p); } EmployeeData = EmployeeData1; dr.Close(); connection.Close(); } return(Page()); }
public async Task <IActionResult> OnPostAsync(string savemore) { try { using (NpgsqlConnection connection = new NpgsqlConnection()) { connection.ConnectionString = "Server=103.118.157.29;Port=5432;Database=attendance;User Id=postgres;Password=princetonhive@123;"; connection.Open(); int year = indianTime.Year; int month = Convert.ToInt32(HttpContext.Session.GetString("amonth")); string txtdate = "00-" + month + "-" + year; string sql = "select name,timein,timeout,date from employee inner join login on login.username=employee.employeeid where timein IS NOT NULL and SUBSTRING(date,3,8)=SUBSTRING('" + txtdate + "',3,8) and employeeid='" + HttpContext.Session.GetString("ausername") + "' order by date"; NpgsqlCommand cmd = new NpgsqlCommand(sql, connection); NpgsqlDataReader dr; dr = cmd.ExecuteReader(); while (dr.Read()) { Employeedata1 p = new Employeedata1(); p.Name = Convert.ToString(dr["name"].ToString()); p.Timein = Convert.ToString(dr["timein"].ToString()); p.Timeout = Convert.ToString(dr["timeout"].ToString()); string date1 = Convert.ToString(dr["date"].ToString()); DateTime tmpDate = DateTime.ParseExact(date1, "dd-MM-yyyy HH:mm", null); p.Date = tmpDate.ToString("dd-MM-yyyy"); EmployeeData1.Add(p); } EmployeeData = EmployeeData1; dr.Close(); connection.Close(); } string sWebRootFolder = _hostingEnvironment.WebRootPath; string sFileName = @"MonthlyAttendance.xlsx"; string URL = string.Format("{0}://{1}/{2}", Request.Scheme, Request.Host, sFileName); FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName)); var memory = new MemoryStream(); using (var fs = new FileStream(Path.Combine(sWebRootFolder, sFileName), FileMode.Create, FileAccess.Write)) { IWorkbook workbook; workbook = new XSSFWorkbook(); ISheet excelSheet = workbook.CreateSheet("Employee"); IRow row = excelSheet.CreateRow(0); int i = 1; row.CreateCell(0).SetCellValue("S.No"); row.CreateCell(1).SetCellValue("Name"); row.CreateCell(2).SetCellValue("Date"); row.CreateCell(3).SetCellValue("Time In"); row.CreateCell(4).SetCellValue("Time Out"); foreach (var segment in EmployeeData) { row = excelSheet.CreateRow(i); row.CreateCell(0).SetCellValue(i); row.CreateCell(1).SetCellValue(segment.Name); row.CreateCell(2).SetCellValue(segment.Date); row.CreateCell(3).SetCellValue(segment.Timein); row.CreateCell(4).SetCellValue(segment.Timeout); i++; } workbook.Write(fs); } using (var stream = new FileStream(Path.Combine(sWebRootFolder, sFileName), FileMode.Open)) { await stream.CopyToAsync(memory); } memory.Position = 0; return(File(memory, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", sFileName)); } catch (Exception ex) { throw ex; } return(Page()); }