bepenfriends


I am not sure whether thisis the right place for this question. This is a problem which i am facing when coding in VB.net

The problem is,

I want to draw a line graph (3 series) in excel from the data present in designated positions in Excel. I worte a code which is as below

charts = XLSheet.ChartObjects()
' Adding a chart
chartObj = charts.Add(450, 200, 350, 200)

Dim lIntI As Integer = 1
Dim xlsAxisCategory, xlsAxisValue As Excel.Axes
With chartObj
.Name = "Cumulative Defects1"
.Chart.ChartType = Excel.XlChartType.xlLineMarkersStacked
.Chart.ProtectGoalSeek = False
'.Chart.SetSourceData(chartRange)
seriesCollection = .Chart.SeriesCollection()
For lIntI = 1 To paramColDaysStart.Count
seriesCollection.NewSeries()
seriesCollection.Item(lIntI).Values = _
XLBook.Worksheets(paramSheetName).range(XLSheet.Cells(paramRowDaysStart, paramColDaysStart.Item(lIntI - 1)), XLSheet.Cells(paramRowDaysEnd, paramColDaysEnd.Item(lIntI - 1)))
.Chart.SeriesCollection.Item(lIntI).Name = paramSeriesNames.Item(lIntI - 1)
Next
End With

With chartObj
'Creating(name)
.Name = "Cumulative Defects1"
.Chart.ChartType = Excel.XlChartType.xlLineMarkersStacked
.Chart.SetSourceData(chartRange)
.Chart.HasTitle = True
.Chart.ChartTitle.Caption = "Cumulative Defects"
' Setting title

'getting the series collection
seriesCollection = chartObj.Chart.SeriesCollection()

'Naming the series
seriesCollection.Item(1).Name = "No. of defects"
seriesCollection.Item(1).MarkerBackgroundColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic
seriesCollection.Item(1).MarkerForegroundColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic
' Counting
series = seriesCollection.Item(seriesCollection.Count)

'Getting the axis of the charts
'X(axis)
xlsAxisCategory = .Chart.Axes(, Excel.XlAxisGroup.xlPrimary)
xlsAxisCategory.Item(Excel.XlAxisType.xlCategory).HasTitle = True
xlsAxisCategory.Item(Excel.XlAxisType.xlCategory).AxisTitle.Characters.Text = "No. of days"
xlsAxisCategory.Item(Excel.XlAxisType.xlCategory).HasMajorGridlines = False
xlsAxisCategory.Item(Excel.XlAxisType.xlCategory).HasMinorGridlines = False
xlsAxisCategory.Item(Excel.XlAxisType.xlCategory).AxisTitle.Interior.PatternColorIndex = 2
' Y Axis
xlsAxisValue = .Chart.Axes(, Excel.XlAxisGroup.xlPrimary)
xlsAxisValue.Item(Excel.XlAxisType.xlValue).HasTitle = True
xlsAxisValue.Item(Excel.XlAxisType.xlValue).AxisTitle.Characters.Text = "No. of Defects"
xlsAxisValue.Item(Excel.XlAxisType.xlValue).HasMajorGridlines = True
xlsAxisValue.Item(Excel.XlAxisType.xlValue).HasMinorGridlines = False
xlsAxisValue.Item(Excel.XlAxisType.xlValue).AxisTitle.Interior.PatternColorIndex = 2
.Chart.SeriesCollection(1).border.Weight = Excel.XlBorderWeight.xlThin
.Chart.HasTitle = True
.Chart.ChartTitle.Caption = "Cumulative Defects"
'Setting(title)

'Positioning the legend
.Chart.ChartArea.Font.Background = Excel.XlBackground.xlBackgroundTransparent
.Chart.Legend.Position = Excel.XlLegendPosition.xlLegendPositionRight

'Coloring the area
.Chart.PlotArea.Interior.ColorIndex = 19
.Chart.ChartArea.Interior.ColorIndex = 2
.Chart.ChartArea.Interior.PatternColorIndex = 2
.Chart.Legend.Interior.PatternColorIndex = 2
chartObj.Interior.PatternColorIndex = 2
.Chart.Location(Excel.XlChartLocation.xlLocationAsObject, paramSheetName)
End With
XLBook.Save()
generalfunctions.ReleaseExcelObjects(XLApp, XLBook)

In the above code paramSeriesNames has the names for the series which is a hash table

paramRowDaysStart is a integer value which denotes the start row value,
paramColDaysStart is a hash table which has the starting column for the series

paramRowDaysEnd is a integer value which denotes the end row value,
paramColDaysEnd is a hash table which has the ending column for the series

In this noth paramColDaysEnd and paramColDaysstart will have same values because i am using only one column series

The problem is, when i am adding a new series in the loop (say 3 series), the first series is coming properly, the second series is displaying as the sum of first series column values and the second series column (13,2 - 24,2) + (13,4 - 24,4)

Third series is the sum of all ie (13,2 - 24,2) + (13,4 - 24,4) + (13,6 - 24,6)

I am not sure y this is happening. If i print only one series then everything is proper.
how to correct this problem

I want a result like below

Series1 = (13,2 - 24,2)
Series2 = (13,4 - 24,4)
Series3 = (13,6 - 24,6)

But actual result is

Series1 = (13,2 - 24,2)
Series2 = (13,2 - 24,2) + (13,4 - 24,4)
Series3 = (13,2 - 24,2) + (13,4 - 24,4) + (13,6 - 24,6)

Please help me friends

Albert




Re: Multiple series addition problem

Andy Pope


Hi,

I think the clue to the problem lies in the chart type constant you are using, xlLineMarkersStacked

try xlLineMarkers which should not cause the lines to appear stacked.






Re: Multiple series addition problem

bepenfriends

Thanks friend,

It worked.