public async Task <IEnumerable <object> > PostSP([FromBody] ClassInput sData) { var returnObject = new List <dynamic>(); using (var cmd = _context.Database.GetDbConnection().CreateCommand()) { cmd.CommandText = "dbo.SP_Test1"; cmd.CommandType = CommandType.StoredProcedure; //加入參數 cmd.Parameters.Add(new SqlParameter("item", sData.bottom)); cmd.Parameters.Add(new SqlParameter("field", sData.high)); if (cmd.Connection.State != ConnectionState.Open) { cmd.Connection.Open(); } var retObject = new List <dynamic>(); using (var dataReader = await cmd.ExecuteReaderAsync()) { while (await dataReader.ReadAsync()) { //定義ExpandoObject,代表無成員物件,意指可以再執行階段動態新增及刪除成員 var dataRow = new ExpandoObject() as IDictionary <string, object>; for (var iFiled = 0; iFiled < dataReader.FieldCount; iFiled++) { dataRow.Add( //取得欄位名稱 dataReader.GetName(iFiled), //如果欄位值為null,則回傳null dataReader.IsDBNull(iFiled) ? null : dataReader[iFiled] ); } //將取得的dataRow加入動態List中 retObject.Add((ExpandoObject)dataRow); } } //返回sp results return(retObject); } }
public IActionResult GenerateExcel([FromBody] ClassInput input) { string path = ""; List <string> totalStudent = new List <string>(); List <string> studentsPresent = new List <string>(); List <string> absentNames = new List <string>(); string csvAbsentee = ""; string[] excelRows = input.AttendenceData.Split('\n', StringSplitOptions.RemoveEmptyEntries); foreach (string row in excelRows) { string[] rowArray = row.Split('\t', StringSplitOptions.RemoveEmptyEntries); if (rowArray.Length >= 2) { if (DateTime.TryParse(rowArray[0], CultureInfo.InvariantCulture, DateTimeStyles.None, out DateTime dateTime)) { if (dateTime.Date == DateTime.Now.Date) { string[] studentDetail = rowArray[1].Split(' ', StringSplitOptions.RemoveEmptyEntries); studentsPresent.Add(studentDetail[0]); } } } } switch (input.ClassName) { //case "6": // path = AppDomain.CurrentDomain.BaseDirectory + "//Excel//VI A.xlsx"; // break; case "7": path = AppDomain.CurrentDomain.BaseDirectory + "//Excel//VII C.xlsx"; break; case "8": path = AppDomain.CurrentDomain.BaseDirectory + "//Excel//8c.xlsx"; break; case "9": path = AppDomain.CurrentDomain.BaseDirectory + "//Excel//9A.xlsx"; break; case "10": path = AppDomain.CurrentDomain.BaseDirectory + "//Excel//10C.xlsx"; break; } if (!string.IsNullOrEmpty(path)) { FileInfo fileInfo = new FileInfo(path); ExcelPackage.LicenseContext = LicenseContext.NonCommercial; ExcelPackage package = new ExcelPackage(fileInfo); ExcelWorksheet worksheet = package.Workbook.Worksheets.FirstOrDefault(); // get number of rows and columns in the sheet int rows = worksheet.Dimension.Rows; // 20 //int columns = worksheet.Dimension.Columns; // 7 //loop through the worksheet rows and columns for (int i = 1; i <= rows; i++) { totalStudent.Add( worksheet.Cells[i, 1].Value == null ? "" : worksheet.Cells[i, 1].Value.ToString()); } List <string> absentee = totalStudent.Except(studentsPresent).ToList(); foreach (string num in absentee) { if (int.TryParse(num, out int rollNo)) { absentNames.Add(worksheet.Cells[rollNo, 2].Value == null ? "" : worksheet.Cells[rollNo, 2].Value.ToString()); } } csvAbsentee = string.Join(',', absentNames); } //var smtpClient1 = new SmtpClient("smtp.gmail.com") //{ // Port = 587, // Credentials = new NetworkCredential("*****@*****.**", "Scorpion@#$123"), // EnableSsl = true, //}; //var mailMessage = new MailMessage //{ // From = new MailAddress("*****@*****.**"), // Subject = input.ClassName + " absentee list | " + DateTime.Now.ToString("dd/MM/yyyy"), // Body = "<h1>Hello, below rollNo were absent today</h1> <p>" + csvAbsentee + "</p>", // IsBodyHtml = true, //}; //mailMessage.To.Add("*****@*****.**"); //try //{ // SmtpClient smtpClient = new SmtpClient("smtp.mail.yahoo.com", 465); // smtpClient.UseDefaultCredentials = false; // smtpClient.Credentials = new NetworkCredential() // { // UserName = "******", // Password = "******" // }; // smtpClient.DeliveryMethod = SmtpDeliveryMethod.Network; // smtpClient.EnableSsl = true; // smtpClient.Send("*****@*****.**", "*****@*****.**", "Account verification", "hhhhhh"); //} //catch (Exception ex) //{ // csvAbsentee = ex.Message; //} if (csvAbsentee.EndsWith(",")) { csvAbsentee = csvAbsentee.TrimEnd(','); } return(Ok(csvAbsentee)); }