Documents for Excel, Java Edition Documentation
Features / Chart / Customize Chart Objects / Series
In This Topic
    Series
    In This Topic

    Series refers to a set of data points, or simply a list of values plotted in a chart.

    While working with spreadsheets, you can plot one or more data series in a chart. Each series is represented with a legend item and provides access to the chart control's collection of series objects.

    In GcExcel Java, the methods of the ISeries interface and the ISeriesCollection interface enables users to insert individual series, access it, delete it and perform other useful operations as per the requirements.

    Refer to the following example code to insert series in your chart.

    Java
    Copy Code
    // Adding Charts
    IShape shape = worksheet.getShapes().addChart(ChartType.ColumnClustered, 250, 20, 360, 230);
    worksheet.getRange("A1:D6").setValue(new Object[][]
    {
           {null, "S1", "S2", "S3"},
           {"Item1", 10, 25, 25},
           {"Item2", -51, -36, 27},
           {"Item3", 52, -85, -30},
           {"Item4", 22, 65, 65},
           {"Item5", 23, 69, 69}
    });
     
    // Detects three series, B2:B6, C2:C6, D2:D6.
    // Does not detect out series labels and category labels, auto generated.
    shape.getChart().getSeriesCollection().add(worksheet.getRange("B2:D6"));
      
    IShape shape2 = worksheet.getShapes().addChart(ChartType.ColumnClustered, 550, 50, 300, 300);
      
    // Detects three series, B2:B6, C2:C6, D2:D6.
    // Detects out series labels and category labels.
    // Series labels are "S1", "S2", "S3".
    // Category labels are "Item1", "Item2", "Item3", "Item4", "Item5".
    shape2.getChart().getSeriesCollection().add(worksheet.getRange("A1:D6"));
    
    IShape shape3 = worksheet.getShapes().addChart(ChartType.ColumnClustered, 200, 450, 300, 300);
    // Detects five series, B2:D2, B3:C3, B4:C4, B5:C5, B6:C6.
    // Does not detects out series labels and category labels, auto generated.
    shape3.getChart().getSeriesCollection().add(worksheet.getRange("B2:D6"), RowCol.Rows);
    
    IShape shape4 = worksheet.getShapes().addChart(ChartType.ColumnClustered, 550, 450, 300, 300);
    // Detects three series, B2:B6, C2:C6, D2:D6
    // Does not detects out series labels and category labels, auto generated.
    shape4.getChart().getSeriesCollection().add(worksheet.getRange("B2:D6"), RowCol.Columns);
    
    IShape shape5 = worksheet.getShapes().addChart(ChartType.ColumnClustered, 850, 450, 300, 300);
    // Detects three series, B2:B6, C2:C6, D2:D6
    // Detects out series labels and category labels.
    // Series labels are "S1", "S2", "S3".
    // Category labels are "Item1", "Item2", "Item3", "Item4", "Item5".
    shape5.getChart().getSeriesCollection().add(worksheet.getRange("A1:D6"), RowCol.Columns);
    
    IShape shape6 = worksheet.getShapes().addChart(ChartType.ColumnClustered, 200, 750, 300, 300);
    // Detects three series, B2:B6, C2:C6, D2:D6
    // Detects out series labels and category labels.
    // Series labels are "S1", "S2", "S3".
    // Category labels are "Item1", "Item2", "Item3", "Item4", "Item5".
    shape6.getChart().getSeriesCollection().add(worksheet.getRange("A1:D6"), RowCol.Columns, true, true);
    
    workbook.getWorksheets().add();
    IWorksheet worksheet1 = workbook.getWorksheets().get(1);
    worksheet1.getRange("A1:D6").setValue(new Object[][]
    {
       {null, "S1", "S2", "S3"},
       {"Item1", 10, 25, 25},
       {"Item2", -51, -36, 27},
       {"Item3", 52, -85, -30},
       {"Item4", 22, 65, 65},
       {"Item5", 23, 69, 69}
    });
      
    // Use ISeriesCollection.NewSeries() to add series
    IShape shape7 = worksheet1.getShapes().addChart(ChartType.ColumnClustered, 200, 50, 300, 300);
    ISeries series1 = shape7.getChart().getSeriesCollection().newSeries();
    ISeries series2 = shape7.getChart().getSeriesCollection().newSeries();
    ISeries series3 = shape7.getChart().getSeriesCollection().newSeries();
    series1.setFormula("=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$6,Sheet1!$B$2:$B$6,1)");
    series2.setFormula("=SERIES(Sheet1!$C$1,Sheet1!$A$2:$A$6,Sheet1!$C$2:$C$6,2)");
    series3.setFormula("=SERIES(Sheet1!$D$1,Sheet1!$A$2:$A$6,Sheet1!$D$2:$D$6,3)");
    
    // Use ISeriesCollection.Extend(IRange source, RowCol rowcol, bool categoryLabels) to add new data points to existing series
    IShape shape8 = worksheet1.getShapes().addChart(ChartType.ColumnClustered, 200, 450, 300, 300);
    shape8.getChart().getSeriesCollection().add(worksheet1.getRange("A1:D6"), RowCol.Columns, true, true);
    worksheet1.getRange("A12:D14").setValue(new Object[][]
    {
        {"Item6", 50, 20, -30},
        {"Item7", 60, 50, 50},
        {"Item8", 35, 80, 60}
    });
    shape8.getChart().getSeriesCollection().extend(worksheet1.getRange("A12:D14"), RowCol.Columns, true);
    
    workbook.getWorksheets().add();
    IWorksheet worksheet2 = workbook.getWorksheets().get(1);
    worksheet2.getRange("A1:D6").setValue(new Object[][]
    {
        {null, "S1", "S2", "S3"},
        {"Item1", 10, 25, 25},
        {"Item2", -51, -36, 27},
        {"Item3", 52, -85, -30},
        {"Item4", 22, 65, 65},
        {"Item5", 23, 69, 69}
    });
    
    // Create a line chart, change one series's AxisGroup, change another one series's chart type.
    IShape shape9 = worksheet2.getShapes().addChart(ChartType.Line, 200, 50, 300, 300);
    shape9.getChart().getSeriesCollection().add(worksheet2.getRange("A1:D6"), RowCol.Columns, true, true);
    ISeries series4 = shape9.getChart().getSeriesCollection().get(0);
    ISeries series5 = shape9.getChart().getSeriesCollection().get(1);
    series4.setAxisGroup(AxisGroup.Secondary);
    series5.setChartType(ChartType.ColumnClustered);
    
    // Set 3D column chart's bar shape.
    IShape shape10 = worksheet2.getShapes().addChart(ChartType.Column3D, 200, 450, 300, 300);
    shape10.getChart().getSeriesCollection().add(worksheet2.getRange("A1:D6"), RowCol.Columns, true, true);
    ISeries series6 = shape10.getChart().getSeriesCollection().get(0);
    ISeries series7 = shape10.getChart().getSeriesCollection().get(1);
    ISeries series8 = shape10.getChart().getSeriesCollection().get(2);;
    series6.setBarShape(BarShape.ConeToMax);
    series7.setBarShape(BarShape.Cylinder);
    series8.setBarShape(BarShape.PyramidToPoint);
    
    // Set negative point's fill color.
    IShape shape11 = worksheet2.getShapes().addChart(ChartType.Column3D, 200, 800, 300, 300);
    shape11.getChart().getSeriesCollection().add(worksheet2.getRange("A1:D6"), RowCol.Columns, true, true);
    ISeries series9 = shape11.getChart().getSeriesCollection().get(0);
    series9.setInvertIfNegative(true);
    series9.getInvertColor().setRGB(Color.GetGreen());
    
    // Set series' plot order as 6
    IShape shape12 = worksheet2.getShapes().addChart(ChartType.ColumnClustered, 200, 1100, 300, 300);
    worksheet.getRange("A1:E6").setValue(new Object[][]
    {
       {null, "S1", "S2", "S3", "S4"},
       {"Item1", 10, 25, 25, 30},
       {"Item2", -51, -36, 27, 35},
       {"Item3", 52, -85, -30, 40},
       {"Item4", 22, 65, 65, 45},
       {"Item5", 23, 69, 69, 50}
    });
    shape12.getChart().getSeriesCollection().add(worksheet2.getRange("A1:E6"), RowCol.Columns, true, true);
    
    ISeries series10 = shape12.getChart().getSeriesCollection().get(0);
    ISeries series11 = shape12.getChart().getSeriesCollection().get(1);;
    ISeries series12 = shape12.getChart().getSeriesCollection().get(2);;
    ISeries series13 = shape12.getChart().getSeriesCollection().get(3);;
    
    // series11 and series13 plot on secondary axis.
    series11.setAxisGroup(AxisGroup.Secondary);
    series13.setAxisGroup(AxisGroup.Secondary);
    
    // series10 and series12 are in one chart group.
    series12.setPlotOrder(1);
    series10.setPlotOrder(2);
    
    // series4 and series2 are in one chart group.
    series13.setPlotOrder(1);
    series11.setPlotOrder(2);
    
    // Configure series' marker.
    IShape shape13 = worksheet2.getShapes().addChart(ChartType.Line, 200, 1450, 300, 300);
    shape13.getChart().getSeriesCollection().add(worksheet2.getRange("A1:D6"), RowCol.Columns, true, true);
    
    ISeries series14 = shape13.getChart().getSeriesCollection().get(0);
    
    series14.setMarkerStyle(MarkerStyle.Diamond);
    series14.setMarkerSize(10);
    series1.getMarkerFormat().getFill().getColor().setRGB(Color.GetRed());
    series1.getMarkerFormat().getLine().setStyle(LineStyle.ThickThin);
    series1.getMarkerFormat().getLine().getColor().setRGB(Color.GetGreen());
    series1.getMarkerFormat().getLine().setWeight(3);