How do I insert a picture as embedded in a cell ?

[zhjr2019]

I want to insert a picture as embedded in a cell. How do I do that with libxlsxwriter? thank you

[jmcnamara]

When you perform this action in Excel it scales the image to fit the cell.

It is also possible with libxslxwriter to scale images (using worksheet_insert_image_opt(). However, there aren't any helper functions to scale it to a cell/pixel size. I had planned to do this at some stage but I didn't get around to it.

You can do it yourself by using some library to get the X and Y pixel size of the the image and also the X and Y DPI. There are internal functions to do this in libxslxwriter but they aren't public.

After that you need to scale the image (using the dimensions and DPI) to the pixel size of the cell. There is some code for that in the Rust version of the library that I wrote. See here.

[zhjr2019]

ok, thank you, I hope to add this feature in the future.

[sunilsabraham]

@jmcnamara : The author is asking how to embed picture in cell. In latest excel there are 2 options to insert picture. 1) place in cell 2) place over cells The first option is to embed picture in cell which automatically scale the picture by excel. The second once is the current one implemented by libxlswriter. Is it possible to add the first option as well. Hope you can implement it asap. Thanks.

[jmcnamara]

@sunilsabraham Could you attach an Excel example with an image embedded in a cell and a similar example with an image just inserted, and not embedded, for reference.

[sunilsabraham]

@jmcnamara Please see the screen shot below. Just open excel and try to insert a picture and you will get 2 options. first one is to embed picture to a cell and excel will automatically scale the image. second one is to place image over a cell and that is the current implementation in libxlswriter. Old excel had only place over a cell may be upto 2016 version. If you can implement the embed in cell that would be great. so we dont need to scale the image and it automatically does according to row height etc. I am attaching an example file as well.

Book1.xlsx

[jmcnamara]

@sunilsabraham Thanks for pointing that out. That is a useful feature. I'll try add it.

[sunilsabraham]

Thanks.

[rickabitdigital]

@jmcnamara I'd love the Place in cell feature too! Images placed in cells will scale according to column width/row height. As we can define the initial column width and row height, to accommodate our inserted images, cells shoudn't be hidden on file open. I think this produces a more natural and robust layout behaviour - e.g. when objects float over cells users can struggle with the overlap.

[rickabitdigital]

Hi @jmcnamara Have you looked into implementing this feature yet?

I've been reading the openxml API reference docs (my first time), but the 'place in cell' xml format is not immediately obvious. So I have begun using an OOXML editor/viewer to see the difference between images placed in cells using typical drawing references vs an image embedded in a cell. I'll let you know if I get anywhere. If there's any way you can think of me helping advance this feature please could you advise? thank you.

[jmcnamara]

Have you looked into implementing this feature yet?

Yes. I started working on it in December but then got sucked into some work on rust_xlsxwriter. I made some progress on the Metadata and RichValue files but there is still quite a bit to do. I'll start working on it again next week after the 0.62.0 release of rust_xlsxwriter but I can't give a timeline.

[rickabitdigital]

Hi John, That's great to hear and thank you very kindly for the update. Much appreciated. I have just reached the point where the basic OOXML viewer I found wasn't showing me the RichValue relationship for an image placed in cell for me to compare with the XML of the existing drawing relationships for image objects. I suppose I may need to give in and use the obvious OOXML explorer for VSCode. I'll keep going with my learning and appreciate any time you find to work on this.

[slw287r]

Snippets to get image dimensions and DPIs for in-cell embedding.

https://github.com/slw287r/imgt/blob/main/imgt.c

[jmcnamara]

Snippets to get image dimensions and DPIs for in-cell embedding.

Note, there is already code like that in the library: https://github.com/jmcnamara/libxlsxwriter/blob/main/src/worksheet.c#L3885

It isn't public, by default, and I could make it public but the feature request is for a newer type of embedded image feature in Excel.

[jmcnamara]

Just a note that I've made some progress on this. I've implemented this feature in the Python and Perl versions of the library (XlsxWriter and Excel::Writer::XLSX). Next up I will implement it in the Rust version rust_xlsxwriter and then I will move on to the C version libxlsxwriter.

https://xlsxwriter.readthedocs.io/worksheet.html#embed_image

[rickabitdigital]

Hi John, I'm creating sheets and images in memory - in your C Library. Will this feature be an option for the _worksheet_insert_image_bufferopt function?

[jmcnamara]

Will this feature be an option for the _worksheet_insert_image_bufferopt function?

Yes. Probably.

[rickabitdigital]

Hey @jmcnamara, Any idea when you may revisit this feature? kind regards Rick

[jmcnamara]

Any idea when you may revisit this feature?

I'll try tackle it this week.

[jmcnamara]

I've pushed an initial functional version of this feature to main.

It works as follows (from the examples folder):

#include "xlsxwriter.h"

int main() {

    /* Create a new workbook and add a worksheet. */
    lxw_workbook  *workbook  = workbook_new("embed_images.xlsx");
    lxw_worksheet *worksheet = workbook_add_worksheet(workbook, NULL);

    /* Change some of the column widths for clarity. */
    worksheet_set_column(worksheet, COLS("A:B"), 30, NULL);

    /* Embed an image. */
    worksheet_write_string(worksheet, CELL("A2"), "Embed an image in a cell:", NULL);
    worksheet_embed_image(worksheet, CELL("B2"), "logo.png");

    /* Make a row bigger and embed the image. */
    worksheet_set_row(worksheet, 3, 72, NULL);
    worksheet_write_string(worksheet, CELL("A4"), "Embed an image in a cell:", NULL);
    worksheet_embed_image(worksheet, CELL("B4"), "logo.png");

    /* Make a row bigger and embed the image. */
    worksheet_set_row(worksheet, 5, 150, NULL);
    worksheet_write_string(worksheet, CELL("A6"), "Embed an image in a cell:", NULL);
    worksheet_embed_image(worksheet, CELL("B6"), "logo.png");

    workbook_close(workbook);

    return 0;
}

Which gives this output:

There is still some work to do:

• Add support for alt_text/description Done
• Add support for urls Done
• Add support for cell format behind the image Done
• Add buffer support Done
• Docs and examples Done

As an aside I'd like to refactor image handling into an Image struct that can be constructed from a path or a buffer and could be configured without the lxw_image_options struct. It could also then return the calculated dimensions of the image. Something like the Rust interface: https://docs.rs/rust_xlsxwriter/latest/rust_xlsxwriter/struct.Image.html However, that will be for another day.

[jmcnamara]

I've added support for image options such as urls and alt text and also support for creating the image from a buffer:

#include "xlsxwriter.h"

/* Simple array with some PNG data. */
unsigned char image_buffer[] = {
    0x89, 0x50, 0x4e, 0x47, 0x0d, 0x0a, 0x1a, 0x0a, 0x00, 0x00, 0x00, 0x0d,
    0x49, 0x48, 0x44, 0x52, 0x00, 0x00, 0x00, 0x20, 0x00, 0x00, 0x00, 0x20,
    0x08, 0x02, 0x00, 0x00, 0x00, 0xfc, 0x18, 0xed, 0xa3, 0x00, 0x00, 0x00,
    0x01, 0x73, 0x52, 0x47, 0x42, 0x00, 0xae, 0xce, 0x1c, 0xe9, 0x00, 0x00,
    0x00, 0x04, 0x67, 0x41, 0x4d, 0x41, 0x00, 0x00, 0xb1, 0x8f, 0x0b, 0xfc,
    0x61, 0x05, 0x00, 0x00, 0x00, 0x20, 0x63, 0x48, 0x52, 0x4d, 0x00, 0x00,
    0x7a, 0x26, 0x00, 0x00, 0x80, 0x84, 0x00, 0x00, 0xfa, 0x00, 0x00, 0x00,
    0x80, 0xe8, 0x00, 0x00, 0x75, 0x30, 0x00, 0x00, 0xea, 0x60, 0x00, 0x00,
    0x3a, 0x98, 0x00, 0x00, 0x17, 0x70, 0x9c, 0xba, 0x51, 0x3c, 0x00, 0x00,
    0x00, 0x46, 0x49, 0x44, 0x41, 0x54, 0x48, 0x4b, 0x63, 0xfc, 0xcf, 0x40,
    0x63, 0x00, 0xb4, 0x80, 0xa6, 0x88, 0xb6, 0xa6, 0x83, 0x82, 0x87, 0xa6,
    0xce, 0x1f, 0xb5, 0x80, 0x98, 0xe0, 0x1d, 0x8d, 0x03, 0x82, 0xa1, 0x34,
    0x1a, 0x44, 0xa3, 0x41, 0x44, 0x30, 0x04, 0x08, 0x2a, 0x18, 0x4d, 0x45,
    0xa3, 0x41, 0x44, 0x30, 0x04, 0x08, 0x2a, 0x18, 0x4d, 0x45, 0xa3, 0x41,
    0x44, 0x30, 0x04, 0x08, 0x2a, 0x18, 0x4d, 0x45, 0x03, 0x1f, 0x44, 0x00,
    0xaa, 0x35, 0xdd, 0x4e, 0xe6, 0xd5, 0xa1, 0x22, 0x00, 0x00, 0x00, 0x00,
    0x49, 0x45, 0x4e, 0x44, 0xae, 0x42, 0x60, 0x82
};

unsigned int image_size = 200;

int main() {

    /* Create a new workbook and add a worksheet. */
    lxw_workbook  *workbook  = workbook_new("embed_image_buffer.xlsx");
    lxw_worksheet *worksheet = workbook_add_worksheet(workbook, NULL);

    /* Embed the image from the buffer. */
    worksheet_embed_image_buffer(worksheet, CELL("B3"), image_buffer, image_size);

    workbook_close(workbook);

    return 0;
}

Output:

[jmcnamara]

Available upstream in v1.1.8.