//Creates pivot table
private void CreatePivotSheet(Excel._Workbook workbook, Excel._Worksheet dataSheet, Excel._Worksheet pivotSheet, string tableName, bool unified)
{
//If consolidated sheet, range selects up until column C, if normal pivot, selects up until K
string col;
if (unified == true)
{
col = "C";
}
else
{
col = "K";
}
//Get last used row
var lastUsedRow = getLastUsedRow(dataSheet); //Select all data from starting cell to last column + row
var dataRange = dataSheet.get_Range("A1", col + lastUsedRow);
var pivotRange = pivotSheet.Cells[1, 1]; //Select target location
var oPivotCache = (Excel.PivotCache)workbook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlDatabase, dataRange); //Create cache specifying data is coming from a table
var oPivotTable = (Excel.PivotTable)pivotSheet.PivotTables().Add(PivotCache: oPivotCache, TableDestination: pivotRange, TableName: tableName); //Create table
if (unified == true) //If consolidated sheet
{
//Set Row field to 'APP'
var RowPivotField = (Excel.PivotField)oPivotTable.PivotFields("APP");
RowPivotField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
//Set Values field to 'Total'
var SumPivotField = (Excel.PivotField)oPivotTable.PivotFields("Total");
SumPivotField.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
SumPivotField.Function = Excel.XlConsolidationFunction.xlSum;
SumPivotField.Name = "CPU Time";
//Set Column field to 'LPAR'
var ColPivotField = (Excel.PivotField)oPivotTable.PivotFields("LPAR");
ColPivotField.Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
}
else //If normal sheet
{
//Set Row field to 'APP'
Excel.PivotField RowPivotField = (Excel.PivotField)oPivotTable.PivotFields("APP");
RowPivotField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
//Set Values field to 'CPUTIME'
Excel.PivotField SumPivotField = (Excel.PivotField)oPivotTable.PivotFields("CPUTIME");
SumPivotField.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
SumPivotField.Function = Excel.XlConsolidationFunction.xlSum;
SumPivotField.Name = "CPU Time";
}
}
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 + @"\SISILHIA";
var rutaSalida = @"C:\Fondos Nacionales\out\" + periodo + @"\Sil\Anexo\";
var rutaTemplate = @"C:\Fondos Nacionales\Templates\ANEXO_SIL";
var rutaDos = @"C:\Fondos Nacionales\in\" + periodo + @"\ESTEMPMESCIERRE";
var rutaAux = @"C:\Fondos Nacionales\Auxiliar\AUX_DINAMICA";
ExcelX.Workbook libroEntrada = Utilidades.AbrirLibro(excelApp, rutaEntrada);
ExcelX.Workbook libroDestino = Utilidades.AbrirLibro(excelApp, rutaTemplate);
//Primero
ExcelX._Worksheet Cuadro1 = libroEntrada.Sheets["CUADRO N° 1"];
ExcelX._Worksheet Cuadro1_a6 = libroDestino.Sheets["SIL Anexo 6 - Cuadro Nº1"];
var from = Cuadro1.Range["D12:F13"];
var to = Cuadro1_a6.Range["E22:G23"];
from.Copy();
to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing);
from = Cuadro1.Range["H12:J13"];
to = Cuadro1_a6.Range["I22:K23"];
from.Copy();
to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing);
from = Cuadro1.Range["D16:F17"];
to = Cuadro1_a6.Range["E25:G26"];
from.Copy();
to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing);
from = Cuadro1.Range["H16:J17"];
to = Cuadro1_a6.Range["I25:K26"];
from.Copy();
to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing);
from = Cuadro1.Range["D20:F21"];
to = Cuadro1_a6.Range["E28:G29"];
from.Copy();
to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing);
from = Cuadro1.Range["H20:J21"];
to = Cuadro1_a6.Range["I28:K29"];
from.Copy();
to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing);
//Segundo
ExcelX._Worksheet Cuadro2a = libroEntrada.Sheets["CUADRO N°2-A"];
ExcelX._Worksheet Cuadro2b = libroEntrada.Sheets["CUADRO N°2-B"];
ExcelX._Worksheet Cuadro2_a6 = libroDestino.Sheets["SIL Anexo 6 - Cuadro N° 2-A y B"];
from = Cuadro2a.Range["C10:J37"];
to = Cuadro2_a6.Range["D18:K45"];
from.Copy();
to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing);
from = Cuadro2b.Range["C10:J37"];
to = Cuadro2_a6.Range["D59:K86"];
from.Copy();
to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing);
//Tercero
ExcelX._Worksheet Cuadro3 = libroEntrada.Sheets["CUADRO N°3"];
ExcelX._Worksheet Cuadro3_a6 = libroDestino.Sheets["SIL Anexo 6 - Cuadro Nº 3"];
from = Cuadro3.Range["C10:J24"];
to = Cuadro3_a6.Range["D18:K32"];
from.Copy();
to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing);
//cuarto
ExcelX._Worksheet Cuadro4 = libroEntrada.Sheets["CUADRO N° 4"];
ExcelX._Worksheet Cuadro4_a6 = libroDestino.Sheets["SIL Anexo 6 - Cuadro Nº 4"];
from = Cuadro4.Range["C10:J19"];
to = Cuadro4_a6.Range["D21:K30"];
from.Copy();
to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing);
//Quinto
ExcelX._Worksheet Cuadro5 = libroEntrada.Sheets["CUADRO N°5"];
ExcelX._Worksheet Cuadro5_a6 = libroDestino.Sheets["SIL Anexo 6 - Cuadro Nº5"];
from = Cuadro5.Range["D15:E29"];
to = Cuadro5_a6.Range["D19:E33"];
from.Copy();
to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing);
///////////////////////////////
ExcelX.Workbook libroDos = Utilidades.AbrirLibro(excelApp, rutaDos);
ExcelX.Workbook libroAux = Utilidades.AbrirLibro(excelApp, rutaAux);
//Quinto
ExcelX._Worksheet Cuadro6 = libroDos.Sheets["Cuadro 6"];
ExcelX._Worksheet Cuadro6y7_a6 = libroDestino.Sheets["SIL Anexo 6-Cuadro Nº 6 Y 7"];
from = Cuadro6.Range["E12:E26"];
to = Cuadro6y7_a6.Range["D16:D30"];
from.Copy();
to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing);
//Quinto
ExcelX._Worksheet Cuadro7 = libroDos.Sheets["Cuadro 7"];
ExcelX._Worksheet auxiliar = libroAux.Sheets["aux"];
from = Cuadro7.Range["M12:M27"];
to = auxiliar.Range["M12:M27"];
from.Copy();
to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing);
from = Cuadro7.Range["P12:P27"];
to = auxiliar.Range["P12:P27"];
from.Copy();
to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing);
ExcelX.PivotTables pts = (ExcelX.PivotTables)auxiliar.PivotTables(Type.Missing);
var ctn = pts.Count;
pts.Item(1).RefreshTable();
from = auxiliar.Range["S13:T22"];
to = Cuadro6y7_a6.Range["D47:E56"];
from.Copy();
to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing);
//Resumen
ExcelX._Worksheet Resumen = libroEntrada.Sheets["ANEXO N° 3"];
ExcelX._Worksheet Resumen_a6 = libroDestino.Sheets["Resumen Cotizaciones"];
from = Resumen.Range["C12:E19"];
to = Resumen_a6.Range["D16:F23"];
from.Copy();
to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing);
from = Resumen.Range["C25:E28"];
to = Resumen_a6.Range["D28:F31"];
from.Copy();
to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing);
//********
from = Resumen.Range["C34:E35"];
to = Resumen_a6.Range["D35:F36"];
from.Copy();
to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing);
from = Resumen.Range["C44:E45"];
to = Resumen_a6.Range["D42:F43"];
from.Copy();
to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing);
from = Resumen.Range["C46:E46"];
to = Resumen_a6.Range["D45:F45"];
from.Copy();
to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing);
from = Resumen.Range["C47:E47"];
to = Resumen_a6.Range["D44:F44"];
from.Copy();
to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing);
from = Resumen.Range["C48:E49"];
to = Resumen_a6.Range["D46:F47"];
from.Copy();
to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing);
from = Resumen.Range["F12:F19"];
to = Resumen_a6.Range["G16:G23"];
from.Copy();
to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing);
from = Resumen.Range["G12:G19"];
to = Resumen_a6.Range["H16:H23"];
from.Copy();
to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing);
from = Resumen.Range["F25:F28"];
to = Resumen_a6.Range["G28:G31"];
from.Copy();
to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing);
from = Resumen.Range["G25:G28"];
to = Resumen_a6.Range["H28:H31"];
from.Copy();
to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing);
from = Resumen.Range["F34:F35"];
to = Resumen_a6.Range["G35:G36"];
from.Copy();
to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing);
from = Resumen.Range["G34:G35"];
to = Resumen_a6.Range["H35:H36"];
from.Copy();
to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing);
from = Resumen.Range["F44:F49"];
to = Resumen_a6.Range["G42:G47"];
from.Copy();
to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing);
from = Resumen.Range["G44:G49"];
to = Resumen_a6.Range["H42:H47"];
from.Copy();
to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing);
/***
*
*
* */
from = Resumen.Range["F46"];
to = Resumen_a6.Range["G45"];
from.Copy();
to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing);
from = Resumen.Range["F47"];
to = Resumen_a6.Range["G44"];
from.Copy();
to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing);
from = Resumen.Range["G46"];
to = Resumen_a6.Range["H45"];
from.Copy();
to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing);
from = Resumen.Range["G47"];
to = Resumen_a6.Range["H44"];
from.Copy();
to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing);
from = Resumen.Range["C26"];
to = Resumen_a6.Range["D30"];
from.Copy();
to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing);
from = Resumen.Range["C27"];
to = Resumen_a6.Range["D29"];
from.Copy();
to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing);
//*********
from = Resumen.Range["D26"];
to = Resumen_a6.Range["E30"];
from.Copy();
to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing);
from = Resumen.Range["D27"];
to = Resumen_a6.Range["E29"];
from.Copy();
to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing);
from = Resumen.Range["E26"];
to = Resumen_a6.Range["F30"];
from.Copy();
to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing);
from = Resumen.Range["E27"];
to = Resumen_a6.Range["F29"];
from.Copy();
to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing);
from = Resumen.Range["F26"];
to = Resumen_a6.Range["G30"];
from.Copy();
to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing);
from = Resumen.Range["F27"];
to = Resumen_a6.Range["G29"];
from.Copy();
to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing);
from = Resumen.Range["G26"];
to = Resumen_a6.Range["H30"];
from.Copy();
to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing);
from = Resumen.Range["G27"];
to = Resumen_a6.Range["H29"];
from.Copy();
to.PasteSpecial(ExcelX.XlPasteType.xlPasteValues, ExcelX.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing);
//----------------------------
//cerrando
System.IO.FileAttributes attr;
try
{
attr = System.IO.File.GetAttributes(rutaSalida);
}
catch (Exception ex)
{
System.IO.Directory.CreateDirectory(rutaSalida);
}
libroDestino.SaveAs(rutaSalida + "Anexo_SIL_" + fecha + Utilidades.ExtensionLibro(libroDestino));
libroDestino.Close(false);
libroEntrada.Close(false);
libroAux.Close(false);
libroDos.Close(false);
excelApp.Quit();
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excelApp);
}
