Interop Chart Series Error
 

Hi Everyone,

i got a big problem creating Charts programmatically. I have a loop where n
Charts get added to a worksheet. The Charts are to be placed behind the sheet
not inside the sheets. So far everything works fine.

If i add more than 2 Charts in my loop i find one of my Excel-Charts contain
3 Series instead of one. I debugged a bit.

When the loop is hit for the first time and the chart is created it shows a
to seriescount of 1. I can delete this one and put in my own one.

The second chart starts off with 2 series being there by default. Delete is
ok.

Third Chart has 3 Series which are totally messed up. I can't delete them
anymore and so my final Book looks good instead of the third one - 3 Series
there that don't make any sense !! What is the problem here ? I suspect
something is happening to the Chartcollection with every loop. But i am
destroying the Chart / SeriesCollection objects i work with every time a loop
starts.

Funny thing: If i put all the Charts into my Sheet where the data comes from
- all works fine.

If someone could give me hint, please - I am totally lost out here...

Excel Version : 2002 (XP)
..Net 2.0 C#

My Function ist this one:

public void CreateCharts(ref Excel.Worksheet Sheet, ref
Excel.Workbook oWorkbook, int RowCount)
{
//Excel._Chart oChart;
//ChartObjects chartObjs =
(ChartObjects)Sheet.ChartObjects(Type.Missing);

for (int iChart = 0; iChart < this.Count; iChart++)
{
//Erstellen eines neuen Chartobjektes.
this[iChart].ExcelChart =
(Excel._Chart)oWorkbook.Charts.Add(Type.Missing, Type.Missing,
1, Type.Missing);
// ChartObject chartObj = chartObjs.Add(0, 0, 0, 0);
// this[iChart].ExcelChart = chartObj.Chart;

this[iChart].ExcelChart.Select(Type.Missing);

XlChartType type = this[iChart].ChartType;

//Chart-Typ setzen.
this[iChart].ExcelChart.ChartType = type;

//Nach dem aktuellen Sheet platzieren.
//this[iChart].ExcelChart.Move(Type.Missing, Sheet);

//Die Collection von Series auslesen.
Excel.SeriesCollection seriesCollection =

(Excel.SeriesCollection)this[iChart].ExcelChart.SeriesCollection(Type.Missing);

bool bDeleted = false;
int iOldSeriesCount = seriesCollection.Count;

try
{
int iTotalSeriesCount = seriesCollection.Count;
//Alle standardm��ig vorhandenen Series l�schen.
for (int iCount = 1; iCount <= iTotalSeriesCount;
iCount++)
{
seriesCollection.Item(iCount).XValues =
Sheet.get_Range("D4:D7", Type.Missing);
seriesCollection.Item(iCount).Delete();
iOldSeriesCount--;
}

bDeleted = true;
}
catch (Exception ex)
{

}

//Variable, die bestimmt ob die Legende angezeigt wird.
bool bShowLegend = false;

for (int iCount = 0; iCount < this[iChart].SeriesList.Count;
iCount++)
{
//Pr�fen ob Series Werte != null enth�lt. Wenn nicht,
dann wird auch keine Series hinzugef�gt.
if
(!CheckIfSeriesIsValid(Sheet.get_Range(this[iChart].SeriesList[iCount].DataSeries, Type.Missing)))
continue;

Excel.Series series = seriesCollection.NewSeries();

//series.ChartType = XlChartType.xlXYScatterLines;

//Die X-Werte setzen.
if
(CheckIfSeriesIsValid(Sheet.get_Range(this[iChart].Primary_X.DataRange,
Type.Missing)))
series.XValues =
Sheet.get_Range(this[iChart].Primary_X.DataRange, Type.Missing);

//Die Y-Werte setzen.
series.Values =
Sheet.get_Range(this[iChart].SeriesList[iCount].DataSeries, Type.Missing);
//this[iChart].SeriesList[iCount].DataSeries;

//Die Serie der jeweiligen Gruppe zuweisen.
try
{
series.AxisGroup =
this[iChart].SeriesList[iCount].AxisGroup;

if (this[iChart].SeriesList[iCount].Title.Length 0)
{
series.Name =
(string)Sheet.get_Range(this[iChart].SeriesList[iCount].Title,
Type.Missing).Value2;
//Legende wird nur angezeigt, wenn separate
Title f�r einzelne Serien gesetzt wurden.
bShowLegend = true;
}
}
catch
{
}

}

//X-Achsen Daten setzen
Excel.Axis axis =
(Excel.Axis)this[iChart].ExcelChart.Axes(1, XlAxisGroup.xlPrimary);
this[iChart].Primary_X.ProcessAxis(ref axis, ref Sheet);

//Y-Achsen Daten setzen
axis = (Excel.Axis)this[iChart].ExcelChart.Axes(2,
XlAxisGroup.xlPrimary);
this[iChart].Primary_Y.ProcessAxis(ref axis, ref Sheet);

//Sekund�re X-Achse
if (this[iChart].Secondary_X != null)
{
axis = (Excel.Axis)this[iChart].ExcelChart.Axes(1,
XlAxisGroup.xlSecondary);
this[iChart].Secondary_X.ProcessAxis(ref axis, ref Sheet);
}

if (this[iChart].Secondary_Y != null)
{
axis = (Excel.Axis)this[iChart].ExcelChart.Axes(2,
XlAxisGroup.xlSecondary);
this[iChart].Secondary_Y.ProcessAxis(ref axis, ref Sheet);
}

if (!bDeleted)
{
//Falls Standardserien nicht gel�scht werden konnten,
hier nochmals versuchen.
try
{
int iTotalSeriesCount = seriesCollection.Count;
//Alle standardm��ig vorhandenen Series l�schen.
for (int iCount = 1; iCount <= iTotalSeriesCount;
iCount++)
{
seriesCollection.Item(iCount).Delete();
}

bDeleted = true;
}
catch (Exception ex)
{

}
}

this[iChart].ExcelChart.HasLegend = bShowLegend;

////Chart auf dem aktuellen Sheet einf�gen.

//this[iChart].ExcelChart.Location(Excel.XlChartLocation.xlLocat ionAsObject,
Sheet.Name);
//Range oResizeRange;
////Das Ende des Datenteils ermitteln.
//oResizeRange = (Excel.Range)Sheet.Rows.get_Item(RowCount +
2, Type.Missing);
////Alle enthaltenen Charts untereinander platzieren.
//Sheet.Shapes.Item(iChart + 1).Top =
(float)(double)oResizeRange.Top + (iChart * Sheet.Shapes.Item(iChart +
1).Height);
////Chart ganz links platzieren.
//oResizeRange = (Excel.Range)Sheet.Columns.get_Item(1,
Type.Missing);
//Sheet.Shapes.Item(iChart + 1).Left =
(float)(double)oResizeRange.Left;


//Sheet.HPageBreaks.Add(oResizeRange.Top);
//Sample Code to add Page Breaks:
//Add a page break at cell Y30
//workbook.Worksheets[0].HPageBreaks.Add("Y30");
//workbook.Worksheets[0].VPageBreaks.Add("Y30");

//http://www.aspose.com/wiki/default.aspx/Aspose.Cells/PageBreaks.html

//Chart separat anzeigen
this[iChart].ExcelChart.Move(Type.Missing, Sheet);

//Chartname setzen, sofern vorhanden.
if (this[iChart].Name.Length 0)
{
try
{
this[iChart].ExcelChart.HasTitle = true;
this[iChart].ExcelChart.ChartTitle.Text =
(string)Sheet.get_Range(this[iChart].Name, Type.Missing).Value2;
string sChartName =
this[iChart].ExcelChart.ChartTitle.Text;
if (sChartName.Length 28)
sChartName = sChartName.Substring(0, 28);
//Ung�ltige Zeichen f�r den Chartnamen: \\ / ? *
[ or ]
sChartName = sChartName.Replace(':', ' ');
sChartName = sChartName.Replace('\\', ' ');
sChartName = sChartName.Replace('/', ' ');
sChartName = sChartName.Replace('?', ' ');
sChartName = sChartName.Replace('[', ' ');
sChartName = sChartName.Replace(']', ' ');

try
{
//Chartname setzen. Wenn dieser Name bereits
existiert, dann einen Index anh�ngen.
SetChartName(this[iChart].ExcelChart,
sChartName, 0);
}
catch (Exception ex)
{
//Wenn kein ChartName gefunden werden kann, dann
wird eine GUID als Name generiert.
sChartName = System.Guid.NewGuid().ToString();
}
}
catch { }
}

seriesCollection = null;
}
}

Interop Chart Series Error
 

Well, I don't know much C#, but I know this is the wrong way to build a loop
to delete series:

int iTotalSeriesCount = seriesCollection.Count;
for (int iCount = 1; iCount <= iTotalSeriesCount; iCount++)
{
seriesCollection.Item(iCount).Delete();
iOldSeriesCount--;
}

Start with your counter at iTotalSeriesCount and count backwards, or delete
the first series each time:

seriesCollection.Item(1).Delete();

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Oliver" wrote in message
...
Hi Everyone,

i got a big problem creating Charts programmatically. I have a loop where
n
Charts get added to a worksheet. The Charts are to be placed behind the
sheet
not inside the sheets. So far everything works fine.

If i add more than 2 Charts in my loop i find one of my Excel-Charts
contain
3 Series instead of one. I debugged a bit.

When the loop is hit for the first time and the chart is created it shows
a
to seriescount of 1. I can delete this one and put in my own one.

The second chart starts off with 2 series being there by default. Delete
is
ok.

Third Chart has 3 Series which are totally messed up. I can't delete them
anymore and so my final Book looks good instead of the third one - 3
Series
there that don't make any sense !! What is the problem here ? I suspect
something is happening to the Chartcollection with every loop. But i am
destroying the Chart / SeriesCollection objects i work with every time a
loop
starts.

Funny thing: If i put all the Charts into my Sheet where the data comes
from
- all works fine.

If someone could give me hint, please - I am totally lost out here...

Excel Version : 2002 (XP)
.Net 2.0 C#

My Function ist this one:

public void CreateCharts(ref Excel.Worksheet Sheet, ref
Excel.Workbook oWorkbook, int RowCount)
{
//Excel._Chart oChart;
//ChartObjects chartObjs =
(ChartObjects)Sheet.ChartObjects(Type.Missing);

for (int iChart = 0; iChart < this.Count; iChart++)
{
//Erstellen eines neuen Chartobjektes.
this[iChart].ExcelChart =
(Excel._Chart)oWorkbook.Charts.Add(Type.Missing, Type.Missing,
1, Type.Missing);
// ChartObject chartObj = chartObjs.Add(0, 0, 0, 0);
// this[iChart].ExcelChart = chartObj.Chart;

this[iChart].ExcelChart.Select(Type.Missing);

XlChartType type = this[iChart].ChartType;

//Chart-Typ setzen.
this[iChart].ExcelChart.ChartType = type;

//Nach dem aktuellen Sheet platzieren.
//this[iChart].ExcelChart.Move(Type.Missing, Sheet);

//Die Collection von Series auslesen.
Excel.SeriesCollection seriesCollection =

(Excel.SeriesCollection)this[iChart].ExcelChart.SeriesCollection(Type.Missing);

bool bDeleted = false;
int iOldSeriesCount = seriesCollection.Count;

try
{
int iTotalSeriesCount = seriesCollection.Count;
//Alle standardm??ig vorhandenen Series l?schen.
for (int iCount = 1; iCount <= iTotalSeriesCount;
iCount++)
{
seriesCollection.Item(iCount).XValues =
Sheet.get_Range("D4:D7", Type.Missing);
seriesCollection.Item(iCount).Delete();
iOldSeriesCount--;
}

bDeleted = true;
}
catch (Exception ex)
{

}

//Variable, die bestimmt ob die Legende angezeigt wird.
bool bShowLegend = false;

for (int iCount = 0; iCount <
this[iChart].SeriesList.Count;
iCount++)
{
//Pr?fen ob Series Werte != null enth?lt. Wenn nicht,
dann wird auch keine Series hinzugef?gt.
if
(!CheckIfSeriesIsValid(Sheet.get_Range(this[iChart].SeriesList[iCount].DataSeries,
Type.Missing)))
continue;

Excel.Series series = seriesCollection.NewSeries();

//series.ChartType = XlChartType.xlXYScatterLines;

//Die X-Werte setzen.
if
(CheckIfSeriesIsValid(Sheet.get_Range(this[iChart].Primary_X.DataRange,
Type.Missing)))
series.XValues =
Sheet.get_Range(this[iChart].Primary_X.DataRange, Type.Missing);

//Die Y-Werte setzen.
series.Values =
Sheet.get_Range(this[iChart].SeriesList[iCount].DataSeries, Type.Missing);
//this[iChart].SeriesList[iCount].DataSeries;

//Die Serie der jeweiligen Gruppe zuweisen.
try
{
series.AxisGroup =
this[iChart].SeriesList[iCount].AxisGroup;

if (this[iChart].SeriesList[iCount].Title.Length
0)
{
series.Name =
(string)Sheet.get_Range(this[iChart].SeriesList[iCount].Title,
Type.Missing).Value2;
//Legende wird nur angezeigt, wenn separate
Title f?r einzelne Serien gesetzt wurden.
bShowLegend = true;
}
}
catch
{
}

}

//X-Achsen Daten setzen
Excel.Axis axis =
(Excel.Axis)this[iChart].ExcelChart.Axes(1, XlAxisGroup.xlPrimary);
this[iChart].Primary_X.ProcessAxis(ref axis, ref Sheet);

//Y-Achsen Daten setzen
axis = (Excel.Axis)this[iChart].ExcelChart.Axes(2,
XlAxisGroup.xlPrimary);
this[iChart].Primary_Y.ProcessAxis(ref axis, ref Sheet);

//Sekund?re X-Achse
if (this[iChart].Secondary_X != null)
{
axis = (Excel.Axis)this[iChart].ExcelChart.Axes(1,
XlAxisGroup.xlSecondary);
this[iChart].Secondary_X.ProcessAxis(ref axis, ref
Sheet);
}

if (this[iChart].Secondary_Y != null)
{
axis = (Excel.Axis)this[iChart].ExcelChart.Axes(2,
XlAxisGroup.xlSecondary);
this[iChart].Secondary_Y.ProcessAxis(ref axis, ref
Sheet);
}

if (!bDeleted)
{
//Falls Standardserien nicht gel?scht werden konnten,
hier nochmals versuchen.
try
{
int iTotalSeriesCount = seriesCollection.Count;
//Alle standardm??ig vorhandenen Series l?schen.
for (int iCount = 1; iCount <= iTotalSeriesCount;
iCount++)
{
seriesCollection.Item(iCount).Delete();
}

bDeleted = true;
}
catch (Exception ex)
{

}
}

this[iChart].ExcelChart.HasLegend = bShowLegend;

////Chart auf dem aktuellen Sheet einf?gen.

//this[iChart].ExcelChart.Location(Excel.XlChartLocation.xlLocat ionAsObject,
Sheet.Name);
//Range oResizeRange;
////Das Ende des Datenteils ermitteln.
//oResizeRange = (Excel.Range)Sheet.Rows.get_Item(RowCount
+
2, Type.Missing);
////Alle enthaltenen Charts untereinander platzieren.
//Sheet.Shapes.Item(iChart + 1).Top =
(float)(double)oResizeRange.Top + (iChart * Sheet.Shapes.Item(iChart +
1).Height);
////Chart ganz links platzieren.
//oResizeRange = (Excel.Range)Sheet.Columns.get_Item(1,
Type.Missing);
//Sheet.Shapes.Item(iChart + 1).Left =
(float)(double)oResizeRange.Left;


//Sheet.HPageBreaks.Add(oResizeRange.Top);
//Sample Code to add Page Breaks:
//Add a page break at cell Y30
//workbook.Worksheets[0].HPageBreaks.Add("Y30");
//workbook.Worksheets[0].VPageBreaks.Add("Y30");

//http://www.aspose.com/wiki/default.aspx/Aspose.Cells/PageBreaks.html

//Chart separat anzeigen
this[iChart].ExcelChart.Move(Type.Missing, Sheet);

//Chartname setzen, sofern vorhanden.
if (this[iChart].Name.Length 0)
{
try
{
this[iChart].ExcelChart.HasTitle = true;
this[iChart].ExcelChart.ChartTitle.Text =
(string)Sheet.get_Range(this[iChart].Name, Type.Missing).Value2;
string sChartName =
this[iChart].ExcelChart.ChartTitle.Text;
if (sChartName.Length 28)
sChartName = sChartName.Substring(0, 28);
//Ung?ltige Zeichen f?r den Chartnamen: \\ / ?
*
[ or ]
sChartName = sChartName.Replace(':', ' ');
sChartName = sChartName.Replace('\\', ' ');
sChartName = sChartName.Replace('/', ' ');
sChartName = sChartName.Replace('?', ' ');
sChartName = sChartName.Replace('[', ' ');
sChartName = sChartName.Replace(']', ' ');

try
{
//Chartname setzen. Wenn dieser Name bereits
existiert, dann einen Index anh?ngen.
SetChartName(this[iChart].ExcelChart,
sChartName, 0);
}
catch (Exception ex)
{
//Wenn kein ChartName gefunden werden kann,
dann
wird eine GUID als Name generiert.
sChartName = System.Guid.NewGuid().ToString();
}
}
catch { }
}

seriesCollection = null;
}
}