Free Support Forum - aspose.com

Positioning Legend using setX setY setWidth setHeight - not correct

Hi,

Please find the below sample, in which, I am trying to place legend at top using setX setY setWidth setHeight methods. As per the unit mentioned in your API docs, (in units of 1/4000 of the chart area), I am trying to put at x position 250, and a width of 3500. I am expecting the generated chart to position legend at vertically centre aligned, as x is 250, and a width of 3500 leave 250 at right as well. But the legend position seems to be not correct.
Please find the screen shot for rederence.
Also, note that, the positioning seems to be correct in the image generated, BUT there is no data found in the image.

Thanks
Muhammed

import java.io.*;
import java.lang.reflect.Array;
import com.aspose.cells.ImageFormat;
import com.aspose.cells.*;
import com.aspose.slides.*;

public class LegendPlacement {

public static void main(String args[]) {

try {

System.out.println("Aspose Version : " + CellsHelper.getReleaseVersion() );

String[] cellsName = new String[] { "B1", "B2", "B3", "B4",
"C1", "C2", "C3", "C4",
"D1", "D2", "D3", "D4",
"E1", "E2", "E3", "E4",
"F1", "F2", "F3", "F4"
};

//Array of cell data
int[] cellsValue = new int[] { 67, 86, 68, 91,
44, 64, 89, 48,
46, 97, 78, 6,
43, 29, 69, 26,
24, 4, 38, 25 };

Workbook wb = new Workbook();
Worksheet dataSheet = wb.getWorksheets().getSheet(0);
String dataSheetName = "DataSheet";
dataSheet.setName(dataSheetName);

dataSheet.getCells().getCell("A1").setValue("India");
dataSheet.getCells().getCell("A2").setValue("United States");
dataSheet.getCells().getCell("A3").setValue("United Kingdom");
dataSheet.getCells().getCell("A4").setValue("Japan");
dataSheet.getCells().getCell("A5").setValue("China");

//Populate DataSheet with data
int size = Array.getLength(cellsName);
for (int i = 0; i < size; i++) {
String cellName = cellsName[i];
int cellValue = cellsValue[i];
dataSheet.getCells().getCell(cellName).setValue(cellValue);
}

//Add a chart sheet
Worksheet chartSheet = wb.getWorksheets().addSheet();
chartSheet.setName("ChartSheet");
int chartSheetIdx = chartSheet.getIndex();

//Add a chart in ChartSheet with data series from DataSheet
int chartWidth = 400;
int chartHeight = 300;

int chartHeightInRows = 0;
int chartWidthInCols = 0;
int cellWidth = dataSheet.getCells().getColumnWidthPixel(0);
int cellHeight = dataSheet.getCells().getRowHeightPixel(0);
chartWidthInCols = (int)(chartWidth / cellWidth);
chartHeightInRows = (int)(chartHeight / cellHeight);
chartWidth = chartWidthInCols * cellWidth;
chartHeight = chartHeightInRows * cellHeight;

Chart chart = chartSheet.getCharts().addChart(ChartType.COLUMN_CLUSTERED, 0, 0, 0, 0, chartWidth, chartHeight);
chart.getNSeries().add(dataSheetName + "!B1:F4", false);

for (int i = 0; i < chart.getNSeries().size(); i++) {
ASeries series = chart.getNSeries().get(i);

PatternFill pf = new PatternFill();
pf.setBackgroundColor(wb.getPalette().getColor(i));
pf.setForegroundColor(wb.getPalette().getColor(i));
series.getArea().setFill(pf);
}

String categoryAreaRef = dataSheet.getName() + "!A1:A5";
chart.getNSeries().setCategoryData(categoryAreaRef);

Font font = chart.getLegend().getFont();
font.setBold(true);
font.setItalic(true);
font.setColor(wb.getPalette().getColor(25));

PatternFill pf1 = new PatternFill();
pf1.setBackgroundColor(wb.getPalette().getColor(30));
pf1.setForegroundColor(wb.getPalette().getColor(30));
chart.getChartArea().getArea().setFill(pf1);

PatternFill pf2 = new PatternFill();
pf2.setBackgroundColor(wb.getPalette().getColor(40));
pf2.setForegroundColor(wb.getPalette().getColor(40));
chart.getChartArea().getArea().setFill(pf2);

Legend legend = chart.getLegend();
legend.setX(250);
legend.setWidth(3500);
legend.setY(10);
legend.setHeight(200);

//Get Chart as image.
ImageOptions imgOpts = new ImageOptions();
imgOpts.setImageFormat(ImageFormat.PNG);
imgOpts.setFashion(FileFormatType.EXCEL2003);

chart.toImage(new FileOutputStream("D:\\Temp\\chart.png"), imgOpts);

wb.getWorksheets().setActiveSheet(chartSheetIdx);
wb.setOleSize(0, chartHeightInRows - 1, 0, chartWidthInCols - 1);

//Save the workbook to stream
ByteArrayOutputStream bout = new ByteArrayOutputStream();
wb.save(bout);
wb.save(new FileOutputStream("D:\\Temp\\output.xls"));

Presentation pres = new Presentation();
Slide sld = pres.getSlideByPosition(1);

int slideWidth = (int) pres.getSlideSize().getX();
int slideHeight = (int) pres.getSlideSize().getY();

int x = (slideWidth - chartWidth * 8) / 2;
int y = (slideHeight - chartHeight * 8) / 2;

OleObjectFrame oof = sld.getShapes().addOleObjectFrame(800, 500, chartWidth * 8, chartHeight * 8, "Excel.Sheet.8", bout.toByteArray());
com.aspose.slides.Picture pic = new com.aspose.slides.Picture(pres, new FileInputStream("D:\\Temp\\chart.png"));
int picId = pres.getPictures().add(pic);
oof.setPictureId(picId);

//Write the presentation on disk
pres.write(new FileOutputStream("D:\\Temp\\output.ppt"));
System.out.println("successfully completed!!!");
}
catch (Exception e) {
e.printStackTrace();
}
}

}

Hi,

After an initial test using your code segment, I have found the issue. We will figure your issue out soon.

Your issue has been registered into our issue tracking system with an issue id: CELLSJAVA-15383. We will inform you when it is sorted out.

Thank you.

Hi,

Please try the attached version.

We have improved the generated file for the
legend position. However, since it is a complex issue to set Legend’s position. The
Legend’s position shown in MS Excel is actually influenced not only by the position and
size of the data of Legend itself, but also influenced by some other components/objects,
such as PlotArea, Axis’ size and position. Currently, we cannot calculate
the proper size and position automatically for the other components when setting
Legend’s size and position. So, the generated Chart by the APIs might be a bit
different from what it should be by the value set for their size and
position.


Thanks for your understanding!

Hi,

Not much improvement. Anyway better than earlier version.
Image generated out of it is not correct. It overlaps with plotarea.

Thanks
Muhammed

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thank you for the feedback.

We have found the overlapping issue in Chart2Image. We will look into it and get back to you.

Thank You & Best Regards,