Scatter chart example using Aspose Cells java

Hi Team,

Could you please provide an example to render scatter chart using Aspose Cells JAVA in excel ?

Thanks,
Thilak Babu

@Thilakbabu,

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

@Thilakbabu,

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,

  1. I would like to get the series name with the header row in the data
  2. I would like to set the market symbol as bubble for all the series
  3. I would like to define a size for the same bubble marker.

Could you please help me with that ?

Thanks.

@Thilakbabu,

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

@Thilakbabu,

We will evaluate your chart in the template file and devise some code accordingly.

@Thilakbabu,

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.