Hello, is there a way wherein a screenshot or image saved in a specific folder can be pasted in excel file via KNIME? Appreciate your inputs. Thanks!
You can write an Image into an excel file using Excel Writer node.
Here is a prototype:
ImageToExcel.knwf (2.3 MB)
Overview:
You can load an Image using Image Reader - then you render it to e.g. PNG and then can write it to Excel:
You’ll probably have to play around with some other topics settings to get it right, but hope this gets you started.
You will have to manually find the image in the prototype - I have included it in the data folder of the workflow, but the image reader does not seem to support reading from that folder by default (as e.g. the excel reader does)
5 Likes
@trafalgarlaw you can use OpenPyxl bundled in KNIME to add images to excel
2 Likes
You can also do this with the Excel Write to Template node if you want to maintain formatting of an excel file. I find it to be the easiest and most flexible way to integrate or output to excel. I pretty much use it exclusively over the standard excel writer and formatting nodes.
2 Likes
Thank you @MartinDDDD !!
But I want the screenshot to be pasted to an excel, not sure how it works in write to excel template?
Thank you @mlauber71 will check this!
It has been a while, but I believe that you need to read in the image file, then use PNG to Image Plus, then render and adjust image if necessary, then Image Plus to PNG again before writing to excel. I will try to put together an example workflow when I get an opening.
3 Likes
Write images to Excel.knwf (528.1 KB)
Here are examples of how to write image files to excel using the Write to Excel Template node. I included a component that you can use to quickly access the workflow data folder or launch the excel files. Just execute it and open the interactive view, then you trigger a one time open by changing the dropdown to yes and hit refresh to open a file or folder selected to right.
Works the same if you use Write to Excel. The template approach just simplifies formatting and opens additional excel side programming capabilities.
3 Likes
Hello @iCFO thank you for sharing it, but can I ask for a favor a screenshot of your configuration for write to excel template? I am not able to open it because of version diff. Appreciate it. Thank you!
Top node 1st tab
Second tab settings for both nodes
Bottom node 1st tab
I will add that the “Remove all Data Before Writing” setting does not appear to remove png images from the template file write area as it does with other data types. Instead it appears to write the new image over the old ones. I recommend creating a template file that has all of the data cleared, then selecting “Create New File” at the bottom of tab 1. That way it always creates a new file with the data written into it avoiding the issue. @AnotherFraudUser - Just tagging you to give a heads up that images don’t appear to be cleared with the remove all data option. However, I could see how that could be a benefit in some instances and a hindrance in others…
2 Likes
Thank you @iCFO but yeah I noticed the images or screenshot being pasted in the Excel file is not clear image. The image is blurry. Is there a way that the actual look of the screenshot can be pasted clearly in the excel?
Have you tried adjusting the image size in the rendering node / the cell size in excel? Maybe start with checking the image dimensions on the original file and set the renderer to the same dimensions.
Hello @iCFO, yes this is the sample screenshot being pasted in the excel which is very blurry. Is there a way to fix it?
Before we try and fix this in the KNIME workflow end of things…
I am going to assume that you are opening your Excel files with the local desktop application (which I recommend) instead of the web app.
Have you manually inserted the png into the excel file for a quality comparison?
Have you tried adjusting to Excel Options - Advanced - Image Size and Quality and adjusted the Settings for:
Do not compress images in file
&
Default Resolution (which I believe defaults to just 220 ppi)?
Here’s the sample when I manually inserted the screenshot into the excel file. It is not blurry.
Yes, I have tried the settings you provided and when I try to run my KNIME workflow, the output screenshot in the excel file is blurried.
I will see if I can get a higher quality image write to excel on my end next time I have an opening.
1 Like
Hi @MartinDDDD may I ask how come your image is so clear but when I tried my screenshot, it is blurry?
Write images to Excel.knwf (435.6 KB)
The trick is to keep the image properties consistent throughout the process. Right click on the original image file to see the pixel width and height, use those pixel settings in the rendering node in KNIME, export it to the template file, go to the image cell and widen the column width and row height to allow for adequate space, right click on the image file and go to “size and properties”, then click on the “reset” button to go to the original size and aspect ratios, then resize the cell to match the image size, then delete image / save the template file. Then re-run the workflow.
This way the cell in the template file will hold the correct size and avoid the blurry look caused by re-rendering in excel.
4 Likes
Thank you @iCFO will check on this.
1 Like