Howdy,
I am creating the chart displayed in output.jpg. It all works perfectly, except that CmpyLabel appears in my legend. I don't want it to, but I do want to plot it for reasons too completed to go into. How do I prevent that appearing in the Legend:
Code is here:
Public Shared Function CreateGraphAquireFormating(ByVal rstResult As DataSet, ByVal oGraph As ChartEx, ByVal bAddZeroColumn As Boolean) As String
Dim intCount As Integer
Dim scurRow As Object
Dim strReportResult As String
Dim intRow As Integer
Dim chtSeries As Integer
Dim intCategories As Integer
'Reference the Chart data
Dim chdata As ChartDataEx = oGraph.ChartData
'Reference the category collection
Dim oChartCategoryCollection As ChartCategoryExCollection = chdata.Categories
'Refence to individual category item
Dim CatValue As ChartCategoryEx
'Refence to individual series item
Dim seriesValue As ChartDataCell
Try
'Reference the underlying Excel data sheet
Dim fact As ChartDataCellFactory
chtSeries = chdata.Series.Count
intCategories = chdata.Categories.Count
'OK lets try deleteing all the data from all the categories and series
''Remove the last category
'While oChartCategoryCollection.Count > 0
' oChartCategoryCollection.Remove(oChartCategoryCollection(oChartCategoryCollection.Count - 1))
'End While
'For intCount = 0 To chtSeries - 1
' 'Remove the last category
' While chdata.Series(intCount).Values.Count > 0
' chdata.Series(intCount).Values.RemoveAt(chdata.Series(intCount).Values.Count - 1)
' End While
'Next
'End removing stuff
' chtSeries = chdata.Series.Count
'intCategories = chdata.Categories.Count
Dim series As ChartSeriesEx
fact = oGraph.ChartData.ChartDataCellFactory
'Let's all try clearing the Excel sheet
' fact.Clear(0)
'Reference the data table returned by the stored procedure.
Dim extractedData As DataTable = rstResult.Tables(0)
'Count the rows in the actual data set
Dim noOfRows As Integer = extractedData.Rows.Count
If bAddZeroColumn = True Then
extractedData.Columns.Add("CmpyLabel", GetType(Integer))
For intRow = 0 To noOfRows - 1
extractedData.Rows(intRow)("CmpyLabel") = 0
Next
End If
'Check whether there is less rows in the stored procedure than in the dummy template file
Dim intDiffCat As Integer
intDiffCat = intCategories - noOfRows
Dim intCurMaxSeries As Integer
If intDiffCat > 0 Then
For intCount = 1 To intDiffCat
For intRow = 0 To chtSeries - 1
'Remove the last record from each series
fact.GetCell(0, intCount + noOfRows, intRow, Nothing)
series = chdata.Series(intRow)
intCurMaxSeries = series.Values.Count
series.Values.RemoveAt(intCurMaxSeries - 1)
Next
'Remove the data from the worksheet
fact.GetCell(0, intCount + noOfRows, chtSeries, Nothing)
'Remove the last category
oChartCategoryCollection.Remove(oChartCategoryCollection(oChartCategoryCollection.Count - 1))
Next
End If
'Count the columns
Dim noOfColumns As Integer = extractedData.Columns.Count
'Note to self - remember the 1st column is the headings which are the categories
'This offset is so we can put the columns names from the stored procedure into the datasheet.
Dim intOffSet As Integer = 1
Dim strColName As String
'Loop through the columns in the dataset, not the chart
For intCount = 0 To noOfColumns - 1
'For the series add the first item as the name
strColName = extractedData.Columns(intCount).ColumnName
'This is just the data so write into the series
If intCount > 0 Then
series = chdata.Series(intCount - 1)
' series.Values.Add(fact.GetCell(0, 0, intCount, strColName))
'series.Values(0).Value = strColNam#
seriesValue = series.Values(0)
seriesValue = fact.GetCell(0, 0, intCount, strColName)
End If
For intRow = 0 To noOfRows - 1
If intCount = 0 Then
'It's the category column so add the data to that and not the series
scurRow = extractedData.Rows(intRow)(intCount).ToString()
'Then do the actual value
If intRow < intCategories Then
CatValue = oChartCategoryCollection(intRow)
'
CatValue.ChartDataCell = fact.GetCell(0, intRow + intOffSet, intCount, scurRow)
'CatValue.ChartDataCell.Value = scurRow
Else
'Note no offset as it'
oChartCategoryCollection.Add(fact.GetCell(0, intRow + intOffSet, intCount, scurRow))
End If
Else
'This section adds the series points
If IsNumeric(extractedData.Rows(intRow)(intCount).ToString()) Then
If extractedData.Columns(intCount).DataType.Name = "Int32" Then
scurRow = CType(extractedData.Rows(intRow)(intCount).ToString(), Integer)
ElseIf extractedData.Columns(intCount).DataType.Name = "Decimal" Then
scurRow = CType(extractedData.Rows(intRow)(intCount).ToString(), Double)
End If
Else
scurRow = Nothing
End If
If intRow < intCategories Then
seriesValue = series.Values(intRow)
seriesValue = fact.GetCell(0, intRow + intOffSet, intCount, scurRow)
'seriesValue.Value = scurRow
Else
series.Values.Add(fact.GetCell(0, intRow + intOffSet, intCount, scurRow))
End If
End If
Next intRow
Next intCount
'Initialise Status
strReportResult = "Success"
Catch ex As Exception
strReportResult = ex.Message
End Try
Return strReportResult
End Function