Documents for Excel, Java Edition Documentation
Features / Sparkline
In This Topic
    Sparkline
    In This Topic

    GcExcel Java enables users to visualize data variations over time with Sparklines. Basically, a sparkline refers to a small, lightweight chart that can be drawn in a cell to rapidly visualize information for enhanced analytics.

    Sparklines are useful especially when business analysts and managers need to work with analytical dashboards, presentations, business reports etc. A sparkline displays the most recent value as the rightmost data point and compares it with earlier values on a scale, enabling you to view general changes in data over time.

    Sparklines fetch data from a range of cells and put it in the form of a small graph that fits inside a cell. Users can mark data values with different colors to denote various values (like low, high, last, first and negative) based on their specific preferences.

    Sparkline

    Sparkline

    Refer to the following tasks in order to use sparklines.

    Add a group of new sparklines

    You can insert a group of new sparklines for each row or column of data in your worksheet by first specifying the data range and then using the add method of the ISparklineGroups interface and getSparklineGroups method of the IRange interface.

    In order to insert a group of new sparklines, refer to the following example code.

    Java
    Copy Code
    // Create workbook and access its first worksheet
    Workbook workbook = new Workbook();
    IWorksheet worksheet = workbook.getWorksheets().get(0);
            
    // Defining data in the range
    Object[][] data = new Object[][] 
    { 
          { 1, 2, 3 }, 
          { 4, 5, 6 }, 
          { 7, 8, 9 }, 
          { 10, 11, 12 } 
    };
            
    // Add a group of new sparklines
    worksheet.getRange("A1:C4").setValue(data);
    worksheet.getRange("D1:D4").getSparklineGroups().add(SparkType.Line, "A1:C4");

    Clear sparkline

    You can remove a sparkline from your worksheet via specifying the data range and then using the clear method of the ISparklineGroups interface. 

    In order to clear sparkline, refer to the following example code.

    Java
    Copy Code
    // Defining data in the range
    Object[][] data = new Object[][] 
    { 
        { 1, 2, 3 }, 
        { 4, 5, 6 }, 
        { 7, 8, 9 }, 
        { 10, 11, 12 } 
    };
    worksheet.getRange("A1:C4").setValue(data);
    worksheet.getRange("D1:D4").getSparklineGroups().add(SparkType.Line, "A1:C4");
    worksheet.getRange("F1:H4").setValue(data);
            
    // Add a group of new sparklines
    worksheet.getRange("J1:J4").getSparklineGroups().add(SparkType.Line, "F1:H4");
    
    // Clear D2 and J1 cell's sparkline.
    worksheet.getRange("D2,J1").getSparklineGroups().clear();

    Clear sparkline groups

    You can remove a group of sparklines (added for a row or column) from the spreadsheet via specifying the data range and then using the clearGroups method of the ISparklineGroups interface. 

    In order to clear sparkline groups, refer to the following example code.

    Java
    Copy Code
    // Defining data in the range
    Object[][] data = new Object[][] 
    { 
        { 1, 2, 3 }, 
        { 4, 5, 6 }, 
        { 7, 8, 9 }, 
        { 10, 11, 12 } 
    };
    worksheet.getRange("A1:C4").setValue(data);
            
    // Add a group of new sparklines
    worksheet.getRange("D1:D4").getSparklineGroups().add(SparkType.Line, "A1:C4");
    worksheet.getRange("F1:H4").setValue(data);
    worksheet.getRange("J1:J4").getSparklineGroups().add(SparkType.Line, "F1:H4");
    
    // Clear sparkline groups
    worksheet.getRange("D2,J1").getSparklineGroups().clearGroups();

    Create a group of existing sparklines

    You can create a group of existing sparklines in your worksheet via specifying the data range and then using the methods of the ISparklineGroups interface. 

    In order to create a group of existing sparklines, refer to the following example code.

    Java
    Copy Code
    // Defining data in the range
    Object[][] data = new Object[][] 
    { 
        { 1, 2, 3 }, 
        { 4, 5, 6 }, 
        { 7, 8, 9 }, 
        { 10, 11, 12 } 
    };
    worksheet.getRange("A1:C4").setValue(data);
    worksheet.getRange("D1:D4").getSparklineGroups().add(SparkType.Line, "A1:C4");
    worksheet.getRange("F1:H4").setValue(data);
            
    // Add a group of new sparklines
    worksheet.getRange("J1:J4").getSparklineGroups().add(SparkType.Column, "F1:H4");
    
    // Create a new group, according to Range["J2"]'s sparkline group setting.
    worksheet.getRange("A1:J4").getSparklineGroups().group(worksheet.getRange("J2"));

    Add group of new sparklines with Date Axis

    You can add a group of new sparklines with date axis by first specifying the data range and then using the methods of the ISparklineGroups interface.

    In order to add group of new sparkline with date axis, refer to the following example code.

    Java
    Copy Code
    // Defining data in the range
    Object[][] data = new Object[][] 
    { 
        { 1, 2, 3 }, 
        { 4, 5, 6 }, 
        { 7, 8, 9 }, 
        { 10, 11, 12 } 
    };
    worksheet.getRange("A1:C4").setValue(data);
            
    // Add a group of new sparklines
    worksheet.getRange("D1:D4").getSparklineGroups().add(SparkType.Line, "A1:C4");
    Object[] date_data = new Object[] 
    { 
          new GregorianCalendar(2011, 11, 16), 
          new GregorianCalendar(2011, 11, 17),
          new GregorianCalendar(2011, 11, 18) 
    };
    worksheet.getRange("A7:C7").setValue(date_data);
    
    // Set horizontal axis's Date range.
    worksheet.getRange("D1").getSparklineGroups().get(0).setDateRange("A7:C7");
    worksheet.getRange("D1").getSparklineGroups().get(0).getAxes().getHorizontal().getAxis().setVisible(true);
    worksheet.getRange("D1").getSparklineGroups().get(0).getAxes().getHorizontal().getAxis().getColor().setColor(Color.GetGreen());
    worksheet.getRange("D1").getSparklineGroups().get(0).getAxes().getVertical().setMinScaleType(SparkScale.SparkScaleCustom);
    worksheet.getRange("D1").getSparklineGroups().get(0).getAxes().getVertical().setMaxScaleType(SparkScale.SparkScaleCustom);
    worksheet.getRange("D1").getSparklineGroups().get(0).getAxes().getVertical().setCustomMinScaleValue(-2);
    worksheet.getRange("D1").getSparklineGroups().get(0).getAxes().getVertical().setCustomMaxScaleValue(8);

    Configure layout of sparkline

    You can configure the layout of the sparkline by using the methods of the ISparklineGroup interface.

    In order to configure the layout of the sparkline, refer to the following example code.

    Java
    Copy Code
    // Defining data in the range
    Object[][] data = new Object[][] 
    { 
        { 1, 2, 3 }, 
        { 4, 5, 6 }, 
        { 7, 8, 9 }, 
        { 10, 11, 12 } 
    };
    worksheet.getRange("A1:C4").setValue(data);
            
    // Adding sparkline
    worksheet.getRange("D1:D4").getSparklineGroups().add(SparkType.Line, "A1:C4");
            
    // Defining source data 
    Object[] date_data = new Object[] 
    { 
          new GregorianCalendar(2011, 11, 16), 
          new GregorianCalendar(2011, 11, 17),
          new GregorianCalendar(2011, 11, 18) 
    };
    worksheet.getRange("A7:C7").setValue(date_data);
            
    // Configuring the layout 
    ISparklineGroup sparklinegroup = worksheet.getRange("D1").getSparklineGroups().get(0);
    sparklinegroup.setLineWeight(2.5);
    sparklinegroup.getPoints().getMarkers().getColor().setColor(Color.GetRed());
    sparklinegroup.getPoints().getMarkers().setVisible(true);
    sparklinegroup.getSeriesColor().setColor(Color.GetPurple());