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