Essentially, I’m trying to convert a range of cells into a picture and then paste that picture at the bottom within a chart. I’m having a hard time placing the picture in the right spot with the right size. First, I can’t seem to place the image at the correct right/left coordinates (no matter what values I use, it seems to be dropped at the top). Secondly, the size of the image is not correct. If I save the image to file, it looks perfect, but if I use the AddPictureInChart method, it scales badly. I can’t seem to figure out what the last two scaling parameters mean. Please explain how to use this method correctly (or another way to do what I’m trying to do). I know I can display the data, but in reality I’m displaying something else (this is just to reproduce the problem). Thanks.
Workbook w = new Workbook();
Worksheet s = w.Worksheets[0];
s.Cells[“A1”].PutValue (“Product”);
s.Cells[“B1”].PutValue(“FY 2007”);
s.Cells[“C1”].PutValue(“FY 2008”);
s.Cells[“D1”].PutValue(“FY 2009”);
s.Cells[“A2”].PutValue(“Apples”);
s.Cells[“B2”].PutValue(150);
s.Cells[“C2”].PutValue(250);
s.Cells[“D2”].PutValue(200);
s.Cells[“A3”].PutValue(“Cherries”);
s.Cells[“B3”].PutValue(210);
s.Cells[“C3”].PutValue(220);
s.Cells[“D3”].PutValue(215);
int i = w.Worksheets.Add(SheetType.Chart);
i = w.Worksheets[1].Charts.Add(ChartType.Bar, 0, 0, 60, 20);
Chart chart = w.Worksheets[1].Charts[i];
//Set Properties of nseries
chart.NSeries.Add(“Sheet1!B2:D3”, true);
chart.NSeries.CategoryData = “Sheet1!B1:D1”;
chart.NSeries.IsColorVaried = true;
chart.PlotArea.Height = (int) (chart.PlotArea.Height * .8);
MemoryStream stream = new MemoryStream();
w.Worksheets[0].PageSetup.LeftMargin = 0;
w.Worksheets[0].PageSetup.RightMargin = 0;
w.Worksheets[0].PageSetup.TopMargin = 0.0;
w.Worksheets[0].PageSetup.BottomMargin = 0.0;
Bitmap bitmap = w.Worksheets[0].SheetToImage();
bitmap.Save(stream, System.Drawing.Imaging.ImageFormat.Png);
bitmap.Save(@“c:\temp\test.png”, ImageFormat.Png);
chart.Shapes.AddPictureInChart(chart.PlotArea.Y, 100, stream, 100, 100);
string f = @“c:\temp\imagetest.xls”;
System.IO.File.Delete(f);
w.Save(f);
Hi,
Thank you for considering Aspose.
Please modify your code as following to show the picture image at the bottom of the Chart. Also, the last two parameters i.e. WidthScale & HeightScale represent the scale of the image in percentage (Width & Height) and 100 percent means original image size.
Workbook w = new Workbook();
Worksheet s = w.Worksheets[0];
s.Cells["A1"].PutValue ("Product");
s.Cells["B1"].PutValue("FY 2007");
s.Cells["C1"].PutValue("FY 2008");
s.Cells["D1"].PutValue("FY 2009");
s.Cells["A2"].PutValue("Apples");
s.Cells["B2"].PutValue(150);
s.Cells["C2"].PutValue(250);
s.Cells["D2"].PutValue(200);
s.Cells["A3"].PutValue("Cherries");
s.Cells["B3"].PutValue(210);
s.Cells["C3"].PutValue(220);
s.Cells["D3"].PutValue(215);
int i = w.Worksheets.Add(SheetType.Chart);
i = w.Worksheets[1].Charts.Add(ChartType.Bar, 0, 0, 60, 20);
Chart chart = w.Worksheets[1].Charts[i];
//Set Properties of nseries
chart.NSeries.Add("Sheet1!B2:D3", true);
chart.NSeries.CategoryData = "Sheet1!B1:D1";
chart.NSeries.IsColorVaried = true;
chart.PlotArea.Height = (int) (chart.PlotArea.Height * .8);
MemoryStream stream = new MemoryStream();
w.Worksheets[0].PageSetup.LeftMargin = 0;
w.Worksheets[0].PageSetup.RightMargin = 0;
w.Worksheets[0].PageSetup.TopMargin = 0.0;
w.Worksheets[0].PageSetup.BottomMargin = 0.0;
Bitmap bitmap = w.Worksheets[0].SheetToImage();
bitmap.Save(stream, System.Drawing.Imaging.ImageFormat.Png);
chart.Shapes.AddPictureInChart(0, 0, stream, 100, 100);
chart.Shapes[0].TopInShape = 4000 - chart.Shapes[0].HeightInShape;
chart.Calculate();
string f = @"c:\templates\imagetest.xls";
System.IO.File.Delete(f);
w.Save(f);
Thank You & Best Regards,
Thanks. That fixes the location issue, but it the image is still not sized correctly. As you can see in the attachment, the image that is within the graph is too large (It appears to be vertically stretched). Interestingly, if you drop the same image on a worksheet, it works fine. See attached file for example:
Here’s my updated code:
chart.Shapes.AddPictureInChart(0, 0, stream, 100, 200); // this image is too big
chart.Shapes[0].TopInShape = 4000 - chart.Shapes[0].HeightInShape; // this works
w.Worksheets[0].Shapes.AddPicture(5, 0, 8, 4, stream); // this image is correct
Hi,
Thank you for considering Aspose.
We have found your mentioned issue after an initial test. We will look into it and get back to you soon. Your issue has been registered in our Issue Tracking System with issue id CELLSNET-11124.
Thank You & Best Regards,
Hi,
Thank you for considering Aspose.
Please call Chart.Calcualte() before save the file. It will give a much accurate image in the chart (may be not exact size).
Workbook w = new Workbook();
Worksheet s = w.Worksheets[0];
s.Cells["A1"].PutValue("Product");
s.Cells["B1"].PutValue("FY 2007");
s.Cells["C1"].PutValue("FY 2008");
s.Cells["D1"].PutValue("FY 2009");
s.Cells["A2"].PutValue("Apples");
s.Cells["B2"].PutValue(150);
s.Cells["C2"].PutValue(250);
s.Cells["D2"].PutValue(200);
s.Cells["A3"].PutValue("Cherries");
s.Cells["B3"].PutValue(210);
s.Cells["C3"].PutValue(220);
s.Cells["D3"].PutValue(215);
int i = w.Worksheets.Add(SheetType.Chart);
i = w.Worksheets[1].Charts.Add(ChartType.Bar, 0, 0, 60, 20);
Chart chart = w.Worksheets[1].Charts[i];
//Set Properties of nseries
chart.NSeries.Add("Sheet1!B2:D3", true);
chart.NSeries.CategoryData = "Sheet1!B1:D1";
chart.NSeries.IsColorVaried = true;
chart.PlotArea.Height = (int)(chart.PlotArea.Height * .8);
MemoryStream stream = new MemoryStream();
w.Worksheets[0].PageSetup.LeftMargin = 0;
w.Worksheets[0].PageSetup.RightMargin = 0;
w.Worksheets[0].PageSetup.TopMargin = 0.0;
w.Worksheets[0].PageSetup.BottomMargin = 0.0;
Bitmap bitmap = w.Worksheets[0].SheetToImage();
bitmap.Save(stream, System.Drawing.Imaging.ImageFormat.Png);
chart.Shapes.AddPictureInChart(0, 0, stream, 100, 200); // this image is too big
chart.Shapes[0].TopInShape = 4000 - chart.Shapes[0].HeightInShape; // this works
w.Worksheets[0].Shapes.AddPicture(5, 0, 8, 4, stream); // this image is correct
chart.Calculate();
string f = @"F:\FileTemp\dest.xls";
w.Save(f);
Thank You & Best Regards,
I don’t see a Calculate method for the chart object. Is that version specific?
Hi,
Thank you for considering Aspose.
Please try the attached latest version of Aspose.Cells. The API Chart.Calculate() may not be supported in the old version you are using.
Thank You & Best Regards,