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