SetOleSize works fine for evaluation version but doesn't work for licensed Aspose.Cells

shakeel.faiz · April 5, 2007, 9:45am

In the attached project, we are modifying the excel chart data embedded inside the power point slide of source presentation.

Everything works fine with evaluation version of Aspose.Cells, but licensed version is not working as expected.

In the licensed version, SetOleSize does not work.

You can see this behavior by running the project attached. It will generate out.ppt using in.ppt.

To check with licensed version, please uncomment the two relevant lines and use your own Aspose.Total.lic file

Please fix this problem as early as possible. It is very urgent.

toddba · April 5, 2007, 10:29am

Thank you Shakeel. It will be good to have SetOleSize() work in the licensed version, but I'm still concerned about one major thing...

Even when SetOleSize() is used, the actual size of the embedded object seems to be altered dramatically. In other words, please take a look at the object's "real" size before/after its data elements have been modified:

Open in.ppt
Right-click on the chart
Select Format Object...
Click on the Size tab.
At the bottom, note the size: 2.12" x 4.16"

Next...

Open out.ppt
Right-click on the chart
Select Format Object...
Click on the Size tab.
At the bottom, note the size: 5.38" x 9.96"

In out.ppt, the size/scale of the object is still being changed. Why isn't Aspose.Cells and/or Aspose.Slides retaining the correct size/scale of the object?

This is very undesireable because it makes the sizing very unreliable. For example:

Open out.ppt
Right-click on the chart
Select Format Object...
Click on the Size tab.
At the bottom, note the size: 5.38" x 9.96"
Click Cancel
Right-click on the chart
Select Chart Object->Open
Select the "Chart1" tab in the Excel window.
Close Excel.
Right-click on the chart
Select Format Object...
Click on the Size tab.
At the bottom, note the size has once again changed: 6.04" x 10.63"

Thanks!

Todd

Laurence · April 6, 2007, 7:23am

Please try this attached fix.

In this new version, please don't use SetOleSize method. Please try to use Worksheet.FirstVisibleRow and Worksheet.FirstVisibleColumn properties to set the first displayed cell.

For OLE object size issue, it's much more complex. We have to spend more time to figure out what caused this problem. Thanks for your patience.

toddba · April 6, 2007, 1:21pm

Thanks Laurence. I've downloaded the attached version. Aside from the OLE sizing issues, this version seems to be losing other chart attributes. For example:

The background of the chart defaults back to gray.
It adds a legend even though one wasn't present in the original chart.
The Axis settings are changed. In the original, the axis is represented as a %, but that is lost when the chart is saved back into the PPT.

I haven't done extensive testing with this version, but I'm sure you guys are probably already aware of those items that I've listed.

I hope you guys can resolve the OLE object size issue. Sorry to hear that it's a complex problem. We really need the ability to simply modify the chart's data properties without affecting any of the other formatting (colors, size, etc). We can do this through Automation of PowerPoint, but the sole reason we purchased an Aspose.Total license is so that we could get away from using Automation in favor of a lighter weight and more scalable solution.

Thanks Much,

Todd

Laurence · April 8, 2007, 8:30pm

Hi Todd,

Could you please post your out.ppt file here? We don't find any of your metioned problems. Please check my attached output file with following code:

static void AddExcelChart()
{
Aspose.Cells.License cellLicense = new Aspose.Cells.License();
Aspose.Slides.License slideLicense = new Aspose.Slides.License();

cellLicense.SetLicense("Aspose.Total.lic");
//slideLicense.SetLicense("Aspose.Total.lic");

//Source presentation contains Excel Chart object,
//Which was inserted inside the slide using Insert-->Object
Presentation srcPres = new Presentation("in.ppt");

Slide fstSlide = srcPres.GetSlideByPosition(1);

//The alternative text of the source is set as oleobject
//to get its reference quickly
Aspose.Slides.Shape srcShape = fstSlide.FindShape("oleobject");

OleObjectFrame oof = (OleObjectFrame)srcShape;

// check, if OleObjectFrame is not null then read the
// object data of OleObjectFrame as an array of bytes.

if (oof != null)
{
// create a workbook
Workbook wb = new Workbook();

// open the Excel Chart inside the workbook
MemoryStream msIn = new MemoryStream(oof.ObjectData);
wb.Open(msIn);

//This line does not work for licensed version
//wb.Worksheets.SetOleSize(1,12, 1, 6);

wb.Worksheets[1].FirstVisibleRow = 1;
wb.Worksheets[1].FirstVisibleColumn = 1;

// now save back the modified workbook to memory stream
//wb.Save("d:\\test\\abc1.xls");
//return;
MemoryStream msOut = wb.SaveToStream();
oof.ObjectData = msOut.ToArray();

}

//Write the presentation on disk
srcPres.Write("out.ppt");

////Now look at the slide, doubl click it, then click chart tab at
////the bottom with three other tabs and see the modified chart
}

shakeel.faiz · April 9, 2007, 10:16am

Dear Laurence,

Thank you for looking into this issue. I have attached the outWithLicense.ppt and outWithoutLicense.ppt so that you could better see the difference between them.

outWithoutLicense.ppt is the desired output presentation generated using evaluation version, wherease outWithLicense.ppt is the undesirable output presentation generated using licensed version of Aspose.Cells.

The code used to generate them is exactly the same as present in the project I had attached earlier.

toddba · April 9, 2007, 10:55am

Attached is my full project. in.ppt / out.ppt. Please note, this is a .NET 1.1 project. Not sure if that makes a difference or not.

Todd

Laurence:

Hi Todd,

Could you please post your out.ppt file here? We don't find any of your metioned problems. Please check my attached output file with following code:

static void AddExcelChart()
{
Aspose.Cells.License cellLicense = new Aspose.Cells.License();
Aspose.Slides.License slideLicense = new Aspose.Slides.License();

cellLicense.SetLicense("Aspose.Total.lic");
//slideLicense.SetLicense("Aspose.Total.lic");

//Source presentation contains Excel Chart object,
//Which was inserted inside the slide using Insert-->Object
Presentation srcPres = new Presentation("in.ppt");

Slide fstSlide = srcPres.GetSlideByPosition(1);

//The alternative text of the source is set as oleobject
//to get its reference quickly
Aspose.Slides.Shape srcShape = fstSlide.FindShape("oleobject");

OleObjectFrame oof = (OleObjectFrame)srcShape;

// check, if OleObjectFrame is not null then read the
// object data of OleObjectFrame as an array of bytes.

if (oof != null)
{
// create a workbook
Workbook wb = new Workbook();

// open the Excel Chart inside the workbook
MemoryStream msIn = new MemoryStream(oof.ObjectData);
wb.Open(msIn);

//This line does not work for licensed version
//wb.Worksheets.SetOleSize(1,12, 1, 6);

wb.Worksheets[1].FirstVisibleRow = 1;
wb.Worksheets[1].FirstVisibleColumn = 1;

// now save back the modified workbook to memory stream
//wb.Save("d:\\test\\abc1.xls");
//return;
MemoryStream msOut = wb.SaveToStream();
oof.ObjectData = msOut.ToArray();

}

//Write the presentation on disk
srcPres.Write("out.ppt");

////Now look at the slide, doubl click it, then click chart tab at
////the bottom with three other tabs and see the modified chart
}

amjad.sahi · April 9, 2007, 12:38pm

Hi Todd,

Thanks for the project with template file(s).

We will figure out your issue soon.

Thank you.

Laurence · April 9, 2007, 8:10pm

It’s a version problem. Please try this attached version.

toddba · April 10, 2007, 10:05am

Thanks. This version appears to be working... none of the strange artifacts like before (gray background, legend, etc). The only things i've noticed are:

wb.Worksheets[0].Charts[0].ToImage() is producing a pretty bland looking chart. See Attachment.
There still seems to be some sizing issues (in powerpoint) if you do a Chart Object -> Open as opposed to editing it in-place using Chart Object -> Edit. As soon as you close Excel, the size of the object shrinks very small.
If you execute a Workbook.Save*() operation, the contents of the workbook seem to go away. For example:

// now save back the modified workbook to memory stream

MemoryStream msOut = wb.SaveToStream();

oof.ObjectData = msOut.ToArray();

// save the workbook to an excel file

wb.Save("C:\\Source\\BO.DocBuilder.Test\\out.xls");

In the code above, the 2nd wb.Save() results in an empty out.xls file.

Thanks!

Todd

toddba · April 10, 2007, 1:59pm

Just an update -- this version is really looking good. Outside of the "bland" looking chart produced by Chart.ToImage(), most everything else seems to be working well.

Even the sizing issue seems to be better. I added the following code to replace the "Object changed" image with a real chart image, and it seems to have addressed some of the sizing problems:

// save the chart image to a bitmap file

System.Drawing.Bitmap img = wb.Worksheets[0].Charts[0].ToImage();

// now save back the modified workbook to memory stream

MemoryStream msOut = wb.SaveToStream();

oof.ObjectData = msOut.ToArray();

// create an aspose picture object from the memory stream

// and add it to the presentation

Aspose.Slides.Picture pic = new Aspose.Slides.Picture(pres, img);

oof.PictureId = pres.Pictures.Add(pic);

I just wanted to let you know that I really appreciate your help resolving this issue! You guys have been great, and I can't wait to have the Chart.ToImage() producing an accurate rendering of the chart so we can finally ditch having to use Microsoft Office Automation. :-)

Todd

levinator · April 10, 2007, 9:26pm

When I call wb.Worksheets(0).Charts(0).ToImage() I get an overflowException. Has anyone seen this and if so know how to resolve it. Thanks.

Matt

Warren · April 10, 2007, 10:03pm

Hi,

Could you post your file and codes?

Warren · April 10, 2007, 10:08pm

Hi Todd,

After calling workbook.Save() method, the content in the workbook will be removed.

toddba · April 11, 2007, 9:42am

Matt, I'm not seeing that problem, but my test app only contains a single slide presnetation and a single chart object. I've attached my test project.

Todd

levinator:

When I call wb.Worksheets(0).Charts(0).ToImage() I get an overflowException. Has anyone seen this and if so know how to resolve it. Thanks.

Matt

amjad.sahi · April 11, 2007, 3:12pm

Hi levinator,

Thanks for considering Aspose.

Well I tested the following code using Aspose.Cells version 4.1.2.16 ( you may download it @: <A href="</A>) and I don't find any error and the output files (attached) are fine: 'Instantiating an Workbook object Dim workbook As Workbook = New Workbook() 'Adding a new worksheet to the Excel object Dim sheetIndex As Integer = workbook.Worksheets.Add() 'Obtaining the reference of the newly added worksheet by passing its sheet index Dim worksheet As Worksheet = workbook.Worksheets(sheetIndex) 'Adding a sample value to "A1" cell

worksheet.Cells("A1").PutValue(50)

'Adding a sample value to "A2" cell

worksheet.Cells("A2").PutValue(100)

'Adding a sample value to "A3" cell

worksheet.Cells("A3").PutValue(150)

'Adding a sample value to "B1" cell

worksheet.Cells("B1").PutValue(4)

'Adding a sample value to "B2" cell

worksheet.Cells("B2").PutValue(20)

'Adding a sample value to "B3" cell

worksheet.Cells("B3").PutValue(50)

'Adding a chart to the worksheet

Dim chartIndex As Integer = worksheet.Charts.Add(ChartType.Column, 5, 0, 15, 5)

'Accessing the instance of the newly added chart

Dim chart As Chart = worksheet.Charts(chartIndex)

'Adding NSeries (chart data source) to the chart ranging from "A1" cell to "B3"

chart.NSeries.Add("A1:B3", True)

'Converting chart to image.

Dim bitmap As Bitmap = chart.ToImage()

'Saving the chart image.

bitmap.Save("d:\test\chartimg.jpg", System.Drawing.Imaging.ImageFormat.Jpeg)

'Saving the excel file.

workbook.Save("d:\test\chartbook.xls")

Could you please send here your template file.

Thank you.

levinator · April 11, 2007, 3:43pm

The problem seemed to be that my chart did not have typical data in it. There were non numerics in the cells that would normally contain them, there were blank cells in the data, etc. Once I entered data for a normal 3x3 chart, it worked fine. Thank you for your help. Do you have an idea when the new version of cells will actually be released?

Matt

Laurence · April 12, 2007, 12:12am

We are migrating our server in these few days. I will release a new version after the migration.

toddba · April 12, 2007, 3:28pm

That's great news! Can't wait to get it. Big Smile [:D]

Todd

toddba · April 20, 2007, 9:35am

Laurence:

We are migrating our server in these few days. I will release a new version after the migration.

Any update on a release date?

Thanks,

Todd