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