Add Icons to Excel cells using OfficeJS

Question

I am trying to build a web-addin for Excel using OfficeJS.

I've added Icons to a range of cells using the following code.

async function addIcon() {
  await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getActiveWorksheet();
    const range = sheet.getRange("A1:E13");
    const conditionalFormat = range.conditionalFormats
        .add(Excel.ConditionalFormatType.iconSet);
    const iconSetCF = conditionalFormat.iconSet;
    iconSetCF.style = Excel.IconSet.threeFlags;
    iconSetCF.showIconOnly=false;
    iconSetCF.criteria = [
        {
            type: Excel.ConditionalFormatIconRuleType.number,
            operator: Excel.ConditionalIconCriterionOperator.greaterThanOrEqual,
            formula: ">0"
        }
    ];

    await context.sync();
});
}

The above code adds different icons for each of the cell values as shown in the images.

Can someone please answer the following questions?

Question1: Can we set the color of the Icon to a single color? I don't want to have different colors of the same Icon but want to set it to either Yellow or Red.

Question2: Can the Icon be applied to non-numeric cells?

As of now the icon is only applied to those cells with numeric values, and all the properties of the Excel.ConditionalFormatIconRuleType are either number,Formula, percentile etc, which are useful only for numeric values.

I have tried the following code, but doesn't work.

iconSetCF.criteria = [
            {
                type: Excel.ConditionalFormatIconRuleType.Formula,
                operator: Excel.ConditionalIconCriterionOperator.greaterThanOrEqual,
                formula: "=ISTEXT(A1)"
            }
        ];

Question3: Can we add custom icons instead of using the predefined iconSet?

Can I use a custom image as an Icon instead of using the office predefined icons? I tried to use the Excel.ConditionalFormatType.custom property but it can only apply colors or format the cells but cannot add any images inside the cells.

Kindly let me know your thoughts on the above questions? I couldn't find anything related to my above requirements in the OfficeJS's documentation.

score 0 · Answer 1 · answered Jun 14 '23 at 10:51

JPEG, PNG, and SVG images can be inserted into a worksheet as shapes. The ShapeCollection.addImage method takes a base64-encoded string as an argument. This is either a JPEG or PNG image in string form. ShapeCollection.addSvg also takes in a string, though this argument is XML that defines the graphic.

The following code sample shows an image file being loaded by a FileReader as a string. The string has the metadata "base64," removed before the shape is created.

// This sample creates an image as a Shape object in the worksheet.
let myFile = document.getElementById("selectedFile");
let reader = new FileReader();

reader.onload = (event) => {
    Excel.run(function (context) {
        let startIndex = reader.result.toString().indexOf("base64,");
        let myBase64 = reader.result.toString().substr(startIndex + 7);
        let sheet = context.workbook.worksheets.getItem("MyWorksheet");
        let image = sheet.shapes.addImage(myBase64);
        image.name = "Image";
        return context.sync();
    }).catch(errorHandlerFunction);
};

// Read in the image file as a data URL.
reader.readAsDataURL(myFile.files[0]);

And if you need to reposition images you need do the following (implemented in the requirement set 1.10):

Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("MyWorksheet");
    let cell = sheet.getRange("D5")
    cell.load('top,left') //pre-load top and left
    let myBase64 = "your bas64string here";
    const shape = sheet.shapes.addImage(myBase64)

    await context.sync()

    shape.incrementLeft(cell.left) // <- left
    shape.incrementTop(cell.top) // <-top
    await context.sync();
})

Add Icons to Excel cells using OfficeJS

1 Answers1