Free Support Forum - aspose.com

Pareto Chart - Chart.toImage generated image - series color differs from Actual chart

Hi,
Please run the sample below and see the issues mentioned.

1) Series colors differ from chart
2) Y1 axis tick label interval differs from chart(0 - 20 -... instead of 0 - 10-20...)
3) Y axis and chart area left border gap is less in image

Thanks
Muhammed

import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.lang.reflect.Array;

import com.aspose.cells.CellHandler;
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;

/**
*
* http://www.brighthub.com/office/project-management/articles/8708.aspx
*
*/
public class ParetolChartMaker2 {

public static void main(String args[]) {

try {
String[] colTitle = new String[] {"Problem", "Count", "Percent on Total", "Cumulative Percent"};
String[] rowTitle = new String[] {"Not compatible", "Doesnot perform as expected", "Found at a cheaper pricel", "Changed mind", "Too complicated", "Missing manual", "Missing disk"};

//Array of cell data
double[][] data = new double[][]{ {23, 0, 0},
{15, 0, 0},
{11, 0, 0},
{10, 0, 0},
{7, 0, 0},
{5, 0, 0},
{4, 0, 0}
};

Workbook wb = new Workbook();
Worksheet dataSheet = wb.getWorksheets().addSheet();
wb.getPalette().setColor(55, Color.RED);
wb.getPalette().setColor(54, Color.GREEN);

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

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

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

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

//Find Percent on Total
String sumFormula = "SUM(" + CellsHelper.convertCellIndexToName(1, 1) + ":" + CellsHelper.convertCellIndexToName(rowTitle.length, 1) + ")";
for (int i = 0; i < rowTitle.length; i++) {
String dataCell = CellsHelper.convertCellIndexToName(i + 1, 1);
//String percentCellFormula = "=100*(" + dataCell + "/" + sumFormula + ")";
String percentCellFormula = "=(" + dataCell + "/" + sumFormula + ")";
dataSheet.getCells().getCell(i + 1, 2).setFormula(percentCellFormula);

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

//System.out.println("cellFormula = " + percentCellFormula);
//System.out.println("cumulativePercentCellFormula = " + cumulativePercentCellFormula);
}

String chartDataRef = dataSheetName + "!A2:" + CellsHelper.convertCellIndexToName(rowTitle.length, colTitle.length - 1);
String categoryAreaRef = dataSheetName + "!A2:A" + rowTitle.length;

//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 = 25, chartCols = 15;

Chart chart = chartSheet.getCharts().addChart(ChartType.COLUMN_CLUSTERED, 0, 0, 1, 1, 500, 400);
chart.getNSeries().add(chartDataRef, true);
chart.getNSeries().setCategoryData(categoryAreaRef);

//Delete first series (Count) to make it pareto
chart.getNSeries().remove(0);

chart.getNSeries().get(1).setType(ChartType.LINE);
chart.getNSeries().get(1).setPlotOnSecondAxis(true);
chart.getNSeries().setSecondCategoryData(categoryAreaRef);
chart.getSecondValueAxis().setMinValue(0);
chart.getSecondValueAxis().setMaxValue(1);
chart.getSecondValueAxis().setNumberFormat("0%");
chart.getSecondValueAxis().getMajorGridLines().setVisible(false);
//chart.getSecondValueAxis().getMajorGridLines().setVisible(false);

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

Presentation pres = new Presentation();
Slide sld = pres.addEmptySlide();

int slideWidth = (int) pres.getSlideSize().getX() - 1500;

int slideHeight = (int) pres.getSlideSize().getY();
int x = 1500 / 2;
OleObjectFrame oof = sld.getShapes().addOleObjectFrame(x, 0, slideWidth, slideHeight, "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("ParetoChartMaker successfully completed!!!" + CellsHelper.getReleaseVersion());
}
catch (Exception e) {
e.printStackTrace();
}
}

}

Hi,

Thanks for your sample code.

After an initial test, we found the issues regarding Pareto chart you have mentioned.

We have logged it into our issue tracking system with an issue id: CELLSJAVA-15088. We will figure it out soon.

Thank you.

Hi,

Please try the attached version. We have fixed the issue of series color, it is a bug of creating chart.
Also, the issue of tick labels interval has been fixed. For the issue of left
border gap, we will fix it later.

Thank you.

The issues you have found earlier (filed as ) have been fixed in this update. This message was posted using BugNotificationTool from Downloads module by MuzammilKhan