private static void refreshPivots(Excel.Workbook theWorkbook)
{
Console.WriteLine("WorkSheets:");
Excel.Sheets oSheets = (Excel.Sheets)theWorkbook.Worksheets;
foreach (Excel.Worksheet oWorkSheet in oSheets)
{
Console.WriteLine(" {0}", oWorkSheet.Name);
Excel.PivotTables pivotTables1 =
(Excel.PivotTables)oWorkSheet.PivotTables();
if (pivotTables1.Count > 0)
{
for (int i = 1; i <= pivotTables1.Count; i++)
{
Console.WriteLine(" PivoteTable Refresh: {0}", pivotTables1.Item(i).Name);
pivotTables1.Item(i).RefreshTable();
}
}
else
{
Console.WriteLine(" !This worksheet contains no pivot tables.");
}
}
}
public MainWindow()
{
InitializeComponent();
AppDomain.CurrentDomain.ProcessExit += new EventHandler(OnProcessExit);
string[] ports = SerialPort.GetPortNames();
foreach (string port in ports)
{
comport_list.Items.Add(port);
}
try
{
xlApp = (Excel.Application)Marshal.GetActiveObject("Excel.Application");
}
catch (Exception)
{
xlApp = new Excel.Application();
}
try
{
xlWorkbook = xlApp.Workbooks.Open(System.Windows.Forms.Application.StartupPath + "\\NFC.xlsx");
xlWorksheet = xlWorkbook.Sheets[1];
xlLog = xlWorkbook.Sheets[2];
xlRange = xlWorksheet.UsedRange;
pivotTables = (Excel.PivotTables)xlLog.PivotTables(Type.Missing);
}
catch (COMException e)
{
MessageBox.Show("Please close and start \"NFC.xlsx\" and try again!");
Log(e.ToString());
}
}
public static void refreshPivotTables(Workbook xlWorkBook)
{
//Refresh pivot tables
foreach (Microsoft.Office.Interop.Excel.Worksheet pivotSheet in xlWorkBook.Worksheets)
{
Microsoft.Office.Interop.Excel.PivotTables pivotTables = pivotSheet.PivotTables();
int pivotTablesCount = pivotTables.Count;
if (pivotTablesCount > 0)
{
for (int i = 1; i <= pivotTablesCount; i++)
{
pivotTables.Item(i).RefreshTable(); //The Item method throws an exception
}
}
}
}
private Excel.Range IdentifyPivotRanges(Excel.Range xlRange)
{
Excel.Range pivotRanges = null;
Excel.PivotTables pivotTables = xlRange.Worksheet.PivotTables();
int pivotCount = pivotTables.Count;
for (int i = 1; i <= pivotCount; i++)
{
Excel.Range tmpRange = xlRange.Worksheet.PivotTables(i).TableRange2;
if (pivotRanges == null)
{
pivotRanges = tmpRange;
}
// pivotRanges = this.Application.Union(pivotRanges, tmpRange);
}
return(pivotRanges);
}
static void Main()
{
string filePath = @"C:\Users\stackoverflow\Desktop\Sample.xlsx";
Excel.Application excel = new Excel.Application();
excel.Visible = true;
excel.EnableAnimations = true;
Excel.Workbook wkb = Open(excel, filePath);
foreach (Excel.Worksheet xlWorksheet in wkb.Worksheets)
{
Excel.PivotTables pivotTablesCollection = xlWorksheet.PivotTables();
if (pivotTablesCollection.Count > 0)
{
for (int i = 1; i <= pivotTablesCollection.Count; i++)
{
Excel.PivotTable currentPivotTable = pivotTablesCollection.Item(i);
Console.WriteLine($"Table is named -> {currentPivotTable.Name}");
foreach (Excel.PivotField pivotField in currentPivotTable.PivotFields())
{
Console.WriteLine($"\nField is named -> {pivotField.Name}");
foreach (Excel.PivotItem visibleItems in pivotField.VisibleItems)
{
Console.WriteLine($"Visible item name -> {visibleItems.Name}");
}
foreach (Excel.PivotItem PivotItem in pivotField.PivotItems())
{
Console.WriteLine($"Item is named -> {PivotItem.Name}");
Console.WriteLine(PivotItem.Visible);
}
}
}
}
}
excel.EnableAnimations = true;
wkb.Close(true);
excel.Quit();
Console.WriteLine("Finished!");
}
/// <summary>
/// 创建一个数据透视表，其中包含的数据来自用制表符分隔的文本文件。
/// </summary>
/// <param name="filePath">文本文件所在位置。</param>
/// <returns>已创建的数据透视表。</returns>
private Excel.PivotTable CreatePivotTable(string filePath)
{
// 如果该表已存在，
// 则返回现有的表。
string tableName = Properties.Resources.AveragesPivotTableName;
Excel.PivotTables tables = (Excel.PivotTables) this.PivotTables(missing);
System.Collections.Generic.Queue <double> savedWidths = new System.Collections.Generic.Queue <double>();
if (tables != null)
{
int count = tables.Count;
for (int i = 1; i <= count; i++)
{
Excel.PivotTable table = tables.Item(i);
if (table.Name == tableName)
{
return(table);
}
}
}
try
{
// AddField 将调整列的大小。
// 保存列宽，以便在添加数据透视字段之后还原
foreach (Excel.Range column in DayInventory.HeaderRowRange.Cells)
{
savedWidths.Enqueue((double)column.ColumnWidth);
}
// 创建数据透视表需要关闭保护功能。
Globals.ThisWorkbook.MakeReadWrite();
Excel.PivotTable table = Globals.ThisWorkbook.CreateSalesPivotTable(this.get_Range(pivotTableAddress, missing), filePath);
table.Name = tableName;
// 在数据透视表中，添加所需的
// 行和列。
table.AddFields("Flavor", missing, missing, missing);
Excel.PivotField soldField = table.AddDataField(table.PivotFields("Sold"), Properties.Resources.AverageSold, Excel.XlConsolidationFunction.xlAverage);
// 在数据透视表中设置所需数据的视图。
// 格式“0.0”- 一个小数位。
soldField.NumberFormat = "0.0";
Excel.PivotField profitField = table.AddDataField(table.PivotFields("Profit"), Properties.Resources.AverageProfit, Excel.XlConsolidationFunction.xlAverage);
// 在数据透视表中设置所需数据的视图。
// 格式“0.00”- 两个小数位。
profitField.NumberFormat = "0.00";
// 隐藏创建数据透视表时添加的两个浮动栏。
Globals.ThisWorkbook.ShowPivotTableFieldList = false;
Globals.ThisWorkbook.Application.CommandBars["PivotTable"].Visible = false;
return(table);
}
finally
{
// AddField 将调整列的大小。还原列宽。
foreach (Excel.Range column in DayInventory.HeaderRowRange.Cells)
{
column.ColumnWidth = savedWidths.Dequeue();
}
Globals.ThisWorkbook.MakeReadOnly();
}
}
private bool PutReportData(Int32 intRPID)
{
try
{
UpdateProgress("Sending Chart data...");
object missing;
int pivotTablesCount = 0;
switch (intRPID)
{
case 32:
worksheet = (Worksheet)workbook.Worksheets.get_Item("ChartData");
worksheet.Activate();
clsExport.ExcelExportExisting(worksheet, ds.Tables[1], 0, 0);
UpdateProgress("Sending Supporting Data... " + ds.Tables[0].Rows.Count.ToString() + " rows in all.");
worksheet = (Worksheet)workbook.Worksheets.get_Item("SupportingData");
worksheet.Activate();
clsExport.ExcelExportExisting(worksheet, ds.Tables[0], 0, 0);
worksheet = (Worksheet)workbook.Worksheets.get_Item("Chart");
worksheet.Activate();
break;
case 51:
clsExport.ExcelExport(ds.Tables[0], true, true);
break;
case 52:
clsExport.ExcelExport(ds.Tables[0], true, true);
break;
case 53:
clsExport.ExcelExport(ds.Tables[0], true, true);
break;
case 54:
worksheet = (Worksheet)workbook.Worksheets.get_Item("ChartData");
worksheet.Activate();
clsExport.ExcelExportExisting(worksheet, ds.Tables[0], 0, 0);
worksheet = (Worksheet)workbook.Worksheets.get_Item("Chart");
worksheet.Activate();
missing = Type.Missing;
Microsoft.Office.Interop.Excel.PivotTables pivotTables = (Microsoft.Office.Interop.Excel.PivotTables)worksheet.PivotTables(missing);
pivotTablesCount = pivotTables.Count;
if (pivotTablesCount > 0)
{
for (int i = 1; i <= pivotTablesCount; i++)
{
pivotTables.Item(i).RefreshTable(); //The Item method throws an exception
}
}
break;
case 56:
worksheet = (Worksheet)workbook.Worksheets.get_Item("ChartData");
worksheet.Activate();
clsExport.ExcelExportExisting(worksheet, ds.Tables[0], 0, 0);
worksheet = (Worksheet)workbook.Worksheets.get_Item("Chart");
worksheet.Activate();
missing = Type.Missing;
Microsoft.Office.Interop.Excel.PivotTables pivotTables56 = (Microsoft.Office.Interop.Excel.PivotTables)worksheet.PivotTables(missing);
pivotTablesCount = pivotTables56.Count;
if (pivotTablesCount > 0)
{
for (int i = 1; i <= pivotTablesCount; i++)
{
pivotTables56.Item(i).RefreshTable(); //The Item method throws an exception
}
}
break;
case 57:
clsExport.ExcelExport(ds.Tables[0], true, true);
break;
case 58:
worksheet = (Worksheet)workbook.Worksheets.get_Item("ChartData");
worksheet.Activate();
clsExport.ExcelExportExisting(worksheet, ds.Tables[0], 0, 0);
worksheet = (Worksheet)workbook.Worksheets.get_Item("RawData");
worksheet.Activate();
clsExport.ExcelExportExisting(worksheet, ds.Tables[1], 0, 0);
worksheet = (Worksheet)workbook.Worksheets.get_Item("Chart");
worksheet.Activate();
missing = Type.Missing;
Microsoft.Office.Interop.Excel.PivotTables pivotTables58 = (Microsoft.Office.Interop.Excel.PivotTables)worksheet.PivotTables(missing);
pivotTablesCount = pivotTables58.Count;
if (pivotTablesCount > 0)
{
for (int i = 1; i <= pivotTablesCount; i++)
{
pivotTables58.Item(i).RefreshTable(); //The Item method throws an exception
}
}
break;
case 60:
worksheet = (Worksheet)workbook.Worksheets.get_Item("RawData");
worksheet.Activate();
clsExport.ExcelExportExistingNoHeader(60, worksheet, ds.Tables[0], 0, 0, false, false);
clsExport.ExcelExportExistingNoHeader(60, worksheet, ds.Tables[1], 0, 8, false, false);
clsExport.ExcelExportExistingNoHeader(60, worksheet, ds.Tables[2], -1, 11, false, false);
Range endCell = (Range)worksheet.Cells[ds.Tables[0].Rows.Count + 2, 1];
Range endCell2 = (Range)worksheet.Cells[1000, 26];
Range writeRange = worksheet.Range[endCell, endCell2];
writeRange.Delete();
break;
case 61:
clsExport.ExcelExport(ds.Tables[0], true, true);
break;
case 62:
worksheet = (Worksheet)workbook.Worksheets.get_Item("ChartData");
worksheet.Activate();
clsExport.ExcelExportExisting(worksheet, ds.Tables[0], 0, 0);
worksheet = (Worksheet)workbook.Worksheets.get_Item("Chart");
worksheet.Activate();
missing = Type.Missing;
Microsoft.Office.Interop.Excel.PivotTables pivotTables62 = (Microsoft.Office.Interop.Excel.PivotTables)worksheet.PivotTables(missing);
pivotTablesCount = pivotTables62.Count;
if (pivotTablesCount > 0)
{
for (int i = 1; i <= pivotTablesCount; i++)
{
pivotTables62.Item(i).RefreshTable(); //The Item method throws an exception
}
}
break;
case 63:
worksheet = (Worksheet)workbook.Worksheets.get_Item("ChartData");
worksheet.Activate();
clsExport.ExcelExportExisting(worksheet, ds.Tables[0], 0, 0);
worksheet = (Worksheet)workbook.Worksheets.get_Item("Chart");
worksheet.Activate();
missing = Type.Missing;
Microsoft.Office.Interop.Excel.PivotTables pivotTables63 = (Microsoft.Office.Interop.Excel.PivotTables)worksheet.PivotTables(missing);
pivotTablesCount = pivotTables63.Count;
if (pivotTablesCount > 0)
{
for (int i = 1; i <= pivotTablesCount; i++)
{
pivotTables63.Item(i).RefreshTable(); //The Item method throws an exception
}
}
break;
case 64:
worksheet = (Worksheet)workbook.Worksheets["MissingRunTimes"];
worksheet.Activate();
clsExport.ExcelExportExisting(worksheet, ds.Tables[0], 0, 0);
worksheet = (Worksheet)workbook.Worksheets["SavedDailySchedule"];
worksheet.Activate();
clsExport.ExcelExportExisting(worksheet, ds.Tables[1], 0, 0);
worksheet = (Worksheet)workbook.Worksheets["DailyConfirmations"];
worksheet.Activate();
clsExport.ExcelExportExisting(worksheet, ds.Tables[2], 0, 0);
worksheet = (Worksheet)workbook.Worksheets["Combined"];
worksheet.Activate();
clsExport.ExcelExportExisting(worksheet, ds.Tables[3], 0, 0);
worksheet = (Worksheet)workbook.Worksheets["Chart"];
worksheet.Activate();
missing = Type.Missing;
try
{
Microsoft.Office.Interop.Excel.PivotTables pivotTables64 = (Microsoft.Office.Interop.Excel.PivotTables)worksheet.PivotTables(missing);
pivotTablesCount = pivotTables64.Count;
if (pivotTablesCount > 0)
{
for (int i = 1; i <= pivotTablesCount; i++)
{
pivotTables64.Item(i).RefreshTable(); //The Item method throws an exception
}
}
}
catch (Exception)
{
}
break;
}
}
catch (Exception ex)
{
UpdateProgress("");
workbook.Application.Visible = true;
this.Cursor = Cursors.Default;
MessageBox.Show(ex.Message, this.Text);
return(false);
}
try
{
workbook.Application.Visible = true;
}
catch (Exception ex)
{
}
return(true);
}
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);
}
/// <summary>
/// Create a PivotTable with data from a tab-delimiter text file.
/// </summary>
/// <param name="filePath">Text file location.</param>
/// <returns>Created PivotTable.</returns>
private Excel.PivotTable CreatePivotTable(string filePath)
{
// If the table is already there,
// return the existing table.
string tableName = Properties.Resources.AveragesPivotTableName;
Excel.PivotTables tables = (Excel.PivotTables) this.PivotTables(missing);
System.Collections.Generic.Queue <double> savedWidths = new System.Collections.Generic.Queue <double>();
if (tables != null)
{
int count = tables.Count;
for (int i = 1; i <= count; i++)
{
Excel.PivotTable table = tables.Item(i);
if (table.Name == tableName)
{
return(table);
}
}
}
try
{
// AddFields will resize the columns. Save the columns' widths
// for restoring them after pivot fields are added
foreach (Excel.Range column in DayInventory.HeaderRowRange.Cells)
{
savedWidths.Enqueue((double)column.ColumnWidth);
}
// PivotTable creation requires that protection be off.
Globals.ThisWorkbook.MakeReadWrite();
Excel.PivotTable table = Globals.ThisWorkbook.CreateSalesPivotTable(this.get_Range(pivotTableAddress, missing), filePath);
table.Name = tableName;
// Adds the desired rows and columns within
// the PivotTable.
table.AddFields("Flavor", missing, missing, missing);
Excel.PivotField soldField = table.AddDataField(table.PivotFields("Sold"), Properties.Resources.AverageSold, Excel.XlConsolidationFunction.xlAverage);
// Sets the view of data desired within the PivotTable.
// Format "0.0" - one decimal place.
soldField.NumberFormat = "0.0";
Excel.PivotField profitField = table.AddDataField(table.PivotFields("Profit"), Properties.Resources.AverageProfit, Excel.XlConsolidationFunction.xlAverage);
// Sets the view of data desired within the PivotTable.
// Format "0.0" - two decimal places.
profitField.NumberFormat = "0.00";
// Hiding the two floating bars that get added when a PivotTable is created.
Globals.ThisWorkbook.ShowPivotTableFieldList = false;
Globals.ThisWorkbook.Application.CommandBars["PivotTable"].Visible = false;
return(table);
}
finally
{
// AddFields will have resized the columns. Restore the columns' widths.
foreach (Excel.Range column in DayInventory.HeaderRowRange.Cells)
{
column.ColumnWidth = savedWidths.Dequeue();
}
Globals.ThisWorkbook.MakeReadOnly();
}
}
static void Main(string[] args)
{
if (checkPing())
{
FileLocation fileLocation = null;
try
{
using (StreamReader sr = new StreamReader(Directory.GetCurrentDirectory() + @"\Input\Config.json"))
{
fileLocation = JsonConvert.DeserializeObject <FileLocation>(sr.ReadToEnd());
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
Console.WriteLine("Connection Successful");
string str = "";
var Files = Directory.EnumerateFiles(fileLocation.SourceLocation, "*.*", SearchOption.AllDirectories)
.Where(s => s.EndsWith(".xlsx"));
Excel.Application xlApp = new Excel.Application();
bool refreshStatus = true;
foreach (string filePath in Files)
{
DirectoryInfo file = new DirectoryInfo(filePath);
Excel.Workbook wb = xlApp.Workbooks.Open(file.FullName);
xlApp.DisplayAlerts = false;
xlApp.Visible = false;
Console.WriteLine("Refreshing : " + file.FullName);
Excel.Sheets excelSheets = wb.Worksheets;
foreach (Excel.Worksheet workSheet in excelSheets)
{
Console.WriteLine("SheetName: " + workSheet.Name);
Excel.PivotTables pivotTables = workSheet.PivotTables();
if (pivotTables.Count > 0)
{
foreach (Excel.PivotTable pivotTable in pivotTables)
{
Console.WriteLine(pivotTable.RefreshDate);
int attempts = 0;
Console.WriteLine("Refreshing " + pivotTable.Name);
for (; attempts < 3; attempts++)
{
refreshStatus = pivotTable.RefreshTable();
Console.WriteLine(refreshStatus);
if (refreshStatus == true)
{
break;
}
else
{
Console.WriteLine("Failed!! Reattempting...");
}
}
if (attempts == 3)
{
Console.WriteLine("All attempts exhausted! Failed.");
}
else
{
Console.WriteLine(pivotTable.RefreshDate);
}
}
}
else
{
Console.WriteLine("No Pivot Found in the Sheet!!");
}
}
if (refreshStatus)
{
Console.WriteLine("Refreshed :" + file.Name);
Console.WriteLine("Saving " + file.Name);
wb.SaveAs(fileLocation.DestinationLocation + file.Name);
}
wb.Close();
xlApp.Quit();
str = str + ", " + file.Name;
}
Console.WriteLine("Press Enter to Continue");
Console.ReadLine();
}
else
{
Console.WriteLine("Connection Unsuccessful");
}
}
public static void createFile()
{
Excel.Application objApp;
Excel.Workbook objBook;
Excel.Sheets objSheets;
//Excel.Workbooks objBooks;
objApp = new Excel.Application();
try
{
//objBooks = objApp.Workbooks;
objBook = objApp.Workbooks.Add(Missing.Value);
objSheets = objBook.Worksheets;
Excel.Worksheet sheet1 = (Excel.Worksheet)objSheets[1];
sheet1.Name = "PivotData";
sheet1.Cells[1, 1] = "Dato 1";
sheet1.Cells[1, 2] = "Dato 2";
sheet1.Cells[2, 1] = "1";
sheet1.Cells[2, 2] = "2";
sheet1.Cells[3, 1] = "3";
sheet1.Cells[3, 2] = "4";
//Crear hoja cache
//CREATE A PIVOT CACHE BASED ON THE EXPORTED DATA
Excel.PivotCache pivotCache = objBook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlDatabase, sheet1.UsedRange);
Console.WriteLine(pivotCache.SourceData.ToString());
//WORKSHEET FOR NEW PIVOT TABLE
var sheet2 = (Excel.Worksheet)objSheets.Application.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//Excel.Worksheet sheet2 = (Excel.Worksheet)objSheets[2];
sheet2.Name = "PivotTable";
//PIVOT TABLE BASED ON THE PIVOTCACHE OF EXPORTED DATA
Excel.PivotTables pivotTables = (Excel.PivotTables)sheet2.PivotTables(Missing.Value);
Excel.PivotTable pivotTable = pivotTables.Add(pivotCache, objApp.ActiveCell, "PivotTable1", Missing.Value, Missing.Value);
//Poner los campos
var pivotFields = (Microsoft.Office.Interop.Excel.PivotFields)pivotTable.PivotFields();
var dato1 = (Microsoft.Office.Interop.Excel.PivotField)pivotFields.Item("Dato 1");
dato1.Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlDataField;
var dato2 = (Microsoft.Office.Interop.Excel.PivotField)pivotFields.Item("Dato 2");
dato2.Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlDataField;
pivotTable.SmallGrid = false;
pivotTable.TableStyle = "PivotStyleLight1";
/*Console.ReadLine();*/
//Almacenar Pivote
objApp.DisplayAlerts = false;
//Se elimina la hoja de datos
((Excel.Worksheet)objSheets["PivotData"]).Delete();
objBook.SaveAs("prueba", Excel.XlFileFormat.xlOpenXMLWorkbook, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
objApp.DisplayAlerts = true;
objApp.Quit();
}
catch (Exception e)
{
objApp.Quit();
Console.WriteLine(e.Message);
Console.ReadLine();
}
}
private void btnAceptar_Click(object sender, EventArgs e)
{
Cursor.Current = Cursors.WaitCursor;
libro = app.Workbooks.Add();
libro.DefaultPivotTableStyle = "PivotStyleLight26";
Excel.PivotCache pivotCache = libro.PivotCaches().Add(Excel.XlPivotTableSourceType.xlExternal);
//string MyConString = ConfigurationManager.ConnectionStrings["ODBCExcel"].ConnectionString;
string MyConString = ConfigurationManager.ConnectionStrings["ODBCExcelLocal"].ConnectionString;
//string MyConString = ConfigurationManager.ConnectionStrings["ODBCMinusculas"].ConnectionString;
string strFecha = dateTimePicker1.Value.ToString("yyyy-MM-dd");
string command = "SELECT * FROM ventash WHERE Fecha >='" + strFecha + "'";
pivotCache.Connection = MyConString;
pivotCache.CommandText = command;
#region ValorAgregado
Excel.Worksheet sheetAgregado = libro.Sheets.Add();
libro.Sheets[libro.ActiveSheet.Name].Select();
libro.Sheets[libro.ActiveSheet.Name].Name = "Valor agregado";
Excel.PivotTables pivotTablesAgregado = sheetAgregado.PivotTables();
Excel.PivotTable pivotTableAgregado = pivotTablesAgregado.Add(pivotCache, app.Range["A4"], "Valor agregado");
sheetAgregado.PivotTables("Valor agregado").PivotFields("NombreLocal").Orientation = Excel.XlPivotFieldOrientation.xlPageField;
sheetAgregado.PivotTables("Valor agregado").PivotFields("NombreLocal").Position = 1;
sheetAgregado.PivotTables("Valor agregado").PivotFields("FormaPago").Orientation = Excel.XlPivotFieldOrientation.xlPageField;
sheetAgregado.PivotTables("Valor agregado").PivotFields("FormaPago").Position = 2;
sheetAgregado.PivotTables("Valor agregado").PivotFields("Fecha").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
app.Range["B6"].Select();
sheetAgregado.PivotTables("Valor agregado").ColumnGrand = false;
sheetAgregado.PivotTables("Valor agregado").RowGrand = false;
sheetAgregado.PivotTables("Valor agregado").TableStyle2 = "PivotStyleLight26";
sheetAgregado.PivotTables("Valor agregado").CalculatedFields.Add("ValorAgregado", "=IF(TotalPublico>0,(TotalPublico/TotalCosto)-1)", true);
sheetAgregado.PivotTables("Valor agregado").PivotFields("ValorAgregado").Orientation = Excel.XlPivotFieldOrientation.xlDataField;
sheetAgregado.PivotTables("Valor agregado").PivotFields("Suma de ValorAgregado").Caption = "Valor agregado";
sheetAgregado.PivotTables("Valor agregado").PivotFields("Valor agregado").NumberFormat = "0,00%";
sheetAgregado.Cells[6, 1].Select();
object[] periodosValor = { false, false, false, false, true, false, true };
Excel.Range rangeValor = sheetAgregado.get_Range("a6");
rangeValor.Group(true, true, 1, periodosValor);
libro.ShowPivotTableFieldList = false;
app.Range["A5"].Select();
app.Range[app.Selection, app.Selection.End(Microsoft.Office.Interop.Excel.XlDirection.xlDown)].Select();
app.Range[app.Selection, app.Selection.End(Microsoft.Office.Interop.Excel.XlDirection.xlToRight)].Select();
Excel.Range rangoGrafico = app.Selection;
app.ActiveSheet.Shapes.AddChart.Select();
app.ActiveSheet.Shapes(1).Name = "Valor agregado";
app.ActiveChart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xl3DColumn;
app.ActiveChart.SetSourceData(Source: rangoGrafico);
app.ActiveChart.ClearToMatchStyle();
app.ActiveChart.ChartStyle = 42;
app.ActiveChart.ClearToMatchStyle();
app.ActiveSheet.Shapes["Valor agregado"].Left = 0;
app.ActiveSheet.Shapes["Valor agregado"].Top = 300;
app.ActiveSheet.Shapes["Valor agregado"].ScaleWidth(1.663541776, Microsoft.Office.Core.MsoTriState.msoFalse
, Microsoft.Office.Core.MsoScaleFrom.msoScaleFromTopLeft);
app.ActiveSheet.Shapes["Valor agregado"].ScaleHeight(1.2777777778, Microsoft.Office.Core.MsoTriState.msoFalse
, Microsoft.Office.Core.MsoScaleFrom.msoScaleFromTopLeft);
app.ActiveSheet.Shapes["Valor agregado"].ThreeD.RotationX = -30;
app.ActiveSheet.Shapes["Valor agregado"].ThreeD.RotationY = 100;
app.ActiveSheet.Shapes["Valor agregado"].ThreeD.FieldOfView = 10;
app.ActiveChart.ChartTitle.Text = "Valor agregado";
app.Range["A1"].Select();
#endregion
#region Prendas
Excel.Worksheet sheetPrendas = libro.Sheets.Add();
string hojaPrendas = libro.ActiveSheet.Name;
libro.Sheets[hojaPrendas].Select();
libro.Sheets[hojaPrendas].Name = "Prendas";
Excel.PivotTables pivotTablesPrendas = sheetPrendas.PivotTables();
Excel.PivotTable pivotTablePrendas = pivotTablesPrendas.Add(pivotCache, app.Range["A4"], "Prendas");
sheetPrendas.PivotTables("Prendas").TableStyle2 = "PivotStyleLight26";
sheetPrendas.PivotTables("Prendas").PivotFields("NombreLocal").Orientation = Excel.XlPivotFieldOrientation.xlPageField;
sheetPrendas.PivotTables("Prendas").PivotFields("NombreLocal").Position = 1;
sheetPrendas.PivotTables("Prendas").PivotFields("FormaPago").Orientation = Excel.XlPivotFieldOrientation.xlPageField;
sheetPrendas.PivotTables("Prendas").PivotFields("FormaPago").Position = 2;
sheetPrendas.PivotTables("Prendas").PivotFields("Fecha").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
Excel.PivotField fldTotalPrendas = pivotTablePrendas.PivotFields("Prendas");
fldTotalPrendas.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
fldTotalPrendas.Function = Excel.XlConsolidationFunction.xlSum;
fldTotalPrendas.Name = " Prendas";
sheetPrendas.Cells[6, 1].Select();
object[] periodosPrendas = { false, false, false, false, true, false, true };
Excel.Range rangePrendas = sheetPrendas.get_Range("a6");
rangePrendas.Group(true, true, 1, periodosPrendas);
libro.ShowPivotTableFieldList = false;
app.Range["A5"].Select();
app.Range[app.Selection, app.Selection.End(Microsoft.Office.Interop.Excel.XlDirection.xlDown)].Select();
app.Range[app.Selection, app.Selection.End(Microsoft.Office.Interop.Excel.XlDirection.xlToRight)].Select();
Excel.Range rangoGraficoPrendas = app.Selection;
app.ActiveSheet.Shapes.AddChart.Select();
app.ActiveSheet.Shapes(1).Name = "Prendas";
app.ActiveChart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xl3DColumn;
app.ActiveChart.SetSourceData(Source: rangoGraficoPrendas);
app.ActiveChart.ClearToMatchStyle();
app.ActiveChart.ChartStyle = 42;
app.ActiveChart.ClearToMatchStyle();
app.ActiveSheet.Shapes["Prendas"].Left = 0;
app.ActiveSheet.Shapes["Prendas"].Top = 300;
app.ActiveSheet.Shapes["Prendas"].ScaleWidth(1.663541776, Microsoft.Office.Core.MsoTriState.msoFalse
, Microsoft.Office.Core.MsoScaleFrom.msoScaleFromTopLeft);
app.ActiveSheet.Shapes["Prendas"].ScaleHeight(1.2777777778, Microsoft.Office.Core.MsoTriState.msoFalse
, Microsoft.Office.Core.MsoScaleFrom.msoScaleFromTopLeft);
app.ActiveSheet.Shapes["Prendas"].ThreeD.RotationX = -30;
app.ActiveSheet.Shapes["Prendas"].ThreeD.RotationY = 100;
app.ActiveSheet.Shapes["Prendas"].ThreeD.FieldOfView = 10;
app.ActiveSheet.PivotTables("Prendas").RowGrand = false;
app.ActiveChart.ChartTitle.Text = "Unidades vendidas";
app.Range["A1"].Select();
#endregion
#region Periodos
Excel.Worksheet sheetDiferenciaPeriodos = libro.Sheets.Add();
libro.Sheets[libro.ActiveSheet.Name].Select();
libro.Sheets[libro.ActiveSheet.Name].Name = "Diferencia períodos";
Excel.PivotTables pivotTablesPeriodos = sheetDiferenciaPeriodos.PivotTables();
Excel.PivotTable pivotTableDif = pivotTablesPeriodos.Add(pivotCache, app.Range["A4"], "Diferencia periodos");
sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields("NombreLocal").Orientation = Excel.XlPivotFieldOrientation.xlPageField;
sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields("NombreLocal").Position = 1;
sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields("FormaPago").Orientation = Excel.XlPivotFieldOrientation.xlPageField;
sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields("FormaPago").Position = 2;
sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields("Fecha").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
Excel.PivotField fldTotalPeriodo = pivotTableDif.PivotFields("TotalPublico");
fldTotalPeriodo.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
fldTotalPeriodo.Function = Excel.XlConsolidationFunction.xlSum;
fldTotalPeriodo.Name = " Ventas períodos";
sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields("Años").Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields("Años").Position = 1;
sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields(" Ventas períodos").NumberFormat = "$ #.##0";
libro.ShowPivotTableFieldList = false;
app.Range["B6"].Select();
sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").ColumnGrand = false;
sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").RowGrand = false;
sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").TableStyle2 = "PivotStyleLight26";
sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields(" Ventas períodos").Calculation = Excel.XlPivotFieldCalculation.xlPercentDifferenceFrom;
sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields(" Ventas períodos").BaseField = "Años";
sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields(" Ventas períodos").BaseItem = "(anterior)";
sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields(" Ventas períodos").NumberFormat = "0,00%";
app.Range["A5"].Select();
app.Range[app.Selection, app.Selection.End(Microsoft.Office.Interop.Excel.XlDirection.xlDown)].Select();
app.Range[app.Selection, app.Selection.End(Microsoft.Office.Interop.Excel.XlDirection.xlToRight)].Select();
Excel.Range rangoGraficoPeriodos = app.Selection;
app.ActiveSheet.Shapes.AddChart.Select();
app.ActiveSheet.Shapes(1).Name = "Diferencia periodos";
app.ActiveChart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xl3DColumn;
app.ActiveChart.SetSourceData(Source: rangoGraficoPeriodos);
app.ActiveChart.ClearToMatchStyle();
app.ActiveChart.ChartStyle = 42;
app.ActiveChart.ClearToMatchStyle();
app.ActiveSheet.Shapes["Diferencia periodos"].Left = 0;
app.ActiveSheet.Shapes["Diferencia periodos"].Top = 300;
app.ActiveSheet.Shapes["Diferencia periodos"].ScaleWidth(1.663541776, Microsoft.Office.Core.MsoTriState.msoFalse
, Microsoft.Office.Core.MsoScaleFrom.msoScaleFromTopLeft);
app.ActiveSheet.Shapes["Diferencia periodos"].ScaleHeight(1.2777777778, Microsoft.Office.Core.MsoTriState.msoFalse
, Microsoft.Office.Core.MsoScaleFrom.msoScaleFromTopLeft);
app.ActiveSheet.Shapes["Diferencia periodos"].ThreeD.RotationX = -30;
app.ActiveSheet.Shapes["Diferencia periodos"].ThreeD.RotationY = 100;
app.ActiveSheet.Shapes["Diferencia periodos"].ThreeD.FieldOfView = 10;
app.Range["A1"].Select();
#endregion
#region Ventas2
Excel.Worksheet sheetVentas2 = libro.Sheets.Add();
string hojaVentas2 = libro.ActiveSheet.Name;
libro.Sheets[hojaVentas2].Select();
libro.Sheets[hojaVentas2].Name = "Ventas2";
Excel.PivotTables pivotTablesVentas2 = sheetVentas2.PivotTables();
Excel.PivotTable pivotTableVentas2 = pivotTablesVentas2.Add(pivotCache, app.Range["A4"], "Ventas2");
sheetVentas2.PivotTables("Ventas2").TableStyle2 = "PivotStyleLight26";
sheetVentas2.PivotTables("Ventas2").PivotFields("FormaPago").Orientation = Excel.XlPivotFieldOrientation.xlPageField;
sheetVentas2.PivotTables("Ventas2").PivotFields("FormaPago").Position = 1;
sheetVentas2.PivotTables("Ventas2").PivotFields("Fecha").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
sheetVentas2.PivotTables("Ventas2").PivotFields("NombreLocal").Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
Excel.PivotField fldTotalVentas2 = pivotTableVentas2.PivotFields("TotalPublico");
fldTotalVentas2.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
fldTotalVentas2.Function = Excel.XlConsolidationFunction.xlSum;
fldTotalVentas2.Name = " Ventas";
Excel.PivotField fldTotalPrendas2 = pivotTableVentas2.PivotFields("Prendas");
fldTotalPrendas2.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
fldTotalPrendas2.Function = Excel.XlConsolidationFunction.xlSum;
fldTotalPrendas2.Name = " Prendas";
sheetVentas2.Cells[6, 1].Select();
object[] periodosVentas2 = { false, false, false, false, true, false, true };
Excel.Range rangeVentas2 = sheetVentas2.get_Range("a6");
rangeVentas2.Group(true, true, 1, periodosVentas2);
sheetVentas2.PivotTables("Ventas2").PivotFields(" Ventas").NumberFormat = "$ #.##0";
libro.ShowPivotTableFieldList = false;
app.ActiveSheet.PivotTables("Ventas2").RowGrand = false;
app.Range["A1"].Select();
#endregion
#region Ventas
Excel.Worksheet sheetVentas = libro.Sheets.Add();
string hojaVentas = libro.ActiveSheet.Name;
libro.Sheets[hojaVentas].Select();
libro.Sheets[hojaVentas].Name = "Ventas";
Excel.PivotTables pivotTablesVentas = sheetVentas.PivotTables();
Excel.PivotTable pivotTableVentas = pivotTablesVentas.Add(pivotCache, app.Range["A4"], "Ventas");
sheetVentas.PivotTables("Ventas").TableStyle2 = "PivotStyleLight26";
sheetVentas.PivotTables("Ventas").PivotFields("NombreLocal").Orientation = Excel.XlPivotFieldOrientation.xlPageField;
sheetVentas.PivotTables("Ventas").PivotFields("NombreLocal").Position = 1;
sheetVentas.PivotTables("Ventas").PivotFields("FormaPago").Orientation = Excel.XlPivotFieldOrientation.xlPageField;
sheetVentas.PivotTables("Ventas").PivotFields("FormaPago").Position = 2;
sheetVentas.PivotTables("Ventas").PivotFields("Fecha").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
Excel.PivotField fldTotalVentas = pivotTableVentas.PivotFields("TotalPublico");
fldTotalVentas.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
fldTotalVentas.Function = Excel.XlConsolidationFunction.xlSum;
fldTotalVentas.Name = " Ventas";
sheetVentas.Cells[6, 1].Select();
object[] periodosVentas = { false, false, false, false, true, false, true };
Excel.Range rangeVentas = sheetVentas.get_Range("a6");
rangeVentas.Group(true, true, 1, periodosVentas);
sheetVentas.PivotTables("Ventas").PivotFields("Años").Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
sheetVentas.PivotTables("Ventas").PivotFields("Años").Position = 1;
sheetVentas.PivotTables("Ventas").PivotFields(" Ventas").NumberFormat = "$ #.##0";
libro.ShowPivotTableFieldList = false;
app.Range["A5"].Select();
app.Range[app.Selection, app.Selection.End(Microsoft.Office.Interop.Excel.XlDirection.xlDown)].Select();
app.Range[app.Selection, app.Selection.End(Microsoft.Office.Interop.Excel.XlDirection.xlToRight)].Select();
Excel.Range rangoGraficoVentas = app.Selection;
app.ActiveSheet.Shapes.AddChart.Select();
app.ActiveSheet.Shapes(1).Name = "Ventas";
app.ActiveChart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xl3DColumn;
app.ActiveChart.SetSourceData(Source: rangoGraficoVentas);
app.ActiveChart.ClearToMatchStyle();
app.ActiveChart.ChartStyle = 42;
app.ActiveChart.ClearToMatchStyle();
app.ActiveSheet.Shapes["Ventas"].Left = 0;
app.ActiveSheet.Shapes["Ventas"].Top = 300;
app.ActiveSheet.Shapes["Ventas"].ScaleWidth(1.663541776, Microsoft.Office.Core.MsoTriState.msoFalse
, Microsoft.Office.Core.MsoScaleFrom.msoScaleFromTopLeft);
app.ActiveSheet.Shapes["Ventas"].ScaleHeight(1.2777777778, Microsoft.Office.Core.MsoTriState.msoFalse
, Microsoft.Office.Core.MsoScaleFrom.msoScaleFromTopLeft);
app.ActiveSheet.Shapes["Ventas"].ThreeD.RotationX = -30;
app.ActiveSheet.Shapes["Ventas"].ThreeD.RotationY = 100;
app.ActiveSheet.Shapes["Ventas"].ThreeD.FieldOfView = 10;
app.ActiveSheet.PivotTables("Ventas").RowGrand = false;
app.Range["A1"].Select();
#endregion
sheetPrendas.PivotTables("Prendas").PivotFields("Años").Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
sheetPrendas.PivotTables("Prendas").PivotFields("Años").Position = 1;
sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields("Años").Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields("Años").Position = 1;
sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields(" Ventas períodos").Calculation = Excel.XlPivotFieldCalculation.xlPercentDifferenceFrom;
sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields(" Ventas períodos").BaseField = "Años";
sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields(" Ventas períodos").BaseItem = "(anterior)";
sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields(" Ventas períodos").NumberFormat = "0,00%";
sheetAgregado.PivotTables("Valor agregado").PivotFields("Años").Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
sheetAgregado.PivotTables("Valor agregado").PivotFields("Años").Position = 1;
sheetVentas2.PivotTables("Ventas2").PivotFields("Años").Orientation = Excel.XlPivotFieldOrientation.xlPageField;
sheetVentas2.PivotTables("Ventas2").PivotFields("Años").Position = 2;
app.ActiveWorkbook.Connections["Conexión"].ODBCConnection.Connection = "ODBC;DATABASE";
libro.Sheets["Hoja1"].Select();
app.ActiveWindow.SelectedSheets.Delete();
libro.Sheets["Ventas"].Select();
libro.Saved = true;
app.Visible = true;
Cursor.Current = Cursors.Arrow;
}
