Solved

Creating a Chart in Excel with C#

Posted on 2009-05-20
9
7,442 Views
Last Modified: 2013-12-17
I have been working on trying to make a chart in Excel from a C# application.

I'm getting the data itself into the sheet fine.  However I need to make a line chart and add each series individually as each series will not have the same amount of data points. Right now C# is now allowing me to set the chart type to xlLine.  

It is also pulling from a SQL database which is working fine.

This is the error I get when trying to make the type xlLine
Unable to cast COM object of type 'Microsoft.Office.Interop.Excel.ChartClass' to interface type 'Microsoft.Office.Interop.Excel._Chart'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{000208D6-0000-0000-C000-000000000046}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).  

Any help appreciated, I tried working with SeriesCollection but I was not able to get that working at all.




object misValue = System.Reflection.Missing.Value;
            Microsoft.Office.Interop.Excel.ApplicationClass excel = new ApplicationClass();
            Microsoft.Office.Interop.Excel.Workbook xlWorkBook = excel.Workbooks.Add(misValue);
            Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
 
            Microsoft.Office.Interop.Excel.Range chartRange;
 
            Microsoft.Office.Interop.Excel.ChartObjects xlCharts = (Microsoft.Office.Interop.Excel.ChartObjects)ws.ChartObjects(Type.Missing);
            Microsoft.Office.Interop.Excel.ChartObject myChart = (Microsoft.Office.Interop.Excel.ChartObject)xlCharts.Add(10, 80, 300, 250);
            Microsoft.Office.Interop.Excel.Chart chartPage = new Microsoft.Office.Interop.Excel.Chart();
 
            Microsoft.Office.Interop.Excel.SeriesCollection evapCollection;
 
 
            excel.Visible = true;
            Worksheet worksheet = (Worksheet)excel.ActiveSheet;
            worksheet.Activate();
            for (int i = 0; i < dgvResults.RowCount; i++)
            {
 
                sql = "Select experiment_ID, FacilityName from experimentdatafull where SummaryName = \'" + dgvResults["SummaryName", i].Value + "\'";
                reader = dbExpID.getReader(sql);
                reader.Read();
                currentExpID = Convert.ToInt32(reader["ExpID"].ToString());
                facility = reader["Place"].ToString();
                dbExpID.closeReader();
                sql = "Select * from data where (ExpID = \'" + currentExpID + "\') AND (ElapsedTime != 0) ORDER BY ElapsedTime";
                reader = dbExpID.getReader(sql);
 
                ws.Cells[1, columns] = dgvResults["SummaryName", i].Value.ToString();
 
                while (reader.Read())
                {
 
 
                    if (facility != ("Test"))
                    {
                        FieldVar = reader["FieldName"].ToString();
                        ws.Cells[count, columns] = FieldVar;
                        finalarray[count] = Convert.ToDouble(FieldVar);
                        //evapCollection.Add(finalarray,XlRowCol.xlRows,misValue,misValue,misValue);
                    }
 
                    count += 1;
                    chartPage.ChartWizard(finalarray,Microsoft.Office.Interop.Excel.XlChartType.xlLine,misValue,misValue,misValue,misValue,misValue,misValue,misValue,misValue,misValue);
 
                }
 
 
 
                //series1.XValues = ws.get_Range(columns, 1);
                //series1.Values = ws.get_Range(columns, count);
                dbExpID.closeReader();
                chartPage.ChartWizard(finalarray, Microsoft.Office.Interop.Excel.XlChartType.xlLine, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
                columns += 1;
                count = 2;

Open in new window

0
Comment
Question by:bigphildogg86
  • 4
  • 4
9 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 24431133
Instead of this:
Microsoft.Office.Interop.Excel.Chart chartPage = new Microsoft.Office.Interop.Excel.Chart();
try:
Microsoft.Office.Interop.Excel.Chart chartPage = myChart.get_Chart();
since the chartobject already contains a chart.
Regards,
Rory
0
 

Author Comment

by:bigphildogg86
ID: 24437424
Awesome, that was the issue for setting it up.  Would you happen to know how I can set the Axis Labels as well as fill in the horizontal axis with whatever scale I want it on?
0
 

Author Comment

by:bigphildogg86
ID: 24437722
Nevermind I got the axis labels, I'm just now trying to figure out how to add series.  If i just add the entire set as the datasource of a XYScatter chart then it tries to use the first column for the x values everytime.  However I have all different X Column Values for Each Column of Y Values.  I would like to lay out the columns with X Values in one column then Y Values in the next column, then for the next series X values in the next column and Y Values in the next column and so on.
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 24438759
I don't know very much about C#, but in VBA you would use the Add method of the Seriescollection object (evapCollection in your code) to create a new Series object. You can then assign ranges to its XValues and Values properties (using get_Range) and repeat as necessary. Does that help?
Rory
0
 

Author Comment

by:bigphildogg86
ID: 24438979
Yeah I've been messing with that.  Was working on trying to assign the graph's seriescollection to the evapcollection.  The seriescollection of the graph still wants me to use a index even when trying to just assign the collection of the graph to my own so that I can get the add methods and such.  But I'll keep working on it,  you got me going on it so thanks!
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 24439072
Can't you use:
chartPage.SeriesCollection(system.missing)
to get the whole lot?
0
 

Author Comment

by:bigphildogg86
ID: 24440290
Yeah I ended up getting it all figured out! :)  And yeah lol, I figured out the round-about way.  Wish I had of read your post sooner!  Here is what my code ended up looking like.  I just use arrays now to directly inject into the series. Definitely had to pull from a lot of different concepts and websites. No where had exactly what I was looking for.  Definitely happy it's working now.  I appreciate all your help! :D
           Microsoft.Office.Interop.Excel.ApplicationClass excel = new ApplicationClass();
            Microsoft.Office.Interop.Excel.Workbook xlWorkBook = excel.Workbooks.Add(misValue);
            Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
 
 
            Microsoft.Office.Interop.Excel.ChartObjects xlCharts = (Microsoft.Office.Interop.Excel.ChartObjects)ws.ChartObjects(Type.Missing);
            Microsoft.Office.Interop.Excel.ChartObject myChart = (Microsoft.Office.Interop.Excel.ChartObject)xlCharts.Add(10, 80, 800, 640);
            Microsoft.Office.Interop.Excel.Chart chartPage = myChart.Chart;            
 
 
            chartPage.ChartWizard(misValue, Microsoft.Office.Interop.Excel.XlChartType.xlXYScatterLines, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
 
            Microsoft.Office.Interop.Excel.SeriesCollection evapCollection = (Microsoft.Office.Interop.Excel.SeriesCollection)chartPage.SeriesCollection(misValue);
 
 
            for (int i = 0; i < dgvResults.RowCount; i++)
            {
 
                sql = "Select field1, field2 from sqlTable where SummaryName = \'" + dgvResults["SummaryName", i].Value + "\'";
                reader = dbAccessClass.getReader(sql);
                reader.Read();
                thisRow = Convert.ToInt32(reader["ID"].ToString());
                placeName = reader["Place"].ToString();
                dbAccessClass.closeReader();
                sql = "Select * from fulldata where (ID = \'" + thisRow + "\') ORDER BY ElapsedTime";
                reader = dbAccessClass.getReader(sql);
 
                
 
                //ws.Cells[1, columns + 1] = "Time";
                //ws.Cells[1, columns] = dgvResults["SummaryName", i].Value.ToString();
                finalarray = new ArrayList();
                timearray = new ArrayList();
                while (reader.Read())
                {
                            timearray.Add(reader["ElapsedTime"]);
                            //ws.Cells[count, columns] = timearray[count].ToString();
                           
 
                            finalarray.Add(reader["DataforGraph"]);                            
                            //ws.Cells[count, columns+1] = finalarray[count];                  
                            
                        
                        
                    }
 
                    count += 1;
                    
 
                }
 
 
                dbAccessClass.closeReader();
 
                for (int x = finalarray.Count-1; x > 0; x--)
                {
                    if (finalarray[x].Equals(DBNull.Value))
                    {
                        finalarray.RemoveAt(x);
                        timearray.RemoveAt(x);
                    }
 
                    
                }
                oSeries = evapCollection.NewSeries();
                oSeries.XValues = timearray.ToArray();
                oSeries.Values = finalarray.ToArray();
                oSeries.Name = dgvResults["SummaryName", i].Value.ToString();
                
                
                columns += 2;
                count = 2;
 
            }
 
 
 
            chartPage.HasTitle = true;
            chartPage.ChartTitle.Text = "Chart Title";
            Microsoft.Office.Interop.Excel.Axis axis;
            axis = (Microsoft.Office.Interop.Excel.Axis)chartPage.Axes(Microsoft.Office.Interop.Excel.XlAxisType.xlCategory, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary);
            axis.HasTitle = true;
            axis.AxisTitle.Text = "X Axis Title";
            axis = (Microsoft.Office.Interop.Excel.Axis)chartPage.Axes(Microsoft.Office.Interop.Excel.XlAxisType.xlValue, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary);
            axis.HasTitle = true;
            axis.AxisTitle.Text = "Y Axis Title";
 
 
            excel.Visible = true;
            Worksheet worksheet = (Worksheet)excel.ActiveSheet;
            ((Microsoft.Office.Interop.Excel._Worksheet)worksheet).Activate();
            loading.Dispose();     
 
        }

Open in new window

0
 
LVL 10

Expert Comment

by:János Szabó
ID: 24440736
Rory,

please contact me per e-mail

kacor
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 24440901
Done! :)
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question