Free Support Forum - aspose.com

Ole frame doesnt work when cells haing Formula

Hello,
The Bar for the chart for the cell haing formula doesnt show up in the PPT but is working fine in the excel.
i am attaching the code for your reference

Imports Aspose.Cells
Imports Aspose.Slides
Imports System.IO
Partial Class zRMX_test1
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim license As Aspose.Cells.License = New Aspose.Cells.License()
license.SetLicense(“Aspose.Total.lic”)
Dim license1 As Aspose.Slides.License = New Aspose.Slides.License()
license1.SetLicense(“Aspose.Total.lic”)
Run()
End Sub
Shared Sub Run()

Dim wb As Workbook = New Workbook()

Dim chartRows As Integer = 55

Dim chartCols As Integer = 25

Dim chartSheetIndex As Integer = AddExcelChartInWorkbook(wb, chartRows, chartCols)


wb.Worksheets.SetOleSize(0, chartRows, 0, chartCols)


Dim imgChart As Bitmap = wb.Worksheets(2).Charts(0).ToImage()
Dim imgChart1 As Bitmap = wb.Worksheets(3).Charts(0).ToImage()


'Save the workbook to stream

Dim wbStream As MemoryStream = wb.SaveToStream()
Dim wbStream1 As MemoryStream = wb.SaveToStream()
'Step - 4 AND 5

'-----------------------------------------------------------

'Step - 4: Embed the chart as an OLE object inside .ppt presentation using Aspose.Slides

'-----------------------------------------------------------

'Step - 5: Replace the object changed image with the image obtained in step 3 to cater Object Changed Issue

'-----------------------------------------------------------

'Create a presentation

Dim pres As Presentation = New Presentation()

Dim sld As Slide = pres.GetSlideByPosition(1)

Dim sld1 As Slide = pres.AddEmptySlide()

'Add the workbook on slide

AddExcelChartInPresentation(pres, sld, wbStream, imgChart)
AddExcelChartInPresentation(pres, sld1, wbStream1, imgChart1)

'Step - 6: Write the output presentation on disk

'-----------------------------------------------------------

'pres.Write(“c:\test\output2.ppt”)

wb.Save(“c:\test\output2.xls”)
HttpContext.Current.Response.ClearHeaders()
HttpContext.Current.Response.AddHeader(“content-disposition”, “attachment; filename=”“zaid.ppt”)
HttpContext.Current.Response.ContentType = “application/vnd.ms-powerpoint”
HttpContext.Current.Response.Flush()
Dim st As System.IO.Stream = HttpContext.Current.Response.OutputStream

’ Send the file
pres.Write(st)
HttpContext.Current.Response.End()
End Sub



Shared Function AddExcelChartInWorkbook(ByVal wb As Workbook, ByVal chartRows As Integer, ByVal chartCols As Integer) As Integer
Dim dataSheetIdx As Integer = wb.Worksheets.Add()

Dim dataSheet As Worksheet = wb.Worksheets(dataSheetIdx)

Dim sheetName As String = “DataSheet”


dataSheet.Name = sheetName
Dim cells As Cells = wb.Worksheets(dataSheetIdx).Cells
Dim style As Aspose.Cells.Style = wb.Styles(wb.Styles.Add())
style.Custom = “0.00%”
cells(0, 0).PutValue(“Male”)
'cells(0, 1).PutValue(0.6)
cells(0, 1).Formula = “=IF(1=0,0,0.6)”
cells(0, 1).SetStyle(Style)
cells(1, 0).PutValue(“Female”)
cells(1, 1).PutValue(0.4)
cells(1, 1).SetStyle(style)

cells(3, 0).PutValue(“Zaid”)
cells(3, 1).PutValue(0.6)
cells(3, 1).SetStyle(style)
cells(4, 0).PutValue(“Obaid”)
cells(4, 1).PutValue(0.4)
cells(4, 1).SetStyle(style)



Dim chartSheetIdx As Integer = wb.Worksheets.Add(SheetType.Chart)

Dim chartSheet As Worksheet = wb.Worksheets(chartSheetIdx)

chartSheet.Name = “ChartSheet1”

'Add a chart in ChartSheet with data series from DataSheet

Dim chartIdx As Integer = chartSheet.Charts.Add(Charts.ChartType.Column, 0, chartRows, 0, chartCols)

Dim _chart As Charts.Chart = chartSheet.Charts(chartIdx)

_chart.NSeries.Add(sheetName + “!B1:B2”, True)
_chart.NSeries.CategoryData = sheetName + “!A1:A2”


Dim chartSheetIdx1 As Integer = wb.Worksheets.Add(SheetType.Chart)

Dim chartSheet1 As Worksheet = wb.Worksheets(chartSheetIdx1)

chartSheet1.Name = “ChartSheet2”

'Add a chart in ChartSheet with data series from DataSheet

Dim chartIdx1 As Integer = chartSheet1.Charts.Add(Charts.ChartType.Column, 0, chartRows, 0, chartCols)

Dim _chart1 As Charts.Chart = chartSheet1.Charts(chartIdx1)

_chart1.NSeries.Add(sheetName + “!B4:B5”, True)
_chart1.NSeries.CategoryData = sheetName + “!A4:A5”

wb.Worksheets.ActiveSheetIndex = 1



End Function



Shared Sub AddExcelChartInPresentation(ByVal pres As Presentation, ByVal sld As Slide, ByVal wbStream As Stream, ByVal imgChart As Bitmap)

Dim pic As Aspose.Slides.Picture = New Aspose.Slides.Picture(pres, imgChart)

Dim picId As Integer = pres.Pictures.Add(pic)

Dim slideWidth As Integer = pres.SlideSize.Width

Dim slideHeight As Integer = pres.SlideSize.Height

Dim x As Integer = 0

Dim chartOleData(0 To wbStream.Length) As Byte

wbStream.Position = 0

wbStream.Read(chartOleData, 0, chartOleData.Length)

Dim oof As OleObjectFrame = sld.Shapes.AddOleObjectFrame(x, 0, slideWidth, slideHeight, “Excel.Sheet.8”, chartOleData)

oof.PictureId = picId

End Sub
End Class



see the line in red colour it doesnt show up in the exported PPt

Thanks & Regards
Lakdawala Zaid

Hi Zaid,

I have worked with on the issue shared by you and have been able to reproduce the issue specified. An issue with ID 28365 has been created in our issue tracking system to further investigate and resolve the issue. We will share the updates with you once the issue is resolved.

I have also observed that when you double click and open chart in PowerPoint then it takes the values of cells having formula.

Thanks and Regards,

Hi,

It is not an issue. Please call wb.CalculateFormula() before converting chart to image. It works fine.

Thank you.

I am facing one more problem in this.

I have a scenario in which i create a excel with about 100-150 sheets each sheet contains some data and a chart below it for that data now what i am doing in reading charts from each sheet and creating ole for it and displaying it on PPT all works fine but when i click suppose slide number 10 in PPT it still opens the 1st sheet in the excel because i have wrote in my code
Workbook.Worksheets.ActiveSheetIndex = 1
what i want is when i click the 10th slide it should open the 10th sheet in that excel 9th for 9th and so on respectively
So i would like u to kindly look into my problem and revert me back with the solution asap.

Thanks & Regards
Lakdawala Zaid

Hi Zaid,

You can set as many active sheets using Ole frames in Aspose.Slides presentation. All you need to set the ActiveSheet before you are trying to save the workbook in stream and later on adding that to Ole frame. You need to access the workbook and set the ActiveSheet to 0 for slide 1. Save the workbook to stream and add that to Ole frame. For slide 2, access the workbook and set the ActiveSheet to 1. Now save the workbook in streams and add that as ole frame in presentation slide. Hope it may have cleared the concept for you. Please visit this link for your kind reference. Although, the shared link is similar code example shared in Java yet it will help you in understanding the logic and porting it to your language easily.

Thanks and Regards,

Sorry i am not able to understand it properly
what is my scenario is i am first with all my logic creating an excel with many sheets in it i am attaching one excel with four sheet for now for your reference and then using a for loop i am reading each worksheet and then a chart from that worksheet like this

pres = New Presentation(fileName)
Dim wbStream As MemoryStream = Workbook.SaveToStream()
Workbook.CalculateFormula()
For i = 0 To intsheetcount - 1
Workbook.Worksheets.ActiveSheetIndex = i
Dim imgChart As Bitmap = Workbook.Worksheets(i).Charts(0).ToImage()
Dim sld As Slide = pres.AddEmptySlide()
AddExcelChartInPresentation(pres, sld, wbStream, imgChart, hashpptchart(i))
Next

Shared Sub AddExcelChartInPresentation(ByVal pres As Presentation, ByVal sld As Slide, ByVal wbStream As Stream, ByVal imgChart As Bitmap, ByVal charttitle As String)

Dim pic As Aspose.Slides.Picture = New Aspose.Slides.Picture(pres, imgChart)

Dim picId As Integer = pres.Pictures.Add(pic)

Dim slideWidth As Integer = pres.SlideSize.Width

Dim slideHeight As Integer = pres.SlideSize.Height
Dim tf As TextFrame
Dim shape1 As Shape = sld.Shapes.AddRectangle(300, 0, slideWidth - 600, 450)
shape1.FillFormat.BackColor = Color.Blue
shape1.LineFormat.ForeColor = Color.Transparent
tf = shape1.AddTextFrame(charttitle)
tf.Paragraphs(0).Portions(0).FontColor = Color.White
tf.Paragraphs(0).Alignment = TextAlignment.Center
shape1.TextFrame.WrapText = True
shape1.TextFrame.Height = 450
shape1.TextFrame.FitTextToShape()
Dim x As Integer = 0

Dim chartOleData(0 To wbStream.Length) As Byte

wbStream.Position = 0

wbStream.Read(chartOleData, 0, chartOleData.Length)

Dim oof As OleObjectFrame = sld.Shapes.AddOleObjectFrame(500, 500, slideWidth - 900, slideHeight - 1100, “Excel.Sheet.8”, chartOleData)

oof.PictureId = picId

End Sub



youy can see from the above example that in the loop before adding the excel chart to ppt i am setting an activesheet but on clicking the output ppt it always open sheet ‘0’ only

Kindly revert back with the proper steps for the solution to this

Thanks & Regards
Lakdawala Zaid


Hi Zaid,


As I requested you earlier that you need to set active sheet and save the workbook to stream. Afterwards, you will add the saved workbook stream to Ole frame. I have modified your code snippet and it works perfectly. Please share if there is still an issue.

Dim licSlide As Aspose.Slides.License = New Aspose.Slides.License()
licSlide.SetLicense(“D:\Aspose Data\Aspose.Total.lic”)

Dim licCell As Aspose.Cells.License = New Aspose.Cells.License()
licCell.SetLicense(“D:\Aspose Data\Aspose.Total.lic”)

’ Dim pres As Presentation = New Presentation(“D:\Aspose Data\demo.ppt”)
Dim pres As Presentation = New Presentation()
Dim Workbook As Aspose.Cells.Workbook = New Aspose.Cells.Workbook(“D:\Aspose Data\test.xls”)

’ Dim wbStream As MemoryStream = Workbook.SaveToStream()
Workbook.CalculateFormula()
Dim i As Integer = 0
Dim intsheetcount As Integer = 4

For i = 0 To intsheetcount - 1
Workbook.Worksheets.ActiveSheetIndex = i
Dim wbStream As MemoryStream = Workbook.SaveToStream()
wbStream.Position = 0
Dim imgChart As Bitmap = Workbook.Worksheets(i).Charts(0).ToImage()
Dim sld As Slide = pres.AddEmptySlide()
’ AddExcelChartInPresentation(pres, sld, wbStream, imgChart, hashpptchart(i))
AddExcelChartInPresentation(pres, sld, wbStream, imgChart, "Chart " + (i + 1).ToString())
Next
pres.Write(“D:\ChartDemo.ppt”)


Thanks and Regards,