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:
The image sparkline formula has the following syntax:
=IMAGE(url, [mode, height, width, clipX, clipY, clipHeight, clipWidth, vAlign, hAlign])
The formula options are described below:
| Option | Description |
|---|---|
| URL | The location of the image on the web or base64 string. |
|
mode Optional |
Specifies how to size the image.
The default value is 1. |
|
height Optional |
The height of image. mode option must be 4. |
|
width Optional |
The width of image. mode option must be 4. |
|
clipX Optional |
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 Optional |
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 Optional |
The height of the source image sub-rectangle to draw into the destination context. The default value is the height of image. |
|
clipWidth Optional |
The width of the source image sub-rectangle to draw into the destination context. The default value is the width of image. |
|
vAlign Optional |
Vertical alignment of the image.
0 - Top The default value is 1 (center). |
|
hAlign Optional |
Horizontal alignment of the image.
0 - Left The default value is 1 (center). |
Behavior with Different Values of Parameters
The following behavior is observed with certain parameter values in Image sparklines:
- The height and width of the image must be specified when the mode is set to 4, otherwise, a blank cell is returned.
- 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.
- If clipX is greater than the image width, then the clipWidth is set to 0. Similarly, for clipY and image height.
- If the mode, vAlign, or hAlign is set to an illegal value, the runtime sets the mode to 1.
- 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
Consider a scenario where a data accounting organization wants to present a list of 10 countries with the largest population in the world. The list can also display the images for country flags picked through a web URL using the image sparkline.
| C# |
Copy Code
|
|---|---|
// Set value in cells worksheet.Cells[1, 0].Value = "Flag"; worksheet.Cells[0, 0].Value = "Top 10 Populated Countries"; fpSpread1_Sheet1.AddSpanCell(0, 0, 1, 5); // Set data worksheet.SetValue(1, 1, new object[,] { {"Country", "Rank", "Population (2020)", "Land Area (km sq)"}, {"China", 1, 1439323776, 9388211 }, {"India", 2, 1380004385, 2973190}, {"United States", 3, 331002651, 9147420}, {"Indonesia", 4, 273523615, 1811570}, {"Pakistan", 5, 220892340, 770880}, {"Brazil", 6, 212559417, 8358140}, {"Nigeria", 7, 206139589, 910770}, {"Bangladesh", 8, 164689383, 130170}, {"Russia", 9, 145934462, 16376870}, {"Mexico", 10, 128932753, 1943950} }); // Set Image function formula in cells worksheet.Cells[2, 0].Formula = "IMAGE(\"https://www.wallpaperflare.com/static/193/1001/133/five-starred-red-flag-china-flag-five-wallpaper.jpg\")"; worksheet.Cells[3, 0].Formula = "IMAGE(\"https://www.pngfind.com/pngs/m/21-211631_the-indian-flag-png-indian-flag-icon-transparent.png\")"; worksheet.Cells[4, 0].Formula = "IMAGE(\"https://previews.123rf.com/images/auttkhamkhauncham/auttkhamkhauncham1507/auttkhamkhauncham150700090/42304741-usa-flag.jpg\")"; worksheet.Cells[5, 0].Formula = "IMAGE(\"https://i.pinimg.com/236x/e8/0a/d5/e80ad5d4ea09700a78719ca051d19cbd.jpg\")"; worksheet.Cells[6, 0].Formula = "IMAGE(\"https://static.vecteezy.com/system/resources/previews/000/114/048/non_2x/free-vector-pakistan-flag.jpg\")"; worksheet.Cells[7, 0].Formula = "IMAGE(\"https://upload.wikimedia.org/wikipedia/en/thumb/0/05/Flag_of_Brazil.svg/2560px-Flag_of_Brazil.svg.png\")"; worksheet.Cells[8, 0].Formula = "IMAGE(\"https://i.pinimg.com/originals/73/22/94/732294310c7e9fa3da611030168923fb.jpg\")"; worksheet.Cells[9, 0].Formula = "IMAGE(\"https://images-na.ssl-images-amazon.com/images/I/31V23jzzMgL._AC_.jpg\")"; worksheet.Cells[10, 0].Formula = "IMAGE(\"https://upload.wikimedia.org/wikipedia/en/thumb/f/f3/Flag_of_Russia.svg/1200px-Flag_of_Russia.svg.png\")"; worksheet.Cells[11, 0].Formula = "IMAGE(\"https://www.pngkey.com/png/detail/442-4423822_mexico-icon-mexican-flag-icon-png.png\")"; // Set backcolor for cells worksheet.Cells["A1"].Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(unchecked((int)0xFF9FD5B7)); worksheet.Cells["A2:E2"].Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(unchecked((int)0xFFd9eee2)); worksheet.Cells["A3:E12"].Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(unchecked((int)0xFFf5fbf8)); |
|
| Visual Basic |
Copy Code
|
|---|---|
'Set value in cells worksheet.Cells(1, 0).Value = "Flag" worksheet.Cells(0, 0).Value = "Top 10 Populated Countries" FpSpread1_Sheet1.AddSpanCell(0, 0, 1, 5) 'Set data worksheet.SetValue(1, 1, New Object(,) { {"Country", "Rank", "Population (2020)", "Land Area (km sq)"}, {"China", 1, 1439323776, 9388211}, {"India", 2, 1380004385, 2973190}, {"United States", 3, 331002651, 9147420}, {"Indonesia", 4, 273523615, 1811570}, {"Pakistan", 5, 220892340, 770880}, {"Brazil", 6, 212559417, 8358140}, {"Nigeria", 7, 206139589, 910770}, {"Bangladesh", 8, 164689383, 130170}, {"Russia", 9, 145934462, 16376870}, {"Mexico", 10, 128932753, 1943950} }) 'Set Image function formula in cells worksheet.Cells(2, 0).Formula = "IMAGE(""https://www.wallpaperflare.com/static/193/1001/133/five-starred-red-flag-china-flag-five-wallpaper.jpg"")" worksheet.Cells(3, 0).Formula = "IMAGE(""https://www.pngfind.com/pngs/m/21-211631_the-indian-flag-png-indian-flag-icon-transparent.png"")" worksheet.Cells(4, 0).Formula = "IMAGE(""https://previews.123rf.com/images/auttkhamkhauncham/auttkhamkhauncham1507/auttkhamkhauncham150700090/42304741-usa-flag.jpg"")" worksheet.Cells(5, 0).Formula = "IMAGE(""https://i.pinimg.com/236x/e8/0a/d5/e80ad5d4ea09700a78719ca051d19cbd.jpg"")" worksheet.Cells(6, 0).Formula = "IMAGE(""https://static.vecteezy.com/system/resources/previews/000/114/048/non_2x/free-vector-pakistan-flag.jpg"")" worksheet.Cells(7, 0).Formula = "IMAGE(""https://upload.wikimedia.org/wikipedia/en/thumb/0/05/Flag_of_Brazil.svg/2560px-Flag_of_Brazil.svg.png"")" worksheet.Cells(8, 0).Formula = "IMAGE(""https://i.pinimg.com/originals/73/22/94/732294310c7e9fa3da611030168923fb.jpg"")" worksheet.Cells(9, 0).Formula = "IMAGE(""https://images-na.ssl-images-amazon.com/images/I/31V23jzzMgL._AC_.jpg"")" worksheet.Cells(10, 0).Formula = "IMAGE(""https://upload.wikimedia.org/wikipedia/en/thumb/f/f3/Flag_of_Russia.svg/1200px-Flag_of_Russia.svg.png"")" worksheet.Cells(11, 0).Formula = "IMAGE(""https://www.pngkey.com/png/detail/442-4423822_mexico-icon-mexican-flag-icon-png.png"")" 'Set backcolor for cells worksheet.Cells("A1").Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(&HFF9FD5B7) worksheet.Cells("A2:E2").Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(&HFFD9EEE2) worksheet.Cells("A3:E12").Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(&HFFF5FBF8) |
|
Using the Spread Designer
- Type data in a cell or a column or row of cells in the designer.
- Select a cell for the sparkline.
- Select the Insert menu.
- Select a sparkline type.
- Set the Data Range in the Create Sparklines dialog (such as =Sheet1!$E$1:$E$3).
Alternatively, set the range by selecting the cells in the range using the pointer.
You can also set additional sparkline settings in the dialog if available.
- Select OK.
- Select Apply and Exit from the File menu to save your changes and close the designer.