?
Solved

Creating a Chart in Excel with C#

Posted on 2009-05-20
9
Medium Priority
?
7,535 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 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
Interactive Way of Training for the AWS CSA Exam

An interactive way of learning that will help you visualize core concepts so that you can be more effective when taking your AWS certification exam.  Built for students by a student to help them understand the concepts that they are being taught.

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

Command Line Tips and Tricks

The command line is a powerful tool at the disposal of every Linux user. Although Linux distros come with beautiful user interfaces, it's worthwhile to learn the command line because it allows you to do a number of things that you otherwise cannot do from the GUI.  

Question has a verified solution.

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

The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

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