Solved

Creating a Chart in Excel with C#

Posted on 2009-05-20
9
7,412 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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

707 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

13 Experts available now in Live!

Get 1:1 Help Now