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