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