[]
DEMOS DOC DESIGNER API
DOWNLOAD
(Showing Draft Content)

Image Sparkline

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 the image sparkline function:




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]

Height of the image.

mode option must be 4.

[width]

Width of the 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 the image.

[clipWidth]

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

The default value is the width of the 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 to 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

Use Case 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 the IMAGE sparkline function.



// 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

  • If the image URL is a cross-domain URL, the cell is not printed on exporting a PDF file. It is only printed when the URL is a locate-domain URL or a base64 string.