bigphildogg86
asked on
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.
Any help appreciated, I tried working with SeriesCollection but I was not able to get that working at all.
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.
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;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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
Rory
ASKER
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!
Can't you use:
chartPage.SeriesCollection
to get the whole lot?
chartPage.SeriesCollection
to get the whole lot?
ASKER
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();
}
Rory,
please contact me per e-mail
kacor
please contact me per e-mail
kacor
Done! :)
ASKER