private void LeerExcel(string institucion)
{
string conStr = "";
string filename = ViewState["nombreExcel"].ToString();
string path = Server.MapPath("~/Temp") + "/" + filename;
string tieneCabecera = "YES";
//switch (Extension)
//{
// case ".xls": //Excel 97-03
// conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"]
// .ConnectionString;
// break;
// case ".xlsx": //Excel 07
conStr = ConfigurationManager.ConnectionStrings["ConnectionExcel"]
.ConnectionString;
// break;
//}
conStr = String.Format(conStr, path, tieneCabecera);
OleDbConnection connExcel = new OleDbConnection(conStr);
OleDbCommand cmdExcel = new OleDbCommand();
OleDbDataAdapter oda = new OleDbDataAdapter();
DataTable dt = new DataTable();
cmdExcel.Connection = connExcel;
//Get the name of First Sheet
connExcel.Open();
DataTable dtExcelSchema;
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
connExcel.Close();
//Read Data from First Sheet
connExcel.Open();
cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
oda.SelectCommand = cmdExcel;
oda.Fill(dt);
connExcel.Close();
List <DataRow> list = new List <DataRow>(dt.Select());
List <TalentosReference.ParticipanteBE> lstParticipantes;
lstParticipantes = (from DataRow row in dt.Rows
where row["DNI"].ToString() != ""
select new TalentosReference.ParticipanteBE
{
Nombres = row["NOMBRES"].ToString().Trim(),
ApellidoPaterno = row["APELLIDO PATERNO"].ToString().Trim(),
ApellidoMaterno = row["APELLIDO MATERNO"].ToString().Trim(),
DNI = row["DNI"].ToString().Trim(),
Cargo = row["CARGO EN LA EMPRESA"].ToString().Trim(),
FechaNacimiento = row["FECHA NACIMIENTO"].ToString().Trim(),
Institucion = institucion.Trim(),
NivelInstruccion = row["NIVEL DE INSTRUCCIÓN"].ToString().Trim(),
CorreoElectronico = row["CORREO ELECTRÓNICO"].ToString().Trim(),
Sexo = row["SEXO"].ToString().Trim(),
}).ToList();
TalentosReference.WSTalentos objService = new TalentosReference.WSTalentos();
objService.InsertarParticipante(lstParticipantes.ToArray());
cargarParticipantesMasivos();
//objService.EnviarEmail(lstParticipantes.ToArray());
objService.EnviarEmailCompleted += new TalentosReference.EnviarEmailCompletedEventHandler(objService_EnviarEmailCompleted);
objService.EnviarEmailAsync(lstParticipantes.ToArray());
//Bind Data to GridView
//gvParticipantes.Caption = Path.GetFileName(path);
//gvParticipantes.DataSource = dt;
//gvParticipantes.DataBind();
}