Hi,
For this data (though the same thing happens with any data):
TherapeuticArea | cnt |
Alimentary & Metabolism | 21 |
Anti-cancer | 61 |
Anti-infectives | 17 |
Blood | 15 |
Cardiovascular | 10 |
Dermatologicals | 3 |
Genitourinary & sex hormones | 3 |
Immunomodulators | 3 |
Musculoskeletal | 9 |
Nervous system | 17 |
Respiratory | 6 |
Sensory | 1 |
I have got a problem with my generic chart population procedure.
It populates perfectly, but when I open the generated file, neither Edit Data or Select Data work, but I can change the axis/colours/data labels, etc!
This really is a bit of a show stopper.
I have attached the template file: Just376.pptx (can edit)
I have attached the result file:Just 376P07.pptx (can't edit)
Code 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
Dim series As ChartSeriesEx
fact = oGraph.ChartData.ChartDataCellFactory
'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(0).Value = 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.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
scurRow = CType(extractedData.Rows(intRow)(intCount).ToString(), Double)
Else
scurRow = Nothing
End If
If intRow < intCategories Then
seriesValue = series.Values(intRow)
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