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 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 } } } }
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 void CheckOut() { try { if (!comport.IsOpen) { MessageBox.Show("No com port selected", "Error"); Log("Error: port not opened"); return; } if (!Int32.TryParse(TextBoxWeight.Text, out weight)) { MessageBox.Show("Please input integer!", "Error"); TextBoxWeight.Text = ""; return; } if (weight < 0) { MessageBox.Show("Please input positive integer!", "Error"); TextBoxWeight.Text = ""; return; } if (!Int32.TryParse(TextBoxUnitPrice.Text, out unitPrice)) { MessageBox.Show("Please input integer in unit price!", "Error"); TextBoxUnitPrice.Text = ""; return; } if (unitPrice < 0) { MessageBox.Show("Please input positive integer in unit price!", "Error"); TextBoxWeight.Text = ""; return; } UpdateSum(); bool flag = false; Window1 w = new Window1(ref card_tapped, ref flag); processed_flag = false; w.ShowDialog(); if (!processed_flag) { weight = 0; UpdateSum(); return; } double value = xlRange.Cells[team_id + 1, 3].Value2; if (costTotal > value) { MessageBox.Show("Not enough credits!", "Error"); weight = 0; UpdateSum(); Log("Error: " + xlRange.Cells[team_id + 1, 2].Text + " remains " + value.ToString() + " credits, requests " + costTotal.ToString() + " credits, not enough credits."); return; } String name = xlWorksheet.Cells[team_id + 1, 2].Text; String inf = "Confirm trade with Team " + name + " for payment of $" + costTotal.ToString() + "?"; if (MessageBox.Show(inf, "Confirmation", MessageBoxButton.YesNo) == MessageBoxResult.No) { MessageBox.Show("Cancelled.", ""); return; } value -= costTotal; //xlRange.Cells[team_id, 3].Value2 = value; int ptr = (int)xlLog.Cells[3, 14].Value2; xlLog.Cells[ptr, 1].Value2 = team_id; xlLog.Cells[ptr, 2].Value2 = -costTotal; xlLog.Cells[ptr, 3] = DateTime.Now.ToLongTimeString() + " " + DateTime.Now.ToLongDateString(); xlLog.Cells[3, 14] = ptr + 1; MessageBox.Show("Balance Updated\n" + "New balance: " + value.ToString(), "Success"); weight = 0; UpdateSum(); Log("Success: " + xlRange.Cells[team_id + 1, 2].Text + " remains " + value.ToString() + " credits, requests " + costTotal.ToString() + " credits, new balance " + value.ToString() + " credits."); pivotTables.Item(1).RefreshTable(); xlWorkbook.Save(); } catch (Exception e) { MessageBox.Show("No com port selected", "Error"); Log("Error: cannot open port" + e.ToString()); return; } }
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(); } }