SpreadJS 14
Features / Sparklines / Image Sparkline
In This Topic
    Image Sparkline
    In This Topic

    The image sparkline can be used to place an image in a cell. The image can be displayed in different sizes by using display modes of image sparkline function:

    image sparklines mode example

    The Image sparkline function has the following arguments where only 'URL' is the required argument:

    Option Description
    URL The location of the image on the web or base64 string.
    [mode] Specifies how to size the image.

    1 - Keep the aspect ratio to fit the cell.

    2 - Stretch the image to cover the entire cell.

    3 - Keep original size even if cropped.

    4 - custom

    The default value is 1.

    [height] The height of image.

    mode option must be 4.

    [width] The width of image.

    mode option must be 4.

    [clipX] The x-axis coordinate of the top left corner of the source image sub-rectangle to draw into the destination context.

    The default value is 0.

    [clipY] The y-axis coordinate of the top left corner of the source image sub-rectangle to draw into the destination context.

    The default value is 0.

    [clipHeight] The height of the source image sub-rectangle to draw into the destination context.

    The default value is the height of image.

    [clipWidth] The width of the source image sub-rectangle to draw into the destination context.

    The default value is the width of image.

    [vAlign] Vertical alignment of the image.

    0 - Top
    1 - Center
    2 - Bottom

    The default value is 1 (center).

    [hAlign] Horizontal alignment of the image.

    0 - Left
    1 - Center
    2 - Right

    The default value is 1 (center).

    The image sparkline formula has the following syntax:

    =IMAGE(url, [mode], [height], [width], [clipX], [clipY], [clipHeight], [clipWidth], [vAlign], [hAlign])

    Behavior with Different Values of Parameters

    The following behavior is observed with certain parameter values in Image sparklines:

    1. The height and width of the image must be specified when the mode is set to 4, otherwise, a blank cell is returned.
    2. If the clipWidth value is not specified while setting clipX, the clipWidth value is set as (Image Width - clipX). The same applies for clipHeight and clipY arguments.
    3. If clipX is greater than the image width, then the clipWidth is set to 0. Similarly, for clipY and image height.
    4. If the mode, vAlign, or hAlign is set to an illegal value, the runtime sets the mode to 1.
    5. The following parameters are replaced with 0, if they are set to a value smaller than 0:
      Width, Height, ClipX, ClipY, ClipHeight, ClipWidth

    Usage Scenario

    In the following use case, a list of 10 countries with the largest population in the world, is displayed. The list also displays the images for country flags which are picked through a web URL by using IMAGE sparkline function.

    image sparkline usage

    JavaScript
    Copy Code
    // initializing Spread
    spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 1 });
    spread.suspendPaint();
    // get sheet
    var sheet1 = spread.getSheet(0);
    // set sheetAreaOffset option
    sheet1.options.sheetAreaOffset = { left: 1, top: 1 };
    // Hide gridlines
    sheet1.options.gridline = { showVerticalGridline: false, showHorizontalGridline: false };
    // Hide row/ col headers
    sheet1.options.colHeaderVisible = false;
    sheet1.options.rowHeaderVisible = false;
    // set data array
    sheet1.setArray(1, 1, Top10_CountryPopulation);
    // set value in cells
    sheet1.setValue(1, 0, "Flag", GC.Spread.Sheets.SheetArea.dataArea);
    sheet1.setValue(0, 0, "Top 10 Populated Countries", GC.Spread.Sheets.SheetArea.dataArea);
    // set alignment of header row
    sheet1.getRange(-1, 0, -1, 10).hAlign(GC.Spread.Sheets.HorizontalAlign.center);
    sheet1.addSpan(0, 0, 1, 5, GC.Spread.Sheets.SheetArea.dataArea);
    // add table to sheet1
    sheet1.tables.add('tablePopulation', 1, 0, 11, 5);
    // set column width
    sheet1.setColumnWidth(0, 70);
    sheet1.setColumnWidth(1, 100);
    sheet1.setColumnWidth(2, 80);
    sheet1.setColumnWidth(3, 150);
    sheet1.setColumnWidth(4, 150);
    // set row height
    sheet1.setRowHeight(0, 40);
    for (var i = 2; i < 12; i++)
        sheet1.setRowHeight(i, 30);
    
    // set Image function formula in cells
    sheet1.setFormula(2, 0, '= IMAGE("https://upload.wikimedia.org/wikipedia/commons/thumb/f/fa/Flag_of_the_People%27s_Republic_of_China.svg/23px-Flag_of_the_People%27s_Republic_of_China.svg.png")');
    sheet1.setFormula(3, 0, '= IMAGE("https://upload.wikimedia.org/wikipedia/en/4/41/Flag_of_India.svg")');
    sheet1.setFormula(4, 0, '= IMAGE("https://previews.123rf.com/images/auttkhamkhauncham/auttkhamkhauncham1507/auttkhamkhauncham150700090/42304741-usa-flag.jpg")');
    sheet1.setFormula(5, 0, '= IMAGE("https://upload.wikimedia.org/wikipedia/commons/9/9f/Flag_of_Indonesia.svg")');
    sheet1.setFormula(6, 0, '= IMAGE("https://static.vecteezy.com/system/resources/previews/000/114/048/non_2x/free-vector-pakistan-flag.jpg")');
    sheet1.setFormula(7, 0, '= IMAGE("https://upload.wikimedia.org/wikipedia/en/0/05/Flag_of_Brazil.svg")');
    sheet1.setFormula(8, 0, '= IMAGE("https://i.pinimg.com/originals/73/22/94/732294310c7e9fa3da611030168923fb.jpg")');
    sheet1.setFormula(9, 0, '= IMAGE("https://images-na.ssl-images-amazon.com/images/I/31V23jzzMgL._AC_.jpg")');
    sheet1.setFormula(10, 0, '= IMAGE("https://upload.wikimedia.org/wikipedia/en/thumb/f/f3/Flag_of_Russia.svg/1200px-Flag_of_Russia.svg.png")');
    sheet1.setFormula(11, 0, '= IMAGE("https://upload.wikimedia.org/wikipedia/en/2/20/Flag_of_Mexico_1917.png")');         
    
    // set style for Row 0
    var style1 = new GC.Spread.Sheets.Style();
    style1.font = "bold 24px Calibri";
    style1.foreColor = "black";
    style1.backColor = "#9FD5B7";
    style1.hAlign = GC.Spread.Sheets.HorizontalAlign.center;
    style1.vAlign = GC.Spread.Sheets.VerticalAlign.center;
    sheet1.setStyle(0, 0, style1, GC.Spread.Sheets.SheetArea.dataArea);
    
    // set style for header row 1
    var style1 = new GC.Spread.Sheets.Style();
    style1.font = "bold 14px Calibri";
    style1.foreColor = "black";
    style1.backColor = "#9FD5B7";
    for (var i = 0; i < 5; i++)
        sheet1.setStyle(1, i, style1, GC.Spread.Sheets.SheetArea.dataArea);
    
    // set style for remaining rows
    var style2 = new GC.Spread.Sheets.Style();
    style2.backColor = "#EDFDF4";
    for (var i = 0; i < 5; i++)
        for (var j = 2; j < 12; j++)
            sheet1.setStyle(j, i, style2, GC.Spread.Sheets.SheetArea.dataArea);
    
    sheet1.getCell(2, 3).formatter("##,##,##0");
    
    // set border for dataArea cells
    sheet1.getRange("A2:E12").setBorder(
        new GC.Spread.Sheets.LineBorder("white", GC.Spread.Sheets.LineStyle.thin),
        { top: true, bottom: true, left: true, right: true }, GC.Spread.Sheets.SheetArea.dataArea);
    spread.resumePaint();
    

    Limitation