Solved

Creating a Chart in Excel with C#

Posted on 2009-05-20
9
7,430 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
 
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
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.

 

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:kacor
ID: 24440736
Rory,

please contact me per e-mail

kacor
0
 
LVL 85

Expert Comment

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
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…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

863 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

Need Help in Real-Time?

Connect with top rated Experts

28 Experts available now in Live!

Get 1:1 Help Now