Free Support Forum - aspose.com

Wrong legend entries are getting deleted for BAR_CLUSTERED chart when first series legend entry is deleted

Hi,

Please run the following sample code and see that wrong legend entries are getting deleted for BAR_CLUSTERED chart when first series legend entry is deleted.

NOTE that expected legend entries are getting deleted when we change the chart type to COLUMN_CLUSTERED .
(Use this line:- Chart chart = chartSheet.getCharts().addChart(ChartType.COLUMN_CLUSTERED, 0, 0, 1, 1, 500, 300);)

ALSO note that the chart image generated contains expected legend entries in both cases.

Thanks & Regards
Muhammed

import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.aspose.cells.ASeries;
import com.aspose.cells.CellsHelper;
import com.aspose.cells.Chart;
import com.aspose.cells.ChartType;
import com.aspose.cells.Color;
import com.aspose.cells.FileFormatType;
import com.aspose.cells.ImageFormat;
import com.aspose.cells.ImageOptions;
import com.aspose.cells.PatternFill;
import com.aspose.cells.Workbook;
import com.aspose.cells.Worksheet;

public class LegendEntryConflict {

public static void main(String args[]) {

try {

String[] colTitle = new String[] { "Iron Enhancer",
"Special Wash",
"Wash Treatment"
};

//Array of cell data
double[][] data = new double[][] { { 877104.1, 481208, 1652079},
{ 1010714, 1276822, 6848673},
{ 1010714, 1276822, 6848673},
};

Workbook wb = new Workbook();
Worksheet dataSheet = wb.getWorksheets().addSheet();
wb.getPalette().setColor(0, new Color(0xFF, 0x00, 0x00));
wb.getPalette().setColor(1, new Color(0x42, 0xDF, 0xF4));
wb.getPalette().setColor(2, new Color(0x00, 0xF4, 0x65));

String dataSheetName = "DataSheet";
dataSheet.setName(dataSheetName);

int rowIndex = 0;
for (int i = 0; i < colTitle.length; i++) {
dataSheet.getCells().getCell(rowIndex, i).setValue(colTitle[i]);
}

for (int i = 0; i < data.length; i++) {
for (int j = 0; j < data[i].length; j++) {
dataSheet.getCells().getCell(i + 1, j).setValue(data[i][j]);
}
}

//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 chartRows = 20, chartCols = 15;
Chart chart = chartSheet.getCharts().addChart(ChartType.BAR_CLUSTERED, 0, 0, 1, 1, 500, 300);
//Chart chart = chartSheet.getCharts().addChart(ChartType.COLUMN_CLUSTERED, 0, 0, 1, 1, 500, 300);

StringBuffer emptySeries = new StringBuffer("{");
for (int i = 0; i < colTitle.length; i++) {
emptySeries.append("0.0");
//emptySeries.append("5000000.0");
if (i != (colTitle.length - 1)) {
emptySeries.append(",");
}
}
emptySeries.append("}");

Map legendLabelMap = new HashMap();
legendLabelMap.put("0", "Unit Sales");
legendLabelMap.put("1", "Value Sales");
legendLabelMap.put("2", "Volume Sales");

for (int i = 1; i <= data.length; i++) {

int emptyIndex = chart.getNSeries().add(emptySeries.toString(), false);
chart.getLegend().getLegendEntries().getLegendEntry(emptyIndex).setDeleted(true);

String lid = "" + (i - 1);

String dataRef = dataSheet.getName() + "!" + CellsHelper.convertCellIndexToName(i, 0) + ":" + CellsHelper.convertCellIndexToName(i, colTitle.length - 1);
int seriesIndex = chart.getNSeries().add(dataRef, false);
ASeries series = chart.getNSeries().get(seriesIndex);
series.setGapWidth(25);
if (legendLabelMap.get(lid) != null) {
series.setName((String)legendLabelMap.get(lid));
}

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

//Set X Axis Data
String categoryAreaRef = dataSheet.getName() + "!" + CellsHelper.convertCellIndexToName(0, 0) + ":" + CellsHelper.convertCellIndexToName(0, colTitle.length - 1);
chart.getNSeries().setCategoryData(categoryAreaRef);

//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, chartRows, 0, chartCols);

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


System.out.println("LegendEntryConflict successfully completed!!!" + CellsHelper.getReleaseVersion());

}
catch (Exception e) {
e.printStackTrace();
}
}

}

Hi,

Thank you for sharing the sample code.

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

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 internal issue tracking system with issue id: CELLSJAVA-16037.

Thank You & Best Regards,

The issues you have found earlier (filed as 16037) have been fixed in this update.


This message was posted using Notification2Forum from Downloads module by aspose.notifier.

Hi,

This issue is still reproducible(with version 2.2.0.0).

Note that, one more issue is there.

1. In both cases(BAR/COLUMN), Category Axis tick labels are not coming in the image. Instead, 1, 2, 3 etc are shown.

Thanks
Muhammed

Hi,

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

Thank you for the feedback.

We have reopened your issue with issue id : CELLSJAVA-16037. We will further look into your reported issues and get back to you soon.

Thank You & Best Regards,

Hi,
For the issue of category axis tick labels are different (e.g they are rendered as 1,2,3 in the output image), we have found the issue and will fix it in later fixes.

For the issue of deleted entries, I am afraid it is an issue of MS Excel2007 itself. To check it, please create such kind of chart in MS EXCEL2003 manually and save the file and then open the generated xls file by MS EXCEL2007. In Excel2003, you will see everything works fine and all deleted entries have been removed from the Legend Box. However, in MS EXCEL2007 the deleted entries are shown in the Legend Box that have been removed from the Legend.


Thank you.

Hi,

Please try the attached version. We have fixed the issue of category axis tick
labels.

Thank you.

Hi,
The issue of category axis tick labels is working fine now.
Thanks
Muhammed