asked on

Combination Chart

Hi
I try to make combination colum and line chart in excel throug c# with vs2008.
I giving what output i geting and my code and what out i need which i manualy created in excel and step
pls find attachment and code

In attachment Mychart sheet Output i geting through c# coding;
Sheet1 from A1 to C20 input i using
from G4 to N19 my required output
From A24 to A33 Steps for crearing that output

I need to know how make that graph in excel through c# vss2008

private void button4_Click(object sender, EventArgs e)
            
             {
 Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
             Excel.Workbook oWB;
             Excel.Worksheet oWS;
             oWB = oXL.Workbooks.Add(Missing.Value);
             oWS = (Excel.Worksheet)oWB.Worksheets.get_Item(1);
             Excel.Range oResizeRange;
             object misValueDelComp = System.Reflection.Missing.Value;
             oResizeRange = oWS.get_Range("A1:C20", Missing.Value).get_Resize(Missing.Value,Missing.Value);

 Excel.Chart chart = AddChart(oWB, " mychart ", "the chart title", Excel.XlChartType.xlColumnClustered, oResizeRange, Excel.XlRowCol.xlRows);
 oWB.SaveAs("TestGraph.xls", Excel.XlFileFormat.xlWorkbookNormal, misValueDelComp, misValueDelComp, misValueDelComp, misValueDelComp, MyExcel.XlSaveAsAccessMode.xlExclusive, misValueDelComp, misValueDelComp, misValueDelComp, misValueDelComp, misValueDelComp);



}



 public static Excel.Chart AddChart(Excel.Workbook workbook, string chartSheetName, string title, Excel.XlChartType chartType, Excel.Range dataRange, Excel.XlRowCol byRowOrCol)
             {
                 Excel.Chart chart;
                 chart = (Excel.Chart)workbook.Charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                 chart.ChartType = chartType;
                 chart.Location(Excel.XlChartLocation.xlLocationAutomatic, chartSheetName);
                 chart.SetSourceData(dataRange, byRowOrCol);
                 chart.HasTitle = true;
                 chart.ChartTitle.Text = title;
                 return chart;
             }

Open in new window

TestGraph.xls

cyberkiwi

This open TestGraph.xls for the data

        private void button6_Click(object sender, EventArgs e)
        {
            Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
            oXL.DisplayAlerts = false;
            Microsoft.Office.Interop.Excel.Workbook oWB;
            Microsoft.Office.Interop.Excel.Worksheet oWS;
            oWB = oXL.Workbooks.Open(@"C:\temp\TestGraph.xls", Missing.Value,Missing.Value,
                Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,
                Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,
                Missing.Value,Missing.Value); //oXL.Workbooks.Add(Missing.Value);
             oWS = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Worksheets.get_Item(1);
             Microsoft.Office.Interop.Excel.Range oResizeRange;
             object misValueDelComp = System.Reflection.Missing.Value;
             oResizeRange = oWS.get_Range("A1:C20", Missing.Value).get_Resize(Missing.Value,Missing.Value);

             Microsoft.Office.Interop.Excel.Chart chart = AddChart(oWB, " mychart ", "the chart title", Microsoft.Office.Interop.Excel.XlChartType.xlColumnClustered, oResizeRange, Microsoft.Office.Interop.Excel.XlRowCol.xlRows);
             oWB.SaveAs(@"C:\temp\TestGraph2.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValueDelComp, misValueDelComp, misValueDelComp, misValueDelComp, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValueDelComp, misValueDelComp, misValueDelComp, misValueDelComp, misValueDelComp);
             oXL.Quit();
        }



 public static Microsoft.Office.Interop.Excel.Chart AddChart(Microsoft.Office.Interop.Excel.Workbook workbook, string chartSheetName, string title, Microsoft.Office.Interop.Excel.XlChartType chartType, Microsoft.Office.Interop.Excel.Range dataRange, Microsoft.Office.Interop.Excel.XlRowCol byRowOrCol)
             {
                 Microsoft.Office.Interop.Excel.Chart chart;
                 chart = (Microsoft.Office.Interop.Excel.Chart)workbook.Charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                 chart.SetSourceData(dataRange.Worksheet.get_Range("B1", "D21"), Missing.Value); //;byRowOrCol);
                 chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlLine;
                 chart.HasTitle = true;
                 chart.ChartTitle.Text = title;
                 Microsoft.Office.Interop.Excel.Series series;
                 series = (Microsoft.Office.Interop.Excel.Series)chart.SeriesCollection(1);
                 series.Name = "='Sheet1'!$A$1";
                 series.Values = "='Sheet1'!$A$2:$A$21";
                 series.XValues = "='Sheet1'!$B$2:$B$21";
                 series.AxisGroup = XlAxisGroup.xlSecondary;
                 series.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlColumnClustered;
                 series = (Microsoft.Office.Interop.Excel.Series)chart.SeriesCollection(2);
                 series.Name = "='Sheet1'!$C$1";
                 series.Values = "='Sheet1'!$C$2:$C$21";
                 series.XValues = "='Sheet1'!$B$2:$B$21";
                 ((Axis)chart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary)).CategoryType = XlCategoryType.xlTimeScale;
                 ((Axis)chart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlSecondary)).CategoryType = XlCategoryType.xlTimeScale;
                 return chart;
             }
    }

Open in new window

RakeshBhandari

ASKER

Hi
Thanks for your answer. But when i building application geting error like

The name 'XlAxisGroup' does not exist in the current context
Error      13      The type or namespace name 'Axis' could not be found (are you missing a using directive or an assembly reference?)
Error      14      The name 'XlAxisType' does not exist in the current context
Error      15      The name 'XlAxisGroup' does not exist in the current context
Error      16      The name 'XlCategoryType' does not exist in the current context
Error      17      The type or namespace name 'Axis' could not be found (are you missing a using directive or an assembly reference?)

cyberkiwi

You need to add a reference to either or both of

Microsoft.Office.Interop
Microsoft.Office.Interop.Excel

RakeshBhandari

ASKER

Yes. now its working

thank you

RakeshBhandari

ASKER

Hi
Thanks for your answer. I geting output. But in output in datepart(X axis) Non feeded Date like 6/05/2010, 6/6/2010, 6/12/2010, 6/13/2010 ............. coming. how can i avoid those dates ?

ASKER CERTIFIED SOLUTION

cyberkiwi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial