protected void BtnMax_Click(object sender, EventArgs e) { try { string conString = ""; using (OleDbConnection conn = new OleDbConnection(conString)) { conn.Open(); OleDbCommand cmd = new OleDbCommand(); cmd.Connection = conn; OleDbDataAdapter objDA = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", conn); DataSet ds = new DataSet(); objDA.Fill(ds); using (testDBEntities dc = new testDBEntities()) { foreach (DataRow dr in ds.Tables[0].Rows) { string grades = dr["Califiacion"].ToString(); string sheetName = dr["Grupo"].ToString(); if (!sheetName.EndsWith("$")) { continue; } cmd.CommandText = "SELECT Nombres, ApellidoMaterno, ApellidoPaterno FROM [" + sheetName + "] WHERE MAX(grades)"; } } } } catch (Exception) { throw; } }
private void PopulateData() { using (testDBEntities t = new testDBEntities()) { GvData.DataSource = t.Grupo.ToList(); GvData.DataBind(); } }
private void PopulateDataReport() { using (testDBEntities dc = new testDBEntities()) { var v = dc.Grupo.ToList(); GvDataGrades.DataSource = v; GvDataGrades.DataBind(); Chart1.DataSource = v; Chart1.DataBind(); } }
public void ImportExcel() { if (FileUpload1.PostedFile.ContentType == "application/vnd.ms-excel" || FileUpload1.PostedFile.ContentType == "application/vdn.openxmlformats-officedocument.spreadsheetml.sheet") { try { string fileName = Path.Combine(Server.MapPath("~/ImportDocument"), Guid.NewGuid().ToString() + Path.GetExtension(FileUpload1.PostedFile.FileName)); FileUpload1.PostedFile.SaveAs(fileName); string ext = Path.GetExtension(FileUpload1.PostedFile.FileName); string conString = ""; if (ext.ToLower() == ".xls") { conString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties =\"Excel 8.0;HDR=Yes;IMEX=2\""; } else if (ext.ToLower() == ".xlsx") { conString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties =\"Excel 12.0;HDR=Yes;IMEX=2\""; } string query = "Select [Alumno ID], [Nombres], [Apellido Materno], [Apellido Paterno], [Fecha Nacimiento], [Grado], [Grupo1], [Calificacion]"; OleDbConnection con = new OleDbConnection(conString); if (con.State == System.Data.ConnectionState.Closed) { con.Open(); } OleDbCommand cmd = new OleDbCommand(query, con); OleDbDataAdapter da = new OleDbDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); da.Dispose(); con.Close(); con.Dispose(); //pasando a bd using (testDBEntities dc = new testDBEntities()) { foreach (DataRow dr in ds.Tables[0].Rows) { string aluId = dr["Alumno ID"].ToString(); var v = dc.Grupo.Where(a => a.AlumnoId.Equals(aluId)).FirstOrDefault(); if (v != null) { //update v.Nombres = dr["Nombres"].ToString(); v.ApellidoMaterno = dr["Apellido Materno"].ToString(); v.ApellidoPaterno = dr["Apellido Paterno"].ToString(); v.FechaNacimiento = dr["Fecha Nacimiento"].ToString(); v.Grupo1 = dr["Grupo1"].ToString(); string grdo = dr["Grado"].ToString(); string grades = dr["Calificacion"].ToString(); } else { //insert dc.Grupo.Add(new Grupo { AlumnoId = Convert.ToInt32(dr["Alumno ID"]), Nombres = dr["Nombres"].ToString(), ApellidoMaterno = dr["Apellido Materno"].ToString(), ApellidoPaterno = dr["Apellido Paterno"].ToString(), FechaNacimiento = dr["Fecha Nacimiento"].ToString(), Grado = Convert.ToInt32(dr["Grado"]), Grupo1 = dr["Grupo"].ToString(), Calificacion = Convert.ToInt32(dr["Calificacion"]) }); } } dc.SaveChanges(); } PopulateData(); LblMessage.Text = "Se importo correctamente el documento!"; } catch (Exception) { throw; } } }