Hi Team,
Could you please provide an example to render scatter chart using Aspose Cells JAVA in excel ?
Thanks,
Thilak Babu
See the sample code for simple example on how to create and render Scatter chart for your reference:
e.g.
Sample code:
Workbook workbook = new Workbook();
Cells cells = workbook.getWorksheets().get(0).getCells();
cells.get("A1").putValue(50);
cells.get("A2").putValue(100);
cells.get("A3").putValue(150);
cells.get("A4").putValue(110);
cells.get("B1").putValue(260);
cells.get("B2").putValue(12);
cells.get("B3").putValue(50);
cells.get("B4").putValue(100);
cells.get("C1").putValue(26);
cells.get("C2").putValue(1);
cells.get("C3").putValue(5);
cells.get("C4").putValue(10);
cells.get("D1").putValue(26);
cells.get("D2").putValue(1);
cells.get("D3").putValue(5);
cells.get("D4").putValue(10);
int chartIndex = workbook.getWorksheets().get(0).getCharts().add(ChartType.SCATTER, 5, 0, 15, 5);
Chart chart = workbook.getWorksheets().get(0).getCharts().get(chartIndex);
// Adding NSeries (chart data source) to the chart ranging from "A1" cell to "D4"
chart.getNSeries().add("A1:D4", true);
workbook.save("f:\\files\\out1.xlsx");
chart.toImage("f:\\files\\out12.png", ImageFormat.getPng());
chart.toPdf("f:\\files\\out13.pdf");
Hope, this helps a bit.
Thanks @Amjad_Sahi .
Could you please provide the sample to set all the series datapoints to bubble. Also can we specify the size of the bubble please ?
Thanks
See the following sample code for your reference:
e.g.
Sample code:
Workbook workbook = new Workbook();
Worksheet sheet = workbook.getWorksheets().get(0);
// Put data
sheet.getCells().get(0, 0).putValue("Y Values");
sheet.getCells().get(0, 1).putValue(2);
sheet.getCells().get(0, 2).putValue(4);
sheet.getCells().get(0, 3).putValue(6);
sheet.getCells().get(1, 0).putValue("Bubble Size");
sheet.getCells().get(1, 1).putValue(1);
sheet.getCells().get(1, 2).putValue(5);
sheet.getCells().get(1, 3).putValue(3);
sheet.getCells().get(2, 0).putValue("X Values");
sheet.getCells().get(2, 1).putValue(1);
sheet.getCells().get(2, 2).putValue(2);
sheet.getCells().get(2, 3).putValue(3);
// Generate chart
int chartIndex = sheet.getCharts().add(ChartType.BUBBLE, 5, 5, 22, 12);
Chart chart = sheet.getCharts().get(chartIndex);
// Insert series, set bubble size and x values
chart.getNSeries().add("B1:D1", false);
chart.getNSeries().get(0).setBubbleSizes("B2:D2");
chart.getNSeries().get(0).setXValues("B3:D3");
// Set a single color the series data points
chart.getNSeries().setColorVaried(true);
workbook.save("f:\\files\\outBubbleChart1.xlsx");
Hope, this helps a bit.
Hi @Amjad_Sahi.
Thanks for your reply. I need scatter chart only. In the scatter,
- I would like to get the series name with the header row in the data
- I would like to set the market symbol as bubble for all the series
- I would like to define a size for the same bubble marker.
Could you please help me with that ?
Thanks.
Please create your desired chart in MS Excel manually and save the file to provide us. We will check and help you on how to do it via Aspose.Cells.
PS. please zip the Excel file prior attaching.
Hi @Amjad_Sahi,
Thanks for the reply. Please find the attached sample scatter chart I am trying to achieve using aspose cells java.
Scatter_Example_Aspose.zip (11.9 KB)
Thanks
See the following sample code to create your desired chart for your reference. I have used your template file for chart’s source data range. I created the chart in a new worksheet in the output file. Please refer to to the code segment and you may create or update your own code accordingly for your custom needs:
e.g.
Sample code:
Workbook workbook = new Workbook("f:\\files\\Scatter_Example_Aspose.xlsx");
Worksheet worksheet = workbook.getWorksheets().get("DataSheet1");
Worksheet chartSheet = workbook.getWorksheets().get(workbook.getWorksheets().add());
chartSheet.setName("ChartSheet");
int idx = chartSheet.getCharts().add(ChartType.SCATTER, 1, 3, 25, 20);
Chart chart = chartSheet.getCharts().get(idx);
chart.getNSeries().setColorVaried(true);
//Set Properties of nseries
//first series
int s1_idx = chart.getNSeries().add("=(DataSheet1!$A$2,DataSheet1!$A$5,DataSheet1!$A$8,DataSheet1!$A$11,DataSheet1!$A$14,DataSheet1!$A$17)", true);
Series s1 = chart.getNSeries().get(s1_idx);
s1.setXValues("DataSheet1!$A$2,DataSheet1!$A$5,DataSheet1!$A$8,DataSheet1!$A$11,DataSheet1!$A$14,DataSheet1!$A$17");
s1.setValues("((DataSheet1!$C$2,DataSheet1!$C$5),DataSheet1!$C$8,DataSheet1!$C$11,DataSheet1!$C$14,DataSheet1!$C$17)");
s1.setName("Mango");
//s1.Area.Formatting = FormattingType.Custom;
//s1.Marker.Area.ForegroundColor = Color.Red;
//second series
int s2_idx = chart.getNSeries().add("=(DataSheet1!$A$2,DataSheet1!$A$5,DataSheet1!$A$8,DataSheet1!$A$11,DataSheet1!$A$14,DataSheet1!$A$17)", true);
Series s2 = chart.getNSeries().get(s2_idx);
s2.setXValues("=(DataSheet1!$A$2,DataSheet1!$A$5,DataSheet1!$A$8,DataSheet1!$A$11,DataSheet1!$A$14,DataSheet1!$A$17)");
s2.setValues("=((DataSheet1!$C$3,DataSheet1!$C$6),DataSheet1!$C$9,DataSheet1!$C$12,DataSheet1!$C$15,DataSheet1!$C$18)");
s2.setName("Banana");
//third series
int s3_idx = chart.getNSeries().add("=(DataSheet1!$A$2,DataSheet1!$A$5,DataSheet1!$A$8,DataSheet1!$A$11,DataSheet1!$A$14,DataSheet1!$A$17)", true);
Series s3 = chart.getNSeries().get(s3_idx);
s3.setXValues("=(DataSheet1!$A$2,DataSheet1!$A$5,DataSheet1!$A$8,DataSheet1!$A$11,DataSheet1!$A$14,DataSheet1!$A$17)");
s3.setValues("=((DataSheet1!$C$4,DataSheet1!$C$7),DataSheet1!$C$10,DataSheet1!$C$13,DataSheet1!$C$16,DataSheet1!$C$19)");
s3.setName("Apple");
for(int i = 0;i<chart.getNSeries().getCount(); i++)
{
//Specify series marker attributes
Marker marker = chart.getNSeries().get(i).getMarker();
marker.setMarkerStyle(ChartMarkerType.CIRCLE);//marker style
marker.setMarkerSize(12);//set size
}
//set gridlines
chart.getCategoryAxis().getMajorGridLines().setVisible(true);
chart.getValueAxis().getMajorGridLines().setVisible(true);
chart.getCategoryAxis().getMajorGridLines().setColor(com.aspose.cells.Color.getLightGray());
chart.getValueAxis().getMajorGridLines().setColor(com.aspose.cells.Color.getLightGray());
//set plot area color
chart.getPlotArea().getArea().getFillFormat().setFillType(FillType.SOLID);
chart.getPlotArea().getArea().setForegroundColor(com.aspose.cells.Color.getWhite());//fill color
chart.getPlotArea().getBorder().setColor(com.aspose.cells.Color.getEmpty());//line color
//set legend position
chart.setShowLegend(true);
chart.getLegend().setPosition(LegendPositionType.LEFT);
workbook.save("f:\\files\\out1.xlsx");
Hope, this helps a bit.