internal void Cargar(DataGridView dgView, string SLibro) { try { System.Data.OleDb.OleDbConnection MyConnection; System.Data.DataSet DtSet; System.Data.OleDb.OleDbDataAdapter MyCommand; MyConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + SLibro + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1'"); MyConnection.Open(); System.Data.DataTable dt = MyConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); string sheetName = string.Empty; if (dt != null) { for (int i = 0; i < dt.Rows.Count; i++) { if (!dt.Rows[i]["TABLE_NAME"].ToString().ToUpper().Trim().Contains("PRINT_TITLES")) { sheetName = dt.Rows[i]["TABLE_NAME"].ToString().Trim(); break; } } } MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [" + sheetName + "]", MyConnection); MyCommand.TableMappings.Add("Table", "TestTable"); DtSet = new System.Data.DataSet(); MyCommand.Fill(DtSet); dgView.DataSource = DtSet.Tables[0]; dgView.AutoResizeColumns(); MyConnection.Close(); GuardarDatos Guardar = new GuardarDatos(); if (DtSet.Tables[0].Rows[3][1].ToString().Trim().ToUpper().Contains("PEQUEÑA MINERÍA")) { try { LblTitulos.Text = "Análisis Químico Pequeña Minería"; typeFile = 0; string sellocontrolCopia = string.Empty; bool contiene = true; string idLab = DtSet.Tables[0].Rows[1][1].ToString().Trim(); for (int i = 10; i < DtSet.Tables[0].Rows.Count; i++) { string selloControl = DtSet.Tables[0].Rows[i][0].ToString().Trim(); if (String.IsNullOrEmpty(sellocontrolCopia)) { contiene = true; sellocontrolCopia = selloControl; } else { if (!selloControl.Contains(sellocontrolCopia)) { contiene = false; sellocontrolCopia = selloControl; } } System.Globalization.NumberStyles style; System.Globalization.CultureInfo culture; style = System.Globalization.NumberStyles.Number | System.Globalization.NumberStyles.AllowCurrencySymbol; culture = System.Globalization.CultureInfo.CreateSpecificCulture("ES-CO"); try { //MessageBox.Show(selloControl, "selloControl"); if (!String.IsNullOrEmpty(selloControl) && !selloControl.ToUpper().Trim().Contains("BLANK_PREP") && !selloControl.ToUpper().Trim().Contains("STD") && !selloControl.ToUpper().Trim().Contains("STD") && !selloControl.ToUpper().Trim().Contains("BLANK")) { //MessageBox.Show("Ingress"); decimal humedad = 0, au = 0, ag = 0, peso = 0; if (!String.IsNullOrEmpty(DtSet.Tables[0].Rows[i][2].ToString().Trim())) { //Forma de capturar decimales y enterior con la cultura de la maquina //var clone = (System.Globalization.CultureInfo)System.Globalization.CultureInfo.InvariantCulture.Clone(); //clone.NumberFormat.NumberDecimalSeparator = ","; //clone.NumberFormat.NumberGroupSeparator = "."; //string s = DtSet.Tables[0].Rows[i][2].ToString().Trim(); //decimal d = decimal.Parse(s, clone); //MessageBox.Show(DtSet.Tables[0].Rows[i][2].ToString().Trim(), "Mensaje2.1.1"); if (!Decimal.TryParse(DtSet.Tables[0].Rows[i][2].ToString().Trim().Replace(".", ","), style, culture, out humedad)) { //MessageBox.Show(humedad.ToString(), "MensajeCC2."); humedad = decimal.Parse(DtSet.Tables[0].Rows[i][2].ToString().Trim());//.Replace(".", ",").Substring(1, DtSet.Tables[0].Rows[i][2].ToString().Length-1)); //MessageBox.Show(humedad.ToString(), "MensajeCC2.1.1.2"); } //MessageBox.Show(humedad.ToString(), "Mensaje2.1.3"); } if (!String.IsNullOrEmpty(DtSet.Tables[0].Rows[i][3].ToString().Trim())) { //MessageBox.Show(DtSet.Tables[0].Rows[i][3].ToString().Trim(), "Mensaje2.1.4.0"); if (!Decimal.TryParse(DtSet.Tables[0].Rows[i][3].ToString().Trim().Replace(".", ","), style, culture, out au)) { au = decimal.Parse(DtSet.Tables[0].Rows[i][3].ToString().Trim());//.Replace(".", ",").Substring(1, DtSet.Tables[0].Rows[i][3].ToString().Length - 1), style, culture); //MessageBox.Show(au.ToString(), "Mensaje2.1.4.1"); } } //MessageBox.Show(au.ToString(), "Mensaje2.1.4.2"); if (!String.IsNullOrEmpty(DtSet.Tables[0].Rows[i][4].ToString().Trim())) if (!Decimal.TryParse(DtSet.Tables[0].Rows[i][4].ToString().Trim().Replace(".", ","), style, culture, out ag)) ag = decimal.Parse(DtSet.Tables[0].Rows[i][4].ToString().Trim());//.Replace(".", ",").Substring(1, DtSet.Tables[0].Rows[i][4].ToString().Length - 1)); //MessageBox.Show(ag.ToString(), "Mensaje2.1.5"); if (!String.IsNullOrEmpty(DtSet.Tables[0].Rows[i][5].ToString().Trim())) { if (!Decimal.TryParse(DtSet.Tables[0].Rows[i][5].ToString().Trim().Replace(".", ","), style, culture, out peso)) { peso = decimal.Parse(DtSet.Tables[0].Rows[i][5].ToString().Trim());//.Replace(".", ",").Substring(1, DtSet.Tables[0].Rows[i][5].ToString().Length - 1)); } //MessageBox.Show(peso.ToString(), "Mensaje2.1.6"); } //MessageBox.Show("Mensaje2.5"); SqlParameter[] ParamSQl = GuardarDatos.Parametros_DetalleExcelPM("", selloControl, humedad, au, ag, peso, "1", idLab); Guardar.Numerico("Sp_Moficiar_AnaQuiPM", ParamSQl); //MessageBox.Show("Mensaje2.6"); if (humedad > 0) { ParamSQl = GuardarDatos.Parametros_ToneladaSeca(selloControl, humedad); Guardar.Numerico("Sp_Moficiar_ToneladasSeca_MuestreoPM", ParamSQl); } //MessageBox.Show("Mensaje2.7"); } } catch (Exception ms) { MessageBox.Show(string.Concat("Sello:", selloControl, " EX:", ms.Message), "selloControl"); throw; } //else // break; } MessageBox.Show("Importacion Finalizada"); } catch (Exception ex) { MessageBox.Show(ex.Message); } } else { if (DtSet.Tables[0].Rows[15][3].ToString().Trim().ToUpper().Contains("HUM")) { MessageBox.Show("Mensaje3"); LblTitulos.Text = "Humedad Laboratorio Zandor"; typeFile = 2; for (int i = 45; i < DtSet.Tables[0].Rows.Count; i++) { string selloControl = DtSet.Tables[0].Rows[i][0].ToString().Replace(" ", ""); if (!String.IsNullOrEmpty(selloControl)) { decimal humedad = 0; if (!String.IsNullOrEmpty(DtSet.Tables[0].Rows[i][3].ToString().Trim())) if (!Decimal.TryParse(DtSet.Tables[0].Rows[i][3].ToString().Trim().Replace(".", ","), out humedad)) humedad = decimal.Parse(DtSet.Tables[0].Rows[i][3].ToString().Trim().Replace(".", ",").Substring(1, DtSet.Tables[0].Rows[i][3].ToString().Length - 1)); SqlParameter[] ParamSQl = GuardarDatos.Parametros_DetalleExcelHumedad("", selloControl, humedad, "1"); Guardar.Numerico("Sp_Moficiar_AnaQuiHum", ParamSQl); } else break; } MessageBox.Show("Importacion Finalizada"); } else { if (DtSet.Tables[0].Rows[0][0].ToString().Trim().ToUpper().Contains("LABORATORIO") && DtSet.Tables[0].Rows[2][0].ToString().Trim().ToUpper().Contains("REPORTE DE ANÁLISIS QUÍMICO")) { MessageBox.Show("Mensaje4"); LblTitulos.Text = "Analisis Laboratorio Químico Zandor Capital"; typeFile = 1; for (int i = 46; i < DtSet.Tables[0].Rows.Count; i++) { string selloControl = DtSet.Tables[0].Rows[i][0].ToString().Replace(" ", ""); if (!String.IsNullOrEmpty(selloControl)) { decimal au = 0, augr = 0; if (!String.IsNullOrEmpty(DtSet.Tables[0].Rows[i][1].ToString().Trim())) if (!Decimal.TryParse(DtSet.Tables[0].Rows[i][1].ToString().Trim().Replace(".", ","), out au)) au = decimal.Parse(DtSet.Tables[0].Rows[i][1].ToString().Trim().Replace(".", ",").Substring(1, DtSet.Tables[0].Rows[i][1].ToString().Length - 1)); if (!String.IsNullOrEmpty(DtSet.Tables[0].Rows[i][2].ToString().Trim())) if (!Decimal.TryParse(DtSet.Tables[0].Rows[i][2].ToString().Trim().Replace(".", ","), out augr)) augr = decimal.Parse(DtSet.Tables[0].Rows[i][2].ToString().Replace(".", ",").Trim().Substring(1, DtSet.Tables[0].Rows[i][2].ToString().Length - 1)); SqlParameter[] ParamSQl = GuardarDatos.Parametros_DetalleExcelZandor("", selloControl, au, augr, "1"); Guardar.Numerico("Sp_Moficiar_AnaQuiZandor", ParamSQl); } else break; } MessageBox.Show("Importacion Finalizada"); } else { if (DtSet.Tables[0].Rows[2][0].ToString().Trim().ToUpper().Contains("REPORTE DE ANÁLISIS QUÍMICO") || DtSet.Tables[0].Rows[1][0].ToString().Trim().ToUpper().Contains("REPORTE DE ANÁLISIS QUÍMICO")) { MessageBox.Show("Mensaje5"); LblTitulos.Text = "Reporte de Análisis Químico"; typeFile = 1; for (int i = 48; i < DtSet.Tables[0].Rows.Count; i++) { string selloControl = DtSet.Tables[0].Rows[i][0].ToString().Replace(" ", ""); if (!selloControl.ToUpper().Contains("DUPLIC")) { if (selloControl.Contains("+") || selloControl.Contains("-")) { int valueSeparator = selloControl.IndexOf("("); selloControl = selloControl.Substring(0, valueSeparator); if (DtSet.Tables[0].Rows[i][0].ToString().Contains("-")) selloControl = string.Concat(selloControl, "A"); if (!String.IsNullOrEmpty(selloControl)) { decimal au = 0, augr = 0; if (!String.IsNullOrEmpty(DtSet.Tables[0].Rows[i][1].ToString().Trim())) if (!Decimal.TryParse(DtSet.Tables[0].Rows[i][1].ToString().Trim().Replace(",", "."), out au)) au = decimal.Parse(DtSet.Tables[0].Rows[i][1].ToString().Trim().Replace(".", ",").Substring(0, DtSet.Tables[0].Rows[i][1].ToString().Length)); SqlParameter[] ParamSQl = GuardarDatos.Parametros_DetalleExcelReanalisis(selloControl, au); Guardar.Numerico("Sp_Moficiar_AnaPMR", ParamSQl); } else break; } else { if (!String.IsNullOrEmpty(selloControl)) { decimal au = 0, augr = 0; if (!String.IsNullOrEmpty(DtSet.Tables[0].Rows[i][1].ToString().Trim())) if (!Decimal.TryParse(DtSet.Tables[0].Rows[i][1].ToString().Trim().Replace(".", ","), out au)) au = decimal.Parse(DtSet.Tables[0].Rows[i][1].ToString().Trim().Replace(".", ",").Substring(1, DtSet.Tables[0].Rows[i][1].ToString().Length - 1)); SqlParameter[] ParamSQl = GuardarDatos.Parametros_DetalleExcelReanalisis(selloControl, au); Guardar.Numerico("Sp_Moficiar_AnaPMR", ParamSQl); } else break; } } } } } } } } catch (Exception oMsg) { MessageBox.Show(oMsg.Message, "Mensaje", MessageBoxButtons.OK, MessageBoxIcon.Error); } }