I updated a slide with an embedded Excel chart. I updated the Chart using Aspose.Cells' Smart Marker feature. The problem is that it appears that Aspose changed the format of the labels.
I am attaching three slides (original one, one after update, templatewith smart markers) for this posting so you can see the difference.
The label on the top of each blue bar shows the height or value of each blue bar. After updating with current data, Aspose changed the background color the label for the blue bars so you see a little black rectangle in the background of each label. Why is this? Could there a bug somewhere? Could the serializer (save as stream) lost some information?
I am expecting the format to be just the same as the original. I am not sure if Aspose.Cells or Aspose.Slides doing this. Note my code does not touch any formating at all, it just calls Aspose.Cells.WorkbookDesigner to inject data into the smart markers.
My code is something like this:
Dim dsBenchmarkReturn As DataSet = Me.getPeBenchmarkReturnData()
Dim dtBmkReturn As DataTable = dsBenchmarkReturn.tables(0)
Dim chartShape As Shape = slide.FindShape("ComparisonWithBenchmarkChart")
Dim oof As OleObjectFrame = CType(chartShape, OleObjectFrame)
Dim mstream As MemoryStream = New MemoryStream(oof.ObjectData)
Dim designer As Aspose.Cells.WorkbookDesigner = New Aspose.Cells.WorkbookDesigner()
designer.Open(mstream)
designer.SetDataSource(dtBmkReturn)
designer.SetDataSource("SP500", SP500)
designer.SetDataSource("MSCI", MSCI)
designer.Process()
oof.ObjectData = designer.Workbook.SaveToStream().ToArray()
Dear BrightStar
Aspose.Slides actually doesn't manipulate the OLE objects, it just insert them as it is. You should write your designer after update to file and then open it up in MS-Excel to see the output.
Dim ms As MemoryStream
msdesigner.Workbook.SaveToStream()
Dim fout As FileStream
fout = New FileStream("c:\out.xls", FileMode.OpenOrCreate, FileAccess.Write)
fout.Write(ms.ToArray(), 0, ms.Length)
fout.Close()
Shakeel :
Thanks for the reply. I took up on your advice and write out the input stream (of the ole object of the shape) and output after I updated the chart. I do see the little unwanted black rectangles around the labels. I then open the input excel file that I dumped and manually copy the values from the out.xls (that I dumped out) and past it on to the input excel file and I do not see the little black rectangles. i.e., when I manually past the values, the formatting is preserverd. However, when I update it with WorkbookDesigner and Smart Markers, the format got changed and background color of the labels changed to black. Below is the identical code I have. The size and position of the chart also got changed.
Could the saveToStream method cause this?
I think most people use the software to update data which usually affect graphs and tables. This is very disheartening if the original format is not preserved. So this is very disappointing. I am not sure if this is Microsoft libraries or Aspose causing this. This is really a show stopper. Please investigate to make sure this is not Aspose causing this.
Dim oof As OleObjectFrame = CType(chartShape, OleObjectFrame)
Dim mstream As MemoryStream = New MemoryStream(oof.ObjectData)
Dim fin As FileStream = New FileStream("c:\Temp\in.xls", FileMode.OpenOrCreate, FileAccess.Write)
fin.Write(mstream.ToArray(), 0, CInt(mstream.Length()))
fin.Close()
Dim designer As Aspose.Cells.WorkbookDesigner = New Aspose.Cells.WorkbookDesigner()
designer.Open(mstream)
designer.SetDataSource(dtBmkReturn)
designer.SetDataSource("SP500", SP500)
designer.SetDataSource("MSCI", MSCI)
designer.Process()
Dim ms As MemoryStream = designer.Workbook.SaveToStream()
Dim fout As FileStream = New FileStream("c:\Temp\out.xls", FileMode.OpenOrCreate, FileAccess.Write)
fout.Write(ms.ToArray(), 0, CInt(ms.Length()))
fout.Close()
If I manually open the result chart (ole object) in the output slide, right click on a label and select "Format Data Labels . . ." then change the background to "transaparent "(my original setting) then I get what I want. So somehow the background of the labels got changed to from "transparent" to "automatic".
So the question is how can I get a handle on the labels and force the background to transparent programatically?
Thanks.
I updated the code to add a line to force the background of the DataLabels to be transparent. However, only two of the four labels got changed to transparent, the other two labels still got the black background. I am attaching the result slide for you to see. Something is fishy here. Even I force the background to transparent, some labels did not get changed. Please see the labels near the top of the blue bars.
Dim oof As OleObjectFrame = CType(chartShape, OleObjectFrame)
Dim mstream As MemoryStream = New MemoryStream(oof.ObjectData)
Dim designer As Aspose.Cells.WorkbookDesigner = New Aspose.Cells.WorkbookDesigner()
designer.Open(mstream)
designer.SetDataSource(dtBmkReturn) ' return data for Venture Econ. benchmark
designer.SetDataSource("SP500", SP500)
designer.SetDataSource("MSCI", MSCI)
designer.Process()
designer.Workbook.Worksheets(0).Charts(0).NSeries(0).DataLabels.Background = Aspose.Cells.BackgroundMode.Transparent
oof.ObjectData = designer.Workbook.SaveToStream().ToArray()
Dear BrightStar,
Thanks for all the information.
As I stated, Aspose.Slides insert OLE objects as it is, so it is actually Aspose.Cells which is causing this problem.
You can verify it, by creating a source input file in Excel and then read it; do your processing and write it back in output Excel file to observe the problem.
Once you reproduced it, you should report it to Aspose.Cells forum, so that they fix it.
Version 4.5.0.3 of Aspose.Cells fixes the problem. Thanks everyone.