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