Free Support Forum - aspose.com

Issue with Dual Axis Column & Bar Chart - wrong secondary axis created

Hi,
Can you please run the following sample and check whether the way you implement setPlotOnSecondAxis is correct or not?

Plaese find the screen shots for expected chart and aspose.cells created one.

If your implementation is correct, can you please send me the code snippet for creating DualAxis chart as like in image 2?

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",
"B2", "B3", "B4",
"C2", "C3", "C4",
"D2", "D3", "D4",
"E2", "E3", "E4",
"F2", "F3", "F4",
"G2", "G3", "G4",
"H2", "H3", "H4",
"I2", "I3", "I4",
"J2", "J3", "J4",
"K2", "K3", "K4"
};

//Array of cell data
int[] cellsValue = new int[] { 60, 80, 60,
67, 86, 68,
44, 64, 89,
46, 97, 78,
43, 29, 69,
24, 40, 38,
40, 90, 70,
42, 92, 72,
44, 64, 89,
46, 97, 78,
43, 29, 69
};

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

chart.getNSeries().add(dataSheetName + "!A2:H4", false);

chart.getNSeries().get(0).setGapWidth(5);

PatternFill pf = new PatternFill();
pf.setBackgroundColor(wb.getPalette().getColor(53));
pf.setForegroundColor(wb.getPalette().getColor(53));
chart.getNSeries().get(0).getArea().setFill(pf);

chart.getNSeries().get(2).setPlotOnSecondAxis(true);

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

-Muhammed

Hi,

Thanks for providing us sample code.

We will look into it and get back to you soon.

Thank you.

Hi,

After look into your issue, we found it is not a bug of creating the secondary axis. Actually, without UI control, it is hard for us to get the behavior of our APIs exactly the same as MS EXCEL. So, please add a line as following in your code to get your desired result:
...
chart.getNSeries().get(2).setPlotOnSecondAxis(true);
chart.getSecondValueAxis().setCrossAtMax(false);
...
Thank you.

working fine.
Thanks.

Muhammed