We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Basic Question about ChartDataCellFactory

Hi,

I am working on a solution to replace an application that used to PowerPoint/MSChart run as COM to update charts with data from a SQL stored procedure.

So I have downloaded trial licenses of Slides and Cells.

It's going OK and I have got it loading my presentation, going to the correct slide, finding the correct shape and references the chart so that's great.

Now my question is what do I do now as I want to keep it very generic because I don't know what data looks like for a particular sproc. It might have 10 columns/100 rows or 1 column, 20 rows, etc.

My old solution in Chart just dumped the data straight into the datasheet of the chart because the chart would have been manually formatted to receive the data and I want to do the same with ASPOSE.

So I get a dataset from my SQL Server procedure

With rstResult.Tables(0)

iColumns = .Columns.Count

'-- Calculate the number of records in the set

iRows = .Rows.Count

End With

And I get a reference to the charts datacellfactory below, So how do I just through my data into the default datasheet without caring about series,etc.

oDataSheet = oGraph.ChartData.ChartDataCellFactory

Hi Andrew,


Thanks for inquiring Aspose.Slides.

I have observed the requirements shared by you and regret to share that I have found them incomplete to completely understand the scenario shared by you. Can you please share that whether you are using charts created in Aspose.Slides or Excel charts created using Aspose.Cells and then added as Ole frame in PowerPoint presentation.

If you are using PowerPoint charts created using Aspose.Slides then I like to share that you can access the ChartDataCellFactory and can replace the chart data excel file. But you need to understand the concept here that the PowerPoint charts are bound to cells in chart data sheet. So, if you are changing the chart data sheet, you need to take care of the fact that the respective position of values for different chart entities like categories, series and their values should be consistent with actual chart data sheet. Please visit this thread link for your convenience. It describes the example of how to replace chart data cell factory and you will observe the two excel sheets. Both of them are same but in different format. In the shared NewData.xlsx the values are organized in single column. However, in NewData2.xlsx the user has organized the values in the format that chart is expecting. The same thing you also have to keep in mind while replacing chart data. The NewData.xlsx has been used as new chart data sheet in the shared example.

I hope the solution will be helpful to you. If there is still an issue then please share the complete scenario with us in the form of example and sample application and I will try my best to help you further.

Many Thanks,

Hi Mudassir,

I have attached an example pptx file. We manually create a preexisting powerpoint template in PowerPoint 2007. So the chart is the chart you would get by clicking add chart within PowerPoint.

The chart and the SQL sProc are developed to match each other, so we just want to replace the data.

ere's some snips of code:

I believe up until this point I am just using ASPOSE.Slides to access it. I have downloaded ASPOSE.Cells as I thought I might need it some point. I saw you your example thread and it seems "wrong" to be writing out to a temp file and back in again!

Dim oPPTPres As PresentationEx

oPPTPres = New PresentationEx(strFileInfoTemp)

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Dim oSlide As SlideEx

Dim oShape As ShapeEx

'--Code finds the chart

oPPTObject = ReturnSlidePartObject(oPPTPres, intSlideId, strObjName, oSlide, oShape)

'--Code to populate the chart

bResult = CreateGraphAquireFormating(dt, oPPTObject, True, intFirstDisabled, intNumDisabled)

' --Works beautifully up to this point my code finds the chart and passes it to the function below. Then I am confused what to do next!

Public Shared Function CreateGraphAquireFormating(ByVal rstResult As DataSet, ByVal oGraph As ChartEx, ByVal bQuitChart As Boolean, ByVal intFirstDisabled As Integer, ByVal intNumDisabled As Integer) As String

Dim iColumns As Integer

Dim iRows As Integer

Dim intCount As Integer

Dim intRecord As Integer

Dim oDataSheet As ChartDataCellFactory

Try

oDataSheet = oGraph.ChartData.ChartDataCellFactory

' oWorkbook = oDataSheet.workbooks

With rstResult.Tables(0)

iColumns = .Columns.Count

'-- Calculate the number of records in the set

iRows = .Rows.Count

End With

oDataSheet = Nothing

Return strReportResult

End Function

Hi Andrew,


I have observed the sample code shared. What I have been able to identify that you are trying to change the chart data and using some existing chart. Please find the sample code below that I have shred with a customer in past. Instead of database, I have used DataTable and have populated to chart. I hope the shared code will help you. If there is still an issue then please share the source template presentation and desired output presentation for reference. I will try to help you further. In case of issue, I will also appreciate if you may please share the sample application as well.

Public Shared Function GetDataTable() As System.Data.DataTable
Dim table As New System.Data.DataTable()
table.Columns.Add(“CatName”, GetType(String))
table.Columns.Add(“Value”, GetType(Double))

table.Rows.Add(“London”, 200)
table.Rows.Add(“Financial”, 92)
table.Rows.Add(“The Ind”, 82)
table.Rows.Add(“Evening Mail”, 65)
table.Rows.Add(“Metro”, 161)
table.Rows.Add(“Independent”, 48)
table.Rows.Add(“Daily Star”, 133)
table.Rows.Add(“Manchester”, 116)
table.Rows.Add(“Sun”, 15)
table.Rows.Add(“Guardian”, 115)

Return table
End Function


Public Shared Sub ModifyColumnChart()

Dim path As String = “D:\Aspose Data”
Dim pres As New PresentationEx(path & “Presentation2.pptx”)
Dim slide As SlideEx = pres.Slides(0)
Dim chart As ChartEx = CType(slide.Shapes(0), ChartEx)
Dim data As ChartDataEx = chart.ChartData
'series.XValues.RemoveAt(0);
'series.YValues.RemoveAt(0);
Dim extractedData As System.Data.DataTable = GetDataTable()

Dim noOfRows As Integer = extractedData.Rows.Count

If noOfRows = 0 OrElse noOfRows > extractedData.Rows.Count Then
noOfRows = extractedData.Rows.Count
End If

Dim catIndex As Integer = 1
chart.ChartDataTable.Format.Equals(noOfRows)
Dim fact As ChartDataCellFactory = chart.ChartData.ChartDataCellFactory
Dim series As ChartSeriesEx = chart.ChartData.Series(0)

‘series.Values.Add(fact.GetCell(0, noOfRows, 0, “test”));
‘series.Values.Add(fact.GetCell(0, noOfRows, 1, 0));

If chart.ChartData.Categories.Count< noOfRows Then
Dim valuesToAdd As Integer = noOfRows - chart.ChartData.Categories.Count
Dim indToAdd As Integer = noOfRows

Do While indToAdd > chart.ChartData.Categories.Count
chart.ChartData.Categories.Add(fact.GetCell(0, indToAdd, 0, 0))

’ series.Values.Add(fact.GetCell(0, indToAdd, 0, 0));

indToAdd -= 1
Loop
End If


If series.Values.Count < noOfRows Then
Dim valuesToAdd As Integer = noOfRows - series.Values.Count

For i As Integer = 0 To valuesToAdd - 1

series.Values.Add(fact.GetCell(0, noOfRows, 1, 0))

Next i
End If

For i As Integer = 0 To noOfRows - 1
Dim insertedValue As String = extractedData.Rows(i)(“Value”).ToString()
Dim catName As String = extractedData.Rows(i)(“CatName”).ToString() ‘.Split(’[’, ‘]’);
Dim cat As String = catName
If insertedValue.Equals("-") OrElse insertedValue.Equals("") Then
insertedValue = “0”
End If
fact.GetCell(0, catIndex, 0, cat)
catIndex += 1
fact.GetCell(0, 0, 1, “Index”)
series.Values(i).Value = Math.Round(Convert.ToDouble(insertedValue), MidpointRounding.AwayFromZero)


Next i
pres.Save(path & “GenPres.pptx”, Aspose.Slides.Export.SaveFormat.Pptx)

End Sub

Many Thanks,

Howdy

I've been having a play at creating a very generic version of your code and came up with this:

It runs through, reports that it was successful, everything seems perfect - but does NOT appear to change the chart at all!

What have I done wrong!

Regards

Andy

Public Shared Function CreateGraphAquireFormating(ByVal rstResult As DataSet, ByVal oGraph As ChartEx, ByVal bQuitChart As Boolean, ByVal intFirstDisabled As Integer, ByVal intNumDisabled As Integer) As String

Dim intCount As Integer

Dim scurRow As Object

Dim strReportResult As String

Dim intRow As Integer

Dim data As ChartDataEx = oGraph.ChartData

Try

Dim fact As ChartDataCellFactory

fact = oGraph.ChartData.ChartDataCellFactory

fact.Clear(0)

Dim extractedData As DataTable = rstResult.Tables(0)

Dim noOfRows As Integer = extractedData.Rows.Count

Dim noOfColumns As Integer = extractedData.Columns.Count

For intRow = 0 To noOfRows - 1

For intCount = 0 To noOfColumns - 1

scurRow = extractedData.Rows(intRow)(intCount).ToString()

fact.GetCell(0, intRow, intCount, scurRow)

Next intCount

Next intRow

'Initialise Status

strReportResult = "Success"

Catch ex As Exception

strReportResult = ex.Message

End Try

Return strReportResult

End Function

Hi Andy,


There is a minor issue here that although the chart data will get changed but when you will first open the chart for the first time in PowerPoint you will see the chart with old values. You will have to just open the chart in edit data mode and then save that back and you will see that values will get reflected automatically. I have also created an issue with ID SLIDESNET-33979 to further investigate and resolve the issue. Meanwhile, you may please use the work around method specified by me.

Please share, if I may help you further in this regard.

Many Thanks,

Hi,

I disagree that this is a minor issue, some of our PowerPoint reports have over 100 slides (some with multiple charts on each slide) that we might run for 30 companies, going into each slide and editing it manually would defeat the object of using ASPOSE.Slides. So I hope there is something daft going on.

Also assuming there is a fix for this "show stopper" I wonder how do you set the "Swich Row/Column" setting that appears on the Chart ribbon. Our current system flips the dataset being read in dependent on which way that is set.

Cheers

Andy

Hi Andy,


I agree with your stance and have raised the priority of the issue to High. But I also like to add further here that as per policy the issues belonging to enterprise and priority support customers that are added in Aspose.Enterprise and Aspose.Priority forums are addressed first. Then the issues from Aspose.Slides forum are added in queue for resolution. With priority set to High now, I am hopeful that issue will be scheduled earlier among the issues added in Aspose.Slides forum.

I regret to share that switching to row/column of chart data is currently unavailable in Aspose.Slides. One may need to manually switch the data for the time being. However, an issue with ID SLIDESNET-33995 has been created as new feature request in our issue tracking system to further investigate and resolve the issue. This thread has been linked with the issue so that you may be automatically notified once the issue will be resolved.

We are sorry for your inconvenience,

I've been having a play with this myself and looked at the example code you sent me:

I have attached the pptx file I created SecondExample.pptx. I basically created a new pptx, inserted a chart and deleted the title so the chart was the the first object.

It fails here because i is too big, As a fix I set the for loop to create only the number of series and that produces a corrupt result. Is it possible to have an example that runs?

Public Shared Function ASPOSEGetDataTable() As System.Data.DataTable

Dim table As New System.Data.DataTable()

table.Columns.Add("CatName", GetType(String))

table.Columns.Add("Value", GetType(Double))

table.Rows.Add("London", 200)

table.Rows.Add("Financial", 92)

table.Rows.Add("The Ind", 82)

table.Rows.Add("Evening Mail", 65)

table.Rows.Add("Metro", 161)

table.Rows.Add("Independent", 48)

table.Rows.Add("Daily Star", 133)

table.Rows.Add("Manchester", 116)

table.Rows.Add("Sun", 15)

table.Rows.Add("Guardian", 115)

Return table

End Function

Public Shared Sub ASPOSEModifyColumnChart()

Dim path As String = "C:\bin\Aspose Data\"

Dim pres As New PresentationEx(path & "SecondExample.pptx")

Dim slide As SlideEx = pres.Slides(0)

Dim chart As ChartEx = CType(slide.Shapes(0), ChartEx)

Dim data As ChartDataEx = chart.ChartData

'series.XValues.RemoveAt(0);

'series.YValues.RemoveAt(0);

Dim extractedData As System.Data.DataTable = ASPOSEGetDataTable()

Dim noOfRows As Integer = extractedData.Rows.Count

If noOfRows = 0 OrElse noOfRows > extractedData.Rows.Count Then

noOfRows = extractedData.Rows.Count

End If

Dim catIndex As Integer = 1

chart.ChartDataTable.Format.Equals(noOfRows)

Dim fact As ChartDataCellFactory = chart.ChartData.ChartDataCellFactory

Dim series As ChartSeriesEx = chart.ChartData.Series(0)

'series.Values.Add(fact.GetCell(0, noOfRows, 0, "test"));

'series.Values.Add(fact.GetCell(0, noOfRows, 1, 0));

If chart.ChartData.Categories.Count < noOfRows Then

Dim valuesToAdd As Integer = noOfRows - chart.ChartData.Categories.Count

Dim indToAdd As Integer = noOfRows

Do While indToAdd > chart.ChartData.Categories.Count

chart.ChartData.Categories.Add(fact.GetCell(0, indToAdd, 0, 0))

' series.Values.Add(fact.GetCell(0, indToAdd, 0, 0));

indToAdd -= 1

Loop

End If

If series.Values.Count < noOfRows Then

Dim valuesToAdd As Integer = noOfRows - series.Values.Count

For i As Integer = 0 To valuesToAdd - 1

series.Values.Add(fact.GetCell(0, noOfRows, 1, 0))

Next i

End If

For i As Integer = 0 To noOfRows - 1

Dim insertedValue As String = extractedData.Rows(i)("Value").ToString()

Dim catName As String = extractedData.Rows(i)("CatName").ToString() '.Split('[', ']');

Dim cat As String = catName

If insertedValue.Equals("-") OrElse insertedValue.Equals("") Then

insertedValue = "0"

End If

fact.GetCell(0, catIndex, 0, cat)

catIndex += 1

fact.GetCell(0, 0, 1, "Index")

series.Values(i).Value = Math.Round(Convert.ToDouble(insertedValue), MidpointRounding.AwayFromZero)

Next i

pres.Save(path & "GenPres.pptx", Aspose.Slides.Export.SaveFormat.Pptx)

End Sub

Hi Andy,


I have worked with the sample code shared by you and have observed the inconsistency in the sample code. I have modified the sample code as per your convenience. However, I have not been able to understand your following comments. Can you please elaborate them and share the generated output and also what is desired output.

inserted a chart and deleted the title so the chart was the the first object.

It fails here because i is too big, As a fix I set the for loop to create only the number of series and that produces a corrupt result. Is it possible to have an example that runs?


Public Shared Sub ASPOSEModifyColumnChart()

Dim path As String = “C:\Users\Mudassir\Downloads”

Dim pres As New PresentationEx(path & “SecondExample.pptx”)

Dim slide As SlideEx = pres.Slides(0)
Dim chart As ChartEx = CType(slide.Shapes(0), ChartEx)
Dim data As ChartDataEx = chart.ChartData

'series.XValues.RemoveAt(0);

'series.YValues.RemoveAt(0);

Dim extractedData As System.Data.DataTable = ASPOSEGetDataTable()
Dim noOfRows As Integer = extractedData.Rows.Count

If noOfRows = 0 OrElse noOfRows > extractedData.Rows.Count Then

noOfRows = extractedData.Rows.Count

End If

Dim catIndex As Integer = 1

chart.ChartDataTable.Format.Equals(noOfRows)

Dim fact As ChartDataCellFactory = chart.ChartData.ChartDataCellFactory
Dim series As ChartSeriesEx = chart.ChartData.Series(0)

'series.Values.Add(fact.GetCell(0, noOfRows, 0, “test”));

‘series.Values.Add(fact.GetCell(0, noOfRows, 1, 0));

If chart.ChartData.Categories.Count < noOfRows Then

Dim valuesToAdd As Integer = noOfRows - chart.ChartData.Categories.Count
Dim indToAdd As Integer = noOfRows

’ Do While indToAdd > chart.ChartData.Categories.Count

’ chart.ChartData.Categories.Add(fact.GetCell(0, indToAdd, 0, 0))
’ series.Values.Add(fact.GetCell(0, indToAdd, 0, 0));

’ indToAdd -= 1

’ Loop

For i As Integer = valuesToAdd - 1 To noOfRows

chart.ChartData.Categories.Add(fact.GetCell(0, i, 0, 0))

Next i

End If



If series.Values.Count < noOfRows Then

Dim valuesToAdd As Integer = noOfRows - series.Values.Count

’ For i As Integer = 0 To valuesToAdd - 1
For i As Integer = valuesToAdd - 1 To noOfRows

series.Values.Add(fact.GetCell(0, i, 1, 0))

Next i

End If

For i As Integer = 0 To noOfRows - 1

Dim insertedValue As String = extractedData.Rows(i)(“Value”).ToString()

Dim catName As String = extractedData.Rows(i)(“CatName”).ToString() ‘.Split(’[’, ‘]’);

Dim cat As String = catName

If insertedValue.Equals("-") OrElse insertedValue.Equals("") Then

insertedValue = “0”

End If

fact.GetCell(0, catIndex, 0, cat)

catIndex += 1

fact.GetCell(0, 0, 1, “Index”)

series.Values(i).Value = Math.Round(Convert.ToDouble(insertedValue), MidpointRounding.AwayFromZero)



Next i

pres.Save(path & “GenPres.pptx”, Aspose.Slides.Export.SaveFormat.Pptx)

End Sub


Many Thanks,

Howdy,

An interesting thing! In my code (the post with the function

Public Shared Function CreateGraphAquireFormating(ByVal rstResult As DataSet, ByVal oGraph As ChartEx, ByVal bQuitChart As Boolean, ByVal intFirstDisabled As Integer, ByVal intNumDisabled As Integer) As String

)

I just commented out the line.

fact.Clear(0)

And it all seems to work - chart refreshes nicely without a double click/edit! Sounds like a bug to me. I tried it by adding .Clear(0) to one of the examples posted on your web site and it stops working. A bug I suspect.

The risk would be that if less data is being returned by my dataset then I could be left with dummy data from my dummy template.

So I am tempted to do a big for - next and set the values to NULL before writing my real data.

Also you send you could not set the Switch Row/Column which is fine, but is there anyway to assess the state it currently is in as a readonly property?

Cheers

Andy

Hi Andy,


I have tried to understand the points shared by you and like to share the fact.Clear(0) actually clears the values of the series and categories in the excel sheet at index 0. However, it does not remove the association of the cells with categories and series. When you populate the respective cell values again they get the assigned values.

fact.Clear(0) does not remove the categories or series in fact. So, I don’t feel it is an issue. If there is any inconsistency then kindly share the sample application that I may use on my end to reproduce the issue. I hope this will elaborate the concept before you.

I regret to share that Switch Row/Column is unavailable in Aspose.Slides for .NET. Once it will be implemented it will be implemented with Get and Set methods. I will really appreciate your patience till the time the feature will be available.

Many Thanks,

The issues you have found earlier (filed as SLIDESNET-33979) have been fixed in this update.


This message was posted using Notification2Forum from Downloads module by Aspose Notifier.
(2)

The issues you have found earlier (filed as SLIDESNET-33995) have been fixed in this update. This message was posted using BugNotificationTool from Downloads module by mudassir.fayyaz