public bool UpdatePersonFoto(IEngineTool Tool, IEngineHttp Funcion) { Excel.Application xlApp = new Excel.Application(); Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"C:\Users\ASUS\Downloads\CARNETIZACION\Rosa\List\NUEVO.xlsx"); Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1]; Excel.Range xlRange = xlWorksheet.UsedRange; xlApp.DisplayAlerts = false; int rowCount = xlRange.Rows.Count; int colCount = xlRange.Columns.Count; string strValue = string.Empty; bool validate = false; string file = string.Empty; string foto = string.Empty; string dni = string.Empty; string s = "#"; for (int fila = 2; fila <= rowCount; fila++) { strValue = string.Empty; for (int columna = 1; columna <= colCount; columna++) { try { if (xlRange.Cells[fila, columna] != null) { if (columna == 1)//foto { if (xlRange.Cells[fila, columna].Value != null) { var g = (xlRange.Cells[fila, columna].Value2.ToString()).Replace("/", "#").Trim(); string[] part = g.Split('#'); file = @"C:\Users\ASUS\Downloads\CARNETIZACION\Rosa\List\fotopeq" + part[1].Trim(); validate = Tool.ExistsFile(file); if (validate) { foto = file; } else { file = @"C:\Users\ASUS\Downloads\CARNETIZACION\Rosa\List\fotopeq" + part[1].Trim(); foto = file; } } else { file = "NO_DEFINIDO"; foto = file; } strValue = strValue + foto + s; } else if (columna == 2)//dni documento { try { if (xlRange.Cells[fila, columna].Value2 != null) { dni = xlRange.Cells[fila, columna].Value2.ToString().Trim(); } else { dni = xlRange.Cells[fila, columna].Value2.ToString().Trim(); } } catch { dni = "-"; } strValue = strValue + dni + s; } } //************************************************************************************************************************************************************************* } catch (Exception ex) { string err = ex.ToString(); } } foto = Tool.ConvertImgTo64Img(foto); validate = Funcion.UpdatePersonFoto(dni, foto); strValue = string.Empty; } GC.Collect(); GC.WaitForPendingFinalizers(); while (Marshal.ReleaseComObject(xlRange) != 0) { ; } while (Marshal.ReleaseComObject(xlWorksheet) != 0) { ; } xlWorkbook.Close(); while (Marshal.ReleaseComObject(xlWorkbook) != 0) { ; } xlApp.Quit(); while (Marshal.ReleaseComObject(xlApp) != 0) { ; } ReadWriteTxt(@"C:\Users\ASUS\Downloads\CARNETIZACION\Rosa\List\NUEVO.xlsx"); return(validate); }
public List <Person> LeerArchivo(string pathArchivo, IEngineTool Tool) { DataTable dt = new DataTable(); dt = BuildColumnDataTable(dt); Person p = new Person(); List <Person> lp = new List <Person>(); Excel.Application application = new Excel.Application(); Excel.Workbook workbook = application.Workbooks.Open(pathArchivo); Excel.Worksheet worksheet = workbook.Sheets[1]; Excel.Range xlRange = worksheet.UsedRange; application.DisplayAlerts = false; int rowCount = xlRange.Rows.Count; int colCount = xlRange.Columns.Count; string foto = string.Empty; string nombre = string.Empty; string apellido = string.Empty; string dni = string.Empty; string matricula = string.Empty; string rh = string.Empty; string grado = string.Empty; string grupo = string.Empty; string email = string.Empty; string empresa = string.Empty; string turno = string.Empty; string file = string.Empty; string strValue = string.Empty; int idx = 0; for (int fila = 2; fila <= rowCount; fila++) { try { if (worksheet.Cells[fila, 1].Value != null && worksheet.Cells[fila, 1].Value != string.Empty) { foto = worksheet.Cells[fila, 1].Value; if (!Tool.ExistsFile(foto)) { foto = "NO_EXISTE_FOTO"; } } else { foto = "NO_DEFINIDO"; } if (worksheet.Cells[fila, 2].Value != null && worksheet.Cells[fila, 2].Value != string.Empty) { nombre = worksheet.Cells[fila, 2].Value; } else { nombre = "NO_DEFINIDO"; } if (worksheet.Cells[fila, 3].Value != null && worksheet.Cells[fila, 3].Value != string.Empty) { apellido = worksheet.Cells[fila, 3].Value; } else { apellido = "NO_DEFINIDO"; } if (worksheet.Cells[fila, 4].Value != null) { dni = worksheet.Cells[fila, 4].Value.ToString(); } else { dni = "NO_DEFINIDO"; } if (worksheet.Cells[fila, 5].Value != null) { matricula = worksheet.Cells[fila, 5].Value.ToString(); } else { matricula = "NO_DEFINIDO"; } if (worksheet.Cells[fila, 6].Value != null && worksheet.Cells[fila, 6].Value != string.Empty) { rh = worksheet.Cells[fila, 6].Value; } else { rh = "NO_DEFINIDO"; } if (worksheet.Cells[fila, 7].Value != null && worksheet.Cells[fila, 7].Value != string.Empty) { grado = worksheet.Cells[fila, 7].Value; } else { grado = "NO_DEFINIDO"; } if (worksheet.Cells[fila, 8].Value != null && worksheet.Cells[fila, 8].Value != string.Empty) { grupo = worksheet.Cells[fila, 8].Value; } else { grupo = "NO_DEFINIDO"; } if (worksheet.Cells[fila, 9].Value != null && worksheet.Cells[fila, 9].Value != string.Empty) { email = worksheet.Cells[fila, 9].Value; } else { email = "NO_DEFINIDO"; } if (worksheet.Cells[fila, 10].Value != null && worksheet.Cells[fila, 10].Value != string.Empty) { empresa = worksheet.Cells[fila, 10].Value; } else { empresa = "NO_DEFINIDO"; } if (worksheet.Cells[fila, 11].Value != null && worksheet.Cells[fila, 11].Value != string.Empty) { turno = worksheet.Cells[fila, 11].Value; if (turno.ToUpper() == "MAÑANA") { turno = "1"; } else if (turno.ToUpper() == "TARDE") { turno = "2"; } else if (turno.ToUpper() == "NOCHE") { turno = "3"; } } else { turno = "NO_DEFINIDO"; } strValue = foto + "#" + nombre + "#" + apellido + "#" + dni + "#" + matricula + "#" + rh + "#" + grado + "#" + grupo + "#" + email.ToLower().Trim() + "#" + empresa + "#" + turno + "#"; p = SetListPerson(strValue, Tool); if (!string.IsNullOrEmpty(p.Email)) { lp.Insert(idx, p); idx++; } else { p.Email = NoDefinido("*****@*****.**", Tool); lp.Insert(idx, p); idx++; } dt = SetDataTable(strValue, dt, idx); } catch (Exception ex) { string error = ex.ToString(); } } Valor.SetDt(dt); return(lp); }