Solved

Creating a Chart in Excel with C#

Posted on 2009-05-20
9
7,504 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 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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
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

More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

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
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

724 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