void SetupAxes(Excel._Chart oChart, string xLabel, float minX, float maxX, string yLabel, float minY, float maxY)
{
Excel.Axis axis = (Excel.Axis)oChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
if (maxX != -1.0f)
{
axis.MaximumScale = maxX;
}
if (minX != -1.0f)
{
axis.MinimumScale = minX;
}
axis.HasTitle = true;
axis.AxisTitle.Text = xLabel;
axis = (Excel.Axis)oChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
if (maxY != -1.0f)
{
axis.MaximumScale = maxY;
}
if (minY != -1.0f)
{
axis.MinimumScale = minY;
}
axis.HasMajorGridlines = false;
axis.HasMinorGridlines = false;
axis.HasTitle = true;
axis.AxisTitle.Text = yLabel;
}
protected override void GenerateDataGraph()
{
string yAxisName = string.Format("{0}", unit);
string xAxisName = "Data Points";
ChartObjects chartObjects = this.dataGraph.ChartObjects(Type.Missing);
ChartObject chartObject = chartObjects.Add(0, 0, 850, 500);
Chart chart = chartObject.Chart;
Range dataRange = null;
string dataStartColumnIndex = "B";
if (_officeVersion > 11.0f)
{
yAxisName = string.Format("Temperature({0})", unit);
xAxisName = "Date Time";
dataStartColumnIndex = "A";
int xAxisPointCount = 10;
int tickSpacing = this.device.tempList.Count / xAxisPointCount;
if (tickSpacing == 0)
{
tickSpacing = 1;
}
if (tickSpacing == 1 && this.device.tempList.Count != xAxisPointCount)
{
tickSpacing = 2;
}
try
{
chart.PlotArea.Width = chart.PlotArea.Width - 10;
chart.PlotArea.Height = chart.PlotArea.Height - 30;
chart.PlotArea.Top = 30;
Microsoft.Office.Interop.Excel.Axis xAxis = (Microsoft.Office.Interop.Excel.Axis)chart.Axes(Microsoft.Office.Interop.Excel.XlAxisType.xlCategory, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary);
xAxis.TickMarkSpacing = tickSpacing;
xAxis.TickLabelSpacing = tickSpacing;
xAxis.TickLabelPosition = Microsoft.Office.Interop.Excel.XlTickLabelPosition.xlTickLabelPositionLow;
xAxis.MajorTickMark = Microsoft.Office.Interop.Excel.XlTickMark.xlTickMarkNone;
}
catch (Exception)
{
}
}
if (_alarmLimits.Count > 0)
{
dataRange = this.dataList.get_Range(dataStartColumnIndex + "3", string.Format("{0}{1}", ConvertAlarmLimitsCountToColumnIndex(_alarmLimits.Count), this.device.tempList.Count + 2));
}
else
{
dataRange = this.dataList.get_Range(dataStartColumnIndex + "3", string.Format("B{0}", this.device.tempList.Count + 2));
}
chart.ChartWizard(dataRange, Microsoft.Office.Interop.Excel.XlChartType.xlLine, Type.Missing, Type.Missing, Type.Missing, Type.Missing, false, "Data Graph", xAxisName, yAxisName, Type.Missing);
for (int i = 0; i < _alarmLimits.Count + 1; i++)
{
Series series = (Series)chart.SeriesCollection(i + 1);
series.MarkerStyle = Microsoft.Office.Interop.Excel.XlMarkerStyle.xlMarkerStyleNone;
series.Smooth = true;
if (i > 0)
{
series.Border.Color = Color.Red;
}
}
}
private void DrawGraphFromSelectedCells(int graphRow, int graphColumn, string title, string valueName, Worksheet worksheet)
{
bool screenUpdating = Application.ScreenUpdating;
Application.ScreenUpdating = false;
Excel.Application exApp = Globals.ThisAddIn.Application;
Excel.Range selectedRange = exApp.Selection;
long row = selectedRange.Row;
long col = selectedRange.Column;
Worksheet activeWorksheet = Globals.Factory.GetVstoObject(Application.ActiveWorkbook.ActiveSheet);
string name = GetGraphName(activeWorksheet);
Chart chart = activeWorksheet.Controls.AddChart(activeWorksheet.Range["A1"].Resize[_GRAPH_HEIGHT, _GRAPH_WIDTH]
.Offset[graphRow * _GRAPH_HEIGHT, graphColumn * _GRAPH_WIDTH], name);
chart.ChartType = Excel.XlChartType.xlXYScatterLinesNoMarkers;
chart.SetSourceData(selectedRange, Excel.XlRowCol.xlColumns);
chart.HasTitle = true;
chart.ChartTitle.Text = title;
Excel.Axis xAxis = (Excel.Axis)chart.Axes(Excel.XlAxisType.xlCategory);
xAxis.HasTitle = true;
xAxis.AxisTitle.Text = activeWorksheet.Cells[row, col].Text;
Excel.Axis yAxis = (Excel.Axis)chart.Axes(Excel.XlAxisType.xlValue);
yAxis.HasTitle = true;
yAxis.AxisTitle.Text = valueName;
xAxis.MinimumScaleIsAuto = false;
Excel.Name newName = activeWorksheet.Names.Add("firstCol", selectedRange.Resize[selectedRange.Rows.Count, 1], false);
xAxis.MinimumScale = (double)activeWorksheet.Evaluate("MIN(firstCol)");
xAxis.MaximumScale = (double)activeWorksheet.Evaluate("MAX(firstCol)");
newName.Delete();
newName = activeWorksheet.Names.Add("col", selectedRange.Resize[selectedRange.Rows.Count, 1].Offset[missing, 1], false);
double maxValue = (double)activeWorksheet.Evaluate("MAX(col)");
double minValue = (double)activeWorksheet.Evaluate("MIN(col)");
newName.Delete();
for (int i = 2; i < selectedRange.Columns.Count; ++i)
{
newName = activeWorksheet.Names.Add("col", selectedRange.Resize[selectedRange.Rows.Count, 1].Offset[missing, i], false);
double maxValueNow = (double)activeWorksheet.Evaluate("MAX(col)");
double minValueNow = (double)activeWorksheet.Evaluate("MIN(col)");
newName.Delete();
if (maxValueNow > maxValue)
{
maxValue = maxValueNow;
}
if (minValueNow < minValue)
{
minValue = minValueNow;
}
}
yAxis.MinimumScaleIsAuto = false;
yAxis.MinimumScale = minValue;
yAxis.MaximumScale = maxValue;
chart.Location(Excel.XlChartLocation.xlLocationAsObject, worksheet.Name);
activeWorksheet.Activate();
Application.ScreenUpdating = screenUpdating;
}
private void DrawFractionChart(Excel.Worksheet activeSheet, Excel.ChartObjects xlCharts, Excel.Range xRange, Excel.Range yRange)
{
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(200, 500, 200, 100);
Excel.Chart chartPage = myChart.Chart;
Excel.SeriesCollection seriesCollection = chartPage.SeriesCollection();
Excel.Series series1 = seriesCollection.NewSeries();
series1.XValues = activeSheet.Range["E1", "E3"];
series1.Values = activeSheet.Range["F1", "F3"];
chartPage.ChartType = Excel.XlChartType.xlDoughnut;
Excel.Axis axis = chartPage.Axes(Excel.XlAxisType.xlValue, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary) as Excel.Axis;
series1.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowPercent, true, true, false, true, true, true, true);
}
/// <summary>
/// 绘制开挖工况的位置线
/// </summary>
/// <param name="Cht"></param>
/// <param name="WorkingStage"></param>
/// <remarks></remarks>
private void DrawWorkingStage(Excel.Chart Cht, List <clsData_WorkingStage> WorkingStage)
{
Excel.Axis AX = Cht.Axes(Excel.XlAxisType.xlCategory) as Excel.Axis;
string[] arrLineName = new string[WorkingStage.Count - 1 + 1];
string[] arrTextName = new string[WorkingStage.Count - 1 + 1];
Excel.Chart with_1 = Cht;
try
{
int i = 0;
foreach (clsData_WorkingStage WS in WorkingStage)
{
// -------------------------------------------------------------------------------------------
Excel.Shape shpLine = with_1.Shapes.AddLine(BeginX: 0, BeginY: Cht.PlotArea.InsideTop, EndX:
0, EndY:
75);
shpLine.Line.Weight = (float)(1.2F);
//.EndArrowheadStyle = Microsoft.Office.Core.MsoArrowheadStyle.msoArrowheadStealth
//.EndArrowheadLength = Microsoft.Office.Core.MsoArrowheadLength.msoArrowheadLengthMedium
//.EndArrowheadWidth = Microsoft.Office.Core.MsoArrowheadWidth.msoArrowheadWidthMedium
shpLine.Line.DashStyle = Microsoft.Office.Core.MsoLineDashStyle.msoLineDashDot;
shpLine.Line.ForeColor.RGB = Information.RGB(255, 0, 0);
//
ExcelFunction.setPositionInChart(shpLine, AX, WS.ConstructionDate.ToOADate());
// -------------------------------------------------------------------------------------------
float TextWidth = 25;
float textHeight = 10;
Excel.Shape shpText = default(Excel.Shape);
shpText = Cht.Shapes.AddTextbox(Orientation: Microsoft.Office.Core.MsoTextOrientation.msoTextOrientationHorizontal, Left: shpLine.Left - TextWidth / 2, Top: shpLine.Top - textHeight, Height: ref textHeight, Width: ref TextWidth);
ExcelFunction.FormatTextbox_Tag(TextFrame: shpText.TextFrame2, Text: WS.Description, HorizontalAlignment: Microsoft.Office.Core.MsoParagraphAlignment.msoAlignCenter);
// -------------------------------------------------------------------------------------------
arrLineName[i] = shpLine.Name;
arrTextName[i] = shpText.Name;
i++;
}
Excel.Shape shp1 = Cht.Shapes.Range[arrLineName].Group();
Excel.Shape shp2 = Cht.Shapes.Range[arrTextName].Group();
Cht.Shapes.Range[new[] { shp1.Name, shp2.Name }].Group();
}
catch (Exception ex)
{
MessageBox.Show("设置开挖工况位置出现异常。" + "\r\n" + ex.Message + "\r\n" + "报错位置：" + ex.TargetSite.Name, "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
private void ConfigureExcelChartFormat(string sheetName, string seriesName, Excel._Chart oChart)
{
//Serie format
Excel.Series oSeries = (Excel.Series)oChart.SeriesCollection(1);
oSeries.Name = seriesName;
oSeries.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlLine;
oSeries.Smooth = true;
oSeries.Border.ColorIndex = 3;
oSeries.Border.Weight = 3;
//Plot area format
oChart.PlotArea.Interior.ColorIndex = 0;
oChart.PlotArea.Border.ColorIndex = 5;
//X axis format
Excel.Axis xAxis = (Excel.Axis)oChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
xAxis.MajorGridlines.Border.ColorIndex = 5;
xAxis.Border.ColorIndex = 5;
xAxis.TickLabels.Font.Name = "Verdana";
xAxis.TickLabels.Font.Size = 8.0f;
xAxis.TickLabels.AutoScaleFont = false;
//Y axis format
Excel.Axis yAxis = (Excel.Axis)oChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
yAxis.TickLabels.Font.Name = "Verdana";
yAxis.TickLabels.Font.Size = 8.0f;
yAxis.TickLabels.AutoScaleFont = false;
yAxis.Border.ColorIndex = 5;
yAxis.MajorTickMark = Microsoft.Office.Interop.Excel.XlTickMark.xlTickMarkNone;
//Legend format
oChart.Legend.Font.Name = "Verdana";
oChart.Legend.Font.Size = 9.0f;
oChart.Legend.Font.Bold = true;
oChart.Legend.AutoScaleFont = false;
oChart.Legend.Position = Microsoft.Office.Interop.Excel.XlLegendPosition.xlLegendPositionTop;
//Chart format
oChart.HasTitle = false;
oChart.Location(Excel.XlChartLocation.xlLocationAsObject, sheetName);
}
public string FastMoveItems(bool FastOrSlowReport, int NumberOfItems, string DateFrom, string DateTo, bool ExportToExcel = false, string ExportPath = "")
{
try
{
Excel.Range chartRange;
xlApp = new Excel.Application();
xlApp.DisplayAlerts = false;
xlApp.Visible = false;
xlApp.SheetsInNewWorkbook = 1;
xlWorkBook = xlApp.Workbooks.Add(misValue);//misValue
string ReportName = (FastOrSlowReport ? "Fast Move Items Report" : "Slow Move Items Report");
DataTable aTable = ReportsMgmt.FastMovItemBasedOnQty(NumberOfItems, DateFrom, DateTo, FastOrSlowReport);
if (aTable.Rows.Count > 0)
{
int RowCnt = 1;
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.Name = ReportName;
List <string> aHeader = ReportsHelper.ImportReportHeader(0, 1);
List <string> aFooter = ReportsHelper.ImportReportHeader(1, 1);
for (int i = 0; i < aHeader.Count; i++)
{
string astringss = aHeader[i];
xlWorkSheet.Cells[RowCnt, 2] = aHeader[i];
RowCnt++;
}
xlWorkSheet.Cells[RowCnt++, 1] = ReportName;
xlWorkSheet.Cells[RowCnt, 2] = "Date From:\t" + DateFrom;
xlWorkSheet.Cells[RowCnt, 3] = "Date To:\t" + DateTo;
RowCnt++;
xlWorkSheet.Cells[RowCnt, 1] = "Item Description";
xlWorkSheet.Cells[RowCnt, 2] = "Qty Sold";
xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).Font.Bold = true;
//xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).WrapText = true;
xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
RowCnt++;
int DataStart = RowCnt;
foreach (DataRow aRow in aTable.Rows)
{
xlWorkSheet.Cells[RowCnt, 1] = aRow["ItemDescription"].ToString();
xlWorkSheet.Cells[RowCnt, 2] = aRow["Summation"].ToString();
RowCnt++;
}
xlWorkSheet.get_Range("A" + DataStart.ToString(), "K" + RowCnt.ToString()).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
xlWorkSheet.get_Range("A" + DataStart.ToString(), "K" + RowCnt.ToString()).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
oRng = xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString());
oRng.EntireColumn.AutoFit();
Excel.ChartObjects myCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
xlWorkSheet.DisplayRightToLeft = false;
int size = aTable.Rows.Count * 100;
if (size >= 600)
{
size = 600;
}
if (size < 300)
{
size = 300;
}
Excel.ChartObject myChart = (Excel.ChartObject)myCharts.Add(0, RowCnt * 15, size, 300);
Excel.Chart chartPage = myChart.Chart;
Excel.SeriesCollection seriesCollection = chartPage.SeriesCollection();
Excel.Series series1 = seriesCollection.NewSeries();
RowCnt--; //because we started from 1 suppose to be 0
series1.Name = ReportName;
series1.XValues = xlWorkSheet.Range["A" + DataStart.ToString(), "A" + RowCnt.ToString()];
series1.Values = xlWorkSheet.Range["B" + DataStart.ToString(), "B" + RowCnt.ToString()];
chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
Excel.Axis axis = chartPage.Axes(Excel.XlAxisType.xlValue, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary) as Excel.Axis;
series1.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowBubbleSizes);
object format = Microsoft.Office.Interop.Excel.XlFileFormat.xlHtml;
xlWorkSheet.SaveAs(ReportsHelper.TempOutputPath, format);
if (ExportToExcel)
{
format = Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7;
xlWorkSheet.SaveAs(ExportPath, format);
}
xlApp.UserControl = false;
return("TRUE");
}
else
{
return("EMPTY");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
return("ERROR");
}
finally
{
//Once done close and quit Excel
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
}
//Revenues Comparison
public string RevenuesComparison(int ByDDMMYYYY, string DateFrom, string DateTo, bool ExportToExcel = false, string ExportPath = "")
{
try
{
string ReportName = "Revenues Comparison Report";
DataTable aTable = ReportsMgmt.RevenuesComparions(ByDDMMYYYY, DateFrom, DateTo);
if (aTable.Rows.Count > 0)
{
xlApp = new Excel.Application();
xlApp.DisplayAlerts = false;
xlApp.Visible = false;
xlApp.SheetsInNewWorkbook = 1;
xlWorkBook = xlApp.Workbooks.Add(misValue);//misValue
int RowCnt = 1;
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.Name = ReportName;
List <string> aHeader = ReportsHelper.ImportReportHeader(0, 1);
List <string> aFooter = ReportsHelper.ImportReportHeader(1, 1);
for (int i = 0; i < aHeader.Count; i++)
{
xlWorkSheet.Cells[RowCnt, 2] = aHeader[i];
RowCnt++;
}
xlWorkSheet.Cells[RowCnt++, 1] = ReportName;
xlWorkSheet.Cells[RowCnt, 2] = "Date From:\t" + DateFrom;
xlWorkSheet.Cells[RowCnt, 3] = "Date To:\t" + DateTo;
RowCnt++;
xlWorkSheet.Cells[RowCnt, 1] = "Date";
xlWorkSheet.Cells[RowCnt, 2] = "Total Cost JOD";
xlWorkSheet.Cells[RowCnt, 3] = "Total Sales JOD";
xlWorkSheet.Cells[RowCnt, 4] = "Gross Profit JOD";
xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).Font.Bold = true;
//xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).WrapText = true;
xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
RowCnt++;
int DataStart = RowCnt;
foreach (DataRow aRow in aTable.Rows)
{
if (ByDDMMYYYY == 0) //BY DAY
{
xlWorkSheet.Cells[RowCnt, 1] = aRow["Day"].ToString() + '\\' + aRow["Month"].ToString() + "\\" + aRow["Year"].ToString();
}
else if (ByDDMMYYYY == 1)//BY MONTH
{
xlWorkSheet.Cells[RowCnt, 1] = aRow["Month"].ToString() + '\\' + aRow["Year"].ToString();
}
else //BY YEAR
{
xlWorkSheet.Cells[RowCnt, 1] = aRow["Year"].ToString();
}
xlWorkSheet.Cells[RowCnt, 2] = Math.Round(double.Parse(aRow["TotalCost"].ToString()), 2);
xlWorkSheet.Cells[RowCnt, 3] = Math.Round(double.Parse(aRow["TotalPrice"].ToString()), 2);
xlWorkSheet.Cells[RowCnt, 4] = Math.Round(double.Parse(aRow["TotalProfit"].ToString()), 2);
RowCnt++;
}
xlWorkSheet.get_Range("A" + DataStart.ToString(), "K" + RowCnt.ToString()).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
xlWorkSheet.get_Range("A" + DataStart.ToString(), "K" + RowCnt.ToString()).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
oRng = xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString());
oRng.EntireColumn.AutoFit();
Excel.ChartObjects myCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
xlWorkSheet.DisplayRightToLeft = false;
int size = aTable.Rows.Count * 100;
if (size > 600)
{
size = 600;
}
if (size < 300)
{
size = 300;
}
Excel.ChartObject myChart = (Excel.ChartObject)myCharts.Add(0, RowCnt * 15, size, 300);
Excel.Chart chartPage = myChart.Chart;
Excel.SeriesCollection seriesCollection = chartPage.SeriesCollection();
Excel.Series series1 = seriesCollection.NewSeries();
Excel.Series series2 = seriesCollection.NewSeries();
Excel.Series series3 = seriesCollection.NewSeries();
RowCnt--; //because we started from 1 suppose to be 0
series1.Name = "Total Cost";
series1.XValues = xlWorkSheet.Range["A" + DataStart.ToString(), "A" + RowCnt.ToString()];
series1.Values = xlWorkSheet.Range["B" + DataStart.ToString(), "B" + RowCnt.ToString()];
series2.Name = "Total Sales";
series2.XValues = xlWorkSheet.Range["A" + DataStart.ToString(), "A" + RowCnt.ToString()];
series2.Values = xlWorkSheet.Range["C" + DataStart.ToString(), "C" + RowCnt.ToString()];
series3.Name = "Gross Profit";
series3.XValues = xlWorkSheet.Range["A" + DataStart.ToString(), "A" + RowCnt.ToString()];
series3.Values = xlWorkSheet.Range["D" + DataStart.ToString(), "D" + RowCnt.ToString()];
chartPage.ChartType = Excel.XlChartType.xlLineMarkers;
Excel.Axis axis = chartPage.Axes(Excel.XlAxisType.xlValue, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary) as Excel.Axis;
series1.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowBubbleSizes);
series2.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowBubbleSizes);
series3.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowBubbleSizes);
object format = Microsoft.Office.Interop.Excel.XlFileFormat.xlHtml;
xlWorkSheet.SaveAs(ReportsHelper.TempOutputPath, format);
if (ExportToExcel)
{
format = Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7;
xlWorkSheet.SaveAs(ExportPath, format);
}
xlApp.UserControl = false;
//Once done close and quit Excel
return("TRUE");
}
else
{
return("EMPTY");
}
}
catch (Exception ex)
{
MessageBox.Show("{EXCEPTION in Revenues Comparison}" + ex.Message + ex.ToString());
return("ERROR " + ex.Message);
}
finally
{
//Once done close and quit Excel
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
}
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 button5_Click(object sender, EventArgs e)
{
//数値の読み込み
int generation_size = Convert.ToInt32(textBox5.Text); //世代数
int popu_size = Convert.ToInt32(textBox6.Text); //個体数
//変数
Random r1 = new Random();
//プログレスバーの設定
progress1 = new ProgressBar();
progress1 = progressBar1;
progress1.Minimum = 0;
progress1.Maximum = generation_size;
progress1.Value = 0;
//世代の生成
GA ga = new GA(form_cluster, form_decks, piping);
ga.makeGenerations(r1, generation_size, popu_size, count_row, g_size, progress1);
for (int i = 0; i < count_row; i++)
{
ga.Generations[generation_size - 1].popu.B.C_List[i].Calc_Center(form_decks);
}
form_decks[0].Arrangement(pictureBox1);
form_decks[1].Arrangement(pictureBox2);
form_decks[2].Arrangement(pictureBox3);
form_decks[3].Arrangement(pictureBox4);
ga.Generations[generation_size - 1].popu.B.Fittness(count_row, g_size, piping);//最適解の配管長を再計算する（出力用）
//------------各クラスタ配置X,Yの表示------------------------------------------------------------------
for (int i = 0; i < count_row; i++)
{
string DECK = Convert.ToString(ga.Generations[generation_size - 1].popu.B.C_List[i].p_deck_No);
string ROW = Convert.ToString(ga.Generations[generation_size - 1].popu.B.C_List[i].c_p_row);
string COL = Convert.ToString(ga.Generations[generation_size - 1].popu.B.C_List[i].c_p_column);
string pos = form_cluster_Inf[i].Name + " DECK:" + DECK + " ROW:" + ROW + " COL:" + COL;
listBox1.Items.Add(pos);
}
//-----------------------------------------------------------------------------------------------------
string pena = Convert.ToString(ga.Generations[generation_size - 1].popu.B.Penalty.Count);
string fit = Convert.ToString(ga.Generations[generation_size - 1].popu.B.Fit);
string results = "Penalty" + pena + " " + "Fit" + fit;
listBox1.Items.Add(results);
//--------和田さんのを参考に--------------------------------------------------------------------------------------------------------
//-------グラフの出力-----------------------------
//自動グラフ作成-------------------------------------------------------------------------------------------------------------
button1.Enabled = false; //謎
Excel.Application oXL = new Excel.Application(); //WindowsのスタートメニューからExcelを起動するようなもの
Excel._Workbook oWB; //Workbookオブジェクトを生成
Excel._Worksheet oSheet; //Workbookオブジェクトに含まれるWorksheetオブジェクトを生成
Excel._Chart oChart; //おそらくグラフを表すオブジェクト
Excel.Range oRng; //ワークシート上の指定された範囲のセルを管理
//よくわからない部分
oXL.Visible = true;
oWB = (Excel._Workbook)(oXL.Workbooks.Add(Type.Missing));
oSheet = (Excel._Worksheet)oWB.ActiveSheet;
//シートの1行目
oSheet.Cells[1, 1] = "sedai";
oSheet.Cells[1, 2] = "fitness";
for (int i = 0; i < generation_size; i++)
{
oSheet.Cells[i + 2, 1] = i + 1;
oSheet.Cells[i + 2, 2] = ga.Generations[i].popu.B.Fit;
}
//--------配管長の出力----------------------------------------------------------
for (int i = 0; i < count_row; i++)
{
for (int j = 0; j < count_row; j++)
{
if (i == 0)
{
oSheet.Cells[i + 1, j + 5] = j + 1;
}
if (j == 0)
{
oSheet.Cells[i + 2, j + 4] = i + 1;
}
oSheet.Cells[i + 2, j + 5] = (piping.P_length[i, j]) / 10;
}
}
//------------------------------------------------------------------------------
//-------------------------------------------------------------------
oChart = (Excel._Chart)oWB.Charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//読み取る部分をExcelに指定
oRng = oSheet.get_Range("A1:B2001", Type.Missing);//とりあえず2000世代までは書き込み可能
oChart.ChartType = Excel.XlChartType.xlXYScatterLinesNoMarkers;
oChart.SetSourceData(oRng, Type.Missing);
//グラフ1作成
Excel.Axis xAxis = (Excel.Axis)oChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
xAxis.MajorUnit = 50;//主な目盛
xAxis.HasTitle = true;
xAxis.AxisTitle.Text = "generation";
Excel.Axis yAxis = (Excel.Axis)oChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
yAxis.HasTitle = true;
yAxis.AxisTitle.Text = "fitness";
MessageBox.Show("終了");
//------------------------------------------------------------------------------------------------------------------------------------
}
// データテーブルをエクセルへエクスポート（箱ヒゲ図付き）
public void ExportToExcelWithBoxPlotChart(DataTable dt)
{
if (dt.Rows.Count == 0)
{
return;
}
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
// column headings
for (int i = 0; i < dt.Columns.Count; i++)
{
xlWorkSheet.Cells[1, (i + 1)] = dt.Columns[i].ColumnName;
}
// rows
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
xlWorkSheet.Cells[(i + 2), (j + 1)] = dt.Rows[i][j];
}
}
int row = xlWorkSheet.UsedRange.Rows.Count;
string address1 = "C1:C" + row.ToString() + ",H1:L" + row.ToString();
string address2 = "M2:M" + row.ToString();
string address3 = "N2:N" + row.ToString();
Excel.Range chartRange1;
Excel.Range chartRange2;
Excel.Range chartRange3;
Excel.ChartObjects xlCharts1 = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart1 = (Excel.ChartObject)xlCharts1.Add(800, 10, 600, 250);
Excel.Chart chartPage1 = myChart1.Chart;
chartRange1 = xlWorkSheet.get_Range(address1);
chartRange2 = xlWorkSheet.get_Range(address2);
chartRange3 = xlWorkSheet.get_Range(address3);
chartPage1.SetSourceData(chartRange1, misValue);
chartPage1.ChartType = Excel.XlChartType.xlColumnStacked;
chartPage1.HasTitle = true;
chartPage1.ChartTitle.Text = dt.Rows[0]["inspect"].ToString() + " " + dt.Rows[0]["line"].ToString();
chartPage1.HasLegend = false;
Excel.Axis xAxis1 = (Excel.Axis)chartPage1.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
xAxis1.CategoryType = Excel.XlCategoryType.xlCategoryScale;
Excel.SeriesCollection oSeriesCollection = (Excel.SeriesCollection)myChart1.Chart.SeriesCollection(misValue);
Excel.Series s5 = oSeriesCollection.Item(5);
s5.ChartType = Excel.XlChartType.xlLine;
s5.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbDarkOrchid;
Excel.Series s4 = oSeriesCollection.Item(4);
s4.ChartType = Excel.XlChartType.xlLine;
s4.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbDarkOrchid;
Excel.Series s1 = oSeriesCollection.Item(1);
s1.Format.Fill.ForeColor.RGB = (int)Excel.XlRgbColor.rgbWhite;
s1.Format.Fill.Transparency = 1;
s1.Format.Line.Weight = 0;
s1.HasErrorBars = true;
s1.ErrorBar(Excel.XlErrorBarDirection.xlY, Excel.XlErrorBarInclude.xlErrorBarIncludeMinusValues,
Excel.XlErrorBarType.xlErrorBarTypeCustom, chartRange2, chartRange2);
Excel.Series s3 = oSeriesCollection.Item(3);
s3.Format.Fill.ForeColor.RGB = (int)Excel.XlRgbColor.rgbAqua;
s3.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbBlack;
s3.Format.Line.Weight = 1.0F;
s3.HasErrorBars = true;
s3.ErrorBar(Excel.XlErrorBarDirection.xlY, Excel.XlErrorBarInclude.xlErrorBarIncludePlusValues,
Excel.XlErrorBarType.xlErrorBarTypeCustom, chartRange3, chartRange3);
Excel.Series s2 = oSeriesCollection.Item(2);
s2.Format.Fill.ForeColor.RGB = (int)Excel.XlRgbColor.rgbAqua;
s2.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbBlack;
s2.Format.Line.Weight = 1.0F;
xlApp.Visible = true;
}
private void GenerateStepsCategoryChart()
{
workSheet_range = xlWorkSheet.Range["A42:D42"];
//columns heading
xlWorkSheet.Range["A42"].Value = "Category";
xlWorkSheet.Range["B42"].Value = "Steps Passed";
xlWorkSheet.Range["C42"].Value = "Steps Failed";
xlWorkSheet.Range["D42"].Value = "Steps Warning";
//format headings
workSheet_range.Font.Bold = true;
workSheet_range.Interior.Color = ColorTranslator.FromHtml(HelperClass.excelHeaderBackgound);
workSheet_range.Font.Color = ColorTranslator.FromHtml(HelperClass.excelHeaderFontColor);
workSheet_range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
workSheet_range.WrapText = true;
rowNo = 42;
var stepstestCaseList =
from testCase in ExecutionSession.lstTestCase
group testCase by testCase.Category into stepsTestCase
select new
{
Category = stepsTestCase.Key,
StepsPassed = stepsTestCase.Sum(testCase => testCase.NoOfStepsPassed),
StepsFailed = stepsTestCase.Sum(testCase => testCase.NoOfStepsFailed),
StepsWarning = stepsTestCase.Sum(testCase => testCase.NoOfWarningSteps)
};
foreach (var item in stepstestCaseList)
{
rowNo++;
xlWorkSheet.Range["A" + rowNo.ToString()].Value = item.Category;
xlWorkSheet.Range["B" + rowNo.ToString()].Value = item.StepsPassed;
xlWorkSheet.Range["C" + rowNo.ToString()].Value = item.StepsFailed;
xlWorkSheet.Range["D" + rowNo.ToString()].Value = item.StepsWarning;
xlWorkSheet.Range["A" + rowNo.ToString()].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
xlWorkSheet.Range["A" + rowNo.ToString()].WrapText = true;
xlWorkSheet.Range["A" + rowNo.ToString()].InsertIndent(5);
}
workSheet_range = xlWorkSheet.Range["A42", "D" + rowNo];
workSheet_range.Borders.Color = System.Drawing.Color.Black.ToArgb();
workSheet_range.WrapText = true;
workSheet_range = xlWorkSheet.Range["B43", "D" + rowNo];
workSheet_range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
myCharts = xlWorkSheet.ChartObjects(Type.Missing);
myCharts1 = myCharts.Add(400, 610, 370, 210);
oChart = myCharts1.Chart;
chartRange = xlWorkSheet.Range["A42", "D" + rowNo];
oChart.SetSourceData(chartRange);;
oChart.PlotBy = XlRowCol.xlColumns;
oChart.ApplyDataLabels(XlDataLabelsType.xlDataLabelsShowNone);
oChart.HasLegend = true;
oChart.HasTitle = true;
oChart.ChartTitle.Text = "Summary report by category";
Excel.Axis axis = (Excel.Axis)oChart.Axes(
Excel.XlAxisType.xlValue,
Excel.XlAxisGroup.xlPrimary);
axis.HasTitle = true;
axis.AxisTitle.Text = "No of steps";
axis = (Excel.Axis)oChart.Axes(
Excel.XlAxisType.xlCategory,
Excel.XlAxisGroup.xlPrimary);
axis.HasTitle = true;
axis.AxisTitle.Text = "Category";
string priorityChartPath = summaryChartsFolder + "\\SummaryReport_Category.jpg";
oChart.Export(priorityChartPath, "jpg", Missing.Value);
}
public void refresh()
{
JObject responses = this.addin.getFormResponses(globalUrl);
List <Question> questions = parseResponses(responses);
int i = 0;
foreach (Excel.ChartObject xlsChart in xlsCharts)
{
Excel.Worksheet dataSheet = dataWorkSheets[i];
//Setting the range of chart
string[] rangeids = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };
string lowerRange = "1";
int upRange = questions[i].Choices.Count() + 1;
string upperRange = upperRangeGlobal;
int lastCellUsed = 0;
lowerRange = rangeids[lastCellUsed + 2] + lowerRange;
//Descobrir o initial range
for (int x = 0; x < 70; x++)
{
if (dataSheet.Cells[2, x + 1].Value != null)
{
lastCellUsed = x;
}
}
if (questions[i].Type == "GRID")
{
int aux = 0;
string categoria1 = questions[i].Choices[0].row;
for (int y = 0; y < questions[i].Choices.Count; y++)
{
if (questions[i].Choices[y].row == categoria1)
{
aux++;
}
}
upperRange = rangeids[aux + lastCellUsed + 2] + (questions[i].Choices.Count / 2 - 1).ToString();
}
else
{
upperRange = rangeids[lastCellUsed + 3] + upRange;
}
Excel.Range tRange = dataSheet.Cells.get_Range(lowerRange, upperRange);
//Setting values for categories and respective series data
string option = lowerRangeGlobal;
string count = upperRangeGlobal;
List <string> rows = new List <string>();
if (questions[i].Type == "GRID")
{
if (newTryCount != 0)
{
option = lowerRangeGlobal + 2;
}
else
{
option = lowerRangeGlobal;
}
int iteraux = 2;
for (var j = 0; j < questions[i].Choices.Count; j++)
{
string optionaux = option + iteraux;
if (!rows.Contains(questions[i].Choices[j].row))
{
dataSheet.Cells.get_Range(optionaux).FormulaR1C1 = questions[i].Choices[j].row;
rows.Add(questions[i].Choices[j].row);
iteraux++;
}
}
string[] columnids = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };
int column_index = 0;
bool series_written = false;
for (int v = 0; v < rows.Count; v++)
{
string categoria = rows[v];
column_index = Array.IndexOf(columnids, lowerRangeGlobal) + 2 * newTryCount;
for (int u = 0; u < questions[i].Choices.Count; u++)
{
if (questions[i].Choices[u].row == categoria)
{
string celula = columnids[column_index + 1] + (v + 2).ToString();
dataSheet.Cells.get_Range(celula).FormulaR1C1 = questions[i].Choices[u].count.ToString();
if (!series_written)
{
dataSheet.Cells.get_Range(columnids[column_index + 1] + (v + 1).ToString()).FormulaR1C1 = questions[i].Choices[u].option;
}
column_index++;
}
}
series_written = true;
}
}
else if (questions[i].Type == "SCALE")
{
for (var j = 0; j < questions[i].Choices.Count; j++)
{
int index1 = j + 2;
string optionaux = option + index1;
dataSheet.Cells.get_Range(optionaux).FormulaR1C1 = "- " + questions[i].Choices[j].option.ToString() + " -";
}
for (var k = 0; k < questions[i].Choices.Count; k++)
{
int index2 = k + 2;
string countaux = count + index2;
dataSheet.Cells.get_Range(countaux).FormulaR1C1 = questions[i].Choices[k].count.ToString();
}
}
else
{
for (var j = 0; j < questions[i].Choices.Count; j++)
{
int index1 = j + 2;
string optionaux = option + index1;
dataSheet.Cells.get_Range(optionaux).FormulaR1C1 = questions[i].Choices[j].option;
}
for (var k = 0; k < questions[i].Choices.Count; k++)
{
int index2 = k + 2;
string countaux = count + index2;
dataSheet.Cells.get_Range(countaux).FormulaR1C1 = questions[i].Choices[k].count.ToString();
}
}
float width = 500F;
float left = 230F;
if (questions[i].Choices.Count > 6)
{
width = 750F;
left = 130F;
}
else if (questions[i].Choices.Count > 12)
{
width = 1100;
left = 80F;
}
Excel.Axis excelaxis = xlsChart.Chart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
if (xlsChart.Chart.ChartType == Excel.XlChartType.xlColumnClustered)
{
int maxScale = 0;
for (int h = 0; h < questions[i].Choices.Count; h++)
{
if (questions[i].Choices[h].count > maxScale)
{
maxScale = questions[i].Choices[h].count;
}
}
excelaxis.MajorUnit = (int)(maxScale + 10.0) / 5;
excelaxis.MinorUnit = (int)(maxScale + 10.0) / 10;
excelaxis.MinimumScale = 0;
excelaxis.MaximumScale = maxScale + 10.0;
}
PowerPoint.Slide currentSlide = this.addin.Application.ActiveWindow.View.Slide;
pptSlides[i].Select();
xlsChart.Copy();
shapeRange = pptSlides[i].Shapes.Paste();
PowerPoint.Shape previousGraph = pptSlides[i].Shapes[2];
previousGraph.Delete();
// Position the chart on the slide.
shapeRange.Left = left;
shapeRange.Top = 160F;
shapeRange.Height = 350F;
shapeRange.Width = width;
i++;
currentSlide.Select();
}
}
static OutputUnits makePerfMonGraphs(string file)
{
Excel.Application excelApp = null;
Excel.Workbook workbook = null;
Excel.Sheets sheets = null;
Excel.Worksheet dataSheet = null;
Excel.Worksheet newSheet = null;
Excel.ChartObjects xlChart = null;
Excel.Range dataY = null;
Excel.Chart memChart = null;
Excel.Chart diskChart = null;
Excel.Chart cpuChart = null;
Excel.Chart netChart = null;
Excel.Axis xAxis = null;
OutputUnits csData = null;
bool leaking = false;
bool highcpu = false;
string exitFile = "";
try {
excelApp = new Excel.Application();
string dir = file.Substring(0, file.LastIndexOf("\\") + 1);
string fm = file.Substring(0, file.Length - 4).Substring(file.LastIndexOf("\\") + 1);
workbook = excelApp.Workbooks.Open(file, 0, false, 6, Type.Missing, Type.Missing, Type.Missing, XlPlatform.xlWindows, ",",
true, false, 0, false, false, false);
sheets = workbook.Sheets;
dataSheet = sheets[1];
dataSheet.Name = "data";
dataSheet.get_Range("A2:A2", Type.Missing).EntireRow.Delete(XlDeleteShiftDirection.xlShiftUp);//garbage row
newSheet = (Worksheet)sheets.Add(Type.Missing, dataSheet, Type.Missing, Type.Missing);
newSheet.Name = "results";
xlChart = (Excel.ChartObjects)newSheet.ChartObjects(Type.Missing);
memChart = xlChart.Add(20, 100, 450, 175).Chart;
diskChart = xlChart.Add(20, 280, 450, 175).Chart;
cpuChart = xlChart.Add(500, 100, 450, 175).Chart;
netChart = xlChart.Add(500, 280, 450, 175).Chart;
int rowTotal = dataSheet.UsedRange.Rows.Count;
int colTotal = dataSheet.UsedRange.Columns.Count;
dataSheet.get_Range("A2", "A" + rowTotal).NumberFormat = "m/d/yyyy h:mm";
string ttime = dataSheet.Cells[2, 1].Value.ToString();
Array availMem = (System.Array)dataSheet.get_Range("C2", "C" + rowTotal).Value;
Array cpuTotal = (System.Array)dataSheet.get_Range("D2", "D" + rowTotal).Value;
Array diskTotal = (System.Array)dataSheet.get_Range("B2", "B" + rowTotal).Value;
dataSheet.Cells[1, colTotal + 1] = "Total LAN (Bytes Total/Sec)";
double[] netties = new double[rowTotal - 1];
for (int i = 2; i <= rowTotal; i++)
{
if (colTotal > 5)
{
Array netLine = (System.Array)dataSheet.get_Range(xlStr(5) + i, xlStr(colTotal) + i).Value;
double netLineTotal = 0;
for (int j = 1; j <= netLine.Length; j++)
{
netLineTotal += Convert.ToDouble(netLine.GetValue(1, j));
}
netties[i - 2] = netLineTotal;
dataSheet.Cells[i, colTotal + 1] = netLineTotal;
}
else
{
dataSheet.Cells[i, colTotal + 1] = "0";
}
}
#region BuildCounters
double[] mems = ColToDouble(availMem);
double[] cpus = ColToDouble(cpuTotal);
double[] disks = ColToDouble(diskTotal);
//netties[]
double avgCPUs = cpus.Average();
PCounter CPU = new PCounter(avgCPUs, cpus.Max(), cpus.Min());
PCounter MEM = new PCounter(mems.Average(), mems.Max(), mems.Min());
PCounter DISK = new PCounter(disks.Average(), disks.Max(), disks.Min());
PCounter NETS = new PCounter(netties.Average(), netties.Max(), netties.Min());
if (avgCPUs > 40)
{
highcpu = true;
}
#endregion
#region leakCheck
double[] eqMB = new double[2];
List <double> memList = new List <double>();
int cX = availMem.Length;
for (int i = 1; i < rowTotal - 1; i++)
{
memList.Add(Convert.ToDouble(availMem.GetValue(i, 1)));
}
eqMB = LeastSquares(memList);
double stdD1 = StandardDev(memList);
List <double> memList2 = sigma(memList, stdD1, eqMB);
cX = memList2.Count();
eqMB = LeastSquares(memList2);
double stdD2 = StandardDev(memList2) * 1.2;
List <double> memList3 = sigma(memList2, stdD2, eqMB);
eqMB = LeastSquares(memList3);
if (eqMB[0] < 0)
{
leaking = true;
newSheet.get_Range("E4", Type.Missing).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Tomato);
}
#endregion
#region formatting
string lan = xlStr(colTotal + 1);
newSheet.get_Range("A1", Type.Missing).EntireColumn.ColumnWidth = 12;
newSheet.get_Range("A1", Type.Missing).EntireColumn.HorizontalAlignment = XlHAlign.xlHAlignRight;
newSheet.get_Range("A2", Type.Missing).EntireRow.HorizontalAlignment = XlHAlign.xlHAlignCenter;
newSheet.Cells[4, 5] = eqMB[0];
newSheet.Cells[2, 2] = "Avg";
newSheet.Cells[2, 3] = "Min";
newSheet.Cells[2, 4] = "Max";
newSheet.Cells[2, 5] = "Slope(3Sigma)";
newSheet.Cells[3, 1] = "CPU";
newSheet.Cells[3, 2] = "=AVERAGE(data!D:D)";
newSheet.Cells[3, 3] = "=MIN(data!D:D)";
newSheet.Cells[3, 4] = "=MAX(data!D:D)";
newSheet.Cells[4, 1] = "Avail.RAM";
newSheet.Cells[4, 2] = "=AVERAGE(data!C:C)";
newSheet.Cells[4, 3] = "=MIN(data!C:C)";
newSheet.Cells[4, 4] = "=MAX(data!C:C)";
newSheet.Cells[5, 1] = "LAN Usage";
newSheet.Cells[5, 2] = "=AVERAGE(data!" + lan + ":" + lan + ")";
newSheet.Cells[5, 3] = "=MIN(data!" + lan + ":" + lan + ")";
newSheet.Cells[5, 4] = "=MAX(data!" + lan + ":" + lan + ")";
newSheet.Cells[6, 1] = "Disk Usage";
newSheet.Cells[6, 2] = "=AVERAGE(data!B:B)";
newSheet.Cells[6, 3] = "=MIN(data!B:B)";
newSheet.Cells[6, 4] = "=MAX(data!B:B)";
#endregion
#region memChart
dataY = dataSheet.Range["C1", "C" + rowTotal];
memChart.SetSourceData(dataY, Type.Missing);
memChart.ChartType = XlChartType.xlXYScatterLinesNoMarkers;
memChart.HasLegend = false;
xAxis = (Axis)memChart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary);
xAxis.MaximumScaleIsAuto = false;
xAxis.MaximumScale = rowTotal + 1;
xAxis.MinimumScaleIsAuto = false;
xAxis.MinimumScale = 0;
#endregion
#region diskChart
dataY = dataSheet.Range["B1", "B" + rowTotal];
diskChart.SetSourceData(dataY, Type.Missing);
diskChart.ChartType = XlChartType.xlXYScatterLinesNoMarkers;
diskChart.HasLegend = false;
xAxis = (Axis)diskChart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary);
xAxis.MaximumScaleIsAuto = false;
xAxis.MaximumScale = rowTotal + 1;
xAxis.MinimumScaleIsAuto = false;
xAxis.MinimumScale = 0;
#endregion
#region cpuChart
dataY = dataSheet.Range["D1", "D" + rowTotal];
cpuChart.SetSourceData(dataY, Type.Missing);
cpuChart.ChartType = XlChartType.xlXYScatterLinesNoMarkers;
cpuChart.HasLegend = false;
xAxis = (Axis)cpuChart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary);
xAxis.MaximumScaleIsAuto = false;
xAxis.MaximumScale = rowTotal + 1;
xAxis.MinimumScaleIsAuto = false;
xAxis.MinimumScale = 0;
#endregion
#region netChart
dataY = dataSheet.Range[xlStr(colTotal + 1) + "1", xlStr(colTotal + 1) + rowTotal];
netChart.SetSourceData(dataY, Type.Missing);
netChart.ChartType = XlChartType.xlXYScatterLinesNoMarkers;
netChart.HasLegend = false;
xAxis = (Axis)netChart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary);
xAxis.MaximumScaleIsAuto = false;
xAxis.MaximumScale = rowTotal + 1;
xAxis.MinimumScaleIsAuto = false;
xAxis.MinimumScale = 0;
#endregion
string host = Path.GetFileNameWithoutExtension(dir + fm);
csData = new OutputUnits(host, ttime + " time chunks: " + (rowTotal - 1), CPU, MEM, NETS, DISK, leaking, highcpu);
exitFile = dir + fm;
excelApp.DisplayAlerts = false;
workbook.SaveAs(@exitFile, XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing, false, false, XlSaveAsAccessMode.xlNoChange, XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing, Type.Missing);
workbook.Close(true, Type.Missing, Type.Missing);
excelApp.Quit();
//releaseObject(sC);
//releaseObject(myChart);
} catch {
Console.WriteLine("Had issues interacting with your Excel installation...maybe try a restart?");
//using (StreamWriter outfile = File.AppendText("output.txt")) {
// outfile.WriteLine("Did have issues interacting with Excel on " + file);
//}
} finally {
releaseObject(xAxis);
releaseObject(dataY);
releaseObject(diskChart);
releaseObject(memChart);
releaseObject(cpuChart);
releaseObject(netChart);
releaseObject(xlChart);
releaseObject(newSheet);
releaseObject(dataSheet);
releaseObject(sheets);
releaseObject(workbook);
releaseObject(excelApp);
}
return(csData);
}
public void generateResponsesSlides(Office.IRibbonControl control)
{
JObject responses = this.addin.getFormResponses(globalUrl);
string fileName = this.addin.getTitle(globalUrl);
string paramWorkbookPath = @".\" + fileName + ".xlsx";
string paramPowerpointPath = @".\" + fileName + ".pptx";
List <Question> questions = parseResponses(responses);
PowerPoint.CustomLayout layout = this.addin.Application.ActivePresentation.SlideMaster.CustomLayouts[6];
for (int i = 0; i < questions.Count(); i++)
{
//Adicionar novo slide e mudar slide de foco
pptSlide = this.addin.Application.ActivePresentation.Slides.AddSlide(this.addin.currentSlide + 1, layout);
pptSlides.Add(pptSlide);
this.addin.Application.ActiveWindow.Presentation.Slides[this.addin.currentSlide + 1].Select();
this.addin.currentSlide++;
//Output do Titulo da Pergunta
this.addin.Application.ActiveWindow.View.Slide.Shapes.Title.TextFrame.TextRange.Text = questions[i].Title;
Excel.Worksheet dataSheet;
if (i == 0)
{
//Create instance to Excel workbook to work with chart data
Excel.Application excelApp = new Excel.Application();
excelApp.Visible = true;
if (!File.Exists(paramWorkbookPath))
{
dataWorkbook = excelApp.Workbooks.Add();
dataWorkbooks.Add(dataWorkbook);
dataWorkbook.Windows[1].WindowState = Excel.XlWindowState.xlMinimized;
//Accessing the data worksheet for chart
dataSheet = ((Excel.Worksheet)dataWorkbook.Worksheets[i + 1]);
dataWorkSheets.Add(dataSheet);
}
else
{
dataWorkbook = excelApp.Workbooks.Open(paramWorkbookPath);
dataWorkbooks.Add(dataWorkbook);
dataWorkbook.Windows[1].WindowState = Excel.XlWindowState.xlMinimized;
//Accessing the data worksheet for chart
dataSheet = ((Excel.Worksheet)dataWorkbook.Worksheets[i + 1]);
dataWorkSheets.Add(dataSheet);
}
}
else
{
dataSheet = dataWorkbook.Worksheets.Add();
dataWorkSheets.Add(dataSheet);
}
//Setting the range of chart
string[] rangeids = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };
string lowerRange = "1";
int upRange = questions[i].Choices.Count() + 1;
string upperRange = upRange.ToString();
lowerRange = "A" + lowerRange;
//TODO TIRAR ESTE "F" HARDCODED
if (questions[i].Type == "GRID")
{
upperRange = "F" + upRange / 2;
int aux = 0;
string categoria1 = questions[i].Choices[0].row;
for (int y = 0; y < questions[i].Choices.Count; y++)
{
if (questions[i].Choices[y].row == categoria1)
{
aux++;
}
}
upperRange = rangeids[aux] + (questions[i].Choices.Count / 2 - 1).ToString();
gridUpperRangeGlobal = upperRange;
dataSheet.Cells.get_Range("A50").FormulaR1C1 = upperRange;
}
else
{
upperRange = "B" + upperRange;
dataSheet.Cells.get_Range("A50").FormulaR1C1 = upperRange;
}
Excel.Range tRange = dataSheet.Cells.get_Range(lowerRange, upperRange);
Excel.ListObject tbl1;
string tableName = "Tabela";
//Applying the set range on chart data table
tableName = tableName + i;
dataSheet.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, tRange, Type.Missing, Excel.XlYesNoGuess.xlYes, Type.Missing).Name = tableName;
tbl1 = dataSheet.ListObjects[tableName];
tbl1.Resize(tRange);
//Setting values for categories and respective series data
string option = "A";
string count = "B";
List <string> rows = new List <string>();
if (questions[i].Type == "GRID")
{
int iteraux = 2;
for (var j = 0; j < questions[i].Choices.Count; j++)
{
string optionaux = option + iteraux;
if (!rows.Contains(questions[i].Choices[j].row))
{
dataSheet.Cells.get_Range(optionaux).FormulaR1C1 = questions[i].Choices[j].row;
rows.Add(questions[i].Choices[j].row);
iteraux++;
}
}
string[] columnids = { "A", "B", "C", "D", "E", "F", "G", "H" };
int column_index = 0;
bool series_written = false;
for (int v = 0; v < rows.Count; v++)
{
string categoria = rows[v];
column_index = 0;
for (int u = 0; u < questions[i].Choices.Count; u++)
{
if (questions[i].Choices[u].row == categoria)
{
string celula = columnids[column_index + 1] + (v + 2).ToString();
dataSheet.Cells.get_Range(celula).FormulaR1C1 = questions[i].Choices[u].count.ToString();
if (!series_written)
{
dataSheet.Cells.get_Range(columnids[column_index + 1] + (v + 1).ToString()).FormulaR1C1 = questions[i].Choices[u].option;
}
column_index++;
}
}
series_written = true;
}
dataSheet.Cells.get_Range("A1").FormulaR1C1 = "Try1";
}
else if (questions[i].Type == "SCALE")
{
for (var j = 0; j < questions[i].Choices.Count; j++)
{
int index1 = j + 2;
string optionaux = option + index1;
dataSheet.Cells.get_Range(optionaux).FormulaR1C1 = "- " + questions[i].Choices[j].option.ToString() + " -";
}
for (var k = 0; k < questions[i].Choices.Count; k++)
{
int index2 = k + 2;
string countaux = count + index2;
dataSheet.Cells.get_Range(countaux).FormulaR1C1 = questions[i].Choices[k].count.ToString();
}
dataSheet.Cells.get_Range("A1").FormulaR1C1 = "Categoria";
dataSheet.Cells.get_Range("B1").FormulaR1C1 = "Try1";
}
else
{
for (var j = 0; j < questions[i].Choices.Count; j++)
{
int index1 = j + 2;
string optionaux = option + index1;
dataSheet.Cells.get_Range(optionaux).FormulaR1C1 = questions[i].Choices[j].option;
}
for (var k = 0; k < questions[i].Choices.Count; k++)
{
int index2 = k + 2;
string countaux = count + index2;
dataSheet.Cells.get_Range(countaux).FormulaR1C1 = questions[i].Choices[k].count.ToString();
}
dataSheet.Cells.get_Range("A1").FormulaR1C1 = "Categoria";
dataSheet.Cells.get_Range("B1").FormulaR1C1 = "Try1";
}
// Insert graphic in Excel
//Output do Gráfico
float width = 500F;
float left = 230F;
if (questions[i].Choices.Count > 6)
{
width = 750F;
left = 130F;
}
else if (questions[i].Choices.Count > 12)
{
width = 1100;
left = 80F;
}
//Save value of last
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)dataSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = xlCharts.Add(50, 150, width, 250);
Excel.Chart chartPage = myChart.Chart;
object paramMissing = Type.Missing;
// Declare variables for the Chart.ChartWizard method.
object paramChartFormat = 1;
object paramCategoryLabels = 0;
object paramSeriesLabels = 0;
bool paramHasLegend = true;
// Create a new chart of the data.
myChart.Chart.ChartWizard(tRange, Excel.XlChartType.xlColumnClustered, paramChartFormat, Excel.XlRowCol.xlRows,
paramCategoryLabels, paramSeriesLabels, paramHasLegend, paramMissing, paramMissing, paramMissing, paramMissing);
chartRange = dataSheet.get_Range(lowerRange, upperRange);
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
if (questions[i].Type != "GRID")
{
chartPage.ChartTitle.Delete();
}
Excel.Axis excelaxis = chartPage.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
int maxScale = 0;
for (int h = 0; h < questions[i].Choices.Count; h++)
{
if (questions[i].Choices[h].count > maxScale)
{
maxScale = questions[i].Choices[h].count;
}
}
excelaxis.MajorUnit = (int)(maxScale + 10.0) / 5;
excelaxis.MinorUnit = (int)(maxScale + 10.0) / 10;
excelaxis.MinimumScale = 0;
excelaxis.MaximumScale = maxScale + 10.0;
myChart.Copy();
shapeRange = pptSlide.Shapes.Paste();
// Position the chart on the slide.
shapeRange.Left = left;
shapeRange.Top = 160F;
shapeRange.Height = 350F;
shapeRange.Width = width;
//Copy chart to xlsCharts
xlsCharts.Add(myChart);
}
// Save excel file
if (newTryBool == true)
{
//dataWorkbook.Save();
}
else
{
//dataWorkbook.SaveAs(paramWorkbookPath, paramMissing, paramMissing, paramMissing, paramMissing,
//paramMissing, Excel.XlSaveAsAccessMode.xlNoChange, paramMissing, paramMissing, paramMissing, paramMissing, paramMissing);
}
pptSlides[0].Select();
// Save the presentation.
//this.addin.Application.ActivePresentation.SaveAs(paramPowerpointPath, PowerPoint.PpSaveAsFileType.ppSaveAsOpenXMLPresentation, Office.MsoTriState.msoTrue);
}
public AreaThreshold()
{
InitializeComponent();
Graphic.RangeData(ref str, ref rows, ref cols);
worksheet = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet);
Excel.Range activecells = Globals.ThisAddIn.Application.ActiveCell;
start_col = activecells.Column;
start_row = activecells.Row;
double Max_Value = Double.MinValue;
double Min_Value = Double.MaxValue;
for (int i = 1; i < rows; i++)
{
if (double.Parse(str[i, 0]) > Max_Value)
{
Max_Value = double.Parse(str[i, 1]);
}
if (double.Parse(str[i, 0]) < Min_Value)
{
Min_Value = double.Parse(str[i, 1]);
}
}
ThreholdArea = (Max_Value + Min_Value) / 2;
textBox_Bandwidth.Text = Convert.ToString(ThreholdArea);
textBox_Bandwidth.Refresh();
((Excel.Range)worksheet.Cells[start_row, start_col + cols + 1]).Value2 = "Assiatant";
((Excel.Range)worksheet.Cells[start_row, start_col + cols + 2]).Value2 = "<=Threshold";
((Excel.Range)worksheet.Cells[start_row, start_col + cols + 3]).Value2 = ">Threshold";
double[,] data = new double[rows - 1, 3];
for (int i = 1; i < rows; i++)
{
if (double.Parse(str[i, 1]) > ThreholdArea)
{
data[i - 1, 2] = double.Parse(str[i, 1]) - ThreholdArea;
data[i - 1, 1] = 0;
data[i - 1, 0] = ThreholdArea - data[i - 1, 1];
}
else
{
data[i - 1, 2] = 0;
data[i - 1, 1] = ThreholdArea - double.Parse(str[i, 1]);
data[i - 1, 0] = ThreholdArea - data[i - 1, 1];
}
}
//double Max_data = Double.MinValue;
//double Min_data = Double.MaxValue;
//for (int i = 1; i < rows; i++)
//{
// if (data[i - 1, 0] > Max_data) Max_data = data[i - 1, 0];
// if (data[i - 1, 1] < Min_data) Min_data = data[i - 1, 1];
//}
Excel.Range c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 1];
Excel.Range c2 = (Excel.Range)worksheet.Cells[start_row + rows - 1, start_col + cols + 3];
Excel.Range range = worksheet.get_Range(c1, c2);
range.Value = data;
string ChartOrder = "AreaThreshold" + Convert.ToString(Nchart);
chart = worksheet.Controls.AddChart(300, 50, 450, 400, ChartOrder);
Nchart = Nchart + 1;
c1 = (Excel.Range)worksheet.Cells[start_row, start_col + cols + 1];
c2 = (Excel.Range)worksheet.Cells[start_row + rows - 1, start_col + cols + 3];
chart.SetSourceData(worksheet.get_Range(c1, c2), Excel.XlRowCol.xlColumns);
chart.ChartType = Excel.XlChartType.xlAreaStacked;
//*****************************************Primary Axis********************************************
Excel.SeriesCollection series = (Excel.SeriesCollection)chart.SeriesCollection();
Excel.Series Sseries1 = series.Item(1);
c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col];
c2 = (Excel.Range)worksheet.Cells[start_row + rows - 1, start_col];
Sseries1.XValues = worksheet.get_Range(c1, c2);
//c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 1];
//c2 = (Excel.Range)worksheet.Cells[start_row + rows-1, start_col + cols + 1];
//Sseries1.Values = worksheet.get_Range(c1, c2);
//c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col];
//c2 = (Excel.Range)worksheet.Cells[start_row + rows - 1, start_col];
//Sseries2.XValues = worksheet.get_Range(c1, c2);
//c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 2];
//c2 = (Excel.Range)worksheet.Cells[start_row + rows - 1, start_col + cols + 2];
//Sseries2.Values = worksheet.get_Range(c1, c2);
((Excel.Range)worksheet.Cells[start_row, start_col + cols]).Value2 = "Threshold";
((Excel.Range)worksheet.Cells[start_row + 1, start_col + cols]).Value2 = ThreholdArea;
//*****************************************Chart Style********************************************
//Excel.Series Sseries1 = series.Item(1);
Sseries1.Format.Fill.Visible = Office.MsoTriState.msoFalse;
Sseries1.Format.Line.Visible = Office.MsoTriState.msoFalse;
Excel.Series Sseries2 = series.Item(2);
Sseries2.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 196, 191, 0).ToArgb();
Sseries2.Format.Line.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 0, 0, 0).ToArgb();
Sseries2.Format.Line.Weight = 0.25F;
Excel.Series Sseries3 = series.Item(3);
Sseries3.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 109, 118, 248).ToArgb();
Sseries3.Format.Line.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 0, 0, 0).ToArgb();
Sseries3.Format.Line.Weight = 0.25F;
//************************************************************************************************
chart.PlotArea.Format.Fill.Visible = Office.MsoTriState.msoCTrue;
chart.PlotArea.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(229, 229, 229).ToArgb();
chart.PlotArea.Format.Fill.Transparency = 0;
chart.PlotArea.Format.Line.Visible = Office.MsoTriState.msoFalse;
// Legend
chart.SetElement(Office.MsoChartElementType.msoElementLegendRight);
chart.Legend.Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 0, 0).ToArgb();
chart.Legend.Format.TextFrame2.TextRange.Font.NameComplexScript = "Times New Roman";
chart.Legend.Format.TextFrame2.TextRange.Font.NameFarEast = "Times New Roman";
chart.Legend.Format.TextFrame2.TextRange.Font.Name = "Times New Roman";
chart.Legend.Format.TextFrame2.TextRange.Font.Size = 10;
// ChartArea Line
chart.ChartArea.Format.Line.Visible = Office.MsoTriState.msoFalse;
chart.ChartArea.Height = 340.157480315;
chart.ChartArea.Width = 380.5039370079;
chart.SetElement(Office.MsoChartElementType.msoElementPrimaryValueGridLinesMinorMajor);
chart.SetElement(Office.MsoChartElementType.msoElementPrimaryCategoryGridLinesMinorMajor);
chart.SetElement(Office.MsoChartElementType.msoElementPrimaryValueAxisTitleAdjacentToAxis);
chart.SetElement(Office.MsoChartElementType.msoElementPrimaryCategoryAxisTitleAdjacentToAxis);
//y axis
Excel.Axis axis = (Excel.Axis)chart.Axes(
Excel.XlAxisType.xlValue,
Excel.XlAxisGroup.xlPrimary);
axis.MinorUnit = axis.MajorUnit / 2;
axis.MajorGridlines.Format.Line.Visible = Office.MsoTriState.msoCTrue;
axis.MajorGridlines.Format.Line.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 255, 255).ToArgb();
axis.MajorGridlines.Format.Line.Weight = (float)1.25;
axis.MajorGridlines.Format.Line.DashStyle = Office.MsoLineDashStyle.msoLineSolid;
axis.MinorGridlines.Format.Line.Visible = Office.MsoTriState.msoCTrue;
axis.MinorGridlines.Format.Line.ForeColor.RGB = System.Drawing.Color.FromArgb(242, 242, 242).ToArgb();
axis.MinorGridlines.Format.Line.Weight = (float)0.25;
axis.MinorGridlines.Format.Line.DashStyle = Office.MsoLineDashStyle.msoLineSolid;
axis.Format.Line.Visible = Office.MsoTriState.msoFalse;
axis.HasTitle = true;
axis.AxisTitle.Text = "y axis";
axis.TickLabels.Font.Color = System.Drawing.Color.FromArgb(0, 0, 0).ToArgb();
axis.TickLabels.Font.Name = "Times New Roman";
axis.TickLabels.Font.Size = 10;
axis.TickLabelPosition = Excel.XlTickLabelPosition.xlTickLabelPositionLow;
axis.AxisTitle.Format.TextFrame2.TextRange.Characters.Font.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 0, 0).ToArgb();
axis.AxisTitle.Format.TextFrame2.TextRange.Font.NameComplexScript = "Times New Roman";
axis.AxisTitle.Format.TextFrame2.TextRange.Font.NameFarEast = "Times New Roman";
axis.AxisTitle.Format.TextFrame2.TextRange.Font.Name = "Times New Roman";
axis.AxisTitle.Format.TextFrame2.TextRange.Font.Size = 10;
axis.AxisTitle.Format.TextFrame2.TextRange.Font.Bold = Office.MsoTriState.msoFalse;
//x axis
axis = (Excel.Axis)chart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
axis.TickMarkSpacing = 3;
axis.MajorGridlines.Format.Line.Visible = Office.MsoTriState.msoCTrue;
axis.MajorGridlines.Format.Line.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 255, 255).ToArgb();
axis.MajorGridlines.Format.Line.Weight = (float)1.25;
axis.MajorGridlines.Format.Line.DashStyle = Office.MsoLineDashStyle.msoLineSolid;
axis.MinorGridlines.Format.Line.Visible = Office.MsoTriState.msoCTrue;
axis.MinorGridlines.Format.Line.ForeColor.RGB = System.Drawing.Color.FromArgb(242, 242, 242).ToArgb();
axis.MinorGridlines.Format.Line.Weight = (float)0.25;
axis.MinorGridlines.Format.Line.DashStyle = Office.MsoLineDashStyle.msoLineSolid;
axis.Format.Line.Visible = Office.MsoTriState.msoFalse;
axis.HasTitle = true;
axis.AxisTitle.Text = "x axis";
axis.TickLabels.Font.Color = System.Drawing.Color.FromArgb(0, 0, 0).ToArgb();
axis.TickLabels.Font.Name = "Times New Roman";
axis.TickLabels.Font.Size = 10;
axis.TickLabelPosition = Excel.XlTickLabelPosition.xlTickLabelPositionLow;
axis.AxisTitle.Format.TextFrame2.TextRange.Characters.Font.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 0, 0).ToArgb();
axis.AxisTitle.Format.TextFrame2.TextRange.Font.NameComplexScript = "Times New Roman";
axis.AxisTitle.Format.TextFrame2.TextRange.Font.NameFarEast = "Times New Roman";
axis.AxisTitle.Format.TextFrame2.TextRange.Font.Name = "Times New Roman";
axis.AxisTitle.Format.TextFrame2.TextRange.Font.Size = 10;
axis.AxisTitle.Format.TextFrame2.TextRange.Font.Bold = Office.MsoTriState.msoFalse;
// Change plot area ForeColor
chart.HasLegend = false;
chart.HasTitle = false;
chart.Refresh();
//worksheet.Activate();
flag = 1;
}
private void exportexcel(System.Data.DataTable dt, string filename)
{
Microsoft.Office.Interop.Excel._Workbook oWB;
Microsoft.Office.Interop.Excel.Series oSeries;
// Microsoft.Office.Interop.Excel.Range oResizeRange;
Microsoft.Office.Interop.Excel._Chart oChart;
//String sMsg;
//int iNumQtrs;
GC.Collect();//系统的垃圾回收
//string filename = @"C:\Documents and Settings\tongxl\桌面\nnn.xls";
//Microsoft.Office.Interop.Excel.Application ep = new Microsoft.Office.Interop.Excel.Application();
//Microsoft.Office.Interop.Excel._Workbook wb = ep.Workbooks.Add(filename);
Microsoft.Office.Interop.Excel.Application ep = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook wb = ep.Workbooks.Add(true);
ep.Visible = true;
Microsoft.Office.Interop.Excel.Sheets sheets = wb.Worksheets;
Microsoft.Office.Interop.Excel._Worksheet ws = (Microsoft.Office.Interop.Excel._Worksheet)sheets.get_Item(1);// [System.Type.Missing];//.get.get_Item("xx");
ws.UsedRange.Select();
ws.UsedRange.Copy(System.Type.Missing);
// wb.Charts.Add(System.Type.Missing, System.Type.Missing, 1, System.Type.Missing);
int rowIndex = 1;
int colIndex = 1;
foreach (DataColumn col in dt.Columns)
{
ws.Cells[rowIndex, colIndex] = col.ColumnName;
colIndex++;
}
for (int drvIndex = 0; drvIndex < dt.Rows.Count; drvIndex++)
{
DataRow row = dt.Rows[drvIndex];
colIndex = 1;
foreach (DataColumn col in dt.Columns)
{
ws.Cells[drvIndex + 2, colIndex] = row[col.ColumnName].ToString();
colIndex++;
}
}
oWB = (Microsoft.Office.Interop.Excel._Workbook)ws.Parent;
oChart = (Microsoft.Office.Interop.Excel._Chart)oWB.Charts.Add(Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
oChart.ChartWizard(ws.get_Range(ws.Cells[1, 1], ws.Cells[30, 30]), Microsoft.Office.Interop.Excel.XlChartType.xlLine, Missing.Value,
XlRowCol.xlColumns, true, true, true,
this.bandedGridView1.GroupPanelText, Missing.Value, Missing.Value, Missing.Value);
oSeries = (Microsoft.Office.Interop.Excel.Series)oChart.SeriesCollection(1);
oChart.PlotVisibleOnly = false;
// oChart.HasDataTable = true;
Microsoft.Office.Interop.Excel.Axis axis = (Microsoft.Office.Interop.Excel.Axis)oChart.Axes(
Microsoft.Office.Interop.Excel.XlAxisType.xlValue,
Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary);
Microsoft.Office.Interop.Excel.Axis ax = (Microsoft.Office.Interop.Excel.Axis)oChart.Axes(
Microsoft.Office.Interop.Excel.XlAxisType.xlCategory, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary);
//ax.HasTitle = true;
//ax.AxisTitle.Text = "Sales Figures";
ax.HasMajorGridlines = true;
//string filename = @"C:\Documents and Settings\tongxl\桌面\ccsb.xls";
ws.SaveAs(filename, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
/// <summary>
/// 根据数据库中的数据信息，绘制两条数据系列图，并在表示基坑深度的那一条数据系列上绘制此区域所在的基坑ID的构件图
/// </summary>
/// <param name="DrawingChart"></param>
/// <param name="SelectedRegion"></param>
/// <returns></returns>
/// <remarks></remarks>
public Series[] SetDataSeries(Chart DrawingChart, Series
series_DeepestExca, Series Series_Depth,
List <clsData_ProcessRegionData> SelectedRegion)
{
int RegionsCount = System.Convert.ToInt32(SelectedRegion.Count);
string[] arrDescrip = new string[RegionsCount - 1 + 1]; //每一个区域的描述，作为坐标轴中的X轴数据
float[] arrDeepest = new float[RegionsCount - 1 + 1]; //每一个区域的坑底标高
float[] arrDepth = new float[RegionsCount - 1 + 1]; //每一个区域在当天的开挖标高，对于初始绘图，先设定这个值为地面标高
clsData_ExcavationID[] arrExcavID = new clsData_ExcavationID[RegionsCount - 1 + 1]; //每一个区域所对应的基坑ID对象
float Elevation_Ground = Project_Expo.Elevation_GroundSurface; //项目的自然地面的标高
//所有选择的区域中的最深的标高位置，以米为单位
float DeepestElevation = Elevation_Ground;
for (UInt16 i = 0; i <= RegionsCount - 1; i++)
{
clsData_ProcessRegionData Region = SelectedRegion.Item(i);
arrDescrip[i] = Region.description;
float BottomElevation = Region.ExcavationID.ExcavationBottom;
arrDeepest[i] = BottomElevation;
arrDepth[i] = Elevation_Ground; // ClsData_DataBase.GetElevation(Region.Range, )
DeepestElevation = Math.Min((short)DeepestElevation, (short)BottomElevation);
arrExcavID[i] = Region.ExcavationID;
}
// ------------------------ 设置Chart数据 ---------------------------
try
{
Series with_2 = series_DeepestExca;
with_2.Name = "";
with_2.XValues = arrDescrip;
with_2.Values = arrDeepest;
Series with_3 = Series_Depth;
with_3.Name = "";
with_3.XValues = arrDescrip;
with_3.Values = arrDepth;
}
catch (Exception ex)
{
MessageBox.Show("设置基坑区域开挖图中的开挖标高数据出错！" + "\r\n" + ex.Message +
"\r\n" + "报错位置：" + ex.TargetSite.Name, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
// ------------------------ 设置坐标轴格式 ---------------------------
try
{
double max = Elevation_Ground;
double min = 0;
min = System.Convert.ToDouble(Min_Array <Single>(arrDeepest));
if (min > 0)
{
min = Math.Ceiling(min);
}
else //注意Math.Ceiling(-3.2)=-3
{
min = Math.Floor(min);
}
Microsoft.Office.Interop.Excel.Axis axY = DrawingChart.Axes(Microsoft.Office.Interop.Excel.XlAxisType.xlValue);
axY.MaximumScale = max;
axY.MinimumScale = min;
axY.AxisTitle.Text = "标高（m）";
Microsoft.Office.Interop.Excel.Axis axX = DrawingChart.Axes(Microsoft.Office.Interop.Excel.XlAxisType.xlValue);
}
catch (Exception ex)
{
MessageBox.Show("设置基坑区域开挖图中的坐标轴格式出错！" + "\r\n" + ex.Message + "\r\n" + "报错位置：" + ex.TargetSite.Name,
"Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
// -------------- 绘制每一个选定区域所属的基坑ID的支撑位置 -----------
DrawComponents(arrExcavID, DrawingChart, series_DeepestExca);
//
return(new[] { series_DeepestExca, Series_Depth });
}
private void button1_Click(object sender, EventArgs e)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlApp.DisplayAlerts = false;
//xlApp.Visible = true;
xlWorkBook = xlApp.Workbooks.Open(fileUrl);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
//Left, Top, Width, Height
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(0, 0, 1000, 600);
Excel.Chart chartPage = myChart.Chart;
int lastRow = xlWorkSheet.UsedRange.Rows.Count;
chartRange = xlWorkSheet.get_Range("B1", "D" + lastRow);
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Excel.XlChartType.xlXYScatterSmoothNoMarkers;
Excel.Axis horizAxis = chartPage.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
horizAxis.MaximumScaleIsAuto = false;
horizAxis.MaximumScale = lastRow;
horizAxis.MinimumScaleIsAuto = false;
horizAxis.MinimumScale = 0;
horizAxis.HasTitle = false;
//horizAxis.AxisTitle.Text = "across the bottom";
Excel.Axis vertAxis = (Excel.Axis)chartPage.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
vertAxis.HasMajorGridlines = false; // change this to whatever you wish
vertAxis.HasTitle = true;
vertAxis.AxisTitle.Text = "Volts";
vertAxis.MaximumScaleIsAuto = true;
//vertAxis.MaximumScale = 4; // you can pick this based on your input
vertAxis.MinimumScaleIsAuto = true;
// vertAxis.MinimumScale = -4;
//chartPage.ApplyChartTemplate("c:\\ctmp.crtx");
chartPage.Export(fileDir + "/Chart1.PNG", "PNG", false);
Image image = Image.FromFile(fileDir + "/Chart1.PNG");
pictureBox1.Image = image;
pictureBox1.SizeMode = PictureBoxSizeMode.StretchImage;
//xlWorkBook.SaveAs(fileUrlNoExt, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlNoChange, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
public void newTry(Office.IRibbonControl control)
{
//Limpar as respostas existentes no Inquérito
//this.addin.deleteResponses(globalUrl);
JObject responses = this.addin.getFormResponses(globalUrl);
List <Question> questions = parseResponses(responses);
int i = 0;
foreach (Excel.ChartObject xlsChart in xlsCharts)
{
xlsChart.Delete();
Excel.Worksheet dataSheet = dataWorkSheets[i];
//Setting the range of chart
string[] rangeids = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };
string lowerRange = "1";
int upRange = questions[i].Choices.Count() + 1;
string upperRange = upRange.ToString();
int lastCellUsed = 0;
//Descobrir o initial range
for (int x = 0; x < 70; x++)
{
if (dataSheet.Cells[2, x + 1].Value != null)
{
lastCellUsed = x;
}
}
lowerRange = rangeids[lastCellUsed + 2] + lowerRange;
lowerRangeGlobal = lowerRange[0].ToString();
if (questions[i].Type == "GRID")
{
int aux = 0;
string categoria1 = questions[i].Choices[0].row;
for (int y = 0; y < questions[i].Choices.Count; y++)
{
if (questions[i].Choices[y].row == categoria1)
{
aux++;
}
}
upperRange = rangeids[aux + lastCellUsed + 2] + (questions[i].Choices.Count / 2 - 1).ToString();
gridUpperRangeGlobal = upperRange[0].ToString();
}
else
{
upperRange = rangeids[lastCellUsed + 3] + upperRange;
upperRangeGlobal = upperRange[0].ToString();
}
Excel.Range tRange = dataSheet.Cells.get_Range(lowerRange, upperRange);
Excel.ListObject tbl1;
string tableName = "Tabela";
//Applying the set range on chart data table
DateTime date1 = DateTime.Now;
dataSheet.Cells.get_Range("A50").FormulaR1C1 = tRange;
tableName = date1.ToString();
dataSheet.Cells.get_Range("A51").FormulaR1C1 = tableName;
dataSheet.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, tRange, Type.Missing, Excel.XlYesNoGuess.xlYes, Type.Missing).Name = tableName;
tbl1 = dataSheet.ListObjects[tableName];
tbl1.Resize(tRange);
//Setting values for categories and respective series data
string option = rangeids[lastCellUsed + 2];
string count = rangeids[lastCellUsed + 3];
List <string> rows = new List <string>();
if (questions[i].Type == "GRID")
{
int iteraux = 2;
for (var j = 0; j < questions[i].Choices.Count; j++)
{
string optionaux = option + iteraux;
if (!rows.Contains(questions[i].Choices[j].row))
{
dataSheet.Cells.get_Range(optionaux).FormulaR1C1 = questions[i].Choices[j].row;
rows.Add(questions[i].Choices[j].row);
iteraux++;
}
}
string[] columnids = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };
int column_index = 0;
bool series_written = false;
for (int v = 0; v < rows.Count; v++)
{
string categoria = rows[v];
column_index = lastCellUsed + 2;
for (int u = 0; u < questions[i].Choices.Count; u++)
{
if (questions[i].Choices[u].row == categoria)
{
string celula = columnids[column_index + 1] + (v + 2).ToString();
dataSheet.Cells.get_Range(celula).FormulaR1C1 = questions[i].Choices[u].count.ToString();
if (!series_written)
{
dataSheet.Cells.get_Range(columnids[column_index + 1] + (v + 1).ToString()).FormulaR1C1 = questions[i].Choices[u].option;
}
column_index++;
}
}
series_written = true;
}
dataSheet.Cells.get_Range(lowerRange).FormulaR1C1 = "Try " + (newTryCount + 2).ToString();
}
else if (questions[i].Type == "SCALE")
{
for (var j = 0; j < questions[i].Choices.Count; j++)
{
int index1 = j + 2;
string optionaux = option + index1;
dataSheet.Cells.get_Range(optionaux).FormulaR1C1 = "- " + questions[i].Choices[j].option.ToString() + " -";
}
for (var k = 0; k < questions[i].Choices.Count; k++)
{
int index2 = k + 2;
string countaux = count + index2;
dataSheet.Cells.get_Range(countaux).FormulaR1C1 = questions[i].Choices[k].count.ToString();
}
dataSheet.Cells.get_Range(lowerRange).FormulaR1C1 = "Categoria";
string auxUpperRange = upperRange[0] + "1";
dataSheet.Cells.get_Range(auxUpperRange).FormulaR1C1 = "Try " + (newTryCount + 2).ToString();
}
else
{
for (var j = 0; j < questions[i].Choices.Count; j++)
{
int index1 = j + 2;
string optionaux = option + index1;
dataSheet.Cells.get_Range(optionaux).FormulaR1C1 = questions[i].Choices[j].option;
}
for (var k = 0; k < questions[i].Choices.Count; k++)
{
int index2 = k + 2;
string countaux = count + index2;
dataSheet.Cells.get_Range(countaux).FormulaR1C1 = questions[i].Choices[k].count.ToString();
}
dataSheet.Cells.get_Range(lowerRange).FormulaR1C1 = "Categoria";
string auxUpperRange = upperRange[0] + "1";
dataSheet.Cells.get_Range(auxUpperRange).FormulaR1C1 = "Try " + (newTryCount + 2).ToString();
}
// Insert graphic in Excel
//Output do Gráfico
float width = 500F;
float left = 230F;
if (questions[i].Choices.Count > 6)
{
width = 750F;
left = 130F;
}
else if (questions[i].Choices.Count > 12)
{
width = 1100;
left = 80F;
}
//Save value of last
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)dataSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = xlCharts.Add(50, 150, width, 250);
Excel.Chart chartPage = myChart.Chart;
object paramMissing = Type.Missing;
// Declare variables for the Chart.ChartWizard method.
object paramChartFormat = 1;
object paramCategoryLabels = 0;
object paramSeriesLabels = 0;
bool paramHasLegend = true;
// Create a new chart of the data.
myChart.Chart.ChartWizard(tRange, Excel.XlChartType.xlColumnClustered, paramChartFormat, Excel.XlRowCol.xlRows,
paramCategoryLabels, paramSeriesLabels, paramHasLegend, paramMissing, paramMissing, paramMissing, paramMissing);
chartRange = dataSheet.get_Range(lowerRange, upperRange);
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
Excel.Axis excelaxis = chartPage.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
int maxScale = 0;
for (int h = 0; h < questions[i].Choices.Count; h++)
{
if (questions[i].Choices[h].count > maxScale)
{
maxScale = questions[i].Choices[h].count;
}
}
if (questions[i].Type != "GRID")
{
chartPage.ChartTitle.Delete();
}
excelaxis.MajorUnit = (int)(maxScale + 10.0) / 5;
excelaxis.MinorUnit = (int)(maxScale + 10.0) / 10;
excelaxis.MinimumScale = 0;
excelaxis.MaximumScale = maxScale + 10.0;
PowerPoint.Slide currentSlide = this.addin.Application.ActiveWindow.View.Slide;
pptSlides[i].Select();
try
{
myChart.Copy();
}
catch (COMException e)
{
Thread.Sleep(500);
myChart.Copy();
}
shapeRange = pptSlides[i].Shapes.Paste();
PowerPoint.Shape previousGraph = pptSlides[i].Shapes[2];
previousGraph.Delete();
shapeRange.Left = left;
shapeRange.Top = 160F;
shapeRange.Height = 350F;
shapeRange.Width = width;
xlsChartsTemp.Add(myChart);
i++;
}
xlsCharts = xlsChartsTemp;
xlsChartsTemp = new List <Excel.ChartObject>();
newTryBool = true;
newTryCount++;
// dataWorkbook.Save();
}
private void diagram_Click(object sender, EventArgs e)
{
KURS.allDataSet kursds = new KURS.allDataSet();
KURS.allDataSetTableAdapters.ZakazTableAdapter zta = new KURS.allDataSetTableAdapters.ZakazTableAdapter();
zta.Fill(kursds.Zakaz);
//
object misValue = System.Reflection.Missing.Value;
//
Excel.Application excelapp = new Excel.Application();
excelapp.Visible = true;
excelapp.SheetsInNewWorkbook = 1;
excelapp.Workbooks.Add(misValue);
Excel.Workbooks excelappworkbooks = excelapp.Workbooks;
Excel.Workbook excelappworkbook = excelappworkbooks[1];
Excel.Sheets excelsheets = excelappworkbook.Worksheets;
Excel.Worksheet excelworksheet = (Excel.Worksheet)excelsheets.get_Item(1);
//excelworksheet.Activate();
//
var c = from p in kursds.Zakaz.AsEnumerable() where p.Data.Month == dateTimePicker1.Value.Month select p;
int count = 0;
excelapp.Cells[1][1] = "date";
excelapp.Cells[2][1] = "summ";
foreach (var p in c)
{
count++;
}
//
int[] a = new int[count]; double[] b = new double[count]; int i = 0; double sum = 0;
while (i < count)
{
foreach (var p in c)
{
a[i] = p.Data.Day;
if (i == 0)
{
sum += p.Summ;
}
if (i > 0)
{
if (a[i] == a[i - 1])
{
sum += p.Summ;
}
else
{
b[i - 1] = sum; sum = p.Summ; i++;
}
}
else
{
i++;
}
}
b[i - 1] = sum;
count = i; i = 0;
break;
}
Dictionary <int, double> dic = new Dictionary <int, double>();
while (i < count)
{
excelapp.Cells[1][i + 2] = a[i];
excelapp.Cells[2][i + 2] = b[i];
dic.Add(a[i], b[i]);
i++;
}
//
string str = (count + 1).ToString();
//
//Excel.Window excelWindow = null;
Excel.Range excelcells = null;
//Определяем диаграммы как объекты Excel.ChartObjects
Excel.ChartObjects chartsobjrcts = (Excel.ChartObjects)excelworksheet.ChartObjects(Type.Missing);
//Добавляем одну диаграмму в Excel.ChartObjects - диаграмма пока не выбрана, но место для нее выделено в методе Add
Excel.ChartObject chartsobjrct = chartsobjrcts.Add(100, 40, 300, 300);
Excel.Chart chartPage = chartsobjrct.Chart;
excelcells = excelworksheet.get_Range("B1", "B" + str);
chartPage.SetSourceData(excelcells, misValue);
chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
Excel.Axis axis = (Excel.Axis)chartPage.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
axis.HasTitle = true;
axis.AxisTitle.Text = "Data";
//
Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chartPage.SeriesCollection(Type.Missing);
Excel.Series series = seriesCollection.Item(1);
series.XValues = a;
//
excelappworkbook.SaveAs(@"D:\siple.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
excelappworkbook.Close(true, misValue, misValue);
excelapp.Quit();
this.chart1.Titles.Add("Отчет за месяц");
this.chart1.Series[0].Points.Clear();
Series ser = this.chart1.Series[0];
ser.LegendText = "Summ";
foreach (KeyValuePair <int, double> pair in dic)
{
ser.Points.AddXY(pair.Key, pair.Value);
}
//
}
/// <summary>
/// Initializes Excel vareables
/// </summary>
public static void ExcelPrep()
{
UsingExcel = true;
R1EPCsDic.Clear();
R2EPCsDic.Clear();
#region config Excel application and workbook
oXL = new Excel.Application
{
Visible = true,
WindowState = Excel.XlWindowState.xlMaximized
};
oWB = oXL.Workbooks.Add(Missing.Value);
#endregion
try
{
#region config sheets
oSheetDataRSSIs = (Excel._Worksheet)oWB.ActiveSheet;
oSheetDataRSSIs.Name = "Rssis Data";
oSheetDataRSSIs.get_Range("A1:Z1").Font.Bold = true;
oSheetDataTags = (Excel._Worksheet)oWB.Sheets.Add(Type.Missing, oSheetDataRSSIs, Type.Missing, Type.Missing);
oSheetDataTags.Name = "Tags Data";
oSheetDataTags.get_Range("A1:Z2").Font.Bold = true;
oSheetDataTags.get_Range("A1:Z2").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
oSheetCharts = (Excel._Worksheet)oWB.Sheets.Add(Type.Missing, oSheetDataTags, Type.Missing, Type.Missing);
oSheetCharts.Name = "Charts";
oSheetTagFlags = (Excel._Worksheet)oWB.Sheets.Add(Type.Missing, oSheetCharts, Type.Missing, Type.Missing);
oSheetTagFlags.Name = "Flags";
oSheetTagFlags.Cells[1, 1] = "A";
oSheetTagFlags.Cells[1, 2] = "B";
// Selects sheet if needed
//oSheetDataTags.Activate();
#endregion
#region config charts
Excel.ChartObjects xlCharts = oSheetCharts.ChartObjects(Type.Missing);
#region config RSSI chart
oRng = oSheetCharts.get_Range("B2:K36");
oChartRSSIs = xlCharts.Add(oRng.Left, oRng.Top, oRng.Width, oRng.Height);
oChartRSSIs.Chart.ChartWizard(
Gallery: Excel.XlChartType.xlLine,
Format: 4,
PlotBy: Excel.XlRowCol.xlColumns,
CategoryLabels: false,
SeriesLabels: 5,
HasLegend: true,
Title: "RSSIs",
CategoryTitle: "Values_Range",
ValueTitle: "RSSI"
);
oChartRSSIs.Chart.SetSourceData(oSheetCharts.get_Range("A1:A2"));
#region RSSIs chart axis
Excel.Axis axis = oChartRSSIs.Chart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
axis.MinimumScaleIsAuto = false;
axis.MaximumScaleIsAuto = false;
axis.MajorUnit = 1;
axis.MinimumScale = 170;
axis.MaximumScale = 220;
#endregion
#endregion
#region config Tags chart
oRng = oSheetCharts.get_Range("M2:U36");
oChartTags = xlCharts.Add(oRng.Left, oRng.Top, oRng.Width, oRng.Height);
oChartTags.Chart.ChartWizard(
Gallery: Excel.XlChartType.xlXYScatter,
Format: 3,
CategoryLabels: true,
SeriesLabels: 5,
HasLegend: true,
Title: "Tags Location",
CategoryTitle: "X",
ValueTitle: "Y"
);
oChartTags.Chart.ChartType = Excel.XlChartType.xlXYScatter;
oChartTags.Chart.SetSourceData(oSheetCharts.get_Range("A1:A2"));
#region Tags chart axis
axis = oChartTags.Chart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
axis.MinimumScaleIsAuto = false;
axis.MaximumScaleIsAuto = false;
axis.MajorUnit = 1;
axis.MinimumScale = 0;
axis.MaximumScale = 10;
axis = oChartTags.Chart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
axis.MinimumScaleIsAuto = false;
axis.MaximumScaleIsAuto = false;
axis.MajorUnit = 1;
axis.MinimumScale = -4;
axis.MaximumScale = 4;
#endregion
#endregion
#endregion
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
private void GenerateByCategoryChart()
{
workSheet_range = xlWorkSheet.Range["A42:C42"];
//columns heading
xlWorkSheet.Range["A42"].Value = "Category";
xlWorkSheet.Range["B42"].Value = "PASS";
xlWorkSheet.Range["C42"].Value = "FAIL";
//format headings
workSheet_range.Font.Bold = true;
workSheet_range.Interior.Color = ColorTranslator.FromHtml(HelperClass.excelSummHeaderBackgound);
workSheet_range.Font.Color = ColorTranslator.FromHtml(HelperClass.excelSummHeaderFontColor);
workSheet_range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
workSheet_range.WrapText = true;
rowNo = 42;
foreach (string category in ExecutionSession.lstTestCategories)
{
rowNo++;
xlWorkSheet.Range["A" + rowNo.ToString()].Value = category;
passCount = ExecutionSession.lstExecutedTestCases.Where(testcase => testcase.Category == category &&
testcase.Status == OverAllResult.PASS).Count();
failCount = ExecutionSession.lstExecutedTestCases.Where(testcase => testcase.Category == category &&
testcase.Status == OverAllResult.FAIL).Count();
xlWorkSheet.Range["B" + rowNo.ToString()].Value = passCount;
xlWorkSheet.Range["C" + rowNo.ToString()].Value = failCount;
xlWorkSheet.Range["A" + rowNo.ToString()].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
xlWorkSheet.Range["A" + rowNo.ToString()].WrapText = true;
xlWorkSheet.Range["A" + rowNo.ToString()].InsertIndent(5);
}
workSheet_range = xlWorkSheet.Range["A42", "C" + rowNo];
workSheet_range.Borders.Color = System.Drawing.Color.Black.ToArgb();
workSheet_range.WrapText = true;
workSheet_range = xlWorkSheet.Range["B43", "C" + rowNo];
workSheet_range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
myCharts = xlWorkSheet.ChartObjects(Type.Missing);
myCharts1 = myCharts.Add(340, 610, 370, 210);
oChart = myCharts1.Chart;
chartRange = xlWorkSheet.Range["A42", "C" + rowNo];
oChart.SetSourceData(chartRange);
oChart.PlotBy = XlRowCol.xlColumns;
oChart.ApplyDataLabels(XlDataLabelsType.xlDataLabelsShowNone);
oChart.HasLegend = true;
oChart.HasTitle = true;
oChart.ChartTitle.Text = "Summary report by category";
Excel.Axis axis = (Excel.Axis)oChart.Axes(
Excel.XlAxisType.xlValue,
Excel.XlAxisGroup.xlPrimary);
axis.HasTitle = true;
axis.AxisTitle.Text = "No of Test Cases";
axis = (Excel.Axis)oChart.Axes(
Excel.XlAxisType.xlCategory,
Excel.XlAxisGroup.xlPrimary);
axis.HasTitle = true;
axis.AxisTitle.Text = "Category";
string categoryChartPath = summaryChartsFolder + "\\SummaryReport_Category.jpg";
oChart.Export(categoryChartPath, "jpg", Missing.Value);
}
/// <summary>
/// Applies the trained neural network model to the selected predictor
/// input data and displays the results in Excel.
///
/// The results consists of 3 columns - the first contains the selected
/// training set input (or predictor) values, the second the selected
/// training set target values and the third contains the trained model
/// output responses to the given input values. An XY-Scatter graph of
/// the data is also produced.
/// </summary>
/// <param name="net">the trained neural network</param>
///
private void ShowOutputInExcel(NeuralNet net)
{
List <double> dX = new List <double>();
List <double> dM = new List <double>();
// plot the model and target data in Excel
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
// open Excel and add a worksheet
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(Type.Missing);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
// add a header line
xlWorkSheet.Cells[1, 1] = "";
xlWorkSheet.Cells[1, 2] = "target";
xlWorkSheet.Cells[1, 3] = "model";
// add the model and training data
for (int i = 0; i < mInputVecs.Count; i++)
{
// calculate the model response value given the predictor value from the training set
dX = mInputVecs[i];
net.GetResponse(dX, dM);
// the required values are stored in vectors and need re-scaling
double xValue = dX[0] * ScaleFactor;
double yValue = mTargetVecs[i][0] * ScaleFactor;
double mValue = dM[0] * ScaleFactor;
// write out the results
xlWorkSheet.Cells[i + 2, 1] = xValue.ToString("G16");
xlWorkSheet.Cells[i + 2, 2] = yValue.ToString("G16");
xlWorkSheet.Cells[i + 2, 3] = mValue.ToString("G16");
}
// plot the data as an xy-scatter graph
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject modelChart = (Excel.ChartObject)xlCharts.Add(200, 10, 450, 300);
Excel.Chart chartPage = modelChart.Chart;
chartRange = xlWorkSheet.get_Range("A1", "C" + (mInputVecs.Count + 1).ToString());
chartPage.SetSourceData(chartRange);
chartPage.ChartType = Excel.XlChartType.xlXYScatter;
// set the axis labels
List <string> colNames = new List <string>();
mDataTab.GetColumnNames(colNames);
Excel.Axis xAxis = (Excel.Axis)chartPage.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
xAxis.HasTitle = true;
xAxis.AxisTitle.Text = colNames[PredictorIdx];
Excel.Axis yAxis = (Excel.Axis)chartPage.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
yAxis.HasTitle = true;
yAxis.AxisTitle.Text = colNames[ResponseIdx];
// set the graph title
chartPage.HasTitle = true;
chartPage.ChartTitle.Font.Size = 12;
chartPage.ChartTitle.Text = GraphTitle();
// make Excel visible to the user
xlApp.Visible = true;
// release resources
ReleaseObject(xlWorkSheet);
ReleaseObject(xlWorkBook);
ReleaseObject(xlApp);
}
/// <summary>
/// 给工作表添加一个图表
/// </summary>
/// <param name="firstRange">数据工作表</param>
/// <param name="xData">x轴数据区域：格式A1:A14</param>
/// <param name="ydata">y轴数据区域：格式B1:E14</param>
/// <param name="chartName">图标名称</param>
/// <param name="xname">y轴名称</param>
/// <param name="yname">y轴名称</param>
/// <param name="chartType">图表类型</param>
public static void createInnerChart(Excel.Worksheet dataSheet, List <ChartStuct> charts, string chartName, string categoryName)
{
Excel.ChartObjects ChartObjects = (Excel.ChartObjects)dataSheet.ChartObjects(Missing.Value);
int cols = 0;
foreach (ChartStuct chartStuct in charts)
{
cols += chartStuct.dataRange.Columns.Count;
}
Excel.ChartObject chartObject = ChartObjects.Add(cols * 60, 30, 800, 350);
if (charts.Count < 1)
{
return; //无数不处理
}
//绘制第一维图表
Excel.Range firstRange = charts[0].dataRange;
Excel.XlChartType chartType = charts[0].chartType;
string yname = charts[0].yname;
chartObject.Chart.ChartWizard(firstRange, chartType, Missing.Value, Excel.XlRowCol.xlColumns, 1, 1, true, chartName, categoryName, yname, Missing.Value);
chartObject.Chart.ChartArea.ClearFormats();
chartObject.Chart.ChartType = chartType;
chartObject.Chart.ChartArea.Fill.BackColor.SchemeColor = 2;//整个图表区背景
//Console.WriteLine(chartObject.Chart.ChartArea.Fill.ForeColor.SchemeColor);
//Console.WriteLine(chartObject.Chart.ChartArea.Fill.ForeColor.RGB);
chartObject.Chart.ChartArea.Fill.ForeColor.SchemeColor = 2;
//Console.WriteLine(chartObject.Chart.ChartArea.Fill.ForeColor.RGB);
//Console.WriteLine(chartObject.Chart.PlotArea.Interior.ColorIndex);
//chartObject.Chart.PlotArea.Interior.PatternColorIndex = 19;
chartObject.Chart.PlotArea.Interior.ColorIndex = 2;//图例区域背景
Excel.Axis firstAxis = (Excel.Axis)chartObject.Chart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
firstAxis.HasMajorGridlines = true;//不显示横向网格线
Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chartObject.Chart.SeriesCollection(Type.Missing);
int sc = seriesCollection.Count;
if (seriesCollection.Count > 0)
{
Excel.Series seires1 = (Excel.Series)seriesCollection.Item(1);//具体数据图表区颜色
//seires1.Fill.BackColor.SchemeColor = 18;
seires1.Fill.ForeColor.SchemeColor = 46;
seires1.MarkerForegroundColorIndex = Excel.XlColorIndex.xlColorIndexNone;
seires1.HasLeaderLines = false;
}
//绘制第二维图表
//修改第一维图表的各个序列的图例,这里有个bug，会用最后的标题覆盖前面的
// for (int i = 0; i < charts[0].seriesTitles.Length; i++)
//{
//string stitle = charts[0].seriesTitles[i];
//Excel.Series tmpSeires = (Excel.Series)seriesCollection.Item(1);
//tmpSeires.Name = stitle;
//}
//绘制第二个图表,多维这里设置 当有图表类型为XlChartType.xl3DColumn或其他3d效果时，抛 xlSeries.AxisGroup = Excel.XlAxisGroup.xlSecondary;异常
for (int i = 1; i < charts.Count; i++)
{
ChartStuct cs = charts[i];
string stitle = cs.seriesTitles[0];
Excel.Series xlSeries = (Excel.Series)seriesCollection.NewSeries();
xlSeries.Name = stitle;
xlSeries.HasLeaderLines = true;
xlSeries.Fill.ForeColor.SchemeColor = 46;
try
{
xlSeries.AxisGroup = Excel.XlAxisGroup.xlSecondary;
}
catch (Exception eee) {
Console.WriteLine(eee.Message);
}
xlSeries.ChartType = cs.chartType;
xlSeries.Values = cs.dataRange;
Excel.Axis valueAxis = (Excel.Axis)chartObject.Chart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlSecondary);
valueAxis.HasTitle = true;
valueAxis.AxisTitle.Text = cs.yname;
}
}
private void CreateChart(Excel._Workbook m_Book, Excel._Worksheet m_Sheet, int num)
{
Excel.Range oResizeRange;
Excel.Series oSeries;
m_Book.Charts.Add(Missing.Value, Missing.Value, 1, Missing.Value);
m_Book.ActiveChart.ChartType = Excel.XlChartType.xlLine;//设置图形
//设置数据取值范围
m_Book.ActiveChart.SetSourceData(m_Sheet.get_Range("A2", "C" + num.ToString()), Excel.XlRowCol.xlColumns);
//m_Book.ActiveChart.Location(Excel.XlChartLocation.xlLocationAutomatic, title);
//以下是给图表放在指定位置
m_Book.ActiveChart.Location(Excel.XlChartLocation.xlLocationAsObject, m_Sheet.Name);
oResizeRange = (Excel.Range)m_Sheet.Rows.get_Item(10, Missing.Value);
m_Sheet.Shapes.Item("Chart 1").Top = (float)(double)oResizeRange.Top; //调图表的位置上边距
oResizeRange = (Excel.Range)m_Sheet.Columns.get_Item(6, Missing.Value); //调图表的位置左边距
// m_Sheet.Shapes.Item("Chart 1").Left = (float)(double)oResizeRange.Left;
m_Sheet.Shapes.Item("Chart 1").Width = 400; //调图表的宽度
m_Sheet.Shapes.Item("Chart 1").Height = 250; //调图表的高度
m_Book.ActiveChart.PlotArea.Interior.ColorIndex = 19; //设置绘图区的背景色
m_Book.ActiveChart.PlotArea.Border.LineStyle = Excel.XlLineStyle.xlLineStyleNone; //设置绘图区边框线条
m_Book.ActiveChart.PlotArea.Width = 400; //设置绘图区宽度
//m_Book.ActiveChart.ChartArea.Interior.ColorIndex = 10; //设置整个图表的背影颜色
//m_Book.ActiveChart.ChartArea.Border.ColorIndex = 8;// 设置整个图表的边框颜色
m_Book.ActiveChart.ChartArea.Border.LineStyle = Excel.XlLineStyle.xlLineStyleNone;//设置边框线条
m_Book.ActiveChart.HasDataTable = false;
//设置Legend图例的位置和格式
m_Book.ActiveChart.Legend.Top = 20.00; //具体设置图例的上边距
m_Book.ActiveChart.Legend.Left = 60.00; //具体设置图例的左边距
m_Book.ActiveChart.Legend.Interior.ColorIndex = Excel.XlColorIndex.xlColorIndexNone;
m_Book.ActiveChart.Legend.Width = 150;
m_Book.ActiveChart.Legend.Font.Size = 9.5;
//m_Book.ActiveChart.Legend.Font.Bold = true;
m_Book.ActiveChart.Legend.Font.Name = "宋体";
//m_Book.ActiveChart.Legend.Position = Excel.XlLegendPosition.xlLegendPositionTop;//设置图例的位置
m_Book.ActiveChart.Legend.Border.LineStyle = Excel.XlLineStyle.xlLineStyleNone;//设置图例边框线条
//设置X轴的显示
Excel.Axis xAxis = (Excel.Axis)m_Book.ActiveChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
xAxis.MajorGridlines.Border.LineStyle = Excel.XlLineStyle.xlDot;
xAxis.MajorGridlines.Border.ColorIndex = 1;//gridLine横向线条的颜色
xAxis.HasTitle = false;
xAxis.MinimumScale = 1500;
xAxis.MaximumScale = 6000;
xAxis.TickLabels.Font.Name = "宋体";
xAxis.TickLabels.Font.Size = 9;
//设置Y轴的显示
Excel.Axis yAxis = (Excel.Axis)m_Book.ActiveChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
yAxis.TickLabelSpacing = 30;
yAxis.TickLabels.NumberFormat = "M月D日";
yAxis.TickLabels.Orientation = Excel.XlTickLabelOrientation.xlTickLabelOrientationHorizontal;//Y轴显示的方向,是水平还是垂直等
yAxis.TickLabels.Font.Size = 8;
yAxis.TickLabels.Font.Name = "宋体";
//m_Book.ActiveChart.Floor.Interior.ColorIndex = 8;
/***以下是设置标题*****
* m_Book.ActiveChart.HasTitle=true;
* m_Book.ActiveChart.ChartTitle.Text = "净值指数";
* m_Book.ActiveChart.ChartTitle.Shadow = true;
* m_Book.ActiveChart.ChartTitle.Border.LineStyle = Excel.XlLineStyle.xlContinuous;
*/
oSeries = (Excel.Series)m_Book.ActiveChart.SeriesCollection(1);
oSeries.Border.ColorIndex = 45;
oSeries.Border.Weight = Excel.XlBorderWeight.xlThick;
oSeries = (Excel.Series)m_Book.ActiveChart.SeriesCollection(2);
oSeries.Border.ColorIndex = 9;
oSeries.Border.Weight = Excel.XlBorderWeight.xlThick;
}
// データテーブルをエクセルへエクスポート（ＸＲ管理図・ヒストグラム付き）
public void ExportToExcelWithXrChart(DataTable dt)
{
if (dt.Rows.Count == 0)
{
return;
}
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
// column headings
for (int i = 0; i < dt.Columns.Count; i++)
{
xlWorkSheet.Cells[1, (i + 1)] = dt.Columns[i].ColumnName;
}
// rows
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
xlWorkSheet.Cells[(i + 2), (j + 1)] = dt.Rows[i][j];
}
}
int row = xlWorkSheet.UsedRange.Rows.Count;
string address1 = "B1:B" + row.ToString() + ",K1:K" + row.ToString() + ",M1:N" + row.ToString();
string address2 = "B1:B" + row.ToString() + ",L1:L" + row.ToString();
string address3 = "AB41:AC53";
string address4 = "F1:J" + row.ToString();
// チャート１（Ｘ－Ｒ管理図１）
Excel.Range chartRange1;
Excel.ChartObjects xlCharts1 = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart1 = (Excel.ChartObject)xlCharts1.Add(800, 10, 600, 250);
Excel.Chart chartPage1 = myChart1.Chart;
chartRange1 = xlWorkSheet.get_Range(address1);
chartPage1.SetSourceData(chartRange1, misValue);
chartPage1.ChartType = Excel.XlChartType.xlLine;
chartPage1.HasLegend = false;
chartPage1.HasTitle = true;
chartPage1.ChartTitle.Text = "X " + dt.Rows[0]["inspect"].ToString() + " " + dt.Rows[0]["line"].ToString();
Excel.Axis xAxis1 = (Excel.Axis)chartPage1.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
xAxis1.CategoryType = Excel.XlCategoryType.xlCategoryScale;
Excel.SeriesCollection SeriesCollection1 = (Excel.SeriesCollection)myChart1.Chart.SeriesCollection(misValue);
Excel.Series s2 = SeriesCollection1.Item(2);
s2.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbCoral;
Excel.Series s3 = SeriesCollection1.Item(3);
s3.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbCoral;
// チャート２（Ｘ－Ｒ管理図２）
Excel.Range chartRange2;
Excel.ChartObjects xlCharts2 = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart2 = (Excel.ChartObject)xlCharts1.Add(800, 280, 600, 250);
Excel.Chart chartPage2 = myChart2.Chart;
chartRange2 = xlWorkSheet.get_Range(address2);
chartPage2.SetSourceData(chartRange2, misValue);
chartPage2.ChartType = Excel.XlChartType.xlLine;
chartPage2.HasLegend = false;
chartPage2.HasTitle = true;
chartPage2.ChartTitle.Text = "R " + dt.Rows[0]["inspect"].ToString() + " " + dt.Rows[0]["line"].ToString();
Excel.Axis xAxis2 = (Excel.Axis)chartPage2.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
xAxis2.CategoryType = Excel.XlCategoryType.xlCategoryScale;
// チャート３（ヒストグラム）
Excel.Range chartRange3;
Excel.ChartObjects xlCharts3 = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart3 = (Excel.ChartObject)xlCharts1.Add(800, 550, 350, 250);
Excel.Chart chartPage3 = myChart3.Chart;
string[,] formulas = new string[13, 3];
string[] formula1 = new string[]
{
"BIN",
"=MIN(" + address4 + ")",
"=AA42+(AA$53-AA$42)/10",
"=AA43+(AA$53-AA$42)/10",
"=AA44+(AA$53-AA$42)/10",
"=AA45+(AA$53-AA$42)/10",
"=AA46+(AA$53-AA$42)/10",
"=AA47+(AA$53-AA$42)/10",
"=AA48+(AA$53-AA$42)/10",
"=AA49+(AA$53-AA$42)/10",
"=AA50+(AA$53-AA$42)/10",
"=AA51+(AA$53-AA$42)/10",
"=MAX(" + address4 + ")",
};
string[] formula2 = new string[]
{
@"LABEL",
@"=TEXT(AA42,""0.0"")",
@"=TEXT(AA42,""0.0"")&"" - ""&TEXT(AA43,""0.0"")",
@"=TEXT(AA43,""0.0"")&"" - ""&TEXT(AA44,""0.0"")",
@"=TEXT(AA44,""0.0"")&"" - ""&TEXT(AA45,""0.0"")",
@"=TEXT(AA45,""0.0"")&"" - ""&TEXT(AA46,""0.0"")",
@"=TEXT(AA46,""0.0"")&"" - ""&TEXT(AA47,""0.0"")",
@"=TEXT(AA47,""0.0"")&"" - ""&TEXT(AA48,""0.0"")",
@"=TEXT(AA48,""0.0"")&"" - ""&TEXT(AA49,""0.0"")",
@"=TEXT(AA49,""0.0"")&"" - ""&TEXT(AA50,""0.0"")",
@"=TEXT(AA50,""0.0"")&"" - ""&TEXT(AA51,""0.0"")",
@"=TEXT(AA51,""0.0"")&"" - ""&TEXT(AA52,""0.0"")",
@"=TEXT(AA53,""0.0"")"
};
string[] formula3 = new string[]
{
@"FREQUENCY",
@"=COUNTIF(" + address4 + @",""<=""&AA42)",
@"=COUNTIF(" + address4 + @","">""&AA42)-COUNTIF(" + address4 + @","">""&AA43)",
@"=COUNTIF(" + address4 + @","">""&AA43)-COUNTIF(" + address4 + @","">""&AA44)",
@"=COUNTIF(" + address4 + @","">""&AA44)-COUNTIF(" + address4 + @","">""&AA45)",
@"=COUNTIF(" + address4 + @","">""&AA45)-COUNTIF(" + address4 + @","">""&AA46)",
@"=COUNTIF(" + address4 + @","">""&AA46)-COUNTIF(" + address4 + @","">""&AA47)",
@"=COUNTIF(" + address4 + @","">""&AA47)-COUNTIF(" + address4 + @","">""&AA48)",
@"=COUNTIF(" + address4 + @","">""&AA48)-COUNTIF(" + address4 + @","">""&AA49)",
@"=COUNTIF(" + address4 + @","">""&AA49)-COUNTIF(" + address4 + @","">""&AA50)",
@"=COUNTIF(" + address4 + @","">""&AA50)-COUNTIF(" + address4 + @","">""&AA51)",
@"=COUNTIF(" + address4 + @","">""&AA51)-COUNTIF(" + address4 + @","">=""&AA52)",
@"=COUNTIF(" + address4 + @","">=""&AA53)"
};
for (int i = 0; i < 13; i++)
{
xlWorkSheet.Cells[41 + i, 27].Formula = formula1[i];
xlWorkSheet.Cells[41 + i, 28].Formula = formula2[i];
xlWorkSheet.Cells[41 + i, 29].Formula = formula3[i];
}
chartRange3 = xlWorkSheet.get_Range(address3);
chartPage3.SetSourceData(chartRange3, misValue);
chartPage3.ChartType = Excel.XlChartType.xlColumnClustered;
chartPage3.HasLegend = false;
chartPage3.HasTitle = true;
chartPage3.ChartTitle.Text = "Frequency " + dt.Rows[0]["inspect"].ToString() + " " + dt.Rows[0]["line"].ToString();
Excel.ChartGroup ChartGroup1 = (Excel.ChartGroup)myChart3.Chart.ChartGroups(1);
ChartGroup1.GapWidth = 0;
xlApp.Visible = true;
}
internal void newChart(int n)
{
Excel.Range chartText1 = BottomCell().get_Offset(2, 0).get_Resize(1, 3);
Excel.Range chartText2 = chartText1.get_Offset(0, 6).get_Resize(1, 8);
((Excel.Range)chartText1[1, 1]).Value2 = "The plots below show the actual energy consumption versus the independent variables for the model year.";
((Excel.Range)chartText2[1, 1]).Value2 = "The line labeled \"Actuals\" in the plot below has not been adjusted. This is the original data entered by the user. The line labeled \"Model\" is the predicted energy consumption using the model selected above.";
chartText1.EntireRow.RowHeight = 75;
chartText1.EntireRow.Font.Bold = true;
chartText1.EntireRow.WrapText = true;
chartText1.Merge();
chartText2.Merge();
Excel.Range start = BottomCell().get_Offset(1, 0);
double topleft;
if (!double.TryParse(start.Top.ToString(), out topleft))
{
topleft = 0;
}
start.EntireRow.RowHeight = Utilities.Constants.CHART_HEIGHT * 1.1;
ArrayList lst = this.Source.Models.ModelSort();
string[] variables = new string[] {};
int longest = 0;
foreach (Utilities.Model model in lst)
{
if (model.VariableNames.Length > longest)
{
longest = model.VariableNames.Length;
variables = model.VariableNames;
}
}
object[] sources = new object[variables.Length];
int k = 0;
for (int i = 0; i < variables.Length; i++)
{
double[] temp = new double[Source.knownXs.Rows.Count];
foreach (System.Data.DataRow row in Source.knownXs.Rows)
{
temp[k] = Convert.ToDouble(row.ItemArray[i]);
k++;
}
k = 0;
sources[i] = temp;
}
for (int i = 0; i < variables.Length; i++)
{
if (i.Equals(0))
{
Excel.ChartObject CO = ((Excel.ChartObjects)WS.ChartObjects(System.Type.Missing))
.Add(10, topleft, Utilities.Constants.CHART_WIDTH, Utilities.Constants.CHART_HEIGHT);
CO.Chart.HasLegend = false;
CO.Chart.ChartType = Excel.XlChartType.xlXYScatter;
CO.Chart.HasTitle = false;
Excel.Series newSeries = ((Excel.SeriesCollection)CO.Chart.SeriesCollection(System.Type.Missing)).NewSeries();
newSeries.Values = Source.Ys;
newSeries.XValues = sources[i];
Excel.Axis COYaxis = (Excel.Axis)CO.Chart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
COYaxis.TickLabels.NumberFormat = "###,##0";
COYaxis.HasTitle = true;
COYaxis.AxisTitle.Text = WS.Name;
Excel.Axis COXaxis = (Excel.Axis)CO.Chart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
COXaxis.TickLabels.NumberFormat = "###,##0";
COXaxis.HasTitle = true;
COXaxis.AxisTitle.Text = variables[i];
}
else
{
Excel.Range afterFirst = BottomCell().get_Offset(1, 0);
afterFirst.EntireRow.RowHeight = Utilities.Constants.CHART_HEIGHT * 1.1;
double topleftinner;
if (!double.TryParse(afterFirst.Top.ToString(), out topleftinner))
{
topleftinner = 0;
}
Excel.ChartObject CO = ((Excel.ChartObjects)WS.ChartObjects(System.Type.Missing))
.Add(10, topleftinner, Utilities.Constants.CHART_WIDTH, Utilities.Constants.CHART_HEIGHT);
CO.Chart.HasLegend = false;
CO.Chart.ChartType = Excel.XlChartType.xlXYScatter;
CO.Chart.HasTitle = false;
Excel.Series newSeries = ((Excel.SeriesCollection)CO.Chart.SeriesCollection(System.Type.Missing)).NewSeries();
newSeries.Values = this.Source.Ys;
newSeries.XValues = sources[i];
Excel.Axis COYaxis = (Excel.Axis)CO.Chart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
COYaxis.TickLabels.NumberFormat = "###,##0";
COYaxis.HasTitle = true;
COYaxis.AxisTitle.Text = WS.Name;
Excel.Axis COXaxis = (Excel.Axis)CO.Chart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
COXaxis.TickLabels.NumberFormat = "###,##0";
COXaxis.HasTitle = true;
COXaxis.AxisTitle.Text = variables[i];
}
}
for (int j = 0; j < Math.Min(n, lst.Count); j++)
{
Utilities.Model mdl = this.Source.Models.Item(j);
Excel.ChartObject CO = ((Excel.ChartObjects)WS.ChartObjects(System.Type.Missing))
.Add(400, topleft, Utilities.Constants.CHART_WIDTH, Utilities.Constants.CHART_HEIGHT);
CO.Placement = Excel.XlPlacement.xlMove;
CO.Name = "Model " + mdl.ModelNumber;
CO.Visible = false;
CO.Chart.ChartType = Excel.XlChartType.xlLineMarkers;
CO.Chart.ChartStyle = 5;
Excel.Axis CharObj2Yaxis = (Excel.Axis)CO.Chart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
CharObj2Yaxis.TickLabels.NumberFormat = "###,##0";
CharObj2Yaxis.HasTitle = true;
CharObj2Yaxis.AxisTitle.Text = "Input Interval";
Excel.Axis CharObj2Xaxis = (Excel.Axis)CO.Chart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
CharObj2Xaxis.TickLabels.NumberFormat = "###,##0";
CharObj2Xaxis.HasTitle = true;
CharObj2Xaxis.AxisTitle.Text = WS.Name;
//Modified by suman: TFS ticket :69333
Excel.Series newSeries = ((Excel.SeriesCollection)CO.Chart.SeriesCollection(System.Type.Missing)).NewSeries();
newSeries.Values = this.Source.Ys;
newSeries.Name = "Actuals";
Excel.Series modelSeries = ((Excel.SeriesCollection)CO.Chart.SeriesCollection(System.Type.Missing)).NewSeries();
modelSeries.Values = mdl.PredictedYs();
modelSeries.Name = "Model " + mdl.ModelNumber;
newSeries.Format.Line.ForeColor.RGB = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.DarkRed);// 0x4177B8;//12089153; // Calculated from color calculator
newSeries.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleSquare;
newSeries.MarkerBackgroundColorIndex = (Microsoft.Office.Interop.Excel.XlColorIndex) 9;
newSeries.Format.Line.Transparency = 1.0f;
if (mdl == this.Source.BestModel())
{
CO.Visible = true;
}
CO.Chart.AutoScaling = true;
CO.Chart.Refresh();
CO.Chart.HasTitle = true;
CO.Chart.ChartTitle.Text = "Comparison of Actual and Model Data";
CO.Chart.Legend.Position = Excel.XlLegendPosition.xlLegendPositionTop;
}
}
public BubbleRelationShip()
{
InitializeComponent();
ratio = double.Parse(textBox_Bandwidth.Text);;
Graphic.RangeData(ref str, ref rows, ref cols);
worksheet = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet);
Excel.Range activecells = Globals.ThisAddIn.Application.ActiveCell;
start_col = activecells.Column;
start_row = activecells.Row;
int i, j;
for (j = 1; j < cols; j++)
{
((Excel.Range)worksheet.Cells[start_row + rows + 1, start_col + j]).Value2 = str[0, j];
}
for (j = 1; j < rows; j++)
{
((Excel.Range)worksheet.Cells[start_row + rows + 1 + j, start_col]).Value2 = str[j, 0];
}
for (i = 1; i < rows; i++)
{
for (j = 1; j < cols; j++)
{
((Excel.Range)worksheet.Cells[start_row + rows + 1 + i, start_col + j]).Value2 = i;// str[i, 0];
}
}
((Excel.Range)worksheet.Cells[start_row + rows + 1 + rows, start_col]).Value2 = "Assistant1";
for (j = 1; j < rows; j++)
{
((Excel.Range)worksheet.Cells[start_row + rows + 1 + rows, start_col + j]).Value2 = 0;
}
((Excel.Range)worksheet.Cells[start_row + rows + 1 + rows + 1, start_col]).Value2 = "Assistant2";
for (j = 1; j < rows; j++)
{
((Excel.Range)worksheet.Cells[start_row + rows + 1 + rows + 1, start_col + j]).Value2 = j;
}
Excel.Range c1 = (Excel.Range)worksheet.Cells[start_row + rows + 1, start_col];
Excel.Range c2 = (Excel.Range)worksheet.Cells[start_row + rows + 1 + rows + 1, start_col + rows - 1];
string ChartOrder = "BubbleRelationShip" + Convert.ToString(Nchart);
chart = worksheet.Controls.AddChart(250, 50, 450, 400, ChartOrder);
Nchart = Nchart + 1;
chart.SetSourceData(worksheet.get_Range(c1, c2), Excel.XlRowCol.xlRows);
chart.ChartType = Excel.XlChartType.xlXYScatter;
//double ratio = 21;
//double Max_size = 0;
Max_size = Double.MinValue;
Min_size = Double.MaxValue;
for (i = 1; i < rows; i++)
{
for (j = 1; j < cols; j++)
{
if (double.Parse(str[i, j]) > Max_size)
{
Max_size = double.Parse(str[i, j]);
}
if (double.Parse(str[i, j]) < Min_size)
{
Min_size = double.Parse(str[i, j]);
}
}
}
Max_size = Math.Sqrt(Max_size);
Min_size = Math.Sqrt(Min_size);
Excel.SeriesCollection series = (Excel.SeriesCollection)chart.SeriesCollection();
Excel.Point point;
Excel.Series Sseries;
int SquareSize;
RGB0 = System.Drawing.Color.FromArgb(255, 248, 118, 109);
for (i = 1; i < rows; i++)
{
Sseries = series.Item(i);
Sseries.Format.Line.Visible = Office.MsoTriState.msoFalse;
//if (comboBox_FourierMethod.SelectedItem.Equals("Circle"))
//{
Sseries.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleCircle;
//}
//else if (comboBox_FourierMethod.SelectedItem.Equals("Square"))
//{
// Sseries.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleSquare;
//}
Sseries.MarkerBackgroundColor = System.Drawing.Color.FromArgb(255, RGB0.B, RGB0.G, RGB0.R).ToArgb();
Sseries.MarkerForegroundColor = System.Drawing.Color.FromArgb(255, RGB0.B, RGB0.G, RGB0.R).ToArgb();
Sseries.Format.Fill.Solid();
Sseries.Format.Fill.Visible = Office.MsoTriState.msoCTrue;
Sseries.Format.Fill.BackColor.RGB = System.Drawing.Color.FromArgb(255, 255, 255, 255).ToArgb();
Sseries.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(255, RGB0.B, RGB0.G, RGB0.R).ToArgb();
Sseries.Format.Fill.Transparency = 0.2F;
//Sseries.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleCircle;
for (j = 1; j < cols; j++)
{
point = (Excel.Point)Sseries.Points(j);
//SquareSize = (int)((double.Parse(str[i, j])-Min_size) / (Max_size-Min_size) * ratio) + 2;
SquareSize = (int)((Math.Sqrt(double.Parse(str[i, j]) * ratio) / Max_size) * Min_MarkerSize + 2); // - Min_size) / (Max_size - Min_size) * ratio) + 2;
//SquareSize = (int)((double.Parse(str[i, j]) / (Max_size * Max_size) * ratio) + 2);// - Min_size) / (Max_size - Min_size) * ratio) + 2;
if (SquareSize > 72)
{
SquareSize = 72;
}
point.MarkerSize = SquareSize;
}
}
Excel.Series SseriesX = series.Item(rows);
for (j = 1; j < cols; j++)
{
point = (Excel.Point)SseriesX.Points(j);
point.HasDataLabel = true;
point.DataLabel.Text = str[0, j];
point.DataLabel.Position = Excel.XlDataLabelPosition.xlLabelPositionBelow;
point.DataLabel.Format.TextFrame2.TextRange.Characters.Font.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 0, 0).ToArgb();
point.DataLabel.Format.TextFrame2.TextRange.Font.NameComplexScript = "Times New Roman";
point.DataLabel.Format.TextFrame2.TextRange.Font.NameFarEast = "Times New Roman";
point.DataLabel.Format.TextFrame2.TextRange.Font.Name = "Times New Roman";
point.DataLabel.Format.TextFrame2.TextRange.Font.Size = 10;
point.DataLabel.Format.TextFrame2.TextRange.Font.Bold = Office.MsoTriState.msoFalse;
}
SseriesX.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleNone;
Excel.Series SseriesY = series.Item(rows + 1);
c1 = (Excel.Range)worksheet.Cells[start_row + rows + 1 + rows, start_col + +1];
c2 = (Excel.Range)worksheet.Cells[start_row + rows + 1 + rows, start_col + rows - 1];
SseriesY.XValues = worksheet.get_Range(c1, c2);
c1 = (Excel.Range)worksheet.Cells[start_row + rows + 1 + rows + 1, start_col + 1];
c2 = (Excel.Range)worksheet.Cells[start_row + rows + 1 + rows + 1, start_col + rows - 1];
SseriesY.Values = worksheet.get_Range(c1, c2);
SseriesY.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleNone;
for (j = 1; j < rows; j++)
{
point = (Excel.Point)SseriesY.Points(j);
point.HasDataLabel = true;
point.DataLabel.Text = str[j, 0];
point.DataLabel.Position = Excel.XlDataLabelPosition.xlLabelPositionLeft;
point.DataLabel.Format.TextFrame2.TextRange.Characters.Font.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 0, 0).ToArgb();
point.DataLabel.Format.TextFrame2.TextRange.Font.NameComplexScript = "Times New Roman";
point.DataLabel.Format.TextFrame2.TextRange.Font.NameFarEast = "Times New Roman";
point.DataLabel.Format.TextFrame2.TextRange.Font.Name = "Times New Roman";
point.DataLabel.Format.TextFrame2.TextRange.Font.Size = 10;
point.DataLabel.Format.TextFrame2.TextRange.Font.Bold = Office.MsoTriState.msoFalse;
}
//***************************************************** Style**********************************************
chart.PlotArea.Format.Fill.Visible = Office.MsoTriState.msoCTrue;
chart.PlotArea.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(229, 229, 229).ToArgb();
chart.PlotArea.Format.Fill.Transparency = 0;
chart.PlotArea.Format.Line.Visible = Office.MsoTriState.msoFalse;
// ChartArea Line
chart.ChartArea.Format.Line.Visible = Office.MsoTriState.msoFalse;
//chart.ChartArea.Height = 340.157480315;
//chart.ChartArea.Width = 380.5039370079;
// Chart Type: XYScatter
// Add GridLinesMinorMajor
chart.SetElement(Office.MsoChartElementType.msoElementPrimaryValueGridLinesMinorMajor);
chart.SetElement(Office.MsoChartElementType.msoElementPrimaryCategoryGridLinesMinorMajor);
chart.SetElement(Office.MsoChartElementType.msoElementPrimaryValueAxisTitleAdjacentToAxis);
chart.SetElement(Office.MsoChartElementType.msoElementPrimaryCategoryAxisTitleAdjacentToAxis);
//y axis
Excel.Axis axis = (Excel.Axis)chart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
axis.MinorUnit = 1;
axis.MajorUnit = 1;
axis.MajorGridlines.Format.Line.Visible = Office.MsoTriState.msoCTrue;
axis.MajorGridlines.Format.Line.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 255, 255).ToArgb();
axis.MajorGridlines.Format.Line.Weight = (float)0.25;
axis.MajorGridlines.Format.Line.DashStyle = Office.MsoLineDashStyle.msoLineSolid;
axis.MinorGridlines.Format.Line.Visible = Office.MsoTriState.msoFalse;
//axis.MinorGridlines.Format.Line.ForeColor.RGB = System.Drawing.Color.FromArgb(242, 242, 242).ToArgb();
//axis.MinorGridlines.Format.Line.Weight = (float)0.25;
//axis.MinorGridlines.Format.Line.DashStyle = Office.MsoLineDashStyle.msoLineSolid;
axis.Format.Line.Visible = Office.MsoTriState.msoFalse;
axis.HasTitle = true;
axis.AxisTitle.Text = "y axis";
axis.HasDisplayUnitLabel = false;
//axis.TickLabels.Font.Color = System.Drawing.Color.FromArgb(0, 0, 0).ToArgb();
//axis.TickLabels.Font.Name = "Times New Roman";
//axis.TickLabels.Font.Size = 10;
axis.TickLabelPosition = Excel.XlTickLabelPosition.xlTickLabelPositionNone;
//axis.AxisTitle.Format.TextFrame2.TextRange.Characters.Font.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 0, 0).ToArgb();
//axis.AxisTitle.Format.TextFrame2.TextRange.Font.NameComplexScript = "Times New Roman";
//axis.AxisTitle.Format.TextFrame2.TextRange.Font.NameFarEast = "Times New Roman";
//axis.AxisTitle.Format.TextFrame2.TextRange.Font.Name = "Times New Roman";
//axis.AxisTitle.Format.TextFrame2.TextRange.Font.Size = 10;
//axis.AxisTitle.Format.TextFrame2.TextRange.Font.Bold = Office.MsoTriState.msoFalse;
//x axis
axis = (Excel.Axis)chart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
axis.MaximumScale = cols;
axis.MinimumScale = 0;
//axis.AxisBetweenCategories = false;
axis.MinorUnit = 1;
axis.MajorUnit = 1;
axis.MinorGridlines.Format.Line.Visible = Office.MsoTriState.msoFalse;
//axis.MajorGridlines.Format.Line.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 255, 255).ToArgb();
//axis.MajorGridlines.Format.Line.Weight = (float)1.25;
//axis.MajorGridlines.Format.Line.DashStyle = Microsoft.Office.Core.MsoLineDashStyle.msoLineSolid;
axis.MajorGridlines.Format.Line.Visible = Office.MsoTriState.msoCTrue;
axis.MajorGridlines.Format.Line.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 255, 255).ToArgb();
axis.MajorGridlines.Format.Line.Weight = (float)0.25;
axis.MajorGridlines.Format.Line.DashStyle = Office.MsoLineDashStyle.msoLineSolid;
axis.Format.Line.Visible = Office.MsoTriState.msoFalse;
axis.HasTitle = true;
axis.AxisTitle.Text = "x axis";
//axis.TickLabels.Font.Color = System.Drawing.Color.FromArgb(0, 0, 0).ToArgb();
//axis.TickLabels.Font.Name = "Times New Roman";
//axis.TickLabels.Font.Size = 10;
axis.TickLabelPosition = Excel.XlTickLabelPosition.xlTickLabelPositionNone;
//axis.AxisTitle.Format.TextFrame2.TextRange.Characters.Font.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 0, 0).ToArgb();
//axis.AxisTitle.Format.TextFrame2.TextRange.Font.NameComplexScript = "Times New Roman";
//axis.AxisTitle.Format.TextFrame2.TextRange.Font.NameFarEast = "Times New Roman";
//axis.AxisTitle.Format.TextFrame2.TextRange.Font.Name = "Times New Roman";
//axis.AxisTitle.Format.TextFrame2.TextRange.Font.Size = 10;
//axis.AxisTitle.Format.TextFrame2.TextRange.Font.Bold = Office.MsoTriState.msoFalse;
chart.HasLegend = false;
chart.HasTitle = false;
//chart.ChartTitle.Delete();
chart.Refresh();
//worksheet.Activate();
}
/// <summary>
/// 根据每天不同的挖深情况，移动挖深线的位置
/// </summary>
/// <param name="ExcavationLineAndTextBox">表示挖深深度的直线，文本框中记录有“挖深”二字</param>
/// <param name="excavElevation"> 当天开挖标高 </param>
/// <param name="dateThisday">施工当天的日期</param>
/// <remarks></remarks>
private void MoveExcavation(SeriesTag_Incline ExcavationLineAndTextBox, float excavElevation,
DateTime dateThisday, TodayState State)
{
Shape iline = ExcavationLineAndTextBox.DepthLine;
Shape itextbox = ExcavationLineAndTextBox.DepthTextbox;
//
if (State == TodayState.DateMatched | State == TodayState.DateNotFound)
{
// 将当天的开挖标高转换为相对于测斜管顶部的深度值
float relativedepth = _inclineTopElevaion - excavElevation;
//Project_Expo.Elevation_GroundSurface - excavElevation
//
Excel.Axis axisY = Chart.Axes(Excel.XlAxisType.xlValue) as Excel.Axis;
float scalemax = (float)axisY.MaximumScale;
float scalemin = (float)axisY.MinimumScale;
//
float linetop = 0;
Excel.PlotArea plotA = Chart.PlotArea;
//将相对深度值转换与图表坐标系中的深度坐标值！！！！！
linetop =
(float)(plotA.InsideTop + plotA.InsideHeight * (relativedepth - scalemin) / (scalemax - scalemin));
//
iline.Visible = MsoTriState.msoCTrue;
iline.Top = linetop;
//
if (itextbox != null)
{
itextbox.Visible = MsoTriState.msoCTrue;
itextbox.Top = iline.Top - itextbox.Height;
//指示此基坑当前的状态，为向下开挖还是向上建造。
string strStatus = "";
if (P_ProcessRegionData == null || (!P_ProcessRegionData.HasBottomDate))
{
strStatus = "挖深/施工深度";
}
else
{
int CompareIndex = DateTime.Compare(dateThisday, P_ProcessRegionData.BottomDate);
if (CompareIndex < 0) //说明还未开挖到底
{
strStatus = "挖深";
}
else if (CompareIndex > 0) //说明已经开挖到底，正在施工上部结构
{
strStatus = "施工深度";
}
else if (CompareIndex == 0) //说明刚好开挖到基坑底部
{
strStatus = "开挖到底";
}
}
itextbox.TextFrame2.TextRange.Text = strStatus + Convert.ToString(relativedepth);
}
}
else
{
iline.Visible = MsoTriState.msoFalse;
//
if (itextbox != null)
{
itextbox.Visible = MsoTriState.msoFalse;
}
}
}