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