public void ProcesarFondo(Maternal m, string periodo) { var excelAppOut = new ExcelX.Application(); var fecha = DateTime.Now.ToString().Replace("/", "").Replace(":", "").Replace(" ", ""); //var periodo = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString().PadLeft(2, '0'); var rutaEntrada = @"C:\Fondos Nacionales\Templates\IF_MATERNAL"; var rutaSalida = @"C:\Fondos Nacionales\out\" + periodo + @"\Maternal\"; Utilidades.AbrirLibro(excelAppOut, rutaEntrada); //"Feb-17" ExcelX._Worksheet Salida = (ExcelX.Worksheet)excelAppOut.Sheets["Template"]; Salida.Cells["14", "K"] = m.A1.Replace(".", "").Replace(",", ""); Salida.Cells["15", "K"] = m.A2.Replace(".", "").Replace(",", ""); Salida.Cells["18", "I"] = m.A31.Replace(".", "").Replace(",", ""); Salida.Cells["19", "I"] = m.A32.Replace(".", "").Replace(",", ""); Salida.Cells["22", "I"] = m.A41.Replace(".", "").Replace(",", ""); Salida.Cells["23", "I"] = m.A42.Replace(".", "").Replace(",", ""); Salida.Cells["30", "K"] = m.C1.Replace(".", "").Replace(",", ""); Salida.Cells["31", "K"] = m.C2.Replace(".", "").Replace(",", ""); Salida.Cells["32", "K"] = m.C3.Replace(".", "").Replace(",", ""); Salida.Cells["33", "K"] = m.C4.Replace(".", "").Replace(",", ""); Salida.Cells["34", "K"] = m.C5.Replace(".", "").Replace(",", ""); Salida.Cells["37", "I"] = m.C61.Replace(".", "").Replace(",", ""); Salida.Cells["38", "I"] = m.C62.Replace(".", "").Replace(",", ""); Salida.Cells["39", "I"] = m.C63.Replace(".", "").Replace(",", ""); Salida.Cells["40", "I"] = m.C64.Replace(".", "").Replace(",", ""); Salida.Cells["41", "I"] = m.C65.Replace(".", "").Replace(",", ""); Salida.Cells["44", "I"] = m.C71.Replace(".", "").Replace(",", ""); Salida.Cells["45", "I"] = m.C72.Replace(".", "").Replace(",", ""); Salida.Cells["46", "I"] = m.C73.Replace(".", "").Replace(",", ""); Salida.Cells["47", "I"] = m.C74.Replace(".", "").Replace(",", ""); Salida.Cells["48", "I"] = m.C75.Replace(".", "").Replace(",", ""); Salida.Cells["51", "I"] = m.C81.Replace(".", "").Replace(",", ""); Salida.Cells["52", "I"] = m.C82.Replace(".", "").Replace(",", ""); Salida.Cells["53", "I"] = m.C83.Replace(".", "").Replace(",", ""); Salida.Cells["54", "I"] = m.C84.Replace(".", "").Replace(",", ""); Salida.Cells["55", "I"] = m.C85.Replace(".", "").Replace(",", ""); Salida.Cells["58", "I"] = m.C91.Replace(".", "").Replace(",", ""); Salida.Cells["59", "I"] = m.C92.Replace(".", "").Replace(",", ""); Salida.Cells["60", "I"] = m.C93.Replace(".", "").Replace(",", ""); Salida.Cells["61", "I"] = m.C94.Replace(".", "").Replace(",", ""); Salida.Cells["62", "I"] = m.C95.Replace(".", "").Replace(",", ""); Salida.Cells["68", "K"] = m.E1.Replace(".", "").Replace(",", ""); Salida.Cells["69", "K"] = m.E2.Replace(".", "").Replace(",", ""); Salida.Cells["70", "K"] = m.E3.Replace(".", "").Replace(",", ""); Salida.Cells["71", "K"] = m.E4.Replace(".", "").Replace(",", ""); Salida.Cells["72", "K"] = m.E5.Replace(".", "").Replace(",", ""); Salida.Cells["76", "K"] = m.F1.Replace(".", "").Replace(",", ""); Salida.Cells["77", "K"] = m.F2.Replace(".", "").Replace(",", ""); Salida.Cells["78", "K"] = m.F3.Replace(".", "").Replace(",", ""); Salida.Cells["79", "K"] = m.F4.Replace(".", "").Replace(",", ""); Salida.Cells["80", "K"] = m.F5.Replace(".", "").Replace(",", ""); Salida.Cells["84", "K"] = m.G1.Replace(".", "").Replace(",", ""); Salida.Cells["85", "K"] = m.G2.Replace(".", "").Replace(",", ""); Salida.Cells["86", "K"] = m.G3.Replace(".", "").Replace(",", ""); Salida.Cells["87", "K"] = m.G4.Replace(".", "").Replace(",", ""); System.IO.FileAttributes attr; try { attr = System.IO.File.GetAttributes(rutaSalida); } catch (Exception ex) { System.IO.Directory.CreateDirectory(rutaSalida); } Salida.Name = periodo; Salida.SaveAs(rutaSalida + "IFMaternal_" + fecha + Utilidades.ExtensionLibro(Salida.Application.ActiveWorkbook)); excelAppOut.Quit(); System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excelAppOut); }
public void GenerarAnexo(string periodo) { var excelApp = new ExcelX.Application(); var fecha = DateTime.Now.ToString().Replace("/", "").Replace(":", "").Replace(" ", ""); //var periodo = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString().PadLeft(2, '0'); var rutaEntrada = @"C:\Fondos Nacionales\in\" + periodo + @"\ANEXO4"; var rutaSalida = @"C:\Fondos Nacionales\out\" + periodo + @"\Maternal\Anexo\"; var rutaTemplate = @"C:\Fondos Nacionales\Templates\ANEXO_MATERNAL"; ExcelX.Workbook libroEntrada = Utilidades.AbrirLibro(excelApp, rutaEntrada); ExcelX.Workbook libroDestino = Utilidades.AbrirLibro(excelApp, rutaTemplate); //Primero ExcelX._Worksheet Cuadro1 = libroEntrada.Sheets["ResCotizPrevi"]; ExcelX._Worksheet Cuadro1_a6 = libroDestino.Sheets["Anexo 4-Res"]; var from = Cuadro1.Range["D11:D20"]; var to = Cuadro1_a6.Range["D13:D22"]; from.Copy(); to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing); from = Cuadro1.Range["F11:F20"]; to = Cuadro1_a6.Range["E13:E22"]; from.Copy(); to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing); from = Cuadro1.Range["H11:H20"]; to = Cuadro1_a6.Range["F13:F22"]; from.Copy(); to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing); from = Cuadro1.Range["I11:I20"]; to = Cuadro1_a6.Range["G13:G22"]; from.Copy(); to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing); from = Cuadro1.Range["K11:K20"]; to = Cuadro1_a6.Range["H13:H22"]; from.Copy(); to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing); from = Cuadro1.Range["L11:L20"]; to = Cuadro1_a6.Range["I13:I22"]; from.Copy(); to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing); from = Cuadro1.Range["M11:M20"]; to = Cuadro1_a6.Range["J13:J22"]; from.Copy(); to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing); //SPRT from = Cuadro1.Range["D24:D29"]; to = Cuadro1_a6.Range["D26:D31"]; from.Copy(); to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing); from = Cuadro1.Range["H24:H29"]; to = Cuadro1_a6.Range["F26:F31"]; from.Copy(); to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing); from = Cuadro1.Range["I24:I29"]; to = Cuadro1_a6.Range["G26:G31"]; from.Copy(); to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing); from = Cuadro1.Range["K24:K29"]; to = Cuadro1_a6.Range["H26:H31"]; from.Copy(); to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing); from = Cuadro1.Range["L24:L29"]; to = Cuadro1_a6.Range["I26:I31"]; from.Copy(); to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing); from = Cuadro1.Range["M24:M29"]; to = Cuadro1_a6.Range["J26:J31"]; from.Copy(); to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing); from = Cuadro1.Range["F33:F34"]; to = Cuadro1_a6.Range["E35:E36"]; from.Copy(); to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing); from = Cuadro1.Range["H33:H34"]; to = Cuadro1_a6.Range["F35:F36"]; from.Copy(); to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing); from = Cuadro1.Range["I33:I34"]; to = Cuadro1_a6.Range["G35:G36"]; from.Copy(); to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing); from = Cuadro1.Range["K33:K34"]; to = Cuadro1_a6.Range["H35:H36"]; from.Copy(); to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing); //SPRT from = Cuadro1.Range["D41:D46"]; to = Cuadro1_a6.Range["D35:D36"]; from.Copy(); to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing); from = Cuadro1.Range["F41:F46"]; to = Cuadro1_a6.Range["E42:E27"]; from.Copy(); to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing); from = Cuadro1.Range["H41:H46"]; to = Cuadro1_a6.Range["F42:F47"]; from.Copy(); to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing); //from = Cuadro1.Range["I41:I46"]; //to = Cuadro1_a6.Range["G42:G37"]; //from.Copy(); //to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing); //from = Cuadro1.Range["K41:K46"]; //to = Cuadro1_a6.Range["H42:H27"]; //from.Copy(); //to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing); //from = Cuadro1.Range["I41:I46"]; //to = Cuadro1_a6.Range["G42:G37"]; //from.Copy(); //to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing); //from = Cuadro1.Range["K41:K46"]; //to = Cuadro1_a6.Range["H42:H27"]; //from.Copy(); //to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing); //SPRT from = Cuadro1.Range["D54:D60"]; to = Cuadro1_a6.Range["D55:D61"]; from.Copy(); to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing); from = Cuadro1.Range["F54:F60"]; to = Cuadro1_a6.Range["E55:E61"]; from.Copy(); to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing); from = Cuadro1.Range["H54:H60"]; to = Cuadro1_a6.Range["F55:F61"]; from.Copy(); to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing); from = Cuadro1.Range["I54:I60"]; to = Cuadro1_a6.Range["G55:G61"]; from.Copy(); to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing); from = Cuadro1.Range["K54:K60"]; to = Cuadro1_a6.Range["H55:H61"]; from.Copy(); to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing); from = Cuadro1.Range["L54:L59"]; to = Cuadro1_a6.Range["I55:I60"]; from.Copy(); to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing); from = Cuadro1.Range["M54:M59"]; to = Cuadro1_a6.Range["J55:J60"]; from.Copy(); to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing); //TO DO: Revisar por que no copia from = Cuadro1.Range["D33:D34"]; to = Cuadro1_a6.Range["D35:D36"]; from.Copy(); to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing); //TO DO: Mirar por que no copia estos valores, aparte no suma la caja de total from = Cuadro1.Range["F24:F29"]; to = Cuadro1_a6.Range["E26:E31"]; from.Copy(); to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing); //SPRT from = Cuadro1.Range["D50"]; to = Cuadro1_a6.Range["D51"]; from.Copy(); to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing); from = Cuadro1.Range["F50"]; to = Cuadro1_a6.Range["E51"]; from.Copy(); to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing); from = Cuadro1.Range["H50"]; to = Cuadro1_a6.Range["F51"]; from.Copy(); to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing); Cuadro1_a6.Range["E32"].Formula = "=SUM(E26:E31)"; Cuadro1_a6.Range["D39"].Formula = "=D23+D32+D37"; Cuadro1_a6.Range["D12:I64"].Replace(".", ""); //Cuadro1_a6.Range["D12:I64"].NumberFormat = "#.##0"; //cerrando System.IO.FileAttributes attr; try { attr = System.IO.File.GetAttributes(rutaSalida); } catch (Exception ex) { System.IO.Directory.CreateDirectory(rutaSalida); } libroDestino.SaveAs(rutaSalida + "Anexo_Maternal_" + fecha + Utilidades.ExtensionLibro(libroDestino)); libroDestino.Close(false); libroEntrada.Close(false); excelApp.Quit(); System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excelApp); }