[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 7603
  • Last Modified:

Creating a Chart in Excel with C#

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
bigphildogg86
Asked:
bigphildogg86
  • 4
  • 4
1 Solution
 
Rory ArchibaldCommented:
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
 
bigphildogg86Author Commented:
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
 
bigphildogg86Author Commented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Rory ArchibaldCommented:
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
 
bigphildogg86Author Commented:
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
 
Rory ArchibaldCommented:
Can't you use:
chartPage.SeriesCollection(system.missing)
to get the whole lot?
0
 
bigphildogg86Author Commented:
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
 
kacorretiredCommented:
Rory,

please contact me per e-mail

kacor
0
 
Rory ArchibaldCommented:
Done! :)
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now