private void GEN_Student_Record_Chart(PowerPoint.Slide Sld)
{
Sld.Select();
Sld.Layout = Microsoft.Office.Interop.PowerPoint.PpSlideLayout.ppLayoutBlank;
//Add a default chart in slide
Sld.Shapes.AddChart(Microsoft.Office.Core.XlChartType.xl3DColumn, 200F, 200F, 400F, 300F);
//Add a heading
PowerPoint.Shape textBox = Sld.Shapes.AddTextbox(Microsoft.Office.Core.MsoTextOrientation.msoTextOrientationHorizontal, 0, 20, 1000, 70);
textBox.TextFrame.TextRange.InsertAfter("Student-wise Consolidated Attendance Record");
textBox.TextFrame.TextRange.Font.Size = 30;
textBox.TextFrame.VerticalAnchor = Microsoft.Office.Core.MsoVerticalAnchor.msoAnchorMiddle;
textBox.TextFrame.HorizontalAnchor = Microsoft.Office.Core.MsoHorizontalAnchor.msoAnchorCenter;
//Access the added chart
Microsoft.Office.Interop.PowerPoint.Chart ppChart = Sld.Shapes[1].Chart;
//Access the chart data
Microsoft.Office.Interop.PowerPoint.ChartData chartData = ppChart.ChartData;
//Create instance to Excel workbook to work with chart data
Microsoft.Office.Interop.Excel.Workbook dataWorkbook = (Microsoft.Office.Interop.Excel.Workbook)chartData.Workbook;
//Accessing the data worksheet for chart
Microsoft.Office.Interop.Excel.Worksheet dataSheet = dataWorkbook.Worksheets[1];
//Setting the range of chart
Microsoft.Office.Interop.Excel.Range tRange = dataSheet.Cells.get_Range("A1", "C4");
//Create a Table and applying the set range on chart data table
Microsoft.Office.Interop.Excel.ListObject tbl1 = dataSheet.ListObjects["Table1"];
tbl1.Resize(tRange);
//Setting values for categories and respective series data
((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("A2"))).FormulaR1C1 = "";
((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("A3"))).FormulaR1C1 = "";
((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("A4"))).FormulaR1C1 = "";
((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("B2"))).FormulaR1C1 = 0;
((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("B3"))).FormulaR1C1 = 0;
((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("B4"))).FormulaR1C1 = 0;
((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("C2"))).FormulaR1C1 = 0;
((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("C3"))).FormulaR1C1 = 0;
((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("C4"))).FormulaR1C1 = 0;
//Delete the legend for neatness
Microsoft.Office.Interop.PowerPoint.Legend legend = null;
legend = ppChart.Legend;
legend.Delete();
//Setting chart title
ppChart.ChartTitle.Font.Italic = false;
ppChart.ChartTitle.Text = "Attendance Results";
ppChart.ChartTitle.Font.Size = 18;
ppChart.ChartTitle.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
ppChart.ChartTitle.Format.Line.Visible = Microsoft.Office.Core.MsoTriState.msoTrue;
ppChart.ChartTitle.Format.Line.ForeColor.RGB = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
//Accessing Chart value axis
Microsoft.Office.Interop.PowerPoint.Axis valaxis = ppChart.Axes(Microsoft.Office.Interop.PowerPoint.XlAxisType.xlValue, Microsoft.Office.Interop.PowerPoint.XlAxisGroup.xlPrimary);
//Setting values axis units
valaxis.MajorUnit = 2000.0F;
valaxis.MinorUnit = 1000.0F;
valaxis.MinimumScale = 0.0F;
valaxis.MaximumScale = 30.0F;
//Accessing Chart Depth axis
Microsoft.Office.Interop.PowerPoint.Axis Depthaxis = ppChart.Axes(Microsoft.Office.Interop.PowerPoint.XlAxisType.xlSeriesAxis, Microsoft.Office.Interop.PowerPoint.XlAxisGroup.xlPrimary);
Depthaxis.Delete();
//Setting chart rotation
ppChart.Rotation = 20; //Y-Value
ppChart.Elevation = 15; //X-Value
ppChart.RightAngleAxes = false;
var ws = new WebSocket("ws://localhost:1234");
var the_message = "";
ws.OnMessage += (sender12, sent) =>
{
try
{
the_message = sent.Data;
var the_message2 = JsonConvert.DeserializeObject <List <StudentRecordNode> >(the_message);
var hiba_p = 0;
var jeehan_p = 0;
var shiza_p = 0;
var hiba_a = 0;
var jeehan_a = 0;
var shiza_a = 0;
foreach (var m in the_message2)
{
if (m.name == "Hiba")
{
}
}
}
catch (InvalidCastException e)
{
Debug.WriteLine("Source: ", e.Source);
}
};
ws.Connect();
ws.OnError += (sender12, sent) =>
{
Debug.WriteLine("error " + sent.Message);
};
ws.OnClose += (sender12, sent) =>
{
Debug.WriteLine("disconnect with host");
};
}
private string readShape(Microsoft.Office.Interop.PowerPoint.Shape shape)
{
string str = "";
// Just Checking this ! //comment it (below) out
//if (shape.Type == MsoShapeType.msoEmbeddedOLEObject)
//{
// Console.WriteLine("Excel Table = true");
//} else
//{
// Console.WriteLine("Excel Table = false");
//}
// extract text
if (shape.HasTextFrame == Microsoft.Office.Core.MsoTriState.msoTrue)
{
var textFrame = shape.TextFrame;
if (textFrame.HasText == Microsoft.Office.Core.MsoTriState.msoTrue)
{
var textRange = textFrame.TextRange;
var paragraphs = textRange.Paragraphs(-1, -1);
foreach (PowerPoint.TextRange paragraph in paragraphs)
{
var text = paragraph.Text;
text = text.Replace("\r", "").Replace("\v", " ").Replace("\f", " ").Trim();
text = Regex.Replace(text, @"[^\t\r\n\u0020-\u007E]+", string.Empty);
text = Regex.Replace(text, @"[ ]{ 2,}", " ");
if (text.Length > 2)
{
str += "* " + text + "\n";
}
}
}
}
// read groups -> ungroup and recursively iterate through this function
else if (shape.Type == MsoShapeType.msoGroup)
{
var p = shape.Ungroup();
foreach (Microsoft.Office.Interop.PowerPoint.Shape shp in p)
{
str += readShape(shp);
}
}
//read tables in ppt
else if (shape.HasTable == MsoTriState.msoTrue)
{
str += "\n";
var t = shape.Table;
for (int j = 1; j <= t.Rows.Count; ++j)
{
// create the table header for the .md format
if (j == 2)
{
str += "| ";
for (int k = 1; k <= t.Columns.Count; ++k)
{
str += "---| ";
}
str += "\n";
}
// for every other row enter |---|---| notation for cells.
str += "| ";
for (int k = 1; k <= t.Columns.Count; ++k)
{
var textFrame = t.Cell(j, k).Shape.TextFrame;
var textRange = textFrame.TextRange;
var paragraphs = textRange.Paragraphs(-1, -1);
foreach (PowerPoint.TextRange paragraph in paragraphs)
{
var text = paragraph.Text;
text = text.Replace("\r", "").Replace("\v", " ").Replace("\f", " ");
text = Regex.Replace(text, @"[^\t\r\n\u0020-\u007E]+", string.Empty);
text = Regex.Replace(text, @"[ ]{ 2,}", string.Empty);
str += " " + text;
}
str += " | ";
}
str += "\n";
}
str += "\n~\n";
}
else if (shape.HasChart == MsoTriState.msoTrue)
{
Console.WriteLine("Has Chart: True");
Microsoft.Office.Interop.PowerPoint.Chart t = shape.Chart;
//string text = "";
if (t.HasTitle)
{
Console.WriteLine("Has Title: True");
Console.WriteLine("Title:" + t.ChartTitle.Text.ToString());
str += t.ChartTitle.Text.ToString() + " ";
}
if (t.HasDataTable)
{
Console.WriteLine("Has DataTable: True");
System.Data.DataTable dp = (System.Data.DataTable)shape.Chart.DataTable;
string strRowCommaSeparated = "";
foreach (DataRow dr in dp.Rows)
{
for (int i = 0; i < dr.ItemArray.Length; i++)
{
strRowCommaSeparated += strRowCommaSeparated == "" ? dr.ItemArray[i].ToString() : ("," + strRowCommaSeparated);
}
}
Console.WriteLine("\n\n\t\tOur DataTable : " + strRowCommaSeparated);
var p = t.DataTable;
str += t.DataTable.ToString(); //.DataTable.ToString();
}
// Extracting series labels and count
Microsoft.Office.Interop.PowerPoint.SeriesCollection tmp = (Microsoft.Office.Interop.PowerPoint.SeriesCollection)t.SeriesCollection();
Console.WriteLine("Series Count:" + tmp.Count);
t.ApplyDataLabels();
for (int j = 1; j <= tmp.Count; ++j)
{
Microsoft.Office.Interop.PowerPoint.Series aSeries = tmp.Item(j);
Console.WriteLine("Series Name: " + aSeries.Name);
//Microsoft.Office.Interop.PowerPoint.Points pts = tmp.Item(j).Points(); // # point in the chart per series - meaning X axis for a pareto chart
//Console.WriteLine("Points #:" + pts.Count);
//Console.WriteLine(pts.Item(2).Name);
}
//Excel Route for chart series data extraction
PowerPoint.ChartData pChartData = t.ChartData;
//Console.WriteLine("Chart Title:" + t.Title); // No need - shape.title takes care of this
if (!t.ChartData.IsLinked)
{
Console.WriteLine("Has Embedded Excel: True");
Excel.Workbook eWorkbook = (Excel.Workbook)pChartData.Workbook;
Excel.Worksheet eWorksheet = (Excel.Worksheet)eWorkbook.Worksheets[1];
var columnsRange = eWorksheet.UsedRange.Columns;
var rowsRange = eWorksheet.UsedRange.Rows;
var columnCount = columnsRange.Columns.Count;
var rowCount = rowsRange.Rows.Count;
//Console.WriteLine("r#, c# : " + rowCount + ":" + columnCount);
foreach (Excel.Range c in eWorksheet.UsedRange)
{
string changedCell = c.get_Address(Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlA1, Type.Missing, Type.Missing);
Console.Write(" | " + c.Value2);
}
eWorkbook.Close();
}
else if (t.ChartData.IsLinked)
{
// add a note for PDF extraction and flagging
}
//Microsoft.Office.Interop.PowerPoint.SeriesCollection chartSeriesA = (Microsoft.Office.Interop.PowerPoint.SeriesCollection)t.SeriesCollection();
//foreach (Microsoft.Office.Interop.PowerPoint.Series Srs in chartSeriesA)
//{
// System.Array a = (System.Array)((object)Srs.Values);
// //var XV = Srs.XValues;
// //var V = Srs.Values;
// //str += Srs.ToString();
//}
}
//else if (shape.Type == MsoShapeType.msoTextBox || shape.Type == MsoShapeType.msoAutoShape)
//{
// var textFrame = shape.TextFrame;
// if (textFrame.HasText == Microsoft.Office.Core.MsoTriState.msoTrue)
// {
// var textRange = textFrame.TextRange;
// var paragraphs = textRange.Paragraphs(-1, -1);
// foreach (PowerPoint.TextRange paragraph in paragraphs)
// {
// var text = paragraph.Text;
// text = text.Replace("\r", "");
// str += text + "\n";
// }
// }
//}
//str = CleanInput(str.Replace("\n\n", "\n"));
str = Regex.Replace(str, @"[^\t\r\n\u0020-\u007E]+", string.Empty);
return(str); //.Replace("\n\n", "\n"); //.Replace("\r","");
}
public static void GEN_Poll_Chart(PowerPoint.Slide Sld)
{
//Select the slide and set its layout
Sld.Select();
Sld.Layout = Microsoft.Office.Interop.PowerPoint.PpSlideLayout.ppLayoutBlank;
//Add a default chart in slide
Sld.Shapes.AddChart(Microsoft.Office.Core.XlChartType.xl3DColumn, 200F, 200F, 400F, 300F);
//Add a heading
PowerPoint.Shape textBox = Sld.Shapes.AddTextbox(Microsoft.Office.Core.MsoTextOrientation.msoTextOrientationHorizontal, 0, 20, 1000, 70);
textBox.TextFrame.TextRange.InsertAfter("Daily Class Poll");
textBox.TextFrame.TextRange.Font.Size = 30;
textBox.TextFrame.VerticalAnchor = Microsoft.Office.Core.MsoVerticalAnchor.msoAnchorMiddle;
textBox.TextFrame.HorizontalAnchor = Microsoft.Office.Core.MsoHorizontalAnchor.msoAnchorCenter;
//Access the added chart
Microsoft.Office.Interop.PowerPoint.Chart ppChart = Sld.Shapes[1].Chart;
//Access the chart data
Microsoft.Office.Interop.PowerPoint.ChartData chartData = ppChart.ChartData;
//Create instance to Excel workbook to work with chart data
Microsoft.Office.Interop.Excel.Workbook dataWorkbook = (Microsoft.Office.Interop.Excel.Workbook)chartData.Workbook;
//Accessing the data worksheet for chart
Microsoft.Office.Interop.Excel.Worksheet dataSheet = dataWorkbook.Worksheets[1];
//Setting the range of chart
Microsoft.Office.Interop.Excel.Range tRange = dataSheet.Cells.get_Range("A1", "B5");
//Create a Table and applying the set range on chart data table
Microsoft.Office.Interop.Excel.ListObject tbl1 = dataSheet.ListObjects["Table1"];
tbl1.Resize(tRange);
//Thread.Sleep(3000);
Debug.WriteLine("over 1");
//Setting values for categories and respective series data
((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("A2"))).FormulaR1C1 = "A";
((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("A3"))).FormulaR1C1 = "B";
((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("A4"))).FormulaR1C1 = "C";
((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("A5"))).FormulaR1C1 = "D";
((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("B2"))).FormulaR1C1 = 0;
((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("B3"))).FormulaR1C1 = 0;
((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("B4"))).FormulaR1C1 = 0;
((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("B5"))).FormulaR1C1 = 0;
//Delete the legend for neatness
Microsoft.Office.Interop.PowerPoint.Legend legend = null;
legend = ppChart.Legend;
legend.Delete();
//Setting chart title
ppChart.ChartTitle.Font.Italic = false;
ppChart.ChartTitle.Text = "Poll Results";
ppChart.ChartTitle.Font.Size = 18;
ppChart.ChartTitle.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
ppChart.ChartTitle.Format.Line.Visible = Microsoft.Office.Core.MsoTriState.msoTrue;
ppChart.ChartTitle.Format.Line.ForeColor.RGB = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
//setting all to red bars
var series = ppChart.SeriesCollection(1) as PowerPoint.Series;
var point = series.Points(1) as PowerPoint.Point;
point.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
point = series.Points(2) as PowerPoint.Point;
point.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
point = series.Points(3) as PowerPoint.Point;
point.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
point = series.Points(4) as PowerPoint.Point;
point.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
//Accessing Chart value axis
Microsoft.Office.Interop.PowerPoint.Axis valaxis = ppChart.Axes(Microsoft.Office.Interop.PowerPoint.XlAxisType.xlValue, Microsoft.Office.Interop.PowerPoint.XlAxisGroup.xlPrimary);
//Setting values axis units
valaxis.MajorUnit = 2000.0F;
valaxis.MinorUnit = 1000.0F;
valaxis.MinimumScale = 0.0F;
valaxis.MaximumScale = 30.0F;
//Accessing Chart Depth axis
Microsoft.Office.Interop.PowerPoint.Axis Depthaxis = ppChart.Axes(Microsoft.Office.Interop.PowerPoint.XlAxisType.xlSeriesAxis, Microsoft.Office.Interop.PowerPoint.XlAxisGroup.xlPrimary);
Depthaxis.Delete();
//Setting chart rotation
ppChart.Rotation = 20; //Y-Value
ppChart.Elevation = 15; //X-Value
ppChart.RightAngleAxes = false;
//Thread.Sleep(5000);
Debug.WriteLine("over 2");
var ws = new WebSocket("ws://localhost:1234");
var the_message = "";
ws.OnMessage += (sender12, sent) =>
{
try
{
the_message = sent.Data;
var the_message2 = JsonConvert.DeserializeObject <List <PollNode> >(the_message);
var ques = "";
var ans_a = "";
var ans_b = "";
var ans_c = "";
var ans_d = "";
var num_a = "";
var num_b = "";
var num_c = "";
var num_d = "";
var correct_ans = "";
foreach (var m in the_message2)
{
ques = m.Question;
ans_a = m.A;
ans_b = m.B;
ans_c = m.C;
ans_d = m.D;
num_a = m.Freq_A;
num_b = m.Freq_B;
num_c = m.Freq_C;
num_d = m.Freq_D;
correct_ans = m.Correct;
}
Debug.WriteLine("The message recieved from Node.js:");
Debug.WriteLine(the_message);
textBox = Sld.Shapes.AddTextbox(Microsoft.Office.Core.MsoTextOrientation.msoTextOrientationHorizontal, 50, 90, 500, 70);
textBox.TextFrame.TextRange.InsertAfter(ques + "\n A: " + ans_a + "\n B: " + ans_b + "\n C: " + ans_c + "\n D: " + ans_d);
textBox.TextFrame.TextRange.Font.Size = 20;
if (correct_ans == "A")
{
point = series.Points(1) as PowerPoint.Point;
point.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Green);
}
else if (correct_ans == "B")
{
point = series.Points(1) as PowerPoint.Point;
point.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Green);
}
else if (correct_ans == "C")
{
point = series.Points(3) as PowerPoint.Point;
point.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Green);
}
else if (correct_ans == "D")
{
point = series.Points(4) as PowerPoint.Point;
point.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Green);
}
((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("B2"))).FormulaR1C1 = Convert.ToInt32(num_a);
((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("B3"))).FormulaR1C1 = Convert.ToInt32(num_b);
((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("B4"))).FormulaR1C1 = Convert.ToInt32(num_c);
((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("B5"))).FormulaR1C1 = Convert.ToInt32(num_d);
}
catch (InvalidCastException e)
{
Debug.WriteLine("Source: ", e.Source);
}
};
ws.Connect();
ws.OnError += (sender12, sent) =>
{
Debug.WriteLine("error " + sent.Message);
};
ws.OnClose += (sender12, sent) =>
{
Debug.WriteLine("disconnect with host");
};
}
