Free Support Forum - aspose.com

Chart.toImage - Issues in BubbleChart with Border

Hi,

Please find the sample below and see the issues mentioned

1. Series border is not reflected in the image
2. Legend Marker Shape is chart is circle whereas in image, its square
3. X and Y axis min and max in chart and image are not consistent (Why X and Y starts from -ve values even though no -ve values are present for X and Y)

Thanks & Regards
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.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 BubbleChartMaker {

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"
};

//Array of cell data
double[] cellsValue = new double[] { 1.689, 62925500, 106295400,
1.808, 1851840, 3347520,
0, 0, 0,
1.027, 699800.8, 718515.3,
1.896, 56409260, 106955700,
1.95, 2, 3.9,
1.225, 665954.2, 816087.1,
1.358, 305, 414.2,
1.07, 49353140, 52803340
};

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.BUBBLE, 0, 0, 1, 1, 500, 400);

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

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

String bRef = "=" + dataSheet.getName() + "!$" + CellsHelper.convertColumnIndexToName(i) + "$" + (rowIndex + 4);
series.setBubbleSizes(bRef);

series.getBorder().setColor(Color.RED);

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

//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!!!" + CellsHelper.getReleaseVersion());

}
catch (Exception e) {
e.printStackTrace();
}
}

}

Hi,

Thanks for sharing sample code.

We found the issue after an initial test, we will figure it out soon.

Your issue has been registered into our issue tracking system with an issue id: CELLSJAVA-15738.

We will let you know when it is sorted out.

Thank you.

Hi,

Please try the attached version.

We have fixed the issue of series border and legend marker shape. For the
issue of X&Y axis min&max values, we are still working on it. For your question: Why X and Y starts from -ve values even though
no -ve values are present for X and Y, I am afraid it is the behavior of
MS EXCEL to render the Chart. When creating a Chart in MS EXCEL mannually with the
same data source, the axis will starts from a negative value too.


Thank you.

Hi,

Issues 1 & 2 are working fine.

Thanks.
Muhammed

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


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