Chart.toImage - wrong image generated for COLUMN_CLUSTERED chart (Pareto Implementation)

Aspose.Cells Product Family
1

Hi,

Please run the following sample and see that image generated is not perfect.
Please copy the file - pareto.xml - to the same folder.
Please find the image attached for details.

Thanks
Muhammed

import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;

import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.NodeList;

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;
import com.aspose.slides.OleObjectFrame;
import com.aspose.slides.Presentation;
import com.aspose.slides.Slide;

public class ParetoToImage {

public static void main(String args[]) {

try {
System.out.println(CellsHelper.getReleaseVersion());

Workbook wb = new Workbook();
Worksheet dataSheet = wb.getWorksheets().addSheet();

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

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

int rowIndex = 1;
int colIndex = 1;
int colCount = 1;

DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
DocumentBuilder db = dbf.newDocumentBuilder();
Document doc = db.parse(ParetoToImage.class.getResourceAsStream("pareto.xml"));

NodeList valList = doc.getElementsByTagName("Value");
int rowCount = valList.getLength();
for (int i = 0; i < valList.getLength(); i++) {
Element eltValue = (Element)valList.item(i);
double cellValue = Double.parseDouble(eltValue.getFirstChild().getNodeValue());

//Each row contains diff series data. Note that first row contain category data.
dataSheet.getCells().getCell(rowIndex, colIndex).setValue(cellValue);
rowIndex++;
}

//Find Percent on Total
String sumFormula = "SUM(" + CellsHelper.convertCellIndexToName(1, colCount + 1) + ":" + CellsHelper.convertCellIndexToName(rowCount, colCount + 1) + ")";
for (int i = 0; i < rowCount; i++) {
String firstDataCell = CellsHelper.convertCellIndexToName(i + 1, 1);
String lastDataCell = CellsHelper.convertCellIndexToName(i + 1, colCount);
String rowSumFormula = "=SUM(" + firstDataCell + ":" + lastDataCell + ")";
dataSheet.getCells().getCell(i + 1, colCount + 1).setFormula(rowSumFormula);

String dataCell = CellsHelper.convertCellIndexToName(i + 1, colCount + 1);

//String percentCellFormula = "=100*(" + dataCell + "/" + sumFormula + ")";
String percentCellFormula = "=(" + dataCell + "/" + sumFormula + ")";
dataSheet.getCells().getCell(i + 1, colCount + 2).setFormula(percentCellFormula);

String cumulativePercentCellFormula = "=SUM(" + CellsHelper.convertCellIndexToName(1, colCount + 2) + ":" + CellsHelper.convertCellIndexToName(i + 1, colCount + 2) + ")";
dataSheet.getCells().getCell(i + 1, colCount + 3).setFormula(cumulativePercentCellFormula);
}

String barDataRef = dataSheet.getName() + "!" + CellsHelper.convertCellIndexToName(1, colCount + 1) + ":" + CellsHelper.convertCellIndexToName(rowCount, colCount + 1);
String lineDataRef = dataSheet.getName() + "!" + CellsHelper.convertCellIndexToName(1, colCount + 3) + ":" + CellsHelper.convertCellIndexToName(rowCount, colCount + 3);
String dataRef = barDataRef + "," + lineDataRef;

Chart chart = chartSheet.getCharts().addChart(ChartType.COLUMN_CLUSTERED, 0, 0, 1, 1, 600, 400);
chart.getNSeries().add(dataRef, true);

PatternFill pf = new PatternFill();
pf.setBackgroundColor(new Color(0xA5, 0x2A, 0x2A));
pf.setForegroundColor(new Color(0xA5, 0x2A, 0x2A));
chart.getNSeries().get(0).getArea().setFill(pf);
chart.getNSeries().get(0).getBorder().setVisible(false);

chart.getNSeries().get(1).setType(ChartType.LINE);
chart.getNSeries().get(1).setPlotOnSecondAxis(true);

chart.getSecondValueAxis().setMinValue(0);

chart.getSecondValueAxis().setMaxValue(1);
chart.getSecondValueAxis().setNumberFormat("0%");

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

int chartRows = 25, chartCols = 15;

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"));

//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);

System.out.println("Pareto successfully completed!!!");

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

}

2

Hi,

Thanks for sharing sample code with files.

We found the issue after an initial test, we will figure it out soon.

Your issue has been logged into our issue tracking system with an issue id: CELLSJAVA-15876.

We will inform you when the issue is sorted out.

Thank you.

3

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

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

working fine
Thanks
Muhammed

5

Hi,

Wrong image is being generated in latest release (2.3.1.0).
Please find sample screen shot attached.

It was working fine with 2.2.0.0.
Some other charts also having similar issue.

Thanks
Muhammed

6

Hi,

I can find the issue as you have mentioned with the new version v2.3.1.x.

I have re-opened your issue again and will provide you the fixed version soon.

Thank you.

7

Hi,

After further investigation, we found it is not an issue, it is because in the old versions (before v2.2.1.2), we call
calculateFormula() automatically in the Chart.toImage() method for the users. But for
performance consideration, we remove this call from the Chart.toImage() and let the
users call it manually when needed. We did notify you in one of our posts too.

So, please add a line of code:
wb.calculateFormula();
chart.toImage(new FileOutputStream("…"), imgOpts);


Thank you.


8

Hi,

And, for following threads about Chart2Image feature, please call Workbook.calculateFormula() before Chart.toImage() method. In the old versions, we call calculateFormula() automatically in the Chart.toImage() for the users. But we see, it would become a big burden for performance to calculate all formulas repeatedly for every chart when there are lots of charts and formulas to be evaluated. So, in our later fix and versions, we will remove the calculateFormula() operation from the Chart.toImage() method and it is needed that the user should call it manully if there are formulas set by API and have not been calculated.
-----------------------------------------------------------------------------------------------------

Moreover, I think for "some other charts" that you pointed out might be those cases mentioned in the above four threads. So, please call Workbook.calculateFormula() before Chart.toImage() method if there are formulas set by the application.
Thanks for your understanding!