Inserting Image in xlsx sheet

Navaneethakrishnan Babu

Hi,
I have to show the logo in excel sheet which is showing the summary report. How i can add the logo image in that xlsx sheet through handlebars in js-report ?
If any playground example is there, please share with me.
Quick response is highly appreciable.

bjrmatos

hi! there is a helper method xlsxAddImage that you can use to add the image, more information in the docs. here is an example using it

Navaneethakrishnan Babu

Ya, it's the same i want. Thanks a lot.

Navaneethakrishnan Babu

This post is deleted!

Sher Bahadur

Can we dynamically display base64 Image and display on the excel file using .xlsx recipe?

bjrmatos

Hi @Sher-Bahadur

Can we dynamically display base64 Image and display on the excel file using .xlsx recipe?

yes, as you can see in the example, the xlsxAddImage helper expects you to pass the base64 representation of the image you want to use, in the case of the example it just uses an existing image (asset) for convenience, but you can also just pass in your data a base64 image and put it in the xlsxAddImage. for example, if your base64 image is inside a property userAvatarImage, then you can pass that value to the xlsxAddImage directly.

{{#xlsxAddImage "logo" "sheet1.xml" 0 0 2 4}}
{{userAvatarImage}}
{{/xlsxAddImage}}

live example here

you can also adapt that to any way you work with your data, for example if you don't pass the base64 representation of an image but instead you pass links to it, you can also use an script to fetch the images there and convert it to base64, store that in the req.data variable and then you will be able to use it inside xlsxAddImage.

I recommend you to pass your images in base64 directly in your data, that way you don't need to deal with http issues you may find if the urls where your images are hosted are not public

Brad Filip

Hi @bjrmatos would you be willing to share an example of the part where you mention fetching the images in beforeRender? I am trying to download an image from a url and then add it to the data object as base64 after its successful, but I am not totally familiar with node, so below is my attempt at making that happen.

async function beforeRender(req, res) {
  // merge in some values for later use in engine
  // and preserve other values which are already in
  req.data = Object.assign({}, req.data, {test_image: ""})
  request.get('MY PUBLIC IMAGE URL', function (error, response, body) {
    if (!error && response.statusCode == 200) {
        data = "data:" + response.headers["content-type"] + ";base64," + Buffer.from(body).toString('base64');
        req.data.test_image = data;
    }
});
}

jan_blaha

@Brad-Filip

Here is the code that downloads an image and converts to base64 in node and jsreport script

const axios = require('axios')

function getBase64(url) {
  return axios
    .get(url, {
      responseType: 'arraybuffer'
    })
    .then(response => Buffer.from(response.data, 'binary').toString('base64'))
}

async function beforeRender(req, res) {
    req.data.imageBase64 = await getBase64('https://jsreport.net/img/js-logo.png')  
}

Working demo with xlsx here
https://playground.jsreport.net/w/jan_blaha/UXsYheEo

To understand what was the issue with your code, check some articles about callbacks and async/await in javascript. Like this one.

Brad Filip

Thanks so much! @jan_blaha

Brad Filip

This post is deleted!

PhilZantiff

HI and thanks for everything!

Can we make it that the image should be inserted with dynamic size? so it doesn't get squeezed or streched?
because currently, if the image is not the same size as the provided range of cells (e.g. 0 0 2 2), then it gets squeezed or streched. can we avoid or work around that?

what I think would be a solution is if we can modify the size of the cells base on the ratio of the image, after inserting, that could help, but I'm not sure how to do that

thanks!!!!

jan_blaha

Here are some ideas, but it isn't working fully, however, perhaps, you sort it out.
https://playground.jsreport.net/w/anon/s9Z5RS7L