Hi,
Please run the sample below and see that vertical gridlines are not drwan for Scatter and Bubble charts.
Blue colored code is for bubble chart.
Yellow highlighted text to change code for scatter plot.
Red colored code is for Column chart, in which gridlines are working fine.
Thanks
Muhammed
import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.lang.reflect.Array;
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.MsoLineDashStyle;
import com.aspose.cells.PatternFill;
import com.aspose.cells.SheetType;
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 BubbleChartGridLines {
public static void main(String args[]) {
try {
String[] colTitle = new String[] { "BrandA Rinse Conditioners",
"BrandB Ltd Rinse Conditioners",
"BrandC Rinse Conditioners",
"BrandD Rinse Conditioners",
"BrandE Rinse Conditioners",
"BrandF Rinse Conditioners",
"BrandG Rinse Conditioners",
"BrandH Rinse Conditioners",
"BrandJ Rinse Conditioners"
};
//Array of cell data
double[][] data = new double[][] { {1.689, 1.808, 0, 1.027, 1.896, 1.95, 1.225, 1.358, 1.07},
{62925500, 1851840, 0, 699800.8, 56409260, 2, 665954.2, 305, 49353140},
{106295400, 3347520, 0, 718515.3, 106955700, 3.9, 816087.1, 414.2, 52803340}
};
Workbook wb = new Workbook();
Worksheet dataSheet = wb.getWorksheets().addSheet();
wb.getPalette().setColor(0, new Color(0xFF, 0x00, 0x00));
wb.getPalette().setColor(1, new Color(0x42, 0xDF, 0xF4));
wb.getPalette().setColor(2, new Color(0x00, 0xF4, 0x65));
wb.getPalette().setColor(3, new Color(0xF9, 0x04, 0x21));
String dataSheetName = "DataSheet";
dataSheet.setName(dataSheetName);
int rowIndex = 0;
for (int i = 0; i < colTitle.length; i++) {
dataSheet.getCells().getCell(rowIndex, i).setValue(colTitle[i]);
}
for (int i = 0; i < data.length; i++) {
for (int j = 0; j < data[i].length; j++) {
dataSheet.getCells().getCell(i + 1, j).setValue(data[i][j]);
}
}
//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.BUBBLE, 0, 0, 1, 1, 500, 400);
for (int i = 0; i < colTitle.length; i++) {
String dataRef = "=" + dataSheet.getName() + "!$" + CellsHelper.convertColumnIndexToName(i) + "$" + (rowIndex + 3);
int index = chart.getNSeries().add(dataRef, true);
ASeries series = chart.getNSeries().get(index);
//String nameRef = dataSheet.getName() + "!$" + CellsHelper.convertColumnIndexToName(lid) + "$" + (rowIndex + 1);
series.setName(dataSheet.getCells().getCell(rowIndex, i).getStringValue());
String xRef = "=" + dataSheet.getName() + "!$" + CellsHelper.convertColumnIndexToName(i) + "$" + (rowIndex + 2);
series.setXValues(xRef);
String bRef = "=" + dataSheet.getName() + "!$" + CellsHelper.convertColumnIndexToName(i) + "$" + (rowIndex + 4);
series.setBubbleSizes(bRef);
//System.out.println("DataRef = " + dataRef + ", xRef = " + xRef + ", bRef = " + bRef);
}
/*Chart chart = chartSheet.getCharts().addChart(ChartType.SCATTER, 0, 0, 1, 1, 500, 400);
for (int i = 0; i < colTitle.length; i++) {
String dataRef = "=" + dataSheet.getName() + "!$" + CellsHelper.convertColumnIndexToName(i) + "$" + (rowIndex + 3);
int index = chart.getNSeries().add(dataRef, true);
ASeries series = chart.getNSeries().get(index);
//String nameRef = dataSheet.getName() + "!$" + CellsHelper.convertColumnIndexToName(lid) + "$" + (rowIndex + 1);
series.setName(dataSheet.getCells().getCell(rowIndex, i).getStringValue());
String xRef = "=" + dataSheet.getName() + "!$" + CellsHelper.convertColumnIndexToName(i) + "$" + (rowIndex + 2);
series.setXValues(xRef);
series.setMarkerStyle(ChartMarkerType.CIRCLE);
//System.out.println("DataRef = " + dataRef + ", xRef = " + xRef + ", bRef = " + bRef);
}*/
/*Chart chart = chartSheet.getCharts().addChart(ChartType.COLUMN_CLUSTERED, 0, 0, 1, 1, 500, 400);
String dataRef = dataSheet.getName() + "!" + CellsHelper.convertCellIndexToName(0, 0) + ":" + CellsHelper.convertCellIndexToName(data.length, colTitle.length - 1);
System.out.println("dataRef = " + dataRef);
int seriesIndex = chart.getNSeries().add(dataRef, false);*/
short dashType = MsoLineDashStyle.SOLID;
chart.getValueAxis().getMajorGridLines().setVisible(true);
chart.getValueAxis().getMajorGridLines().setDashType(dashType);
chart.getCategoryAxis().getMajorGridLines().setVisible(true);
chart.getCategoryAxis().getMajorGridLines().setDashType(dashType);
//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("BubbleChartGridLines successfully completed!!!" + CellsHelper.getReleaseVersion());
}
catch (Exception e) {
e.printStackTrace();
}
}
}