public void CreateCityPivotTable(Excel.Worksheet PivotSheet, string Location, string TableName, ReportType TableType)
{
ListingSheet.Select();
string LastRow = "";
string LastCol = "";
string LastCell = "";
long lRow = 0;
long lCol = 0;
string RankBaseField = "";
////////////
//FIND THE LAST NON-BLANK CELL IN COLUMN A
lRow = ListingSheet.Cells[ListingSheet.Rows.Count, 1].End(Excel.XlDirection.xlUp).Row;
LastRow = "R" + lRow;
lCol = ListingSheet.Cells[1, ListingSheet.Columns.Count].End(Excel.XlDirection.xlToLeft).Column;
LastCol = "C" + lCol;
LastCell = ListingSheet.Cells[lRow, lCol].Address;
Excel.Range PivotData = ListingSheet.Range["A1", LastCell];
PivotData.Select();
Excel.PivotCaches pch = ListingBook.PivotCaches();
Excel.PivotCache pc = pch.Create(Excel.XlPivotTableSourceType.xlDatabase, PivotData);
Excel.PivotTable pvt = pc.CreatePivotTable(PivotSheet.Range[Location], TableName);
//pvt.MergeLabels = true; // The only thing I noticed this doing was centering the heading labels
PivotSheet.Select();
//Excel.PivotField pvf = pvt.PivotFields("Status");
//pvf.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
//pvf.CurrentPage = Status;
//Group 1 S/A
switch (TableType)
{
case ReportType.MonthlyDetachedAllCities:
pvt.PivotFields("City").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
RankBaseField = "City";
break;
case ReportType.MonthlyDetachedAllCommunities:
pvt.PivotFields("S/A").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pvt.PivotFields("S/A").Caption = "Neighborhood";
RankBaseField = "Neighborhood";
break;
default:
break;
}
//pvt.PivotFields("S/A").Name = "Neighborhood";
//Sales Total Amount
pvt.AddDataField(pvt.PivotFields("Sold Price"), "Rank", Excel.XlConsolidationFunction.xlSum);
pvt.PivotFields("Rank").Calculation = Excel.XlPivotFieldCalculation.xlRankDecending;
pvt.PivotFields("Rank").BaseField = RankBaseField;
//Sort By Rank
pvt.PivotFields(RankBaseField).AutoSort(2, "Rank");
//Total Amount
pvt.AddDataField(pvt.PivotFields("Sold Price"), "Total Sales Amount", Excel.XlConsolidationFunction.xlSum);
pvt.PivotFields("Total Sales Amount").NumberFormat = "$#,##0";
pvt.AddDataField(pvt.PivotFields("Sold Price"), "Market Share", Excel.XlConsolidationFunction.xlSum);
pvt.PivotFields("Market Share").Calculation = Excel.XlPivotFieldCalculation.xlPercentOfTotal;
//Sales Count
pvt.AddDataField(pvt.PivotFields("Status"), "Sales", Excel.XlConsolidationFunction.xlCount);
pvt.PivotFields("Sales").NumberFormat = "0";
//Ave Sold Price
pvt.AddDataField(pvt.PivotFields("Sold Price"), "Avg. Sold Price", Excel.XlConsolidationFunction.xlAverage);
pvt.PivotFields("Avg. Sold Price").NumberFormat = "$#,##0";
pvt.AddDataField(pvt.PivotFields("Sold Price"), "Avg. S.Price Rank", Excel.XlConsolidationFunction.xlAverage);
pvt.PivotFields("Avg. S.Price Rank").Calculation = Excel.XlPivotFieldCalculation.xlRankDecending;
pvt.PivotFields("Avg. S.Price Rank").BaseField = RankBaseField;
//Price Per SqFt
pvt.AddDataField(pvt.PivotFields("SP Sqft"), "Avg. $PerSQFT", Excel.XlConsolidationFunction.xlAverage);
pvt.PivotFields("Avg. $PerSQFT").NumberFormat = "$#,##0";
pvt.AddDataField(pvt.PivotFields("SP Sqft"), "Avg. $PSF Rank", Excel.XlConsolidationFunction.xlAverage);
pvt.PivotFields("Avg. $PSF Rank").Calculation = Excel.XlPivotFieldCalculation.xlRankDecending;
pvt.PivotFields("Avg. $PSF Rank").BaseField = RankBaseField;
//Days On Market
pvt.AddDataField(pvt.PivotFields("CDOM"), "Avg. Days OnMkt", Excel.XlConsolidationFunction.xlAverage);
pvt.PivotFields("Avg. Days OnMkt").NumberFormat = "0";
pvt.RowAxisLayout(Excel.XlLayoutRowType.xlTabularRow);
}
public void Create()
{
Excel.Worksheet PivotSheet = this.PivotSheet;
string Location = this.PivotTableLocation;
string TableName = this.PivotTableName;
char Status = this.Status;
ListingSheet.Select();
string LastRow = "";
string LastCol = "";
string LastCell = "";
long lRow = 0;
long lCol = 0;
////////////
//FIND THE LAST NON-BLANK CELL IN COLUMN A
lRow = ListingSheet.Cells[ListingSheet.Rows.Count, 1].End(Excel.XlDirection.xlUp).Row;
LastRow = "R" + lRow;
lCol = ListingSheet.Cells[1, ListingSheet.Columns.Count].End(Excel.XlDirection.xlToLeft).Column;
LastCol = "C" + lCol;
LastCell = ListingSheet.Cells[lRow, lCol].Address;
Excel.Range PivotData = ListingSheet.Range["A1", LastCell];
PivotData.Select();
Excel.PivotCaches pch = ListingBook.PivotCaches();
Excel.PivotCache pc = pch.Create(Excel.XlPivotTableSourceType.xlDatabase, PivotData);
Excel.PivotTable pvt = pc.CreatePivotTable(PivotSheet.Range[Location], TableName);
PivotSheet.Select();
Excel.PivotField pvf = pvt.PivotFields("Status");
pvf.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
switch ((ListingStatus)Status)
{
case ListingStatus.Active:
case ListingStatus.Sold:
pvf.CurrentPage = Status.ToString();
break;
case ListingStatus.OffMarket:
try { pvf.PivotItems(((char)ListingStatus.Active).ToString()).Visible = false; } catch (Exception e) { };
try { pvf.PivotItems(((char)ListingStatus.Sold).ToString()).Visible = false; } catch (Exception e) { };
try { pvf.PivotItems(((char)ListingStatus.Terminate).ToString()).Visible = true; } catch (Exception e) { };
try { pvf.PivotItems(((char)ListingStatus.Cancel).ToString()).Visible = true; } catch (Exception e) { };
try { pvf.PivotItems(((char)ListingStatus.Expire).ToString()).Visible = true; } catch (Exception e) { };
pvf.EnableMultiplePageItems = true;
break;
}
//Group 1 S/A
pvt.PivotFields("S/A").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pvt.PivotFields("S/A").Name = "Neighborhood";
//Group 2 Complex
pvt.PivotFields("Complex/Subdivision Name").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pvt.PivotFields("Complex/Subdivision Name").Name = this.ReportType.ToString().IndexOf("Detached") < 0 ? "Complex" : "SubDivision";
//Group 3 Address
pvt.PivotFields("Address2").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pvt.PivotFields("Address2").Name = "Civic Address";
//Group 4 UnitNo
if (this.bShowUnitNo || this.ReportType.ToString().IndexOf("Detached") < 0)
{
pvt.PivotFields("Unit#").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pvt.PivotFields("Unit#").Name = "Unit No";
}
pvt.AddDataField(pvt.PivotFields("MLS"), "Count", Excel.XlConsolidationFunction.xlCount);
pvt.AddDataField(pvt.PivotFields("Price0"), "Price", Excel.XlConsolidationFunction.xlAverage);
pvt.AddDataField(pvt.PivotFields("CDOM"), "Days On Mkt", Excel.XlConsolidationFunction.xlAverage);
pvt.AddDataField(pvt.PivotFields("TotFlArea"), "Floor Area", Excel.XlConsolidationFunction.xlAverage);
pvt.AddDataField(pvt.PivotFields("PrcSqft"), "$PSF", Excel.XlConsolidationFunction.xlAverage);
//TEST Add Calculated Fields
//Excel.PivotField ptField;
//Excel.CalculatedFields cfField = pvt.CalculatedFields();
//ptField = cfField.Add("New PSF", "='PrcSqft' * 'Age'", true);
//pvt.AddDataField(ptField, " New PSF", Excel.XlConsolidationFunction.xlAverage);
//
pvt.AddDataField(pvt.PivotFields("Age"), "Building Age", Excel.XlConsolidationFunction.xlAverage);
if (this.ReportType.ToString().IndexOf("Detached") < 0)
{
pvt.AddDataField(pvt.PivotFields("StratMtFee"), "Monthly Fee", Excel.XlConsolidationFunction.xlAverage);
}
else
{
pvt.AddDataField(pvt.PivotFields("Lot Sz (Sq.Ft.)"), "Land Size", Excel.XlConsolidationFunction.xlAverage);
pvt.AddDataField(pvt.PivotFields("LandValue"), "Land Assess.", Excel.XlConsolidationFunction.xlAverage);
}
pvt.AddDataField(pvt.PivotFields("BCAValue"), "BC Assess.", Excel.XlConsolidationFunction.xlAverage);
pvt.AddDataField(pvt.PivotFields("Change%"), "Chg% to BCA", Excel.XlConsolidationFunction.xlAverage);
pvt.AddDataField(pvt.PivotFields("Lot$ PerSF"), "Lot$PSF", Excel.XlConsolidationFunction.xlAverage);
pvt.AddDataField(pvt.PivotFields("Improve$ PerSF"), "Improve$PSF", Excel.XlConsolidationFunction.xlAverage);
pvt.PivotFields("Price").NumberFormat = "$#,##0";
pvt.PivotFields("Days On Mkt").NumberFormat = "0";
pvt.PivotFields("Floor Area").NumberFormat = "0";
pvt.PivotFields("$PSF").NumberFormat = "$#,##0";
pvt.PivotFields("Building Age").NumberFormat = "0";
if (this.ReportType.ToString().IndexOf("Detached") < 0)
{
pvt.PivotFields("Monthly Fee").NumberFormat = "$#,##0";
}
else
{
pvt.PivotFields("Land Size").NumberFormat = "0";
pvt.PivotFields("Land Assess.").NumberFormat = "$#,##0";
}
pvt.PivotFields("BC Assess.").NumberFormat = "$#,##0";
pvt.PivotFields("Chg% to BCA").NumberFormat = "0%";
pvt.PivotFields("Lot$PSF").NumberFormat = "$#,##0";
pvt.PivotFields("Improve$PSF").NumberFormat = "$#,##0";
pvt.RowAxisLayout(Excel.XlLayoutRowType.xlTabularRow);
}
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;
}
}
