Positioning chart in the middle of cells in Aspose.Cells for Java

Aspose.Cells Product Family
1

Hi,
How can I position a chart in excel to begin from the middle of a cell? I think older version of Aspose.Cells accept additional parameters to set horizontal and vertical offset inside left top cell. How can I do this in latest version of Aspose.Cells for java?

Workbook workbook = new Workbook();
Worksheet sheet = workbook.getWorksheets().get(0);
Cells cells = sheet.getCells();
cells.get(0, 1).putValue(“Income”);
cells.get(1, 0).putValue(“Company A”);
cells.get(2, 0).putValue(“Company B”);
cells.get(3, 0).putValue(“Company C”);
cells.get(1, 1).putValue(10000);
cells.get(2, 1).putValue(20000);
cells.get(3, 1).putValue(30000);
int chartIndex = sheet.getCharts().add(ChartType.COLUMN, 9, 9, 21, 15);
Chart chart = sheet.getCharts().get(chartIndex);
//Older Version
//Chart chart = chartSheet.getCharts().addChart(ChartType.COLUMN, 9, 9, 0, 0, 21, 15);
// chart.getNSeries().add(dataSheetName + “!B1:F4”, false);
chart.getNSeries().add(“B2:B4”, true);
chart.getNSeries().setCategoryData(“A2:A4”);
Series aSeries = chart.getNSeries().get(0);
aSeries.setName("=B1");
chart.setLegendShown(true);
chart.getTitle().setText(“Income Analysis”);

Thanks
Muhammed
2

Hi,


Thanks for your posting and using Aspose.Cells.

Please try one of these methods (properties), it should fulfill your needs. Let us know your feedback.

  • Chart.getChartObject().setLowerDeltaX(value);
  • Chart.getChartObject().setLowerDeltaY(value);
  • Chart.getChartObject().setUpperDeltaX(value);
  • Chart.getChartObject().setUpperDeltaY(value);

3

Hi,

It partially worked. BTW, your API Doc is very poor. Please see attached images.
Couple of questions…

1) What is the unit of the mentioned parameters?
2) How can I position the chart to start from centre of a cell and end at quarter position of a cell - ie How can we pass different values for lower and upper deltas?

Please attached images for details.

Thanks
4

Hi,


Thanks for your posting and using Aspose.Cells.

From your screenshots, it seems, code is working good. Please note, Aspose.Cells for Java is just a replica of Aspose.Cells for .NET. I have attached the Aspose.Cells for .NET offline documentation chm file and the screenshot for your reference. We will fix the online documentation issues as well but for the meanwhile, you can use this chm file. You can always download the latest chm file from Aspose.Cells for .NET Downloads Section. Just download the .zip or msi file and extract the offline chm documentation. Thank you and have a good day.

Shape.LowerDeltaX Property

Gets or sets the shape’s horizontal offset from its lower right corner column. The range of value is 0 to 1024.

public int LowerDeltaX {get; set;}
5

Hi,

Please help me to resolve this issue mentioned in Qst-2. Please refer screenshots Aspose500_0.PNG, Aspose500_250.PNG. That seems to be not working as expected.

2) How can I position the chart to start from centre of a cell and end at quarter position of a cell - ie How can we pass different values for lower and upper deltas?

Thanks
6

Hi,


Thanks for your posting and using Aspose.Cells.

Please see the following sample code and its comments. Delta X ranges from 0 to 1024 while Delta Y ranges from 0 to 256.

I have also attached the sample excel file used in this code and output excel file generated by it for your reference.

Java
Workbook wb = new Workbook(dirPath + “sample.xlsx”);

Worksheet ws = wb.getWorksheets().get(0);

Chart ch = ws.getCharts().get(0);

ch.getChartObject().setUpperDeltaX(1024 / 2); //Range is 0 to 1024
ch.getChartObject().setUpperDeltaY(256 / 2); //Range is 0 to 256

// ch.calculate();

wb.save(dirPath + “output.xlsx”);

7

Hi,
Can you try the following sample code and explain me why the highlighted code does not make any difference.

Workbook workbook = new Workbook();
Worksheet sheet = workbook.getWorksheets().get(0);
Cells cells = sheet.getCells();
cells.get(0, 1).putValue(“Income”);
cells.get(1, 0).putValue(“Company A”);
cells.get(2, 0).putValue(“Company B”);
cells.get(3, 0).putValue(“Company C”);
cells.get(1, 1).putValue(10000);
cells.get(2, 1).putValue(20000);
cells.get(3, 1).putValue(30000);
int chartIndex = sheet.getCharts().add(ChartType.COLUMN, 1, 2, 12, 8);
Chart chart = sheet.getCharts().get(chartIndex);
//Option 1
chart.getChartObject().setUpperDeltaX(1024 * 3 / 4);
chart.getChartObject().setLowerDeltaX(1024 * 1 / 4);

 //Option 2
chart.getChartObject().setUpperDeltaX(1024 * 1 / 4);
chart.getChartObject().setLowerDeltaX(1024 * 1 / 4);

 //Option 3
chart.getChartObject().setUpperDeltaX(1024 * 1 / 2);
chart.getChartObject().setLowerDeltaX(1024 * 1 / 4);
// chart.getChartObject().setUpperDeltaY(256 / 2);
// chart.getChartObject().setLowerDeltaY(256 / 4);
chart.getNSeries().add(“B2:B4”, true);
chart.getNSeries().setCategoryData(“A2:A4”);
Series aSeries = chart.getNSeries().get(0);
aSeries.setName("=B1");
chart.setLegendShown(true);
chart.getTitle().setText(“Income Analysis”);
File excelFile = new File(“D:\TEMP\temp\Aspose\ChartOffset.xlsx”);
workbook.save(excelFile.getAbsolutePath());

Thanks
8

Hi,


Thanks for your posting and using Aspose.Cells.

Please also provide your expected output excel file so that we could easily sort out this issue. I have generated the output excel file from your sample code and attached it here. You can create expected output excel file manually using Microsoft Excel. Thanks for your cooperation in this regard and have a good day.

Java
Workbook workbook = new Workbook();
Worksheet sheet = workbook.getWorksheets().get(0);
Cells cells = sheet.getCells();
cells.get(0, 1).putValue(“Income”);
cells.get(1, 0).putValue(“Company A”);
cells.get(2, 0).putValue(“Company B”);
cells.get(3, 0).putValue(“Company C”);
cells.get(1, 1).putValue(10000);
cells.get(2, 1).putValue(20000);
cells.get(3, 1).putValue(30000);

int chartIndex = sheet.getCharts().add(ChartType.COLUMN, 1, 2, 12, 8);
Chart chart = sheet.getCharts().get(chartIndex);

// Option 1
chart.getChartObject().setUpperDeltaX(1024 * 3 / 4);
chart.getChartObject().setLowerDeltaX(1024 * 1 / 4);

// Option 2
chart.getChartObject().setUpperDeltaX(1024 * 1 / 4);
chart.getChartObject().setLowerDeltaX(1024 * 1 / 4);

// Option 3
chart.getChartObject().setUpperDeltaX(1024 * 1 / 2);
chart.getChartObject().setLowerDeltaX(1024 * 1 / 4);

// chart.getChartObject().setUpperDeltaY(256 / 2);
// chart.getChartObject().setLowerDeltaY(256 / 4);

chart.getNSeries().add(“B2:B4”, true);
chart.getNSeries().setCategoryData(“A2:A4”);
Series aSeries = chart.getNSeries().get(0);
aSeries.setName("=B1");
chart.setShowLegend(true);
chart.getTitle().setText(“Income Analysis”);

workbook.save(dirPath + “out.xlsx”);


9

Hi,

I have attached the expected excel file. You can see the image with offset highlighted.

Thanks

10

Hi,


Thanks for using Aspose.Cells.

We were able to observe this issue. We will look into it further and log it in our database for a fix and update you asap.

11

Hi,


Thanks for using Aspose.Cells.

We were able to find the issue and logged it in our database for a fix.

This issue has been logged as

  • CELLSJAVA-42140 - DeltaX does not update the chart dimension properly

For a meanwhile, please use this workaround for your needs.

Java
chart.getChartObject().setUpperDeltaX(1024 * 1 / 4);

int width = chart.getChartObject().getWidth() + sheet.getCells().getColumnWidthPixel(chart.getChartObject().getLowerRightColumn())/2;
chart.getChartObject().setWidth(width);

12

Hi again,


This it update you that we have looked further into the matter logged earlier as CELLSJAVA-42140. Please note, If you change DeltaX, Aspose.Cells APIs only move the whole shape, not change the size of the shape. If you want to change the size of the shape, please update Shape.Width or Shape.Height properties accordingly.
13

Hi,

Now I am able to achieve my requirement with the following two lines:-

chart.getChartObject().setLowerDeltaX(1024 * 1 / 4);
chart.getChartObject().setWidthInch(cells.getColumnWidthInch(0) * 6.5);

BTW, what is the use of setUpperDeltaX and setUpperDeltaY if these methods are meant to move the whole shape.

Thanks
14

Hi again,


Thank you for writing back. It is good to know that you are able to achieve your desired goals. Please note, the Shape.UpperDeltaX property represents the shape’s horizontal offset from its upper left corner column where the range of value is 0 to 1024. Similarly the Shape.UpperDeltaY property represents the shape’s vertical offset from its upper left corner row where the range of value is 0 to 256.