- SpreadJS Overview
- Getting Started
- JavaScript Frameworks
- Best Practices
-
Features
- Workbook
- Worksheet
- Rows and Columns
- Headers
- Cells
- Data Binding
- TableSheet
- GanttSheet
- ReportSheet
- JSON Schema with SpreadJS
- SpreadJS File Format
- Data Validation
- Conditional Formatting
- Sort
- Group
- Formulas
- Serialization
- Keyboard Actions
- Shapes
- Form Controls
- Floating Objects
- Barcodes
- Charts
-
Sparklines
- Column, Line, and Winloss Sparklines with Methods
- Markers and Points
- Horizontal and Vertical Axes
- Column, Line, and Winloss Sparklines with Formulas
- Area Sparkline
- Pie Sparkline
- Scatter Sparkline
- Bullet Sparkline
- Spread Sparkline
- Stacked Sparkline
- Hbar Sparkline
- Vbar Sparkline
- Box Plot Sparkline
- Vari Sparkline
- Cascade Sparkline
- Pareto Sparkline
- Month Sparkline
- Year Sparkline
- Custom Sparkline
- Rangeblock Sparkline
-
Image Sparkline
- Histogram Sparkline
- Gauge KPI Sparkline
- Lollipop Variance Sparkline
- Tables
- Pivot Table
- Slicer
- Theme
- Culture
- SpreadJS Designer
- SpreadJS Designer Component
- Touch Support
- Formula Reference
- Import and Export Reference
- Frequently Used Events
- API Documentation
- Release Notes
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 the display modes of the image sparkline function.
The image sparkline function has the following arguments where only 'source' is the required argument:
Argument | Description |
|---|---|
source | The URL path, using an "https" protocol, of the image file. |
[alt_text] | Alternative text that describes the image for accessibility. Note that this argument supports the cell filter and find features of the cell. |
[sizing] | Specifies the image dimensions as follows: 0 - Keep the aspect ratio to fit the cell. 1 - Stretch the image to cover the entire cell. 2 - Maintain the original image size, even if it exceeds the cell boundary. 3 - Customize the image size by using the height and width arguments. The default value is 0. |
[height] | The custom height of the image in pixels. |
[width] | The custom width of the image in pixels. |
[clipX] | The x-axis coordinate of the top left corner of the sub-rectangle of the source image to draw into the destination context. The default value is 0. |
[clipY] | The y-axis coordinate of the top left corner of the sub-rectangle of the source image to draw into the destination context. The default value is 0. |
[clipHeight] | The height of the sub-rectangle of the source image to draw into the destination context. The default value is the height of the image. |
[clipWidth] | The width of the sub-rectangle of the source image 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 2 (Bottom). |
[hAlign] | Horizontal alignment of the image. 0 - Left 1 - Center 2 - Right The default value is 0 (Left). |
The image sparkline formula has the following syntax:
=IMAGE(source, [alt_text], [sizing], [height], [width], [clipX], [clipY], [clipHeight], [clipWidth], [vAlign], [hAlign])
Notes:
The source of the image can be a URL or a base64 string.
SpreadJS supports the import and export of image functions from Excel. However, import/export will be disabled if the image source is base64.
While exporting to Excel, some parameters, such as ([clipX], [clipY], [clipHeight], [clipWidth], [vAlign], and [hAlign], will be discarded.
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
When the URL is a cross-domain URL, SpreadJS will not be able to collect image information and will not display images when exporting to Excel. You can edit the cell to request image information in Excel.