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; } }
private void button1_Click(object sender, EventArgs e) { //申明保存对话框 SaveFileDialog dlg = new SaveFileDialog(); //默然文件后缀 dlg.DefaultExt = "xlsx "; //文件后缀列表 dlg.Filter = "EXCEL文件(*.XLSX)|*.xlsx "; //默然路径是系统当前路径 dlg.InitialDirectory = System.IO.Directory.GetCurrentDirectory(); //打开保存对话框 if (dlg.ShowDialog() == DialogResult.Cancel) { return; } //返回文件路径 string fileNameString = dlg.FileName; //验证strFileName是否为空或值无效 if (fileNameString.Trim() == " ") { return; } Excel.Application objExcel = new Excel.Application();//创建一个excel的实例 Excel.Workbook objWorkbook = null; Excel.Worksheet objsheet = null; //Excel.Sheets objsheets = objExcel.Workbooks.Item[1].Worksheets; //objsheets.Item[1] = ""; //Excel.Worksheet ws = (Excel.Worksheet)objExcel.Worksheets.get_Item(1); //ws.Name = "狐狸!"; // Excel.Sheets objsheets = objExcel.Sheets; //var ss = objsheets.Item[1]; // var aa = (Excel.Worksheet)ss; // aa.Name = "dasdasdasd"; //objsheets["Sheet1"].Nasdasda = "11"; //objsheet = (Excel.Worksheet)objsheets.get_Item(objsheets.Count); //worksheet.Name = "sadas "; ////objsheets.get_Item(1 //objWorkbooks = objExcel.Workbooks; ////oBooks.Open(sTemplate, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); //objWorkbook = objWorkbooks.get_Item(1); //objsheets = objWorkbook.Worksheets; //objsheet = (Excel.Worksheet)objsheets.get_Item(1); ////命名该sheet //objsheet.Name = "Sheet1"; try { //申明对象 objExcel = new Excel.Application(); objWorkbook = objExcel.Workbooks.Add(Missing.Value); objsheet = (Excel.Worksheet)objWorkbook.ActiveSheet; //合格率 objExcel.Cells[1, 1] = "客户"; objExcel.Cells[1, 2] = "数量"; objExcel.Cells[1, 3] = "占比"; objExcel.Cells[2, 1] = "不合格"; objExcel.Cells[3, 1] = "合格"; objExcel.Cells[2, 2] = tyDataPie[0]; objExcel.Cells[3, 2] = tyDataPie[1]; objExcel.Cells[2, 3] = @"=B2 / B4"; objExcel.Cells[3, 3] = @"=B3 / B4"; //求和 Excel.Range rangesummary73 = objsheet.Range["B4"];//--ActiveCell = rangesummary110 rangesummary73.Formula = "=SUM(R[-2]C:R[-1]C)"; rangesummary73.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"; // Range("A2:A3,C2:C3").Select //Range("C2").Activate //ActiveSheet.Shapes.AddChart2(251, xlPie).Select //ActiveChart.SetSourceData Source:= Range("Sheet1!$A$2:$A$3,Sheet1!$C$2:$C$3") //ActiveChart.SetElement(msoElementDataLabelBestFit) // Excel.Range rangesummary5 = objsheet.Range["A2:A3", "C2:C3"]; //设置百分比格式 Excel.Range rangesummary4 = objsheet.Range["C2", "C3"]; //Range("C2:C3").Select; rangesummary4.NumberFormatLocal = "0.00%"; //饼图 //新建一个饼图 Excel.Chart xlChart = (Excel.Chart)objWorkbook.Charts.Add(Type.Missing, objsheet, Type.Missing, Type.Missing); xlChart.ChartType = Excel.XlChartType.xlPie; //设置图形 xlChart.SetSourceData(objsheet.get_Range("A1:A3, C1:C3"), Excel.XlRowCol.xlColumns); //两种方法都可以 // xlChart.SetSourceData(objsheet.Range["A1:A3", "C1:C3"], Excel.XlRowCol.xlColumns); //加border和居中设置 Excel.Range rangesummary110 = objsheet.Range["A1", "C3"]; rangesummary110.Borders.Color = 0; rangesummary110.Borders.Weight = 2; rangesummary110.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //设置属性标签 xlChart.SetElement(MsoChartElementType.msoElementDataLabelOutSideEnd); //数据标签 xlChart.SetElement(MsoChartElementType.msoElementLegendBottom); //设为底部显示 xlChart.SetElement(MsoChartElementType.msoElementChartTitleAboveChart); //设置标题 objsheet.Range["F:F"].ColumnWidth = 20.5; //设置宽度 xlChart.ChartTitle.Text = "客户占比(2020.3.23-3.28)"; //objWorkbook.ActiveChart.Location(Excel.XlChartLocation.xlLocationAutomatic, "合格率");//xlLocationAutomatic :Excel 控制图表位置。 objWorkbook.ActiveChart.Location(Excel.XlChartLocation.xlLocationAsObject, objsheet.Name); // xlLocationAsObject,将图表嵌入到现有工作表中。 // oResizeRange = (Excel.Range)objsheet.Rows.get_Item(7, Missing.Value); objsheet.Shapes.Item("Chart 1").Top = 150; //调图表的位置上边距 objsheet.Shapes.Item("Chart 1").Left = 10; objsheet.Shapes.Item("Chart 1").Width = 200; //调图表的宽度 objsheet.Shapes.Item("Chart 1").Height = 250; //调图表的高度 /////////////////////////////////////////////////////////////// #region 管理人员 int col = 6; objExcel.Cells[2, col] = "日期"; objExcel.Cells[2, col + 1] = "产品名称"; objExcel.Cells[2, col + 2] = "数量"; int row = 3; for (int i = 0; i < txDataColumn.Count; i++) { objExcel.Cells[row, col] = txDataColumn[i]; row++; } row = 3; for (int i = 0; i < tyDataOk.Count; i++) { objExcel.Cells[row, col + 1] = tyDataOk[i]; row++; } row = 3; for (int i = 0; i < tyDataNo.Count; i++) { objExcel.Cells[row, col + 2] = tyDataNo[i]; row++; } #endregion //假定要合并excel文件中第2行的1~3列,并且显示黑色边框7a686964616fe4b893e5b19e31333264656666 //objExcel.ActiveSheet.Columns[7] = 5; Microsoft.Office.Interop.Excel.Range rangesummary1 = objsheet.Range["F3", "F5"]; Microsoft.Office.Interop.Excel.Range rangesummary2 = objsheet.Range["F6", "F8"]; Microsoft.Office.Interop.Excel.Range rangesummary3 = objsheet.Range["F9", "F12"]; Excel.Range rangesummary120 = objsheet.Range["F2", "H12"]; rangesummary120.Borders.Color = 0; rangesummary120.Borders.Weight = 2; rangesummary120.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; rangesummary120.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; rangesummary1.Value2 = Type.Missing; rangesummary1.Merge(Type.Missing); rangesummary1.Value2 = "2020-03-23"; rangesummary2.Value2 = Type.Missing; rangesummary2.Merge(Type.Missing); rangesummary2.Value2 = "2020-03-24"; rangesummary3.Value2 = Type.Missing; rangesummary3.Merge(Type.Missing); rangesummary3.Value2 = "2020-03-25"; //柱状图 Excel.Chart xlChart2 = (Excel.Chart)objWorkbook.Charts.Add(Type.Missing, objsheet, Type.Missing, Type.Missing); Excel.Range cellRange = objsheet.get_Range((Excel.Range)objsheet.Cells[2, 6], (Excel.Range)objsheet.Cells[3 + txDataColumn.Count - 1, 8]); //1-cellRange:数据源的范围,2-图表类型,3-Type.Missing,4-在图表上将列或行用作数据系列的方式,5、6-第五个第六个参数设置图表的x轴和y轴分别是数据源的哪些列/行,7-图表是否有图例,8、9、10-设置标题 xlChart2.ChartWizard(cellRange, Excel.XlChartType.xlColumnClustered, //2-图表类型 Type.Missing, //内置自动套用格式的选项编号。 可为从 1 到 10 的数字,其取值依赖于库的类型。 如果省略此参数, 则 Excel 根据库的类型和数据源选择默认值。 Excel.XlRowCol.xlColumns, //在图表上将列或行用作数据系列的方式 2, //第五个第六个参数设置图表的x轴和y轴分别是数据源的哪些列/行--这个2代表数据源的x轴由两个参数确认;可以不写,默认的就很难看 1, //--这个2代表数据源的x轴由1个参数确认;可以不写,默认的就很难看 true, //图表是否有图例 "每日总量统计", //以下都是标题 Type.Missing, Type.Missing, ""); xlChart2.SetElement(MsoChartElementType.msoElementDataLabelOutSideEnd); xlChart2.Location(Excel.XlChartLocation.xlLocationAsObject, objsheet.Name); Excel.Range oResizeRange1 = (Excel.Range)objsheet.Rows.get_Item(1); Excel.Range oResizeRange2 = (Excel.Range)objsheet.Columns.get_Item(10); objsheet.Shapes.Item("Chart 2").Top = (float)oResizeRange1.Top; //调图表的位置上边距--1行的高度 objsheet.Shapes.Item("Chart 2").Left = (float)(double)oResizeRange2.Left; //调图表的位置左边距--10列的宽度 objsheet.Shapes.Item("Chart 2").Width = 300; //调图表的宽度 objsheet.Shapes.Item("Chart 2").Height = 200; //调图表的高度 //保存文件 objWorkbook.SaveAs(fileNameString, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); } catch (Exception error) { MessageBox.Show(error.Message, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } finally { ////关闭Excel应用 //if (objWorkbook != null) objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value); //if (objExcel.Workbooks != null) objExcel.Workbooks.Close(); //if (objExcel != null) objExcel.Quit(); //objsheet = null; //objWorkbook = null; //objExcel = null; objsheet.Name = "牙模盒使用记录"; objsheet.Tab.Color = Excel.XlThemeColor.xlThemeColorLight1; ClosePro(fileNameString, objExcel, objWorkbook); System.Diagnostics.Process.Start(fileNameString); } }