Copy Image from Taskpane to Excel - Office-JS

Question

I'm working on making barcodes via an Excel Add-In. So far, I've got https://github.com/lindell/JsBarcode working to generate a barcode inside the TaskPane, but I'd like to get this onto the Worksheet.

My HTML knowledge is somewhat limited, this is the tag I'm using, but I think I could use other tags if needed, examples offered in github using Canvas/svg etc.

<img id="barcode" />

So far this is what I've got, I get no errors, but it doesn't work.

await JsBarcode("#barcode", "Hi!");

const data = {};
JsBarcode(data, 'text') //, { ...options });
console.log('data:')
console.log(data)

var ws = context.workbook.worksheets.getActiveWorksheet();
ws.getRange("A1").values = data

Here is the Object data:

[object Object]
   {
      [functions]: ,
      __proto__: { },
      encodings: [
         0: {
            [functions]: ,
            __proto__: { },
            data: "1101001000010011110100101100100001111001001010011110100111101011101100011101011",
            options: {
               [functions]: ,
               __proto__: { },
               background: "#ffffff",
               displayValue: true,
               font: "monospace",
               fontOptions: "",
               fontSize: 20,
               format: "CODE128",
               height: 100,
               lineColor: "#000000",
               margin: 10,
               marginBottom: 10,
               marginLeft: 10,
               marginRight: 10,
               marginTop: 10,
               Symbol()_7.6h76ic49xap: undefined,
               Symbol(nodejs.util.inspect.custom)_j.6h76ic49xdy: undefined,
               text: undefined,
               textAlign: "center",
               textMargin: 2,
               textPosition: "bottom",
               width: 2
            },
            Symbol()_7.6h76ic49xap: undefined,
            Symbol(nodejs.util.inspect.custom)_j.6h76ic49xdy: undefined,
            text: "text"
         },
         length: 1
      ],
      Symbol()_7.6h76ic49xap: undefined,
      Symbol(nodejs.util.inspect.custom)_j.6h76ic49xdy: undefined
   }

Update: I need the solution to be available under HTTP as that is how I currently load my Add-In due to Excel having issues with my company SSL Certificate. I've looked/read into using new Clipboard API, but its only available under HTTPS.

Eugene Astafiev · Accepted Answer · 2023-04-19T22:41:28.493

Bar-code or qr-code generators allow exporting the generated graphical code as image. In Office web add-ins starting from Excel requirement set 1.9 you can use the addImage method to insert images:

addImage(base64ImageString: string): Excel.Shape

For example, you may use the following code:

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();
})

score 0 · Answer 2 · answered Apr 19 '23 at 19:47

Basically, I had to convert the image to Base64 then insert that into Worksheet.

https://stackoverflow.com/a/35703395/5079799

function textToBase64Barcode(text) {
    var canvas = document.createElement("canvas");
    JsBarcode(canvas, text, { format: "CODE39" });
    return canvas.toDataURL("image/png");
}

export async function Button_BARCODES_Run_OnClick() {
    try {
        await Excel.run(async (context) => {
            //START
            console.log('Button_BARCODES_Run_OnClick')
            var Base64Image = await textToBase64Barcode('text')
            Base64Image = Base64Image.split(',')[1]
            console.log('Base64Image:')
            console.log(Base64Image)
            
            var ws = context.workbook.worksheets.getActiveWorksheet();
            ws.getRange("A1").select()
            await context.sync()
            await IMAGES.Insert_Image_At_Selection(context, Base64Image)
            //data.encodings.data
            
            //END
            await context.sync();
        });
    } catch (error) {
        console.error(error);
    }
}

IMAGES.Insert_Image_At_Selection = async function Insert_Image_At_Selection(context,Image_Base64_Str) {
    //Leaving as reference, but use "shapes"
    Office.context.document.setSelectedDataAsync(Image_Base64_Str, {
        coercionType: Office.CoercionType.Image,
        imageLeft: 50,
        imageTop: 50,
        imageWidth: 100,
        imageHeight: 100
    },
        function (asyncResult) {
            if (asyncResult.status === Office.AsyncResultStatus.Failed) {
                console.log("Action failed with error: " + asyncResult.error.message);
            }
        });
}

Copy Image from Taskpane to Excel - Office-JS

2 Answers2