Vertical Gridlines are not drawn for Buble and Scatter charts

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

}

Hi,

Thanks for your sample code.

We found the vertical gridlines issue regarding Bubble and Scatter chart. We will figure it out soon.

Your issue has been logged into issue tracking system with an issue id: CELLSJAVA-15197.

Thank you.

Hi,

After closely looking your code, we found your code is not fine.
For Bubble and Scatter charts, please replace the code:

chart.getCategoryAxis().getMajorGridLines().setVisible(true);
chart.getCategoryAxis().getMajorGridLines().setDashType(dashType);
to:
chart.getValueXAxis().getMajorGridLines().setVisible(true);
chart.getValueXAxis().getMajorGridLines().setDashType(dashType);


Thank you.

Working fine.
Thanks. Sorry for the mistake.

Hi,

Please try the attached version.

We have fixed the issue of generated image for Bubble chart.

Thank you.

The issues you have found earlier (filed as 15197) have been fixed in this update.


This message was posted using Notification2Forum from Downloads module by aspose.notifier.