Scatter Chart - enable vertical Major GridLines - and issues with chart.toImage

Aspose.Cells Product Family
1

Hi,
Plaese find the sample below to generate SCATTER chart using aspose, and to generate image out of it. I have the following queries &/ issues.

1. How can I enable vertical Major Grid Lines ? I tried with

cart.getCategoryAxis().getMajorGridLines().setVisible(true); , which seems to be not working.
2. chart.toImage shows diff marker type than actual chart.
3. chart.toImage generated image consume more space for legend where as actual chart take more space for plot area.

Thanks
Muhammed

package demo.aspose.slides;

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.ChartMarkerType;

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.SheetType;

import com.aspose.cells.TickLabelPositionType;

import com.aspose.cells.TickMarkerType;

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 ScatterPlotMaker {

public static void main(String args[]) {

try {

String[] cellsName = new String[] { "A2", "A3",

"B2", "B3",

"C2", "C3",

"D2", "D3",

"E2", "E3",

"F2", "F3",

"G2", "G3",

"H2", "H3",

"I2", "I3",

};

//Array of cell data

double[] cellsValue = new double[] { 1.001, 73,

1.626, 1273211,

1.257, 930732.6,

1.666, 2284024,

2.352, 262502,

2.636, 4973.4,

1.835, 2554.3,

1.399, 33498.3,

1.874, 67.9

};

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("BrandA Rinse Conditioners");

dataSheet.getCells().getCell("B1").setValue("BrandB Rinse Conditioners");

dataSheet.getCells().getCell("C1").setValue("BrandC Rinse Conditioners");

dataSheet.getCells().getCell("D1").setValue("BrandD Rinse Conditioners");

dataSheet.getCells().getCell("E1").setValue("BrandE Rinse Conditioners");

dataSheet.getCells().getCell("F1").setValue("BrandF Rinse Conditioners");

dataSheet.getCells().getCell("G1").setValue("BrandG Rinse Conditioners");

dataSheet.getCells().getCell("H1").setValue("BrandH Rinse Conditioners");

dataSheet.getCells().getCell("I1").setValue("BrandI Rinse Conditioners");

int size = Array.getLength(cellsName);

for (int i = 0; i < size; i++) {

String cellName = cellsName[i];

double 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.SCATTER, 0, 0, 1, 1, 300, 200);

int rowIndex = 0;

for (int i = 0; i < 9; 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());

series.setMarkerStyle(ChartMarkerType.CIRCLE);

String xRef = "=" + dataSheet.getName() + "!$" + CellsHelper.convertColumnIndexToName(i) + "$" + (rowIndex + 2);

series.setXValues(xRef);

System.out.println("DataRef = " + dataRef + ", xRef = " + xRef);

}

chart.getValueAxis().getMajorGridLines().setVisible(true);

chart.getValueAxis().getMinorGridLines().setVisible(false);

chart.getValueAxis().setTickLabelPosition(TickLabelPositionType.NEXT_TO_AXIS);

chart.getValueAxis().setMajorTickMark(TickMarkerType.OUTSIDE);

/*chart.getValueXAxis().getMajorGridLines().setVisible(true);

chart.getValueXAxis().getMinorGridLines().setVisible(false);*/

chart.getCategoryAxis().getMajorGridLines().setVisible(true);

chart.getCategoryAxis().getMinorGridLines().setVisible(false);

chart.getCategoryAxis().setTickLabelPosition(TickLabelPositionType.NEXT_TO_AXIS);

chart.getCategoryAxis().setMajorTickMark(TickMarkerType.OUTSIDE);

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

}

}

}

2

Hi,

1) Please use getValueXAxis() instead of getCategoryAxis() method when you are creating Scatter or Bubble chart, you may change your code to:
e.g

chart.getValueXAxis().getMajorGridLines().setVisible(true);

chart.getValueXAxis().getMinorGridLines().setVisible(false);


2) and 3)

I found the issues using your code as you have mentioned, we will figure the issues out soon.
I have logged it into our issue tracking system with an issue id: CELLSJAVA-14486.
We will inform you when it is sorted out.

Thank you.


3

I already tried with these as well, which also not working.

chart.getValueXAxis().getMajorGridLines().setVisible(true);

chart.getValueXAxis().getMinorGridLines().setVisible(false);

4

Hi,

Well, it works fine, I am using v2.1.2, which version you are using.

I have attached my output file using your code with the replaced lines.

Thank you.

5

Yes.. vertical grid lines working.
In the example, unit & maximum value on x axis are same, so that I couldn't see the vertical grid lines.

Thanks.
Muhammed

6

Hi Muhammad,

Please try the attached version. We have fixed the issue of marker type. For the issue of Legend space, we are
still working on it and will provide new fix when finish.

Thank you.

7

Hi,

Marker is working fine now.
Can I know the estimated time for the issue - "Image consume more space for legend where as actual chart take more space for plot area." ?

-Muhammed

8

Hi,

We are working on the issue and hopefully we can provide you a fix in the next week.

Thanks for your understanding!

9

Hi,

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Please try the attached latest version. We have fixed the issue of extra space consumption by legends.

Thank You & Best Regards,

10

Thanks.
It seems to be working fine.

11

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

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