void SetupAxes(Excel._Chart oChart, string xLabel, float minX, float maxX, string yLabel, float minY, float maxY)
{
Excel.Axis axis = (Excel.Axis)oChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
if (maxX != -1.0f)
{
axis.MaximumScale = maxX;
}
if (minX != -1.0f)
{
axis.MinimumScale = minX;
}
axis.HasTitle = true;
axis.AxisTitle.Text = xLabel;
axis = (Excel.Axis)oChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
if (maxY != -1.0f)
{
axis.MaximumScale = maxY;
}
if (minY != -1.0f)
{
axis.MinimumScale = minY;
}
axis.HasMajorGridlines = false;
axis.HasMinorGridlines = false;
axis.HasTitle = true;
axis.AxisTitle.Text = yLabel;
}
Excel._Chart MakeChart(Excel._Workbook oWB, Excel.Range input, Excel.XlChartType type, Excel.XlRowCol rowcol, int numXValues, int numLabels, bool bLegend)
{
Excel._Chart oChart = (Excel._Chart)oWB.Charts.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
oChart.ChartWizard(input, type, Missing.Value, rowcol, numXValues, numLabels, bLegend, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
oChart.ChartArea.Format.Line.Visible = Microsoft.Office.Core.MsoTriState.msoFalse;
return(oChart);
}
/// <summary>
/// Display the result
/// 1. Create a new sheet
/// 2. Create two range to display the key and value
/// 3. Create a chart based the range object
/// </summary>
private void Display1DResult(Result1D result1d)
{
if (result1d.Keys.Count > 0)
{
Excel.Worksheet resultSheet = this.Sheets.Add(missing, missing, missing, missing) as Excel.Worksheet;
Excel.Range KeyColumnHead = resultSheet.get_Range("A1", missing);
KeyColumnHead.Value2 = result1d.KeyLabel;
Excel.Range ValueColumnHead = resultSheet.get_Range("B1", missing);
ValueColumnHead.Value2 = result1d.ValueLabel;
Excel.Range headRange = resultSheet.get_Range("A1", "B1");
headRange.Style = headStyle;
headRange.EntireColumn.AutoFit( );
//Bind the data
float[,] keyvalues = new float[result1d.Keys.Count, 2];
for (int i = 0; i < result1d.Keys.Count; i++)
{
keyvalues[i, 0] = result1d.Keys[i];
keyvalues[i, 1] = result1d.Values[i];
}
string rangeKey = string.Format("A2:A{0}", result1d.Keys.Count + 1);
string rangeValue = string.Format("B2:B{0}", result1d.Values.Count + 1);
Excel.Range dataRange = resultSheet.get_Range(rangeKey, rangeValue);
dataRange.Value2 = keyvalues;
dataRange.Style = dataStyle;
dataRange.EntireColumn.AutoFit( );
//Add a Chart for the selected data.
Excel._Chart oChart = (Excel._Chart) this.Charts.Add(Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
//Use the ChartWizard to create a new chart from the selected data.
Excel.Range oResizeRange = resultSheet.get_Range(rangeValue, missing).get_Resize(
result1d.Keys.Count, 1);
oChart.ChartWizard(oResizeRange, Excel.XlChartType.xlLine, Type.Missing,
Excel.XlRowCol.xlColumns, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
oChart.Visible = Microsoft.Office.Interop.Excel.XlSheetVisibility.xlSheetHidden;
//Configure the format of chart
ConfigureExcelChartFormat(resultSheet.Name, result1d.ValueLabel, oChart);
//Position of the chart
resultSheet.Shapes.Item(1).Top = (float)(double)oResizeRange.Top;
resultSheet.Shapes.Item(1).Left = (float)(double)oResizeRange.Left + (float)(double)oResizeRange.Width;
}
}
private void button1_Click(object sender, EventArgs e)
{
#region For Excel Part
oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.Visible = true;
oWB = (Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));
oSheet = (Excel._Worksheet)oWB.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
string[] titleCCSummary = { "Charge Class", "Total Call", "Total Cost", "Total Duration" };
oSheet.get_Range("A4", "D4").Value2 = titleCCSummary;
oRng = oSheet.get_Range("A1", "D2");
oRng.Merge(Missing.Value);
string Formula = "Charge Class Summary from " + forform1.stat_datetimestart.Date.ToString("yyyy-MM-dd") + " to " + forform1.stat_datetimeend.Date.ToString("yyyy-MM-dd") + " From ";
for (int ServicesIndex = 0; ServicesIndex <= 4; ServicesIndex++)
{
if (forform1.stat_flagservices[ServicesIndex])
{
Formula = string.Concat(Formula, ServiceStr[ServicesIndex], ", ");
}
}
Formula = Formula.Remove(Formula.Length - 2);
oRng.Formula = Formula;
oRng.Font.Bold = true;
oRng.VerticalAlignment = Excel.XlVAlign.xlVAlignDistributed;
oRng.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
oRng = oSheet.get_Range("A1", "D1");
oRng.EntireColumn.AutoFit();
oRng.EntireColumn.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
string CellSelectionStr;
for (int ChargeClassIndex = 1; ChargeClassIndex <= 11; ChargeClassIndex++)
{
CellSelectionStr = string.Concat("A", ChargeClassIndex + 4);
oSheet.get_Range(CellSelectionStr, CellSelectionStr).Value2 = ChargeClassIndex.ToString();
CellSelectionStr = string.Concat("B", ChargeClassIndex + 4);
oSheet.get_Range(CellSelectionStr, CellSelectionStr).Value2 = forChargeClass.TotalRec [ChargeClassIndex - 1].ToString();
CellSelectionStr = string.Concat("C", ChargeClassIndex + 4);
oSheet.get_Range(CellSelectionStr, CellSelectionStr).Value2 = forChargeClass.sumCallCost[ChargeClassIndex - 1].ToString();
CellSelectionStr = string.Concat("D", ChargeClassIndex + 4);
oSheet.get_Range(CellSelectionStr, CellSelectionStr).Value2 = forChargeClass.SumTotalDuration[ChargeClassIndex - 1].ToString();
}
oRng = oSheet.get_Range("C5:C15", Missing.Value);
oChart = (Excel._Chart)oWB.Charts.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
oChart.ChartWizard(oRng, Excel.XlChartType.xl3DPie, Missing.Value, Excel.XlRowCol.xlColumns, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
oChart.Name = "ChargeClass Chart(Cost)";
#endregion
}
void BuildFramerateAnalysis(Excel._Workbook oWB, Word.Document oDoc, GetBucketValue grv, string uniqueName, string bookmarkName, float k_ignoreTime, int maxFPS)
{
const float k_fpsSize = 400.0f;
const float aspect = 3.0f / 4.0f;
Excel._Worksheet oSheet = AddWorksheet(oWB, uniqueName);
FillBuckets(oSheet, grv, 0, maxFPS, k_ignoreTime);
Excel._Chart oChart = MakeChart(oWB, oSheet.get_Range("C1", "D61"), Excel.XlChartType.xlXYScatterSmoothNoMarkers, Excel.XlRowCol.xlColumns, 1, 0, false);
SetupAxes(oChart, "Frame Rate", 0.0f, maxFPS, "Percentage of Time Below", 0.0f, -1.0f);
Word.InlineShape oShape = InsertChart(oChart, oDoc, uniqueName + "acc", bookmarkName, k_fpsSize, k_fpsSize * aspect, 0.5f);
oChart = MakeChart(oWB, oSheet.get_Range("A1", "B61"), Excel.XlChartType.xlXYScatterSmoothNoMarkers, Excel.XlRowCol.xlColumns, 1, 0, false);
SetupAxes(oChart, "Frame Rate", 0.0f, maxFPS, "Percentage of Time", 0.0f, -1.0f);
oShape = InsertChart(oChart, oDoc, uniqueName, bookmarkName, k_fpsSize, k_fpsSize * aspect, 0.5f);
}
Word.InlineShape InsertChart(Excel._Chart oChart, Word.Document oDoc, string filename, string placementName, float width, float height, float scale)
{
object oMissing = Missing.Value;
string filepath = System.IO.Path.GetTempPath() + filename + ".png";
oChart.ChartArea.Width = width;
oChart.ChartArea.Height = height;
oChart.Export(filepath, "png", false);
object oEndOfDoc = placementName;
Word.Range insertPoint = oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range;
Word.InlineShape oShape = insertPoint.InlineShapes.AddPicture(filepath, ref oMissing, ref oMissing, ref oMissing);
oShape.Width = width * scale;
oShape.Height = height * scale;
return(oShape);
}
/// <summary>
/// This method wil set the papersize for a chart
/// </summary>
/// <param name="chart"></param>
private static void SetChartPaperSize(ExcelInterop._Chart chart)
{
var pageSetup = chart.PageSetup;
var pages = pageSetup.Pages;
try
{
pageSetup.LeftHeader = chart.Name;
pageSetup.PaperSize = ExcelInterop.XlPaperSize.xlPaperA4;
pageSetup.Orientation = ExcelInterop.XlPageOrientation.xlLandscape;
}
finally
{
Marshal.ReleaseComObject(pages);
Marshal.ReleaseComObject(pageSetup);
}
}
private void ConfigureExcelChartFormat(string sheetName, string seriesName, Excel._Chart oChart)
{
//Serie format
Excel.Series oSeries = (Excel.Series)oChart.SeriesCollection(1);
oSeries.Name = seriesName;
oSeries.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlLine;
oSeries.Smooth = true;
oSeries.Border.ColorIndex = 3;
oSeries.Border.Weight = 3;
//Plot area format
oChart.PlotArea.Interior.ColorIndex = 0;
oChart.PlotArea.Border.ColorIndex = 5;
//X axis format
Excel.Axis xAxis = (Excel.Axis)oChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
xAxis.MajorGridlines.Border.ColorIndex = 5;
xAxis.Border.ColorIndex = 5;
xAxis.TickLabels.Font.Name = "Verdana";
xAxis.TickLabels.Font.Size = 8.0f;
xAxis.TickLabels.AutoScaleFont = false;
//Y axis format
Excel.Axis yAxis = (Excel.Axis)oChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
yAxis.TickLabels.Font.Name = "Verdana";
yAxis.TickLabels.Font.Size = 8.0f;
yAxis.TickLabels.AutoScaleFont = false;
yAxis.Border.ColorIndex = 5;
yAxis.MajorTickMark = Microsoft.Office.Interop.Excel.XlTickMark.xlTickMarkNone;
//Legend format
oChart.Legend.Font.Name = "Verdana";
oChart.Legend.Font.Size = 9.0f;
oChart.Legend.Font.Bold = true;
oChart.Legend.AutoScaleFont = false;
oChart.Legend.Position = Microsoft.Office.Interop.Excel.XlLegendPosition.xlLegendPositionTop;
//Chart format
oChart.HasTitle = false;
oChart.Location(Excel.XlChartLocation.xlLocationAsObject, sheetName);
}
private void CreateChart(Excel._Worksheet oSheet, BackgroundWorker backgroundWorker)
{
//Connect to workbook
Excel._Workbook oWB = (Excel._Workbook)oSheet.Parent;
//Get range of data in which the chart will reference to
Excel.Range oRng = oSheet.get_Range("A1", "C" + getLastUsedRow(oSheet));
//Create chart using ChartWizard
Excel._Chart oChart = (Excel._Chart)oWB.Charts.Add(Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
oChart.ChartWizard(oRng, Excel.XlChartType.xlColumnStacked, Missing.Value,
Excel.XlRowCol.xlRows, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value);
oChart.Location(Excel.XlChartLocation.xlLocationAsObject, oSheet.Name);
//Set location, height and width of the chart
oSheet.Shapes.Item(1).Top = 75;
oSheet.Shapes.Item(1).Left = 600;
oSheet.Shapes.Item(1).Width = 1304;
oSheet.Shapes.Item(1).Height = 419;
backgroundWorker.ReportProgress(95);
}
protected void DrawChart(XlChartType ChartType)
{
// Variable declaration
object misValue = System.Reflection.Missing.Value;
ExcelObj.Range chartRange = null;
try
{
this.Validate();
// split the cell range for the work sheet range
string cell1 = strCellRange.Split(':')[0];
string cell2 = strCellRange.Split(':')[1];
base.InitWorkSheet();
if (base.SheetIndex > 0 && base.xlWorkSheet != null)
{
ExcelObj.ChartObjects xlCharts = (ExcelObj.ChartObjects)base.xlWorkSheet.ChartObjects(Type.Missing);
try
{
// Define the cell range to the work sheet
chartRange = xlWorkSheet.get_Range(cell1, cell2);
}
catch (Exception)
{
base.ClearObject();
throw new Exception("Invalid Range");
}
// fix chart position by left,top,width,height
ExcelObj.ChartObject chartObj = (ExcelObj.ChartObject)xlCharts.Add(dblChartLeft, dblChartTop, dblChartWidth, dblChartHeight);
// Define char object
ExcelObj._Chart chart = chartObj.Chart;
// assign the datasource for the chart
chart.SetSourceData(chartRange, misValue);
// set the chart type.
chart.ChartType = ChartType;
// Ledgend show / hide
chart.HasLegend = this.Options.ShowLedgend;
// assign the chart title based on empty
if (!string.IsNullOrEmpty(strChartTitle))
{
chart.HasTitle = true;
chart.ChartTitle.Text = strChartTitle;
}
else
{
chart.HasTitle = false;
}
XlDataLabelsType XlDataLabelsType = (XlDataLabelsType)this.Options.DataLabelsType;
XlDataLabelsType XlLedgendKey = (XlDataLabelsType)this.Options.LedgendKey;
// assign the data lable properties
chart.ApplyDataLabels(XlDataLabelsType, XlLedgendKey, this.Options.AutoText, this.Options.HasLeaderLines,
this.Options.ShowSeriesName, this.Options.ShowCategoryName, this.Options.ShowValue,
this.Options.ShowPercentage, this.Options.ShowBubbleSize, this.Options.Separator);
if (this.strImagecopy != null && !string.IsNullOrEmpty(this.strImagecopy))
{
string strExten = new FileInfo(this.strImagecopy).Extension;
strExten = strExten.Replace(".", "");
chart.Export(this.strImagecopy, strExten.ToUpper(), misValue);
}
base.SaveWorkBook(true);
}
}
catch (ValidationException Vex)
{
base.ClearObject();
throw Vex;
}
catch (Exception ex)
{
base.ClearObject();
throw new Exception("DrawChart : " + ex.Message);
}
}
private void DisplayFFTResult(Result2D result2d)
{
if (result2d.Keys.Count > 0)
{
Excel.Worksheet resultSheet = this.Sheets.Add(missing, missing, missing, missing) as Excel.Worksheet;
//Bind Head's data
string[] heads = new string[3] {
result2d.KeyLabel, result2d.ValueLabel1, result2d.ValueLabel2
};
Excel.Range headRange = resultSheet.get_Range("A1:C1", missing);
headRange.Style = headStyle;
headRange.Value2 = heads;
//Bind the data : key, value1,value2
float[,] keyvalues = new float[result2d.Keys.Count, 3];
for (int i = 0; i < result2d.Keys.Count; i++)
{
keyvalues[i, 0] = result2d.Keys[i];
keyvalues[i, 1] = result2d.Values1[i];
keyvalues[i, 2] = result2d.Values2[i];
}
string rangeKey = string.Format("A2:C{0}", result2d.Keys.Count + 1);
Excel.Range dataRange = resultSheet.get_Range(rangeKey, missing);
dataRange.Value2 = keyvalues;
dataRange.Style = dataStyle;
dataRange.EntireColumn.AutoFit( );
//Chart 1
//Add a Chart for the selected data.
Excel._Chart oChart1 = (Excel._Chart) this.Charts.Add(Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
//Use the ChartWizard to create a new chart from the selected data.
string value1Range = string.Format("B2:B{0}", result2d.Values1.Count + 1);
Excel.Range oResizeRange = resultSheet.get_Range(value1Range, missing).get_Resize(
result2d.Values1.Count, 1);
oChart1.ChartWizard(oResizeRange, Excel.XlChartType.xlLine, Type.Missing,
Excel.XlRowCol.xlColumns, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
oChart1.Visible = Microsoft.Office.Interop.Excel.XlSheetVisibility.xlSheetHidden;
//Configure the format of chart
ConfigureExcelChartFormat(resultSheet.Name, result2d.ValueLabel1, oChart1);
//Position of the chart
resultSheet.Shapes.Item(1).Top = (float)(double)dataRange.Top;
resultSheet.Shapes.Item(1).Left = (float)(double)dataRange.Left + (float)(double)dataRange.Width;
//Chart 2
//Add a Chart for the selected data.
Excel._Chart oChart2 = (Excel._Chart) this.Charts.Add(Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
//Use the ChartWizard to create a new chart from the selected data.
string value2Range = string.Format("C2:C{0}", result2d.Values2.Count + 1);
oResizeRange = resultSheet.get_Range(value2Range, missing).get_Resize(
result2d.Values2.Count, 1);
oChart2.ChartWizard(oResizeRange, Excel.XlChartType.xlLine, Type.Missing,
Excel.XlRowCol.xlColumns, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
oChart2.Visible = Microsoft.Office.Interop.Excel.XlSheetVisibility.xlSheetHidden;
//Configure the format of chart
ConfigureExcelChartFormat(resultSheet.Name, result2d.ValueLabel2, oChart2);
//Position of the chart
resultSheet.Shapes.Item(2).Top = (float)resultSheet.Shapes.Item(1).Top + (float)resultSheet.Shapes.Item(1).Height;
resultSheet.Shapes.Item(2).Left = (float)(double)dataRange.Left + (float)(double)dataRange.Width;
}
}
