static public void SetChartDataColor(string chartName, object[,] datas, Excel.ChartObjects chObjs, int colIdx) { foreach (Excel.ChartObject chObj in chObjs) { if (chObj.Name.Equals(chartName)) { Excel.Series oSeries = chObj.Chart.SeriesCollection(1); for (int i = 1; i <= datas.GetLength(0); i++) { object oVal = datas.GetValue(i, colIdx); double val = Convert.ToDouble(oVal); Excel.Point oPoint = oSeries.Points(i); if (val >= 1) { oPoint.Format.Fill.ForeColor.RGB = 0x0000FF; } else if ((val < 1) && (val >= 0.5)) { oPoint.Format.Fill.ForeColor.RGB = 0xCC99FF; } } return; } } }
private void textBox_Bandwidth_TextChanged(object sender, EventArgs e) { if (textBox_Bandwidth.Text == "") { return; } Hrange = double.Parse(textBox_Bandwidth.Text); if (Hrange == 0) { return; } Excel.SeriesCollection series = (Excel.SeriesCollection)chart.SeriesCollection(); Excel.Series Sseries = series.Item(1); Excel.Point point; System.Drawing.Color RGB1 = System.Drawing.Color.FromArgb(255, 96, 157, 202); int[] HSV0 = new int[3]; Graphic.RGB2HSV(RGB0, ref HSV0); int[] HSV = new int[3]; HSV0.CopyTo(HSV, 0); double ratio; for (int i = 1; i < rows; i++) { point = (Excel.Point)Sseries.Points(i); point.Format.Fill.Solid(); point.Format.Fill.Visible = Office.MsoTriState.msoCTrue; point.Format.Fill.BackColor.RGB = System.Drawing.Color.FromArgb(255, 229, 229, 229).ToArgb(); ratio = (double.Parse(str[i, 2]) - Min_Value) / (Max_Value - Min_Value); HSV[2] = HSV0[2] + Convert.ToInt32(Hrange * (ratio - 0.5)); Graphic.HSV2RGB(ref RGB1, HSV); point.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(255, RGB1.B, RGB1.G, RGB1.R).ToArgb(); point.Format.Fill.Transparency = 0.0F; } chart.Refresh(); worksheet.Activate(); }
private void textBox_Bandwidth_TextChanged(object sender, EventArgs e) { if (textBox_Bandwidth.Text == "") { return; } ratio = double.Parse(textBox_Bandwidth.Text);; if (ratio == 0) { return; } Excel.SeriesCollection series = (Excel.SeriesCollection)chart.SeriesCollection(); Excel.Series Sseries = series.Item(1); Excel.Point point; int SquareSize; for (int i = 1; i < rows; i++) { point = (Excel.Point)Sseries.Points(i); //SquareSize = (int)((Math.Sqrt(double.Parse(str[i, 2]))));// - Min_size) / (Max_size - Min_size) * ratio) + 2; SquareSize = (int)((Math.Sqrt(double.Parse(str[i, 2])) / Max_size * ratio) + 2); //point = (Excel.Point)Sseries.Points(i); //SquareSize = (int)((double.Parse(str[i, 2]) - Min_size) / (Max_size - Min_size) * ratio) + 2; //PointSize[i - 1, 0] = SquareSize; //((Excel.Range)worksheet.Cells[Graphic.start_row + i, Graphic.start_col + 3]).Value2 = SquareSize; point.MarkerSize = SquareSize; } Sseries.MarkerBackgroundColor = System.Drawing.Color.FromArgb(255, 77, 175, 74).ToArgb(); Sseries.MarkerForegroundColor = System.Drawing.Color.FromArgb(255, 77, 175, 74).ToArgb(); Sseries.Format.Fill.Solid(); Sseries.Format.Fill.Visible = Office.MsoTriState.msoCTrue; Sseries.Format.Fill.BackColor.RGB = System.Drawing.Color.FromArgb(255, 229, 229, 229).ToArgb(); Sseries.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 77, 175, 74).ToArgb(); Sseries.Format.Fill.Transparency = 0.3F; chart.Refresh(); worksheet.Activate(); }
public ColumnColor() { 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; Excel.Range c1 = (Excel.Range)worksheet.Cells[start_row, start_col]; Excel.Range c2 = (Excel.Range)worksheet.Cells[start_row + rows - 1, start_col + cols - 2]; string ChartOrder = "ColorColumn" + Convert.ToString(Nchart); chart = worksheet.Controls.AddChart(250, 50, 450, 400, ChartOrder); Nchart = Nchart + 1; chart.SetSourceData(worksheet.get_Range(c1, c2), Excel.XlRowCol.xlColumns); chart.ChartType = Excel.XlChartType.xlColumnClustered; Excel.SeriesCollection series = (Excel.SeriesCollection)chart.SeriesCollection(); if (series.Count == 2) { Excel.Series Sseries2 = series.Item(2); Sseries2.Delete(); } Excel.Series Sseries = series.Item(1); c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col]; c2 = (Excel.Range)worksheet.Cells[start_row + rows - 1, start_col]; Sseries.XValues = worksheet.get_Range(c1, c2); c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + 1]; c2 = (Excel.Range)worksheet.Cells[start_row + rows - 1, start_col + 1]; Sseries.Values = worksheet.get_Range(c1, c2); Excel.Point point; int[] HSV0 = new int[3]; RGB0 = System.Drawing.Color.FromArgb(255, 96, 157, 202); System.Drawing.Color RGB1 = System.Drawing.Color.FromArgb(255, 96, 157, 202); Graphic.RGB2HSV(RGB0, ref HSV0); Max_Value = Double.MinValue; Min_Value = Double.MaxValue; for (int i = 1; i < rows; i++) { if (double.Parse(str[i, 2]) > Max_Value) { Max_Value = double.Parse(str[i, 1]); } if (double.Parse(str[i, 2]) < Min_Value) { Min_Value = double.Parse(str[i, 1]); } } int[] HSV = new int[3]; HSV0.CopyTo(HSV, 0); double ratio; Hrange = double.Parse(textBox_Bandwidth.Text); for (int i = 1; i < rows; i++) { point = (Excel.Point)Sseries.Points(i); point.Format.Fill.Solid(); point.Format.Fill.Visible = Office.MsoTriState.msoCTrue; point.Format.Fill.BackColor.RGB = System.Drawing.Color.FromArgb(255, 229, 229, 229).ToArgb(); ratio = (double.Parse(str[i, 2]) - Min_Value) / (Max_Value - Min_Value); HSV[2] = HSV0[2] + Convert.ToInt32(Hrange * (ratio - 0.5)); Graphic.HSV2RGB(ref RGB1, HSV); point.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(255, RGB1.B, RGB1.G, RGB1.R).ToArgb(); point.Format.Fill.Transparency = 0.0F; } //Sseries.MarkerBackgroundColor = System.Drawing.Color.FromArgb(255, 77, 175, 74).ToArgb(); //Sseries.MarkerForegroundColor = System.Drawing.Color.FromArgb(255, 77, 175, 74).ToArgb(); Sseries.Format.Line.Visible = Office.MsoTriState.msoCTrue; Sseries.Format.Line.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 0, 0).ToArgb(); Sseries.Format.Line.Weight = 0.75F; Excel.ChartGroup group = (Excel.ChartGroup)chart.ChartGroups(1); group.GapWidth = 0; chart.HasLegend = false; chart.HasTitle = false; //chart.ChartTitle.Delete(); worksheet.Activate(); }
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(); }
private void DrawBubbleLegend(Excel.Chart chart) { var revenues = new double[] { 200, 400, 600, 800, 1000 }; const double revenueIncrement = 200; var zeroArray = revenues.Select(x => x).ToArray(); var bubbleSizes = revenues.Select(x => GetBubbleSizeFromRevenue(x - revenueIncrement / 2)).ToArray(); Excel.Series dummyBubbleSeries = chart.SeriesCollection().NewSeries(); dummyBubbleSeries.XValues = zeroArray; dummyBubbleSeries.Values = zeroArray; dummyBubbleSeries.BubbleSizes = bubbleSizes; var bubbleWidths = new List <float>(); foreach (Excel.Point bubble in dummyBubbleSeries.Points()) { bubbleWidths.Add((float)bubble.Width); } var bubbleLegendNeededSpace = bubbleWidths.Max() + BubbleLegendTop; chart.PlotArea.Height -= bubbleLegendNeededSpace; float currentLeft = BubbleLegendLeft + RevenueTitleLength; for (var i = 0; i < bubbleWidths.Count; i++) { var bubbleWidth = bubbleWidths[i]; var topMargin = (float)chart.PlotArea.Height + BubbleLegendTop + bubbleWidths.Max() / 2 - bubbleWidth / 2; var oval = chart.Shapes.AddShape(MsoAutoShapeType.msoShapeOval, currentLeft, topMargin, bubbleWidth, bubbleWidth); oval.Fill.ForeColor.RGB = (int)Excel.XlRgbColor.rgbWhite; oval.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbBlack; var label = chart.Shapes.AddLabel(MsoTextOrientation.msoTextOrientationHorizontal, currentLeft + bubbleWidth + BubbleLegendLabelMargin, topMargin + bubbleWidth / 2 - RevenueTextLabelHeight / 2, RevenueTextLabelLength, RevenueTextLabelHeight); var revenueText = string.Empty; if (i == 0) { revenueText = "<" + revenues[i]; } else if (i == revenues.Length - 1) { revenueText = ">" + revenues[i - 1]; } else { revenueText = revenues[i - 1] + "-" + revenues[i]; } label.TextFrame.Characters().Text = revenueText; currentLeft += bubbleWidth + RevenueTextLabelLength + BubbleLegendLabelMargin * 2; } var revenueLabel = chart.Shapes.AddLabel(MsoTextOrientation.msoTextOrientationHorizontal, BubbleLegendLeft, BubbleLegendTop + bubbleWidths.Max() / 2 + (float)chart.PlotArea.Height - RevenueTextLabelHeight / 2, RevenueTitleLength, RevenueTextLabelHeight); revenueLabel.TextFrame.Characters().Text = "Turnover kkr:"; dummyBubbleSeries.Delete(); }
private void AddChartToShape( SlideQuestionModel slideQuestionModel, Slide resultsSlide, List <ResultModel> clickResults, List <ArsnovaVotingResultReturnElement> votingResults, int floatLeft, int floatTop, int width, int height) { var chartName = "ARSnova Results Chart"; var currentAssembly = System.Reflection.Assembly.GetExecutingAssembly().Location; var excelWorkBookPath = Path.GetDirectoryName(currentAssembly) + "\\" + "resultsChartData.xlsx"; var excelApp = new Excel.Application(); var workBook = excelApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); try { if (File.Exists(excelWorkBookPath)) { File.Delete(excelWorkBookPath); } var workSheet = (Excel.Worksheet)(workBook.Worksheets[1]); workSheet.Name = "ARSnovaResults"; Excel.Range dataRange; switch (slideQuestionModel.QuestionType) { case QuestionTypeEnum.MultipleChoiceClick: case QuestionTypeEnum.FreeTextClick: case QuestionTypeEnum.RangedQuestionClick: var correctAnswerOptionsAmount = this.FilterForCorrectResponsesClick(slideQuestionModel, clickResults).Count; var wrongAnswerOptionsAmount = clickResults.Count - correctAnswerOptionsAmount; this.SetExcelCellValue(workSheet, "A1", this.localizationService.Translate("Right")); this.SetExcelCellValue(workSheet, "B1", correctAnswerOptionsAmount); this.SetExcelCellValue(workSheet, "A2", this.localizationService.Translate("Wrong")); this.SetExcelCellValue(workSheet, "B2", wrongAnswerOptionsAmount); dataRange = workSheet.get_Range("A1", "B2"); break; case QuestionTypeEnum.SingleChoiceClick: case QuestionTypeEnum.YesNoClick: case QuestionTypeEnum.TrueFalseClick: case QuestionTypeEnum.SurveyClick: // Range: One Column for each answer option // One row for the amount of students voted for that answer option for (var i = 0; i < slideQuestionModel.AnswerOptions.Count; i++) { var answerOption = slideQuestionModel.AnswerOptions.First(ao => ao.Position - 1 == i); this.SetExcelCellValue(workSheet, $"A{i + 1}", answerOption.Text); this.SetExcelCellValue(workSheet, $"B{i + 1}", clickResults.Count(r => r.answerOptionNumber.Contains(answerOption.Position - 1))); } dataRange = workSheet.get_Range("A1", $"B{slideQuestionModel.AnswerOptions.Count}"); break; // arsnova.voting case QuestionTypeEnum.SingleChoiceVoting: case QuestionTypeEnum.YesNoVoting: case QuestionTypeEnum.EvaluationVoting: case QuestionTypeEnum.GradsVoting: for (var i = 0; i < slideQuestionModel.AnswerOptions.Count; i++) { var answerOption = slideQuestionModel.AnswerOptions.First(ao => ao.Position - 1 == i); var resultItem = votingResults.FirstOrDefault(vr => vr.answerText == answerOption.Text); var answerCount = resultItem?.answerCount ?? 0; this.SetExcelCellValue(workSheet, $"A{i + 1}", answerOption.Text); this.SetExcelCellValue(workSheet, $"B{i + 1}", answerCount); } dataRange = workSheet.get_Range("A1", $"B{slideQuestionModel.AnswerOptions.Count}"); break; case QuestionTypeEnum.MultipleChoiceVoting: var correctAnswerOptionsCount = 0; var wrongAnswerOptionsCount = 0; var correctAnswerOptionString = string.Empty; for (var i = 0; i < slideQuestionModel.AnswerOptions.Count; i++) { var answerOption = slideQuestionModel.AnswerOptions.First(ao => ao.Position - 1 == i); if (answerOption.IsTrue) { correctAnswerOptionString += "1"; } else { correctAnswerOptionString += "0"; } } foreach (var resultElement in votingResults) { var answerTextString = resultElement.answerText.Replace(",", string.Empty); if (correctAnswerOptionString == answerTextString) { correctAnswerOptionsCount += resultElement.answerCount; } else { wrongAnswerOptionsCount += resultElement.answerCount; } } this.SetExcelCellValue(workSheet, "A1", this.localizationService.Translate("Right")); this.SetExcelCellValue(workSheet, "B1", correctAnswerOptionsCount); this.SetExcelCellValue(workSheet, "A2", this.localizationService.Translate("Wrong")); this.SetExcelCellValue(workSheet, "B2", wrongAnswerOptionsCount); dataRange = workSheet.get_Range("A1", "B2"); break; case QuestionTypeEnum.FreeTextVoting: var correctFreeTextAnswers = this.GetCorrectFreeTextResultsVoting(slideQuestionModel, votingResults).Count; var falseFreeTextAnswers = votingResults.Count - correctFreeTextAnswers; this.SetExcelCellValue(workSheet, "A1", this.localizationService.Translate("Right")); this.SetExcelCellValue(workSheet, "B1", correctFreeTextAnswers); this.SetExcelCellValue(workSheet, "A2", this.localizationService.Translate("Wrong")); this.SetExcelCellValue(workSheet, "B2", falseFreeTextAnswers); dataRange = workSheet.get_Range("A1", "B2"); break; default: dataRange = workSheet.get_Range("A1", "B2"); break; // 0,0,0,1 -> answeroption 4; 1,0,0,0 -> answeroption 1 etc.; create tuples of position and answerElemt // 1,1,0,0 is a answerText of a mulitple choice question1 } var chartObjects = (Excel.ChartObjects)workSheet.ChartObjects(Type.Missing); var newChartObject = chartObjects.Add(floatLeft, floatTop, width, height); newChartObject.Name = chartName; newChartObject.Chart.ChartWizard( dataRange, slideQuestionModel.ChartType, this.GetChartFormat(slideQuestionModel.ChartType), // chart format Excel.XlRowCol.xlColumns, 1, //slideQuestionModel.AnswerOptions.Count - 1, // category labels 0, // series labels false, // has legend slideQuestionModel.QuestionText, // title this.localizationService.Translate("Answers"), // category title this.localizationService.Translate("Amount"), // value title Type.Missing); // extra titel // save before edit - user needs to confirm the overwriting of the file -> don't save the file twice! //workBook.SaveAs(excelWorkBookPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, //Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); // editing newChartObject.Chart.Perspective = 0; Excel.Series serie = newChartObject.Chart.SeriesCollection(1); // Evaluation, grades and surveys shouldn't be colorized switch (slideQuestionModel.QuestionType) { case QuestionTypeEnum.SingleChoiceVoting: case QuestionTypeEnum.YesNoVoting: case QuestionTypeEnum.SingleChoiceClick: case QuestionTypeEnum.YesNoClick: case QuestionTypeEnum.TrueFalseClick: // normal evaluated graph (correct = green, any other answer option = red) for (var i = 0; i < slideQuestionModel.AnswerOptions.Count; i++) { var point = (Excel.Point)serie.Points(i + 1); point.Interior.Color = slideQuestionModel.AnswerOptions.First(ao => ao.Position == i + 1).IsTrue ? Color.FromArgb(0, 255, 0).ToArgb() : Color.FromArgb(0, 0, 255).ToArgb(); } break; case QuestionTypeEnum.MultipleChoiceVoting: case QuestionTypeEnum.FreeTextVoting: case QuestionTypeEnum.MultipleChoiceClick: case QuestionTypeEnum.FreeTextClick: case QuestionTypeEnum.RangedQuestionClick: // freetext, ranged and multiple: right and wrong only // the part above sets the first point of the series as the correct answer and the second one as the false one serie.Points(1).Interior.Color = Color.FromArgb(0, 255, 0).ToArgb(); // green serie.Points(2).Interior.Color = Color.FromArgb(0, 0, 255).ToArgb(); // red break; } // save again to avoid question before closing workBook.SaveAs(excelWorkBookPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); // Copy chart to PowerPoint slide newChartObject.Copy(); var shapeRange = resultsSlide.Shapes.Paste(); shapeRange.Left = floatLeft; shapeRange.Top = floatTop; shapeRange.LinkFormat.Update(); excelApp.Quit(); } catch (Exception e) { // Error handling? workBook.SaveAs(excelWorkBookPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); excelApp.Quit(); } }
public BubbleSquare() { 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; Excel.Range c1 = (Excel.Range)worksheet.Cells[start_row, start_col]; Excel.Range c2 = (Excel.Range)worksheet.Cells[start_row + rows - 1, start_col + 1]; string ChartOrder = "SquareBubble" + Convert.ToString(Nchart); chart = worksheet.Controls.AddChart(250, 50, 450, 400, ChartOrder); Nchart = Nchart + 1; chart.SetSourceData(worksheet.get_Range(c1, c2), Excel.XlRowCol.xlColumns); chart.ChartType = Excel.XlChartType.xlXYScatter; Excel.SeriesCollection series = (Excel.SeriesCollection)chart.SeriesCollection(); if (series.Count == 2) { Excel.Series Sseries2 = series.Item(2); Sseries2.Delete(); } Excel.Series Sseries = series.Item(1); c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col]; c2 = (Excel.Range)worksheet.Cells[start_row + rows - 1, start_col]; Sseries.XValues = worksheet.get_Range(c1, c2); c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + 1]; c2 = (Excel.Range)worksheet.Cells[start_row + rows - 1, start_col + 1]; Sseries.Values = worksheet.get_Range(c1, c2); Excel.Point point; //double ratio = 42; int SquareSize; //((Excel.Range)worksheet.Cells[Graphic.start_row, Graphic.start_col + 3]).Value2 = "Square Size"; Max_size = Double.MinValue; Min_size = Double.MaxValue; int i; for (i = 1; i < rows; i++) { if (double.Parse(str[i, 2]) > Max_size) { Max_size = double.Parse(str[i, 2]); } if (double.Parse(str[i, 2]) < Min_size) { Min_size = double.Parse(str[i, 2]); } } Max_size = Math.Sqrt(Max_size); Min_size = Math.Sqrt(Min_size); //int[,] PointSize = new int[Graphic.rows - 1, 1]; for (i = 1; i < rows; i++) { point = (Excel.Point)Sseries.Points(i); //SquareSize = (int)((Math.Sqrt(double.Parse(str[i, 2])) - Min_size));// / (Max_size-Min_size) * ratio) + 2; SquareSize = (int)((Math.Sqrt(double.Parse(str[i, 2])) / Max_size * ratio) + 2); //PointSize[i - 1, 0] = SquareSize; //((Excel.Range)worksheet.Cells[Graphic.start_row + i, Graphic.start_col + 3]).Value2 = SquareSize; point.MarkerSize = SquareSize; } Sseries.MarkerBackgroundColor = System.Drawing.Color.FromArgb(255, 77, 175, 74).ToArgb(); Sseries.MarkerForegroundColor = System.Drawing.Color.FromArgb(255, 77, 175, 74).ToArgb(); Sseries.Format.Fill.Solid(); Sseries.Format.Fill.Visible = Office.MsoTriState.msoCTrue; Sseries.Format.Fill.BackColor.RGB = System.Drawing.Color.FromArgb(255, 229, 229, 229).ToArgb(); Sseries.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 77, 175, 74).ToArgb(); Sseries.Format.Fill.Transparency = 0.3F; Sseries.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleSquare; //Rggplot2(); chart.HasLegend = false; chart.HasTitle = false; //chart.ChartTitle.Delete(); worksheet.Activate(); }
private void matlabDataAnalysis(string file, string sheetToAnalyze) { excel_doc doc = new excel_doc(); Excel.Application excel = new Excel.Application(); Excel.Workbooks books = excel.Workbooks; Excel.Workbook wb = books.Open(Filename: file, ReadOnly: false); var activationContext = Type.GetTypeFromProgID("matlab.application.single"); var matlab = (MLApp.MLApp)Activator.CreateInstance(activationContext); string path = resultPath.Text; j = 1; while (wb.Sheets["Unloaded Q"].Cells[j, 2].Value2 != null) { j++; } Excel.Range rngFirst = wb.Sheets["Unloaded Q"].Cells[1, 2]; string rangeFirst = rngFirst.get_AddressLocal(false, false, Excel.XlReferenceStyle.xlA1); Excel.Range rngSecond = wb.Sheets["Unloaded Q"].Cells[j, 2]; string rangeSecond = rngSecond.get_AddressLocal(false, false, Excel.XlReferenceStyle.xlA1); string range = rangeFirst + ":" + rangeSecond; Console.WriteLine("range = " + range); string excel_file = file; double num_avg = Convert.ToDouble(numberOfAvg.Value); //Bring file path to the location of the MATLAB script matlab.Execute(@path); matlab.PutWorkspaceData("file", "base", excel_file); matlab.PutWorkspaceData("range", "base", range); matlab.PutWorkspaceData("Q_Unloaded_Sheet", "base", "Unloaded Q"); matlab.PutWorkspaceData("Q_Loaded_Sheet", "base", "Loaded Q"); matlab.PutWorkspaceData("NA_Q_Sheet", "base", "Q vs. Time"); matlab.PutWorkspaceData("NA_Center_Sheet", "base", "Center Freq."); matlab.PutWorkspaceData("SVD_Center_Sheet", "base", "SVD Center Frequency"); matlab.PutWorkspaceData("num_avg", "base", num_avg); string execute = string.Format("[manual_means,manual_stdevs,auto_means,auto_stdevs,sections,indices,a] = q_analysis(file,range,{0},num_avg)", sheetAnalysis.Text); Console.WriteLine(matlab.Execute("[manual_means,manual_stdevs,auto_means,auto_stdevs,sections,indices,a] = q_analysis(file,range,Q_Loaded_Sheet,num_avg)")); object sections = getMLData(matlab)[4]; int sectionNum = Convert.ToInt16(sections); //Creat the new excel result document doc.createAnalysisDoc(sectionNum, Convert.ToInt16(num_avg)); doc.workbook1.SaveAs(resultPath.Text + resultFileName.Text); string[] worksheets = new string[6] { "Loaded Q", "Unloaded Q", "Q vs. Time", "Center Freq.", "Room Temp.", "Cavity Temp." }; foreach (string sheet in worksheets) { Console.WriteLine(sheet); Excel.Worksheet worksheet = wb.Sheets[sheet]; Excel.ChartObjects charObjects = (Excel.ChartObjects)worksheet.ChartObjects(Type.Missing); Excel.ChartObject chart = (Excel.ChartObject)charObjects.Item(1); Excel.Chart myChart = chart.Chart; Excel.Series series = myChart.SeriesCollection(1); series.MarkerBackgroundColor = (int)Excel.XlRgbColor.rgbBlue; series.MarkerForegroundColor = (int)Excel.XlRgbColor.rgbBlue; } //Obtain the workspace data returned from the MATLAB Script and fill the new excel file with that data object indices = getMLData(matlab)[5]; IEnumerable enumerable = indices as IEnumerable; if (enumerable != null) { int i = 1; j = 1; int v = 0; int level = 0; int sample_count = Convert.ToUInt16(num_avg); List <double> qLoadedAvg = new List <double>(); List <double> qUnloadedAvg = new List <double>(); List <double> qNAAvg = new List <double>(); List <double> resFreqNAAvg = new List <double>(); List <double> resFreqSVDAvg = new List <double>(); List <double> roomTAvg = new List <double>(); List <double> cavityTAvg = new List <double>(); foreach (object element in enumerable) { level = Convert.ToUInt16((num_avg * 2 + 12) * (i - 1)); int index = Convert.ToUInt16(element); Excel.Worksheet ws = wb.Sheets["Loaded Q"]; Console.WriteLine(ws.Cells[element, 1].text); string value = Convert.ToString(ws.Cells[element, 1].text); doc.addData(level + 10 + sample_count + v, 2, value, "analysis"); Excel.ChartObjects charObjects = (Excel.ChartObjects)ws.ChartObjects(Type.Missing); Excel.ChartObject chart = (Excel.ChartObject)charObjects.Item(1); Excel.Chart myChart = chart.Chart; Excel.Series series = myChart.SeriesCollection(1); series.Points(element).MarkerForeGroundColor = (int)Excel.XlRgbColor.rgbRed; series.Points(element).MarkerBackGroundColor = (int)Excel.XlRgbColor.rgbRed; value = Convert.ToString(ws.Cells[element, 2].Value2); doc.addData(level + 10 + sample_count + v, 3, value, "analysis"); qLoadedAvg.Add(Convert.ToDouble(value)); ws = wb.Sheets["Unloaded Q"]; value = Convert.ToString(ws.Cells[element, 2].Value2); doc.addData(level + 10 + sample_count + v, 4, value, "analysis"); qUnloadedAvg.Add(Convert.ToDouble(value)); charObjects = (Excel.ChartObjects)ws.ChartObjects(Type.Missing); chart = (Excel.ChartObject)charObjects.Item(1); myChart = chart.Chart; series = myChart.SeriesCollection(1); series.Points(element).MarkerForeGroundColor = (int)Excel.XlRgbColor.rgbRed; series.Points(element).MarkerBackGroundColor = (int)Excel.XlRgbColor.rgbRed; ws = wb.Sheets["Q vs. Time"]; value = Convert.ToString(ws.Cells[element, 2].Value2); doc.addData(level + 10 + sample_count + v, 5, value, "analysis"); qNAAvg.Add(Convert.ToDouble(value)); charObjects = (Excel.ChartObjects)ws.ChartObjects(Type.Missing); chart = (Excel.ChartObject)charObjects.Item(1); myChart = chart.Chart; series = myChart.SeriesCollection(1); series.Points(element).MarkerForeGroundColor = (int)Excel.XlRgbColor.rgbRed; series.Points(element).MarkerBackGroundColor = (int)Excel.XlRgbColor.rgbRed; ws = wb.Sheets["Center Freq."]; value = Convert.ToString(ws.Cells[element, 2].Value2); doc.addData(level + 10 + sample_count + v, 6, value, "analysis"); resFreqNAAvg.Add(Convert.ToDouble(value)); charObjects = (Excel.ChartObjects)ws.ChartObjects(Type.Missing); chart = (Excel.ChartObject)charObjects.Item(1); myChart = chart.Chart; series = myChart.SeriesCollection(1); series.Points(element).MarkerForeGroundColor = (int)Excel.XlRgbColor.rgbRed; series.Points(element).MarkerBackGroundColor = (int)Excel.XlRgbColor.rgbRed; ws = wb.Sheets["SVD Center Frequency"]; value = Convert.ToString(ws.Cells[element, 2].Value2); doc.addData(level + 10 + sample_count + v, 7, value, "analysis"); resFreqSVDAvg.Add(Convert.ToDouble(value)); charObjects = (Excel.ChartObjects)ws.ChartObjects(Type.Missing); chart = (Excel.ChartObject)charObjects.Item(1); myChart = chart.Chart; series.Points(element).MarkerForeGroundColor = (int)Excel.XlRgbColor.rgbRed; series.Points(element).MarkerBackGroundColor = (int)Excel.XlRgbColor.rgbRed; ws = wb.Sheets["Room Temp."]; value = Convert.ToString(ws.Cells[element, 2].Value2); doc.addData(level + 10 + sample_count + v, 8, value, "analysis"); roomTAvg.Add(Convert.ToDouble(value)); charObjects = (Excel.ChartObjects)ws.ChartObjects(Type.Missing); chart = (Excel.ChartObject)charObjects.Item(1); myChart = chart.Chart; series = myChart.SeriesCollection(1); series.Points(element).MarkerForeGroundColor = (int)Excel.XlRgbColor.rgbRed; series.Points(element).MarkerBackGroundColor = (int)Excel.XlRgbColor.rgbRed; ws = wb.Sheets["Cavity Temp."]; value = Convert.ToString(ws.Cells[element, 2].Value2); doc.addData(level + 10 + sample_count + v, 9, value, "analysis"); cavityTAvg.Add(Convert.ToDouble(value)); charObjects = (Excel.ChartObjects)ws.ChartObjects(Type.Missing); chart = (Excel.ChartObject)charObjects.Item(1); myChart = chart.Chart; series = myChart.SeriesCollection(1); series.Points(element).MarkerForeGroundColor = (int)Excel.XlRgbColor.rgbRed; series.Points(element).MarkerBackGroundColor = (int)Excel.XlRgbColor.rgbRed; v++; j++; if ((j - 1) % (num_avg) == 0) { double avg = qLoadedAvg.Sum() / num_avg; double sd = qLoadedAvg.Select(x => (x - avg) * (x - avg)).Sum(); sd = Math.Sqrt(sd / qLoadedAvg.Count); Console.WriteLine("AVG = " + Convert.ToString(avg)); doc.addData(level + 10 + (2 * sample_count), 3, Convert.ToString(avg), "analysis"); doc.addData(level + 11 + (2 * sample_count), 3, Convert.ToString(sd), "analysis"); qLoadedAvg.Clear(); avg = qUnloadedAvg.Sum() / num_avg; sd = qUnloadedAvg.Select(x => (x - avg) * (x - avg)).Sum(); sd = Math.Sqrt(sd / qUnloadedAvg.Count); doc.addData(level + 10 + (2 * sample_count), 4, Convert.ToString(Convert.ToString(avg)), "analysis"); doc.addData(level + 11 + (2 * sample_count), 4, Convert.ToString(Convert.ToString(sd)), "analysis"); qUnloadedAvg.Clear(); avg = qNAAvg.Sum() / num_avg; sd = qNAAvg.Select(x => (x - avg) * (x - avg)).Sum(); sd = Math.Sqrt(sd / qNAAvg.Count); doc.addData(level + 10 + (2 * sample_count), 5, Convert.ToString(Convert.ToString(avg)), "analysis"); doc.addData(level + 11 + (2 * sample_count), 5, Convert.ToString(Convert.ToString(sd)), "analysis"); qNAAvg.Clear(); avg = resFreqNAAvg.Sum() / num_avg; sd = resFreqNAAvg.Select(x => (x - avg) * (x - avg)).Sum(); sd = Math.Sqrt(sd / resFreqNAAvg.Count); doc.addData(level + 10 + (2 * sample_count), 6, Convert.ToString(Convert.ToString(avg)), "analysis"); doc.addData(level + 11 + (2 * sample_count), 6, Convert.ToString(Convert.ToString(sd)), "analysis"); resFreqNAAvg.Clear(); avg = resFreqSVDAvg.Sum() / num_avg; sd = resFreqSVDAvg.Select(x => (x - avg) * (x - avg)).Sum(); sd = Math.Sqrt(sd / resFreqSVDAvg.Count); doc.addData(level + 10 + (2 * sample_count), 7, Convert.ToString(Convert.ToString(avg)), "analysis"); doc.addData(level + 11 + (2 * sample_count), 7, Convert.ToString(Convert.ToString(sd)), "analysis"); resFreqSVDAvg.Clear(); avg = roomTAvg.Sum() / num_avg; sd = roomTAvg.Select(x => (x - avg) * (x - avg)).Sum(); sd = Math.Sqrt(sd / roomTAvg.Count); doc.addData(level + 10 + (2 * sample_count), 8, Convert.ToString(Convert.ToString(avg)), "analysis"); doc.addData(level + 11 + (2 * sample_count), 8, Convert.ToString(Convert.ToString(sd)), "analysis"); roomTAvg.Clear(); avg = cavityTAvg.Sum() / num_avg; sd = cavityTAvg.Select(x => (x - avg) * (x - avg)).Sum(); sd = Math.Sqrt(sd / cavityTAvg.Count); doc.addData(level + 10 + (2 * sample_count), 9, Convert.ToString(Convert.ToString(avg)), "analysis"); doc.addData(level + 11 + (2 * sample_count), 9, Convert.ToString(Convert.ToString(sd)), "analysis"); cavityTAvg.Clear(); i++; v = 0; } } } object a = getMLData(matlab)[6]; enumerable = a as IEnumerable; if (enumerable != null) { int i = 1; j = 1; int v = 0; int level = 0; List <double> qLoadedAvg = new List <double>(); List <double> qUnloadedAvg = new List <double>(); List <double> qNAAvg = new List <double>(); List <double> resFreqNAAvg = new List <double>(); List <double> resFreqSVDAvg = new List <double>(); List <double> roomTAvg = new List <double>(); List <double> cavityTAvg = new List <double>(); foreach (object element in enumerable) { int sample_count = Convert.ToUInt16(num_avg); level = Convert.ToUInt16((num_avg * 2 + 12) * (i - 1)); int index = Convert.ToUInt16(element); Excel.Worksheet ws = wb.Sheets["Loaded Q"]; Console.WriteLine(ws.Cells[element, 1].text); string value = Convert.ToString(ws.Cells[element, 1].text); doc.addData(level + 5 + v, 2, value, "analysis"); Excel.ChartObjects charObjects = (Excel.ChartObjects)ws.ChartObjects(Type.Missing); Excel.ChartObject chart = (Excel.ChartObject)charObjects.Item(1); Excel.Chart myChart = chart.Chart; Excel.Series series = myChart.SeriesCollection(1); series.Points(element).MarkerForeGroundColor = (int)Excel.XlRgbColor.rgbGreen; series.Points(element).MarkerBackGroundColor = (int)Excel.XlRgbColor.rgbGreen; value = Convert.ToString(ws.Cells[element, 2].Value2); doc.addData(level + 5 + v, 3, value, "analysis"); qLoadedAvg.Add(Convert.ToDouble(value)); ws = wb.Sheets["Unloaded Q"]; value = Convert.ToString(ws.Cells[element, 2].Value2); doc.addData(level + 5 + v, 4, value, "analysis"); qUnloadedAvg.Add(Convert.ToDouble(value)); charObjects = (Excel.ChartObjects)ws.ChartObjects(Type.Missing); chart = (Excel.ChartObject)charObjects.Item(1); myChart = chart.Chart; series = myChart.SeriesCollection(1); series.Points(element).MarkerForeGroundColor = (int)Excel.XlRgbColor.rgbGreen; series.Points(element).MarkerBackGroundColor = (int)Excel.XlRgbColor.rgbGreen; ws = wb.Sheets["Q vs. Time"]; value = Convert.ToString(ws.Cells[element, 2].Value2); doc.addData(level + 5 + v, 5, value, "analysis"); qNAAvg.Add(Convert.ToDouble(value)); charObjects = (Excel.ChartObjects)ws.ChartObjects(Type.Missing); chart = (Excel.ChartObject)charObjects.Item(1); myChart = chart.Chart; series = myChart.SeriesCollection(1); series.Points(element).MarkerForeGroundColor = (int)Excel.XlRgbColor.rgbGreen; series.Points(element).MarkerBackGroundColor = (int)Excel.XlRgbColor.rgbGreen; ws = wb.Sheets["Center Freq."]; value = Convert.ToString(ws.Cells[element, 2].Value2); doc.addData(level + 5 + v, 6, value, "analysis"); resFreqNAAvg.Add(Convert.ToDouble(value)); charObjects = (Excel.ChartObjects)ws.ChartObjects(Type.Missing); chart = (Excel.ChartObject)charObjects.Item(1); myChart = chart.Chart; series = myChart.SeriesCollection(1); series.Points(element).MarkerForeGroundColor = (int)Excel.XlRgbColor.rgbGreen; series.Points(element).MarkerBackGroundColor = (int)Excel.XlRgbColor.rgbGreen; ws = wb.Sheets["SVD Center Frequency"]; value = Convert.ToString(ws.Cells[element, 2].Value2); doc.addData(level + 5 + v, 7, value, "analysis"); resFreqSVDAvg.Add(Convert.ToDouble(value)); charObjects = (Excel.ChartObjects)ws.ChartObjects(Type.Missing); chart = (Excel.ChartObject)charObjects.Item(1); myChart = chart.Chart; series = myChart.SeriesCollection(1); series.Points(element).MarkerForeGroundColor = (int)Excel.XlRgbColor.rgbGreen; series.Points(element).MarkerBackGroundColor = (int)Excel.XlRgbColor.rgbGreen; ws = wb.Sheets["Room Temp."]; value = Convert.ToString(ws.Cells[element, 2].Value2); doc.addData(level + 5 + v, 8, value, "analysis"); roomTAvg.Add(Convert.ToDouble(value)); charObjects = (Excel.ChartObjects)ws.ChartObjects(Type.Missing); chart = (Excel.ChartObject)charObjects.Item(1); myChart = chart.Chart; series = myChart.SeriesCollection(1); series.Points(element).MarkerForeGroundColor = (int)Excel.XlRgbColor.rgbGreen; series.Points(element).MarkerBackGroundColor = (int)Excel.XlRgbColor.rgbGreen; ws = wb.Sheets["Cavity Temp."]; value = Convert.ToString(ws.Cells[element, 2].Value2); doc.addData(level + 5 + v, 9, value, "analysis"); cavityTAvg.Add(Convert.ToDouble(value)); charObjects = (Excel.ChartObjects)ws.ChartObjects(Type.Missing); chart = (Excel.ChartObject)charObjects.Item(1); myChart = chart.Chart; series = myChart.SeriesCollection(1); series.Points(element).MarkerForeGroundColor = (int)Excel.XlRgbColor.rgbGreen; series.Points(element).MarkerBackGroundColor = (int)Excel.XlRgbColor.rgbGreen; v++; j++; if ((j - 1) % (num_avg) == 0) { double avg = qLoadedAvg.Sum() / num_avg; double sd = qLoadedAvg.Select(x => (x - avg) * (x - avg)).Sum(); sd = Math.Sqrt(sd / qLoadedAvg.Count); Console.WriteLine("AVG = " + Convert.ToString(avg)); doc.addData(level + 5 + sample_count, 3, Convert.ToString(avg), "analysis"); doc.addData(level + 6 + sample_count, 3, Convert.ToString(sd), "analysis"); qLoadedAvg.Clear(); avg = qUnloadedAvg.Sum() / num_avg; sd = qUnloadedAvg.Select(x => (x - avg) * (x - avg)).Sum(); sd = Math.Sqrt(sd / qUnloadedAvg.Count); doc.addData(level + 5 + sample_count, 4, Convert.ToString(Convert.ToString(avg)), "analysis"); doc.addData(level + 6 + sample_count, 4, Convert.ToString(Convert.ToString(sd)), "analysis"); qUnloadedAvg.Clear(); avg = qNAAvg.Sum() / num_avg; sd = qNAAvg.Select(x => (x - avg) * (x - avg)).Sum(); sd = Math.Sqrt(sd / qNAAvg.Count); doc.addData(level + 5 + sample_count, 5, Convert.ToString(Convert.ToString(avg)), "analysis"); doc.addData(level + 6 + sample_count, 5, Convert.ToString(Convert.ToString(sd)), "analysis"); qNAAvg.Clear(); avg = resFreqNAAvg.Sum() / num_avg; sd = resFreqNAAvg.Select(x => (x - avg) * (x - avg)).Sum(); sd = Math.Sqrt(sd / resFreqNAAvg.Count); doc.addData(level + 5 + sample_count, 6, Convert.ToString(Convert.ToString(avg)), "analysis"); doc.addData(level + 6 + sample_count, 6, Convert.ToString(Convert.ToString(sd)), "analysis"); resFreqNAAvg.Clear(); avg = resFreqSVDAvg.Sum() / num_avg; sd = resFreqSVDAvg.Select(x => (x - avg) * (x - avg)).Sum(); sd = Math.Sqrt(sd / resFreqSVDAvg.Count); doc.addData(level + 5 + sample_count, 7, Convert.ToString(Convert.ToString(avg)), "analysis"); doc.addData(level + 6 + sample_count, 7, Convert.ToString(Convert.ToString(sd)), "analysis"); resFreqSVDAvg.Clear(); avg = roomTAvg.Sum() / num_avg; sd = roomTAvg.Select(x => (x - avg) * (x - avg)).Sum(); sd = Math.Sqrt(sd / roomTAvg.Count); doc.addData(level + 5 + sample_count, 8, Convert.ToString(Convert.ToString(avg)), "analysis"); doc.addData(level + 6 + sample_count, 8, Convert.ToString(Convert.ToString(sd)), "analysis"); roomTAvg.Clear(); avg = cavityTAvg.Sum() / num_avg; sd = cavityTAvg.Select(x => (x - avg) * (x - avg)).Sum(); sd = Math.Sqrt(sd / cavityTAvg.Count); doc.addData(level + 5 + sample_count, 9, Convert.ToString(Convert.ToString(avg)), "analysis"); doc.addData(level + 6 + sample_count, 9, Convert.ToString(Convert.ToString(sd)), "analysis"); cavityTAvg.Clear(); i++; v = 0; } } } Excel.Range workSheet_range = doc.Analysis.Range[doc.Analysis.Cells[1, 1], doc.Analysis.Cells[10000, 20]]; workSheet_range.Columns.AutoFit(); doc.workbook1.Save(); wb.Save(); wb.Close(); excel.Quit(); Marshal.FinalReleaseComObject(excel); Marshal.FinalReleaseComObject(books); Marshal.FinalReleaseComObject(wb); }