Hi,
Please run the below sample and see that Y and Y1 axis titles are missing from image.
Also see differences of actual chart and image. When can we expect a 1-1 mapping?
-Muhammed
import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.lang.reflect.Array;
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 DualAxisChartMaker {
public static void main(String args[]) {
try {
String[] cellsName = new String[] { "A2", "A3", "A4", "A5",
"B2", "B3", "B4", "B5",
"C2", "C3", "C4", "C5",
"D2", "D3", "D4", "D5",
"E2", "E3", "E4", "E5",
"F2", "F3", "F4", "F5",
"G2", "G3", "G4", "G5",
"H2", "H3", "H4", "H5",
"I2", "I3", "I4", "I5",
"J2", "J3", "J4", "J5",
"K2", "K3", "K4", "K5"
};
//Array of cell data
int[] cellsValue = new int[] { 60, 80, 60, 80,
67, 86, 68, 60,
44, 64, 89, 40,
46, 97, 78, 66,
43, 29, 69, 80,
24, 40, 38, 80,
40, 90, 70, 50,
42, 92, 72, 90,
44, 64, 89, 78,
46, 97, 78, 80,
43, 29, 69, 60
};
Workbook wb = new Workbook();
Worksheet dataSheet = wb.getWorksheets().addSheet();
wb.getPalette().setColor(55, Color.RED);
wb.getPalette().setColor(54, Color.GREEN);
wb.getPalette().setColor(53, Color.YELLOW);
String dataSheetName = "DataSheet";
dataSheet.setName(dataSheetName);
//Populate DataSheet with data
dataSheet.getCells().getCell("A1").setValue("United States");
dataSheet.getCells().getCell("B1").setValue("United Kingdom");
dataSheet.getCells().getCell("C1").setValue("Italy");
dataSheet.getCells().getCell("D1").setValue("Japan");
dataSheet.getCells().getCell("E1").setValue("India");
dataSheet.getCells().getCell("F1").setValue("Canada");
dataSheet.getCells().getCell("G1").setValue("Brazil");
dataSheet.getCells().getCell("H1").setValue("Mexico");
dataSheet.getCells().getCell("I1").setValue("China");
dataSheet.getCells().getCell("J1").setValue("Germany");
dataSheet.getCells().getCell("K1").setValue("Australia");
int size = Array.getLength(cellsName);
for (int i = 0; i < size; i++) {
String cellName = cellsName[i];
int cellValue = cellsValue[i];
dataSheet.getCells().getCell(cellName).setValue(cellValue);
}
//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);
String emptyVals = "{0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0}";
for (int i = 2; i <= 5; i++) {
chart.getNSeries().add(dataSheetName + "!A" + i + ":K" + i, false);
}
for (int i = 2; i <= 5; i++) {
chart.getNSeries().add(dataSheetName + "!A" + i + ":K" + i, false);
}
chart.getNSeries().get(1).setValues(emptyVals);
chart.getNSeries().get(3).setValues(emptyVals);
chart.getNSeries().get(4).setValues(emptyVals);
chart.getNSeries().get(6).setValues(emptyVals);
chart.getLegend().getLegendEntries().getLegendEntry(1).setDeleted(true);
chart.getLegend().getLegendEntries().getLegendEntry(3).setDeleted(true);
chart.getLegend().getLegendEntries().getLegendEntry(4).setDeleted(true);
chart.getLegend().getLegendEntries().getLegendEntry(6).setDeleted(true);
chart.getNSeries().get(0).setGapWidth(5);
for (int i = 0; i < 8; i++) {
PatternFill pf = new PatternFill();
pf.setBackgroundColor(wb.getPalette().getColor(i * 2));
pf.setForegroundColor(wb.getPalette().getColor(i * 2));
chart.getNSeries().get(0).getArea().setFill(pf);
}
int serCount = chart.getNSeries().size();
for (int i = 4; i < 8; i++) {
chart.getNSeries().get(i).setPlotOnSecondAxis(true);
}
chart.getSecondValueAxis().setVisible(true);
chart.getSecondValueAxis().setCrossAtMax(false);
chart.getCategoryAxis().getTitle().setText("X Axis Title");
chart.getValueAxis().getTitle().setText("Y Axis Title");
chart.getSecondValueAxis().getTitle().setText("Y1 Axis Title");
String categoryAreaRef = dataSheetName + "!A1:K1";
chart.getNSeries().setCategoryData(categoryAreaRef);
chart.getNSeries().setSecondCategoryData(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"));
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("DualAxisChartMaker successfully completed!!!");
}
catch (Exception e) {
e.printStackTrace();
}
}
}