ToImage() and Palette

Aspose.Cells Product Family
1

Hi

I’m having some dificulties regarding Aspose.Charts and the Aspose.Cells.Workbook’s palette. I have this palette that we use in all our documents and I programmatically upload it to the workbook (right after I create a new instance of this class) using the ChangePalette method. This works perfectly because if I save this Workbook to an excel file and then open it, the chart appears with the new colors. The problem resides in the ToImage method of the chart. When I create a BMP from the ToImage method, the image returns the chart with the original chart colors, the ones automatically generated by excel.

Is there a solution to this?

Thanks in advance

2

Hi,

Thanks for considering Aspose.

Could you elaborate to explain it more. If possible, please do create a sample console test with your excel file (which may contain the output chart) to create chart image to show the issue. Kindly zip all the files including code and post here. It will really help us to check the difference and figure out the issue soon.

Thank you.

3

Hello Amjad

I have attached some sample code that represents the creation of the workbook, containing both data and the chart; the code that takes that workbook and places it as an OLE Object Frame inside a slide and at the same time extracts an image from that chart to replace the Object Changed one. I’ve also attached the output PPT.

Everything works fine except the toImage() method of the chart. If you see the attached PPT you ill notice 3 normal pie charts with the standard colors of excel 2003. But if you double click anyone of them, you will notice the new colours of the palette, assigned in the beggining of the creation code.

So the problem is, in my opinion, that the ToImage() methos doesn’t consider the palette of the workbook or it overlaps it with the standard colors.

Do you have any idea of what’s wrong?

4

Hi,

Thanks for the sample project,

We will check and get back to you soon.

Thank you.

5

Hi,

Thanks fro your sample code.

Please try this fix. We have fixed the bug.

And we are fixing another bugs in converting these charts to image.(The pie is too big and the position of data labels is not corrected).

6

Hi Warren,

Thanks, that fix worked great with colours. One small detail, though… I changed nothing in my code (except the replacement of the dll file) and these lines of code now return an “Invalid row index” error:

Range rng = sheet.Cells.CreateRange(0, 1, 10, 2);
rng.SetOutlineBorder(BorderType.LeftBorder, CellBorderType.Thin, Color.Black);
rng.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Black);

I checked it and the error occurs because of the row index 0 (zero), but it worked fine before.

Also another subject about stacked column chart ToImage(). I attached the ppt containing an OLEObjectFrame with a stacked column chart, which ObjectChanged image was replaced by the ToImage() of the chart. Basically, the columns aren’t showing in the bmp. (If you double cick it, you’ll see the graph underneath)

Thanks in advance,
Cruz

7

Hi Cruz,

We tested the two issues you listed. It works fine.See following codes:

internal static void TestPPT()
{
Presentation srcPres = new Presentation(@"F:\FileTemp\CRM_Auto.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.Shapes[5];//.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)
{
Console.WriteLine(oof.ObjectName);
//// create a workbook
Workbook wb = new Workbook();
// open the Excel Chart inside the workbook
MemoryStream msIn = new MemoryStream(oof.ObjectData);
wb.Open(msIn);
wb.Worksheets[0].Cells.ClearRange(0, 1, 10, 2);
Bitmap bitmap = wb.Worksheets[1].Charts[0].ToImage();
bitmap.Save(@"F:\FileTemp\dest.bmp");
wb.Save(@"F:\FileTemp\dest.xls");
return;
}
}

8

Hello again, Warren, thanks for your reply.

Well I rechecked those issues and you were right about the ToImage, it was my mistake, I forgot to define the scale.

Nevertheless, the other issue still takes place. I tried the code you posted and it works fine, but you used “ClearRange” method, and my problem is with the “CreateRange” method. The ClearRange method works with 0 (zero) index in the initial row but the CreateRange doesn’t. This didn’t happen before the fix you posted, 3 posts back, so I assume it’s a little bug. I was using this range to place borders on the cells of the first row, but now I can’t do that, because it returns the “Invalid row index” error. Can you help with this issue?

Thanks
Cruz

9

Hi Cruz,

Well, it 's working here. It does not produce any error what so ever.

Here is my code and attached is the template file.

Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
Range rng = sheet.Cells.CreateRange(0, 1, 10, 2);
rng.Name = "MyRange";
rng.SetOutlineBorder(BorderType.LeftBorder, CellBorderType.Thin, Color.Black);
rng.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Black);
workbook.Save("d:\\test\\outrangeprob.xls");
Thank you.
10

Hi again, Amjad.

Well, I managed to solve the issue, but I didn’t found a reason for it… I explain it to you now, in case you want to test it as a bug or something.

I picked up your code and modified it to simulate my issue… here’s what I came up with:

Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];

Aspose.Cells.Style style_test = workbook.Styles[workbook.Styles.Add()];
style_test.HorizontalAlignment = TextAlignmentType.Center;
style_test.Font.Size = 12;

sheet.Cells.CreateRange(0, 1, 1, 12).Style = style_test;
Range rng = sheet.Cells.CreateRange(0, 1, 10, 2);
rng.Name = “MyRange”;
rng.SetOutlineBorder(BorderType.LeftBorder, CellBorderType.Thin, Color.Black);
rng.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Black);
workbook.Save(“c:\test.xls”);

In bold is the code I added to yours.
In bold&underline is the line where the problem resides. If this line is removed, all works well.
In italic is the line that returns the “Invalid row index” value.

So I solved the problem removing that line from my code and doing the same operation in a different way.

11

And by the way, since this also related to ToImage(), I have another issue…

In the StackedColumnCharts, everything works fine and the resulting excel file is perfect. But when I get the chart’s picture, to replace the “object changed” in a ppt, the image always returns a chart with the same max value in the ValueAxis, which is 10.000.000 (€ in this case), while the real chart has the auto scale property to true, which is the objective. Can you help me with this?

As a quick workaround, I was trying to get the maximum value in the chart, so I could find a dynamic maximum. Is this possible?

Many thanks,
Cruz

12

Hi Curz,

Thanks for the code related range issue,

We found the issue and will fix it soon.

Related your second StackedColumnChart issue, Could you create a simple console application and post us here, It will help us to figure out the issue soon.

Thank you.

13

I attached 3 files, the code with the function that creates the chart based on a datatable (a datatable example is provided); the excel file generated by the function (with a different datatable that the one provided but with the same format, only different values); and the ppt file containing the saved workbook inside an OleObjectFrame, which “ObjectChanged” image as been replaced by the ToImage picture of the chart. In this picture you can see that the maximum value is so high that the bars won’t even show up because they have low values.

Thanks for the reply
Cruz

14

Hi Cruz,

Thanks for the files you privided,

We will figure out the issue soon.

Thank you.

15

Hi Cruz,

Thanks for your files. Please try this fix.

But I still could not find the problem of the maximum value. I read the file from the ppt and convert the chart to image. The maximum value is same as the displayed value in Excel.

Presentation srcPres = new Presentation(@"F:\FileTemp\test.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.Shapes[5];//.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);
wb.Worksheets[0].Cells.CreateRange(0, 1, 10, 2);
Bitmap bitmap = wb.Worksheets[1].Charts[0].ToImage();
bitmap.Save(@"F:\FileTemp\dest.bmp");
wb.Save(@"F:\FileTemp\dest.xls");
return;
}

16

Hi Cruz,

Please check the created file with the demo codes.

17

Hi Warren,

I copy pasted your code exactly and attached the result image and excel file. It happened exactly what happened before. The excel file is perfect, but the ToImage method always returns a chart with a valueaxis maximum value high above the maximum value in the datatable, turning the columns invisible. Maybe its the version of Aspose.Slides? I had the latest fix you have on the download section but one of you already gave me a fix for another issue I had.

I’m trying to figure out which line of code provokes this, if this is provoked by something in my code. Will get back to you as soon as I have any progress.

And another thing… if I explicitly define the maximum for the valueaxis, the resulting image reflects that change perfectly, so I assume that the bug (if there is one) is on the AutoScale of the ToImage method (forgive me if I said something stupid :P).

Thanks for the patience :slight_smile:
Cruz

18

Hi Cruz,

Do you mean your created image file with my codes is not same as my attached image?

19

Yeap, Warren, that’s right. Your code resulted in an image with the same problem I had: the maximum value is always much greater than the values, and thus resulting in a different image from the one you attached in the previous post. That’s what makes me believe that the problem resides in the version of the Aspose.Slides.dll. Can you send me your latest fix/version?

20

Hi,

You mean latest fix/version of Aspose.Cells, here it is (attached)

Thank you.