Spread Windows Forms 17
In This Topic
    IMAGE Sparkline
    In This Topic

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

    The image sparkline formula has the following syntax:

    =GC.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.
    • 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

    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
    1 - Center
    2 - Bottom

    The default value is 1 (center).

    hAlign

    Optional

    Horizontal alignment of the image.

    0 - Left
    1 - Center
    2 - Right

    The default value is 1 (center).

    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

    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.

    image-sparkline-usage

    C#
    Copy Code
    // Get sheet
    var worksheet = fpSpread1_Sheet1.AsWorksheet(); 
    // 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 GC.IMAGE function formula in cells
    worksheet.Cells[2, 0].Formula = "GC.IMAGE(\"https://www.wallpaperflare.com/static/193/1001/133/five-starred-red-flag-china-flag-five-wallpaper.jpg\")";
    worksheet.Cells[3, 0].Formula = "GC.IMAGE(\"https://www.pngfind.com/pngs/m/21-211631_the-indian-flag-png-indian-flag-icon-transparent.png\")";
    worksheet.Cells[4, 0].Formula = "GC.IMAGE(\"https://previews.123rf.com/images/auttkhamkhauncham/auttkhamkhauncham1507/auttkhamkhauncham150700090/42304741-usa-flag.jpg\")";
    worksheet.Cells[5, 0].Formula = "GC.IMAGE(\"https://i.pinimg.com/236x/e8/0a/d5/e80ad5d4ea09700a78719ca051d19cbd.jpg\")";
    worksheet.Cells[6, 0].Formula = "GC.IMAGE(\"https://static.vecteezy.com/system/resources/previews/000/114/048/non_2x/free-vector-pakistan-flag.jpg\")";
    worksheet.Cells[7, 0].Formula = "GC.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 = "GC.IMAGE(\"https://i.pinimg.com/originals/73/22/94/732294310c7e9fa3da611030168923fb.jpg\")";
    worksheet.Cells[9, 0].Formula = "GC.IMAGE(\"https://images-na.ssl-images-amazon.com/images/I/31V23jzzMgL._AC_.jpg\")";
    worksheet.Cells[10, 0].Formula = "GC.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 = "GC.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
     'Get sheet
    Dim worksheet = FpSpread1_Sheet1.AsWorksheet()
           
    '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 GC.IMAGE function formula in cells
    worksheet.Cells(2, 0).Formula = "GC.IMAGE(""https://www.wallpaperflare.com/static/193/1001/133/five-starred-red-flag-china-flag-five-wallpaper.jpg"")"
    worksheet.Cells(3, 0).Formula = "GC.IMAGE(""https://www.pngfind.com/pngs/m/21-211631_the-indian-flag-png-indian-flag-icon-transparent.png"")"
    worksheet.Cells(4, 0).Formula = "GC.IMAGE(""https://previews.123rf.com/images/auttkhamkhauncham/auttkhamkhauncham1507/auttkhamkhauncham150700090/42304741-usa-flag.jpg"")"
    worksheet.Cells(5, 0).Formula = "GC.IMAGE(""https://i.pinimg.com/236x/e8/0a/d5/e80ad5d4ea09700a78719ca051d19cbd.jpg"")"
    worksheet.Cells(6, 0).Formula = "GC.IMAGE(""https://static.vecteezy.com/system/resources/previews/000/114/048/non_2x/free-vector-pakistan-flag.jpg"")"
    worksheet.Cells(7, 0).Formula = "GC.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 = "GC.IMAGE(""https://i.pinimg.com/originals/73/22/94/732294310c7e9fa3da611030168923fb.jpg"")"
    worksheet.Cells(9, 0).Formula = "GC.IMAGE(""https://images-na.ssl-images-amazon.com/images/I/31V23jzzMgL._AC_.jpg"")"
    worksheet.Cells(10, 0).Formula = "GC.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 = "GC.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

    1. Select a cell for the sparkline.
    2. Select the Insert menu.
    3. Select a sparkline type.
    4. Set the URL in the Image Sparkline Setting dialog.

      image-sparkline-dialog

      Set the additional sparkline settings as shown in the image above.

    5. Select OK.
    6. Select Apply and Exit from the File menu to save your changes and close the designer.

    NOTE

    Spread for WinForms provides a function that allows you to insert an image in a cell. For more information, refer to IMAGE function.