Combination Chart

RakeshBhandari
RakeshBhandari used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
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

Author

Commented:
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?)      
      
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
You need to add a reference to either or both of

Microsoft.Office.Interop
Microsoft.Office.Interop.Excel
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

Author

Commented:
Yes. now its  working



thank you



Author

Commented:
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 ?
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
In the X-axis, because it is defined as a Time series, the tick marks are controlled via the settings
Min, Max, Tick interval, small tick interval.  I don't remember if there is an option to show ticks only where source data exists - probably a question for the Excel zone.

I generated most of the code from recording/trying a Macro in Excel and porting the code over.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial