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; }