/// <summary>
/// Allinea il grafico al range in modo da far combaciare la barra delle ordinate con la prima colonna dell'area dati. Per far questo calcola la dimensione in punti dei label di ordinata e sposta di conseguenza l'area del grafico.
/// </summary>
/// <param name="chart">Microsoft.Office.Interop.Excel.Chart da aggiornare.</param>
/// <param name="rigaGrafico">Microsoft.Office.Interop.Excel.Range a cui il grafico appartiene.</param>
private void AggiornaGrafici(Excel.Chart chart, Excel.Range rigaGrafico)
{
SplashScreen.UpdateStatus("Aggiorno grafici " + chart.Name);
chart.Refresh();
//resize dell'area del grafico per adattarla alle ore
using (Graphics grfx = Graphics.FromImage(new Bitmap(1, 1)))
{
grfx.PageUnit = GraphicsUnit.Point;
grfx.TextRenderingHint = System.Drawing.Text.TextRenderingHint.AntiAliasGridFit;
float sizeMax = float.MinValue;
SizeF tmpSize;
double val = chart.Axes(Excel.XlAxisType.xlValue).MinimumScale;
//controllo anche il fondo scala: se cambia l'ordine di grandezza excel lascia lo spazio nel label come se ci fosse!!
while (val <= chart.Axes(Excel.XlAxisType.xlValue).MaximumScale)
{
string tmpval = ""
+ (val >= 1000 ? Math.Round(val / 1000.0, 1) : val)
+ ((val / 1000.0) % 1 == 0 ? ",0" : "")
+ (val >= 1000 ? "K" : "");
tmpval = tmpval.Replace(".", ",");
tmpSize = grfx.MeasureString(tmpval, new Font(chart.Axes(Excel.XlAxisType.xlValue).TickLabels.Font.Name, (float)chart.Axes(Excel.XlAxisType.xlValue).TickLabels.Font.Size));
sizeMax = Math.Max(sizeMax, tmpSize.Width);
val += chart.Axes(Excel.XlAxisType.xlValue).MajorUnit;
}
//MANTENERE ORDINE DI QUESTE ISTRUZIONI
chart.ChartArea.Left = rigaGrafico.Left - Math.Ceiling(sizeMax) - 7; //sposto a destra il grafico
chart.ChartArea.Width = rigaGrafico.Width + Math.Ceiling(sizeMax) + 4; //aumento la larghezza del grafico
Excel.PlotArea plotArea = chart.PlotArea;
try
{
plotArea.InsideLeft = 0d; //allineo il grafico al bordo sinistro dell'area esterna al grafico
}
catch { }
plotArea.Width = chart.ChartArea.Width + 3; //aumento la larghezza dell'area esterna al grafico
Marshal.ReleaseComObject(plotArea);
plotArea = null;
bool start = TimeZone.CurrentTimeZone.IsDaylightSavingTime(Workbook.DataAttiva);
bool end = TimeZone.CurrentTimeZone.IsDaylightSavingTime(Workbook.DataAttiva.AddDays(Struct.intervalloGiorni));
if (!start || end)
{
chart.ChartArea.Width -= _ws.Range[Range.GetRange(1, _definedNames.GetColFromDate(Date.SuffissoDATA1, Date.GetSuffissoOra(25)))].Width;
}
}
chart.Refresh();
}
private void btnCreateChart_Click(object sender, EventArgs e)
{
try
{
Excel.Chart oChart = xlWBook.Charts.Add();
Excel.Range rngChart = (Excel.Range)xlWSheet.get_Range("" + cmb1.Text + (int)No1.Value + "", "" + cmb2.Text + (int)No2.Value + "");
FormatAsTable(rngChart, "Table1", "TableStyleMedium1");
oChart.ChartWizard(rngChart, Excel.XlChartType.xl3DColumn);
oChart.Refresh();
//xlWBook.AcceptAllChanges();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void Exportar(String Empresa, Int32 Anio, String Titulo, String SubTitulo, String Nombre, System.Data.DataTable Reporte, System.Data.DataTable Comparacion1, System.Data.DataTable Comparacion2, Boolean MostrarRebate = false)
{
XLExcel.Application xlApplication;
XLExcel.Workbook xlWorkbook;
XLExcel.Worksheet xlWorksheetData;
XLExcel.Worksheet xlWorksheetPivot;
XLExcel.Worksheet xlWorksheetComparacion;
XLExcel.Worksheet xlWorksheetPivotRebate = null;
XLExcel.Worksheet xlWorksheetPivotFFVV = null;
XLExcel.Worksheet xlWorksheetPivotFFVVServicios = null;
XLExcel.Range xlRange;
XLExcel.Range xlRangeComparacion;
object misValue = System.Reflection.Missing.Value;
xlApplication = new XLExcel.Application();
Int32 _WorksheetsIndex = 1;
xlWorkbook = xlApplication.Workbooks.Add(misValue);
xlWorksheetData = (XLExcel.Worksheet)xlWorkbook.Worksheets[_WorksheetsIndex];
xlWorksheetData.Name = "BD";
_WorksheetsIndex += 1;
if (xlWorkbook.Worksheets.Count < _WorksheetsIndex)
{
xlWorkbook.Worksheets.Add(misValue, xlWorksheetData, misValue, misValue);
}
xlWorksheetPivot = (XLExcel.Worksheet)xlWorkbook.Worksheets[_WorksheetsIndex];
xlWorksheetPivot.Name = "TOTAL " + Nombre + (MostrarRebate ? " SIN REBATE" : "");
if (MostrarRebate)
{
_WorksheetsIndex += 1;
if (xlWorkbook.Worksheets.Count < _WorksheetsIndex)
{
xlWorkbook.Worksheets.Add(misValue, xlWorksheetPivot, misValue, misValue);
}
xlWorksheetPivotRebate = (XLExcel.Worksheet)xlWorkbook.Worksheets[_WorksheetsIndex];
xlWorksheetPivotRebate.Name = "TOTAL " + Nombre + " CON REBATE";
}
_WorksheetsIndex += 1;
if (xlWorkbook.Worksheets.Count < _WorksheetsIndex)
{
xlWorkbook.Worksheets.Add(misValue, xlWorksheetPivot, misValue, misValue);
}
xlWorksheetPivotFFVV = (XLExcel.Worksheet)xlWorkbook.Worksheets[_WorksheetsIndex];
xlWorksheetPivotFFVV.Name = "FUERZA VENTAS";
_WorksheetsIndex += 1;
if (xlWorkbook.Worksheets.Count < _WorksheetsIndex)
{
xlWorkbook.Worksheets.Add(misValue, xlWorksheetPivot, misValue, misValue);
}
xlWorksheetPivotFFVVServicios = (XLExcel.Worksheet)xlWorkbook.Worksheets[_WorksheetsIndex];
xlWorksheetPivotFFVVServicios.Name = "FFVV POR SERVICIOS";
_WorksheetsIndex += 1;
if (xlWorkbook.Worksheets.Count < _WorksheetsIndex)
{
xlWorkbook.Worksheets.Add(misValue, xlWorksheetPivot, misValue, misValue);
}
xlWorksheetComparacion = (XLExcel.Worksheet)xlWorkbook.Worksheets[_WorksheetsIndex];
xlWorksheetComparacion.Name = "COMPARATIVO";
try
{
String fuente = "Calibri";
int fontsize = 11;
int _sheetIndex = 1;
Int32 rowIndex = 0;
Int32 colIndex = 0;
#region [ Statment ]
xlWorksheetData.Activate();
//HEADER
var _columns = new Object[1, Reporte.Columns.Count];
foreach (System.Data.DataColumn _column in Reporte.Columns)
{
_columns[rowIndex, colIndex] = _column.ColumnName; colIndex += 1;
}
xlRange = xlWorksheetData.Range[xlWorksheetData.Cells[1, 1], xlWorksheetData.Cells[1, Reporte.Columns.Count]];
xlRange.Value2 = _columns;
rowIndex = 0;
colIndex = 0;
//CELLS
var _data = new Object[Reporte.Rows.Count, Reporte.Columns.Count];
foreach (System.Data.DataRow _row in Reporte.Rows)
{
colIndex = 0;
foreach (System.Data.DataColumn _column in Reporte.Columns)
{
_data[rowIndex, colIndex] = _row[_column.ColumnName];
colIndex += 1;
}
rowIndex += 1;
}
xlRange = xlWorksheetData.Range[xlWorksheetData.Cells[2, 1], xlWorksheetData.Cells[Reporte.Rows.Count + 1, Reporte.Columns.Count]];
xlRange.Value2 = _data;
#endregion
#region [ Comparacion ]
xlWorksheetComparacion = (XLExcel.Worksheet)xlWorkbook.Worksheets[(MostrarRebate ? 6 : 5)];
xlWorksheetComparacion.Name = "COMPARATIVO";
xlWorksheetComparacion.Activate();
rowIndex = 0;
colIndex = 0;
//HEADER
_columns = new Object[1, Comparacion1.Columns.Count];
foreach (System.Data.DataColumn _column in Comparacion1.Columns)
{
_columns[rowIndex, colIndex] = _column.ColumnName; colIndex += 1;
}
xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[7, 1], xlWorksheetComparacion.Cells[7, Comparacion1.Columns.Count]];
xlRangeComparacion.Value2 = _columns;
rowIndex = 0;
colIndex = 0;
//CELLS
_data = new Object[Comparacion1.Rows.Count, Comparacion1.Columns.Count];
foreach (System.Data.DataRow _row in Comparacion1.Rows)
{
colIndex = 0;
foreach (System.Data.DataColumn _column in Comparacion1.Columns)
{
_data[rowIndex, colIndex] = _row[_column.ColumnName]; colIndex += 1;
}
rowIndex += 1;
}
xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[8, 1], xlWorksheetComparacion.Cells[Comparacion1.Rows.Count + 7, Comparacion1.Columns.Count]];
xlRangeComparacion.Value2 = _data;
xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[7, 1], xlWorksheetComparacion.Cells[7, 1]];
xlRangeComparacion.Value = (Anio - 1).ToString();
xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[7, 1], xlWorksheetComparacion.Cells[10, 14]];
setBorderCelda(xlRangeComparacion, XLExcel.XlBorderWeight.xlThin, XLExcel.XlLineStyle.xlContinuous, true, true, true, true, true, true, true, false, false);
xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[7, 1], xlWorksheetComparacion.Cells[7, 14]];
xlRangeComparacion.Interior.Pattern = XLExcel.XlPattern.xlPatternSolid;
xlRangeComparacion.Interior.PatternColorIndex = XLExcel.XlPattern.xlPatternAutomatic;
xlRangeComparacion.Interior.ThemeColor = XLExcel.XlThemeColor.xlThemeColorAccent2;
xlRangeComparacion.Interior.TintAndShade = 0;
xlRangeComparacion.Interior.PatternTintAndShade = 0;
xlRangeComparacion.Font.ThemeColor = XLExcel.XlThemeColor.xlThemeColorDark1;
xlRangeComparacion.Font.TintAndShade = 0;
xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[7, 1], xlWorksheetComparacion.Cells[10, 1]];
xlRangeComparacion.Interior.Pattern = XLExcel.XlPattern.xlPatternSolid;
xlRangeComparacion.Interior.PatternColorIndex = XLExcel.XlPattern.xlPatternAutomatic;
xlRangeComparacion.Interior.ThemeColor = XLExcel.XlThemeColor.xlThemeColorAccent2;
xlRangeComparacion.Interior.TintAndShade = 0;
xlRangeComparacion.Interior.PatternTintAndShade = 0;
xlRangeComparacion.Font.ThemeColor = XLExcel.XlThemeColor.xlThemeColorDark1;
xlRangeComparacion.Font.TintAndShade = 0;
rowIndex = 0;
colIndex = 0;
//HEADER
_columns = new Object[1, Comparacion2.Columns.Count];
foreach (System.Data.DataColumn _column in Comparacion2.Columns)
{
_columns[rowIndex, colIndex] = _column.ColumnName; colIndex += 1;
}
xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[12, 1], xlWorksheetComparacion.Cells[12, Comparacion2.Columns.Count]];
xlRangeComparacion.Value2 = _columns;
rowIndex = 0;
colIndex = 0;
//CELLS
_data = new Object[Comparacion2.Rows.Count, Comparacion2.Columns.Count];
foreach (System.Data.DataRow _row in Comparacion2.Rows)
{
colIndex = 0;
foreach (System.Data.DataColumn _column in Comparacion2.Columns)
{
_data[rowIndex, colIndex] = _row[_column.ColumnName]; colIndex += 1;
}
rowIndex += 1;
}
xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[13, 1], xlWorksheetComparacion.Cells[Comparacion2.Rows.Count + 12, Comparacion2.Columns.Count]];
xlRangeComparacion.Value2 = _data;
xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[12, 1], xlWorksheetComparacion.Cells[12, 1]];
xlRangeComparacion.Value = Anio.ToString();
xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[12, 1], xlWorksheetComparacion.Cells[15, 14]];
setBorderCelda(xlRangeComparacion, XLExcel.XlBorderWeight.xlThin, XLExcel.XlLineStyle.xlContinuous, true, true, true, true, true, true, true, false, false);
xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[12, 1], xlWorksheetComparacion.Cells[12, 14]];
xlRangeComparacion.Interior.Pattern = XLExcel.XlPattern.xlPatternSolid;
xlRangeComparacion.Interior.PatternColorIndex = XLExcel.XlPattern.xlPatternAutomatic;
xlRangeComparacion.Interior.ThemeColor = XLExcel.XlThemeColor.xlThemeColorAccent2;
xlRangeComparacion.Interior.TintAndShade = 0;
xlRangeComparacion.Interior.PatternTintAndShade = 0;
xlRangeComparacion.Font.ThemeColor = XLExcel.XlThemeColor.xlThemeColorDark1;
xlRangeComparacion.Font.TintAndShade = 0;
xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[12, 1], xlWorksheetComparacion.Cells[15, 1]];
xlRangeComparacion.Interior.Pattern = XLExcel.XlPattern.xlPatternSolid;
xlRangeComparacion.Interior.PatternColorIndex = XLExcel.XlPattern.xlPatternAutomatic;
xlRangeComparacion.Interior.ThemeColor = XLExcel.XlThemeColor.xlThemeColorAccent2;
xlRangeComparacion.Interior.TintAndShade = 0;
xlRangeComparacion.Interior.PatternTintAndShade = 0;
xlRangeComparacion.Font.ThemeColor = XLExcel.XlThemeColor.xlThemeColorDark1;
xlRangeComparacion.Font.TintAndShade = 0;
XLExcel.Chart XLChart1 = (XLExcel.Chart)xlWorksheetComparacion.Shapes.AddChart(XLExcel.XlChartType.xl3DColumnClustered, 100, 250, 500, 300).Chart;
XLExcel.SeriesCollection XLSeriesCollection1 = XLChart1.SeriesCollection();
XLExcel.Series XLSerie1 = XLSeriesCollection1.NewSeries();
XLSerie1.Name = "=COMPARATIVO!$A$7";
XLSerie1.Values = "=COMPARATIVO!$B$8:$M$8";
XLSerie1.XValues = "=COMPARATIVO!$B$7:$M$7";
XLExcel.Series XLSerie2 = XLSeriesCollection1.NewSeries();
XLSerie2.Name = "=COMPARATIVO!$A$12";
XLSerie2.Values = "=COMPARATIVO!$B$13:$M$13";
XLSerie2.XValues = "=COMPARATIVO!$B$7:$M$7";
XLChart1.SetElement(Microsoft.Office.Core.MsoChartElementType.msoElementChartTitleAboveChart);
XLChart1.ChartTitle.Text = String.Format("COMPARATIVO {0} - TEUS {1} VS. {2}", Titulo, (Anio - 1), Anio);
XLChart1.SetElement(Microsoft.Office.Core.MsoChartElementType.msoElementPrimaryValueAxisTitleAdjacentToAxis);
XLExcel.Axis XlAxis1 = XLChart1.Axes(XLExcel.XlAxisType.xlValue, XLExcel.XlAxisGroup.xlPrimary) as XLExcel.Axis;
XlAxis1.HasTitle = true;
XlAxis1.AxisTitle.Caption = "TEUS";
//XLChart1.Activate();
XLChart1.Refresh();
XLExcel.Chart XLChart2 = (XLExcel.Chart)xlWorksheetComparacion.Shapes.AddChart(XLExcel.XlChartType.xl3DColumnClustered, 100, 600, 500, 300).Chart;
XLExcel.SeriesCollection XLSeriesCollection2 = XLChart2.SeriesCollection();
XLExcel.Series XLSerie3 = XLSeriesCollection2.NewSeries();
XLSerie3.Name = "=COMPARATIVO!$A$7";
XLSerie3.Values = "=COMPARATIVO!$B$9:$M$9";
XLSerie3.XValues = "=COMPARATIVO!$B$7:$M$7";
XLExcel.Series XLSerie4 = XLSeriesCollection2.NewSeries();
XLSerie4.Name = "=COMPARATIVO!$A$12";
XLSerie4.Values = "=COMPARATIVO!$B$14:$M$14";
XLSerie4.XValues = "=COMPARATIVO!$B$7:$M$7";
XLChart2.SetElement(Microsoft.Office.Core.MsoChartElementType.msoElementChartTitleAboveChart);
XLChart2.ChartTitle.Text = String.Format("COMPARATIVO {0} - PROFIT {1} VS. {2}", Titulo, (Anio - 1), Anio);
XLChart2.SetElement(Microsoft.Office.Core.MsoChartElementType.msoElementPrimaryValueAxisTitleAdjacentToAxis);
XLExcel.Axis XlAxis2 = XLChart2.Axes(XLExcel.XlAxisType.xlValue, XLExcel.XlAxisGroup.xlPrimary) as XLExcel.Axis;
XlAxis2.HasTitle = true;
XlAxis2.AxisTitle.Caption = "PROFIT";
//XLChart2.Activate();
XLChart2.Refresh();
//TITLE
xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[2, 1], xlWorksheetComparacion.Cells[2, 14]];
xlRangeComparacion.MergeCells = true;
xlRangeComparacion.Value = Titulo + " - COMPARATIVO TEUS Y PROFIT";
xlRangeComparacion.Font.Bold = true;
xlRangeComparacion.Font.Size = 16;
xlRangeComparacion.VerticalAlignment = XLExcel.XlVAlign.xlVAlignCenter;
xlRangeComparacion.HorizontalAlignment = XLExcel.XlHAlign.xlHAlignCenter;
xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[3, 1], xlWorksheetComparacion.Cells[3, 14]];
xlRangeComparacion.MergeCells = true;
xlRangeComparacion.Value = SubTitulo + " - " + (Anio - 1).ToString() + " VS. " + Anio.ToString();
xlRangeComparacion.Font.Bold = true;
xlRangeComparacion.Font.Size = 12;
xlRangeComparacion.VerticalAlignment = XLExcel.XlVAlign.xlVAlignCenter;
xlRangeComparacion.HorizontalAlignment = XLExcel.XlHAlign.xlHAlignCenter;
#endregion
#region [ TOTAL SIN REBATE ]
xlWorksheetData.Activate();
xlRange = xlWorksheetData.Range[xlWorksheetData.Cells[1, 1], xlWorksheetData.Cells[Reporte.Rows.Count + 1, Reporte.Columns.Count]];
xlWorksheetPivot = (XLExcel.Worksheet)xlWorkbook.Worksheets[2];
xlWorksheetPivot.Name = "TOTAL " + Nombre + (MostrarRebate ? " SIN REBATE" : "");
xlWorksheetPivot.Activate();
XLExcel.Range xlRangePivot;
//TITLE
xlRangePivot = xlWorksheetPivot.Range[xlWorksheetPivot.Cells[2, 1], xlWorksheetPivot.Cells[2, 14]];
xlRangePivot.MergeCells = true;
xlRangePivot.Value = Empresa + "-" + Titulo;
xlRangePivot.Font.Bold = true;
xlRangePivot.Font.Size = 16;
xlRangePivot.VerticalAlignment = XLExcel.XlVAlign.xlVAlignCenter;
xlRangePivot.HorizontalAlignment = XLExcel.XlHAlign.xlHAlignCenter;
xlRangePivot = xlWorksheetPivot.Range[xlWorksheetPivot.Cells[3, 1], xlWorksheetPivot.Cells[3, 14]];
xlRangePivot.MergeCells = true;
xlRangePivot.Value = SubTitulo + " " + Anio.ToString();
xlRangePivot.Font.Bold = true;
xlRangePivot.Font.Size = 12;
xlRangePivot.VerticalAlignment = XLExcel.XlVAlign.xlVAlignCenter;
xlRangePivot.HorizontalAlignment = XLExcel.XlHAlign.xlHAlignCenter;
xlRangePivot = xlWorksheetPivot.Range[xlWorksheetPivot.Cells[6, 1], xlWorksheetPivot.Cells[6, 1]];;
// create Pivot Cache and Pivot Table
XLExcel.PivotCache XLPivotCache = (XLExcel.PivotCache)xlWorkbook.PivotCaches().Add(XLExcel.XlPivotTableSourceType.xlDatabase, xlRange);
XLExcel.PivotTable XLPivotTable = (XLExcel.PivotTable)xlWorksheetPivot.PivotTables().Add(PivotCache: XLPivotCache, TableDestination: xlRangePivot, TableName: "SIN REBATE");
// create Pivot Field, note that name will be the same as column name on sheet one
XLExcel.PivotField XLPivotFieldMES = (XLExcel.PivotField)XLPivotTable.PivotFields("MES");
XLPivotFieldMES.Orientation = XLExcel.XlPivotFieldOrientation.xlColumnField;
XLPivotFieldMES.Name = "MES";
XLExcel.PivotField XLPivotFieldVENDEDOR = (XLExcel.PivotField)XLPivotTable.PivotFields("VENDEDOR");
XLPivotFieldVENDEDOR.Orientation = XLExcel.XlPivotFieldOrientation.xlRowField;
XLPivotFieldVENDEDOR.Name = "VENDEDOR";
XLExcel.PivotField XLPivotFieldTEUS = (XLExcel.PivotField)XLPivotTable.PivotFields("TEUS");
XLPivotFieldTEUS.Orientation = XLExcel.XlPivotFieldOrientation.xlDataField;
XLPivotFieldTEUS.Function = XLExcel.XlConsolidationFunction.xlSum;
XLPivotFieldTEUS.Name = "Sum TEUS";
XLExcel.PivotField XLPivotFieldRENT_TOTAL = (XLExcel.PivotField)XLPivotTable.PivotFields("RENT_TOTAL");
XLPivotFieldRENT_TOTAL.Orientation = XLExcel.XlPivotFieldOrientation.xlDataField;
XLPivotFieldRENT_TOTAL.Function = XLExcel.XlConsolidationFunction.xlSum;
XLPivotFieldRENT_TOTAL.Name = "Sum RENT_TOTAL";
XLPivotTable.SubtotalLocation(XLExcel.XlSubtototalLocationType.xlAtBottom);
XLPivotTable.DataPivotField.Orientation = XLExcel.XlPivotFieldOrientation.xlRowField;
XLPivotTable.DataPivotField.Position = 2;
XLPivotTable.TableStyle2 = "PivotStyleLight18";
XLPivotTable.RowAxisLayout(XLExcel.XlLayoutRowType.xlCompactRow);
#endregion
if (MostrarRebate)
{
#region [ TOTAL CON REBATE ]
xlWorksheetData.Activate();
xlRange = xlWorksheetData.Range[xlWorksheetData.Cells[1, 1], xlWorksheetData.Cells[Reporte.Rows.Count + 1, Reporte.Columns.Count]];
xlWorksheetPivotRebate = (XLExcel.Worksheet)xlWorkbook.Worksheets[3];
xlWorksheetPivotRebate.Name = "TOTAL " + Nombre + " CON REBATE";
xlWorksheetPivotRebate.Activate();
XLExcel.Range xlRangePivotRebate;
//TITLE
xlRangePivotRebate = xlWorksheetPivotRebate.Range[xlWorksheetPivotRebate.Cells[2, 1], xlWorksheetPivotRebate.Cells[2, 14]];
xlRangePivotRebate.MergeCells = true;
xlRangePivotRebate.Value = Empresa + "-" + Titulo;
xlRangePivotRebate.Font.Bold = true;
xlRangePivotRebate.Font.Size = 16;
xlRangePivotRebate.VerticalAlignment = XLExcel.XlVAlign.xlVAlignCenter;
xlRangePivotRebate.HorizontalAlignment = XLExcel.XlHAlign.xlHAlignCenter;
xlRangePivotRebate = xlWorksheetPivotRebate.Range[xlWorksheetPivotRebate.Cells[3, 1], xlWorksheetPivotRebate.Cells[3, 14]];
xlRangePivotRebate.MergeCells = true;
xlRangePivotRebate.Value = SubTitulo + " " + Anio.ToString();
xlRangePivotRebate.Font.Bold = true;
xlRangePivotRebate.Font.Size = 12;
xlRangePivotRebate.VerticalAlignment = XLExcel.XlVAlign.xlVAlignCenter;
xlRangePivotRebate.HorizontalAlignment = XLExcel.XlHAlign.xlHAlignCenter;
xlRangePivotRebate = xlWorksheetPivotRebate.Range[xlWorksheetPivotRebate.Cells[6, 1], xlWorksheetPivotRebate.Cells[6, 1]];;
// create Pivot Cache and Pivot Table
//XLExcel.PivotCache XLPivotCacheRebate = (XLExcel.PivotCache)xlWorkbook.PivotCaches().Add(XLExcel.XlPivotTableSourceType.xlDatabase, xlRange);
XLExcel.PivotTable XLPivotTableRebate = (XLExcel.PivotTable)xlWorksheetPivotRebate.PivotTables().Add(PivotCache: XLPivotCache, TableDestination: xlRangePivotRebate, TableName: "CON REBATE");
// create Pivot Field, note that name will be the same as column name on sheet one
XLExcel.PivotField XLPivotFieldMESRebate = (XLExcel.PivotField)XLPivotTableRebate.PivotFields("MES");
XLPivotFieldMESRebate.Orientation = XLExcel.XlPivotFieldOrientation.xlColumnField;
XLPivotFieldMESRebate.Name = "MES";
XLExcel.PivotField XLPivotFieldVENDEDORRebate = (XLExcel.PivotField)XLPivotTableRebate.PivotFields("VENDEDOR");
XLPivotFieldVENDEDORRebate.Orientation = XLExcel.XlPivotFieldOrientation.xlRowField;
XLPivotFieldVENDEDORRebate.Name = "VENDEDOR";
XLExcel.PivotField XLPivotFieldTEUSRebate = (XLExcel.PivotField)XLPivotTableRebate.PivotFields("TEUS");
XLPivotFieldTEUSRebate.Orientation = XLExcel.XlPivotFieldOrientation.xlDataField;
XLPivotFieldTEUSRebate.Function = XLExcel.XlConsolidationFunction.xlSum;
XLPivotFieldTEUSRebate.Name = "Sum TEUS";
XLExcel.PivotField XLPivotFieldRENT_TOTALRebate = (XLExcel.PivotField)XLPivotTableRebate.PivotFields("RENTABILIDAD_CON_REBATE");
XLPivotFieldRENT_TOTALRebate.Orientation = XLExcel.XlPivotFieldOrientation.xlDataField;
XLPivotFieldRENT_TOTALRebate.Function = XLExcel.XlConsolidationFunction.xlSum;
XLPivotFieldRENT_TOTALRebate.Name = "Sum RENTABILIDAD_CON_REBATE";
XLPivotTableRebate.SubtotalLocation(XLExcel.XlSubtototalLocationType.xlAtBottom);
XLPivotTableRebate.DataPivotField.Orientation = XLExcel.XlPivotFieldOrientation.xlRowField;
XLPivotTableRebate.DataPivotField.Position = 2;
XLPivotTableRebate.TableStyle2 = "PivotStyleLight18";
XLPivotTableRebate.RowAxisLayout(XLExcel.XlLayoutRowType.xlCompactRow);
#endregion
}
#region [ FUERZA DE VENTAS ]
xlWorksheetData.Activate();
xlRange = xlWorksheetData.Range[xlWorksheetData.Cells[1, 1], xlWorksheetData.Cells[Reporte.Rows.Count + 1, Reporte.Columns.Count]];
xlWorksheetPivotFFVV = (XLExcel.Worksheet)xlWorkbook.Worksheets[(MostrarRebate ? 4 : 3)];
xlWorksheetPivotFFVV.Name = "FUERZA VENTAS";
xlWorksheetPivotFFVV.Activate();
XLExcel.Range xlRangePivotFFVV;
//TITLE
xlRangePivotFFVV = xlWorksheetPivotFFVV.Range[xlWorksheetPivotFFVV.Cells[2, 1], xlWorksheetPivotFFVV.Cells[2, 14]];
xlRangePivotFFVV.MergeCells = true;
xlRangePivotFFVV.Value = Empresa + "-" + Titulo;
xlRangePivotFFVV.Font.Bold = true;
xlRangePivotFFVV.Font.Size = 16;
xlRangePivotFFVV.VerticalAlignment = XLExcel.XlVAlign.xlVAlignCenter;
xlRangePivotFFVV.HorizontalAlignment = XLExcel.XlHAlign.xlHAlignCenter;
xlRangePivotFFVV = xlWorksheetPivotFFVV.Range[xlWorksheetPivotFFVV.Cells[3, 1], xlWorksheetPivotFFVV.Cells[3, 14]];
xlRangePivotFFVV.MergeCells = true;
xlRangePivotFFVV.Value = SubTitulo + " " + Anio.ToString();
xlRangePivotFFVV.Font.Bold = true;
xlRangePivotFFVV.Font.Size = 12;
xlRangePivotFFVV.VerticalAlignment = XLExcel.XlVAlign.xlVAlignCenter;
xlRangePivotFFVV.HorizontalAlignment = XLExcel.XlHAlign.xlHAlignCenter;
xlRangePivotFFVV = xlWorksheetPivotFFVV.Range[xlWorksheetPivotFFVV.Cells[6, 1], xlWorksheetPivotFFVV.Cells[6, 1]];;
// create Pivot Cache and Pivot Table
//XLExcel.PivotCache XLPivotCache = (XLExcel.PivotCache)xlWorkbook.PivotCaches().Add(XLExcel.XlPivotTableSourceType.xlDatabase, xlRange);
XLExcel.PivotTable XLPivotTableFFVV = (XLExcel.PivotTable)xlWorksheetPivotFFVV.PivotTables().Add(PivotCache: XLPivotCache, TableDestination: xlRangePivotFFVV, TableName: "FFVV");
// create Pivot Field, note that name will be the same as column name on sheet one
XLExcel.PivotField XLPivotFieldMESFFVV = (XLExcel.PivotField)XLPivotTableFFVV.PivotFields("MES");
XLPivotFieldMESFFVV.Orientation = XLExcel.XlPivotFieldOrientation.xlColumnField;
XLPivotFieldMESFFVV.Name = "MES";
XLExcel.PivotField XLPivotFieldVENDEDORFFVV = (XLExcel.PivotField)XLPivotTableFFVV.PivotFields("VENDEDOR");
XLPivotFieldVENDEDORFFVV.Orientation = XLExcel.XlPivotFieldOrientation.xlRowField;
XLPivotFieldVENDEDORFFVV.Name = "VENDEDOR";
XLExcel.PivotField XLPivotFieldTEUSFFVV = (XLExcel.PivotField)XLPivotTableFFVV.PivotFields("TEUS");
XLPivotFieldTEUSFFVV.Orientation = XLExcel.XlPivotFieldOrientation.xlDataField;
XLPivotFieldTEUSFFVV.Function = XLExcel.XlConsolidationFunction.xlSum;
XLPivotFieldTEUSFFVV.Name = "Sum TEUS";
if (MostrarRebate)
{
XLExcel.PivotField XLPivotFieldRENT_TOTALFFVV = (XLExcel.PivotField)XLPivotTableFFVV.PivotFields("RENTABILIDAD_CON_REBATE");
XLPivotFieldRENT_TOTALFFVV.Orientation = XLExcel.XlPivotFieldOrientation.xlDataField;
XLPivotFieldRENT_TOTALFFVV.Function = XLExcel.XlConsolidationFunction.xlSum;
XLPivotFieldRENT_TOTALFFVV.Name = "Sum RENTABILIDAD_CON_REBATE";
}
else
{
XLExcel.PivotField XLPivotFieldRENT_TOTALFFVV = (XLExcel.PivotField)XLPivotTableFFVV.PivotFields("RENT_TOTAL");
XLPivotFieldRENT_TOTALFFVV.Orientation = XLExcel.XlPivotFieldOrientation.xlDataField;
XLPivotFieldRENT_TOTALFFVV.Function = XLExcel.XlConsolidationFunction.xlSum;
XLPivotFieldRENT_TOTALFFVV.Name = "Sum RENT_TOTAL";
}
XLPivotTableFFVV.SubtotalLocation(XLExcel.XlSubtototalLocationType.xlAtBottom);
XLPivotTableFFVV.DataPivotField.Orientation = XLExcel.XlPivotFieldOrientation.xlRowField;
XLPivotTableFFVV.DataPivotField.Position = 2;
XLPivotTableFFVV.TableStyle2 = "PivotStyleLight18";
XLPivotTableFFVV.RowAxisLayout(XLExcel.XlLayoutRowType.xlCompactRow);
#endregion
#region [ FUERZA DE VENTAS TRAFICO ]
xlWorksheetData.Activate();
xlRange = xlWorksheetData.Range[xlWorksheetData.Cells[1, 1], xlWorksheetData.Cells[Reporte.Rows.Count + 1, Reporte.Columns.Count]];
xlWorksheetPivotFFVVServicios = (XLExcel.Worksheet)xlWorkbook.Worksheets[(MostrarRebate ? 5 : 4)];
xlWorksheetPivotFFVVServicios.Name = "FF VV Servicios";
xlWorksheetPivotFFVVServicios.Activate();
XLExcel.Range xlRangePivotFFVVServicios;
//TITLE
xlRangePivotFFVVServicios = xlWorksheetPivotFFVVServicios.Range[xlWorksheetPivotFFVVServicios.Cells[2, 1], xlWorksheetPivotFFVVServicios.Cells[2, 14]];
xlRangePivotFFVVServicios.MergeCells = true;
xlRangePivotFFVVServicios.Value = Empresa + "-" + Titulo;
xlRangePivotFFVVServicios.Font.Bold = true;
xlRangePivotFFVVServicios.Font.Size = 16;
xlRangePivotFFVVServicios.VerticalAlignment = XLExcel.XlVAlign.xlVAlignCenter;
xlRangePivotFFVVServicios.HorizontalAlignment = XLExcel.XlHAlign.xlHAlignCenter;
xlRangePivotFFVVServicios = xlWorksheetPivotFFVVServicios.Range[xlWorksheetPivotFFVVServicios.Cells[3, 1], xlWorksheetPivotFFVVServicios.Cells[3, 14]];
xlRangePivotFFVVServicios.MergeCells = true;
xlRangePivotFFVVServicios.Value = SubTitulo + " " + Anio.ToString();
xlRangePivotFFVVServicios.Font.Bold = true;
xlRangePivotFFVVServicios.Font.Size = 12;
xlRangePivotFFVVServicios.VerticalAlignment = XLExcel.XlVAlign.xlVAlignCenter;
xlRangePivotFFVVServicios.HorizontalAlignment = XLExcel.XlHAlign.xlHAlignCenter;
xlRangePivotFFVVServicios = xlWorksheetPivotFFVVServicios.Range[xlWorksheetPivotFFVVServicios.Cells[6, 1], xlWorksheetPivotFFVVServicios.Cells[6, 1]];;
// create Pivot Cache and Pivot Table
//XLExcel.PivotCache XLPivotCache = (XLExcel.PivotCache)xlWorkbook.PivotCaches().Add(XLExcel.XlPivotTableSourceType.xlDatabase, xlRange);
XLExcel.PivotTable XLPivotTableFFVVServicios = (XLExcel.PivotTable)xlWorksheetPivotFFVVServicios.PivotTables().Add(PivotCache: XLPivotCache, TableDestination: xlRangePivotFFVVServicios, TableName: "FFVV Servicios");
// create Pivot Field, note that name will be the same as column name on sheet one
XLExcel.PivotField XLPivotFieldMESFFVVServicios = (XLExcel.PivotField)XLPivotTableFFVVServicios.PivotFields("MES");
XLPivotFieldMESFFVVServicios.Orientation = XLExcel.XlPivotFieldOrientation.xlColumnField;
XLPivotFieldMESFFVVServicios.Name = "MES";
XLExcel.PivotField XLPivotFieldVENDEDORFFVVServicios = (XLExcel.PivotField)XLPivotTableFFVVServicios.PivotFields("TRAFICO");
XLPivotFieldVENDEDORFFVVServicios.Orientation = XLExcel.XlPivotFieldOrientation.xlRowField;
XLPivotFieldVENDEDORFFVVServicios.Name = "TRAFICO";
XLExcel.PivotField XLPivotFieldSERVICIOFFVVServicios = (XLExcel.PivotField)XLPivotTableFFVVServicios.PivotFields("VENDEDOR");
XLPivotFieldSERVICIOFFVVServicios.Orientation = XLExcel.XlPivotFieldOrientation.xlRowField;
XLPivotFieldSERVICIOFFVVServicios.Name = "VENDEDOR";
XLExcel.PivotField XLPivotFieldTEUSFFVVServicios = (XLExcel.PivotField)XLPivotTableFFVVServicios.PivotFields("TEUS");
XLPivotFieldTEUSFFVVServicios.Orientation = XLExcel.XlPivotFieldOrientation.xlDataField;
XLPivotFieldTEUSFFVVServicios.Function = XLExcel.XlConsolidationFunction.xlSum;
XLPivotFieldTEUSFFVVServicios.Name = "Sum TEUS";
if (MostrarRebate)
{
XLExcel.PivotField XLPivotFieldRENT_TOTALFFVVServicios = (XLExcel.PivotField)XLPivotTableFFVVServicios.PivotFields("RENTABILIDAD_CON_REBATE");
XLPivotFieldRENT_TOTALFFVVServicios.Orientation = XLExcel.XlPivotFieldOrientation.xlDataField;
XLPivotFieldRENT_TOTALFFVVServicios.Function = XLExcel.XlConsolidationFunction.xlSum;
XLPivotFieldRENT_TOTALFFVVServicios.Name = "Sum RENTABILIDAD_CON_REBATE";
}
else
{
XLExcel.PivotField XLPivotFieldRENT_TOTALFFVVServicios = (XLExcel.PivotField)XLPivotTableFFVVServicios.PivotFields("RENT_TOTAL");
XLPivotFieldRENT_TOTALFFVVServicios.Orientation = XLExcel.XlPivotFieldOrientation.xlDataField;
XLPivotFieldRENT_TOTALFFVVServicios.Function = XLExcel.XlConsolidationFunction.xlSum;
XLPivotFieldRENT_TOTALFFVVServicios.Name = "Sum RENT_TOTAL";
}
XLPivotTableFFVVServicios.SubtotalLocation(XLExcel.XlSubtototalLocationType.xlAtBottom);
XLPivotTableFFVVServicios.DataPivotField.Orientation = XLExcel.XlPivotFieldOrientation.xlRowField;
XLPivotTableFFVVServicios.DataPivotField.Position = 2;
XLPivotTableFFVVServicios.TableStyle2 = "PivotStyleLight18";
XLPivotTableFFVVServicios.RowAxisLayout(XLExcel.XlLayoutRowType.xlCompactRow);
#endregion
xlApplication.Visible = true;
releaseObject(xlWorksheetData);
releaseObject(xlWorksheetPivot);
if (MostrarRebate)
{
releaseObject(xlWorksheetPivotRebate);
}
releaseObject(xlWorksheetComparacion);
releaseObject(xlWorkbook);
releaseObject(xlApplication);
}
catch (Exception ex)
{
releaseObject(xlWorksheetData);
releaseObject(xlWorksheetPivot);
if (MostrarRebate)
{
releaseObject(xlWorksheetPivotRebate);
}
releaseObject(xlWorksheetComparacion);
releaseObject(xlWorkbook);
releaseObject(xlApplication);
throw ex;
}
}
