Stacked column chart: moving legend changes which legend entry is deleted

Aspose.Cells Product Family
1
After deleting a legend entry from a stacked column chart, if I change the position of the legend (from right to bottom, for example), it changes which legend entry is deleted.

To reproduce the problem:

//Instantiating a Workbook object
Workbook workbook = new Workbook();

//Obtaining the reference of the first worksheet
WorksheetCollection worksheets = workbook.getWorksheets();
Worksheet sheet = worksheets.get(0);

//Adding some sample value to cells
Cells cells = sheet.getCells();
com.aspose.cells.Cell cell = cells.get("A1");
cell.setValue(50);
cell = cells.get("A2");
cell. setValue (100);
cell = cells.get("A3");
cell.setValue(150);
cell = cells.get("B1");
cell.setValue(4);
cell = cells.get("B2");
cell.setValue(20);
cell = cells.get("B3");
cell.setValue(50);

ChartCollection charts = sheet.getCharts();

//Adding a chart to the worksheet
int chartIndex = charts.add(ChartType.COLUMN_STACKED,5,0,15,5);
Chart chart = charts.get(chartIndex);

//Adding NSeries (chart data source) to the chart ranging from "A1" cell to "B3"
SeriesCollection serieses = chart.getNSeries();
serieses.add("A1:B3", true);

// Delete Series1 legend entry
Series series1 = serieses.get(0);
series1.getLegendEntry().setDeleted(true);
// This workbook will show only Series2 in the legend
workbook.save("C:\\legend_right.xls");
// Move the legend from the right side to the bottom
chart.getLegend().setPosition(LegendPositionType.BOTTOM);
// This workbook will show only Series1 in the legend
workbook.save("C:\\legend_bottom.xls");

2

Hi Todd,

Thanks for your posting and using Aspose.Cells.

We were able to observe this issue after executing your sample code using the latest version: Aspose.Cells
for Java v8.2.1.1. Moving legend changes which legend entry is deleted.

We have logged this issue in our database for investigation. We will look into it and fix this issue. Once, the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as CELLSJAVA-41022.

I have also attached the output xls files for a reference.

3

Hi,

Please try our latest fix: Aspose.Cells for Java v8.2.2.2 and use the new method by adding the line of code:
e.g.
Sample code:

chart.getPlotArea().setPositionAuto().

We investigated the issues and found the deleted legendEntry displayed is caused by the different Excel version and MS Excel behavior. If you could open the file using MS Excel 2003, the result is right. We actually save the .xls file format while abiding by and with respect to MS Excel 2003 specifications. So we suggest you to kindly save the resultant file to .xlsx file for you requirements. Please try the following code segment.
e.g.
Sample code:

//Instantiating a Workbook object

Workbook workbook = new Workbook();

//Obtaining the reference of the first worksheet

WorksheetCollection worksheets = workbook.getWorksheets();

Worksheet sheet = worksheets.get(0);

//Adding some sample value to cells

Cells cells = sheet.getCells();

com.aspose.cells.Cell cell = cells.get("A1");

cell.setValue(50);

cell = cells.get("A2");

cell. setValue (100);

cell = cells.get("A3");

cell.setValue(150);

cell = cells.get("B1");

cell.setValue(4);

cell = cells.get("B2");

cell.setValue(20);

cell = cells.get("B3");

cell.setValue(50);

ChartCollection charts = sheet.getCharts();

//Adding a chart to the worksheet

int chartIndex = charts.add(ChartType.*COLUMN_STACKED* ,5,0,15,5);

Chart chart = charts.get(chartIndex);

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

SeriesCollection serieses = chart.getNSeries();

serieses.add("A1:B3", true);

// Delete Series1 legend entry

Series series1 = serieses.get(0);

series1.getLegendEntry().setDeleted(true);

String dir = "D:\\Aspose\\User\\0879\\";

// This workbook will show only Series2 in the legend

workbook.save(dir + "legend_right.xls");

// Move the legend from the right side to the bottom

chart.getLegend().setPosition(LegendPositionType.*BOTTOM* );

//For placing the legend to more better position, set position of plot area to automatic.

chart.getPlotArea().setPositionAuto();

// This workbook will show only Series1 in the legend

workbook.save(dir + "legend_bottom.xls");

//Saves to xlsx formatted file for opening with excel 2007+.

workbook.save(dir + "legend_bottom.xlsx");

Thank you.