How do I print image on their original size in Excel using libxlsxwriter?

Question

I just started to use libxlsxwriter to create excel sheet. My problem is I don't know how to centralized the picture and how to print pictures on their original size. Just consider the set having only 3 columns and each column having different size for example 30,10,20. I need to know what is the calculation do I need to find offset and scale values.

lxw_image_options options = {.x_offset = 0,  .y_offset = 0,.x_scale  = 0.9, .y_scale  = 0.9};
worksheet_insert_image_opt(worksheet, row, 0, img_path, &options);

With that, I need to Know How many rows the picture can hold.Then only I can create the upcoming set without overlap.

score 1 · Answer 1 · answered Oct 02 '17 at 13:48

how to print pictures on their original size

libxlsxwriter inserts images into an xlsx file at their original size based on the width, height and DPI information in the image. It should insert images in exactly the same way as if you did it in Excel via the user interface.

However, images in OpenOffice or LibreOffice may not appear to the correct size. This isn't an libxlsxwriter issue: the same thing happens with Excel.

To insert images at precise positions you will need to know the dimensions of the images in pixels and also the DPI since Excel scales according to it's default DPI (usually 96) Here is an example of inserting two images in a row:

/*
 * An example of inserting images with the libxlsxwriter library.
 */
#include "xlsxwriter.h"

int main() {

    /* Create a new workbook and add a worksheet. */
    lxw_workbook  *workbook   = workbook_new("demo.xlsx");
    lxw_worksheet *worksheet  = workbook_add_worksheet(workbook, NULL);
    lxw_image_options options = {.x_offset = 0, .y_offset = 0,
                                 .x_scale  = 1, .y_scale = 1};

    double default_dpi   = 96.0;
    double image_dpi     = 90.0;
    int32_t image_height = 138;
    int32_t image_offset = (int32_t)(image_height * default_dpi/image_dpi);

    /* Insert the first image. */
    worksheet_insert_image(worksheet, 1, 2, "logo.png");

    /* Insert the second image relative to the first. */
    options.y_offset += image_offset;
    worksheet_insert_image_opt(worksheet, 1, 2, "logo.png", &options);    

    workbook_close(workbook);

    return 0;
}

Output:

score 1 · Answer 2 · answered Oct 04 '17 at 05:58

How many rows did picture hold?

I want to Present my findings here to improvise the result.

int row=1;
lxw_image_options options = {.x_offset = 0,  .y_offset = 0};
worksheet_insert_image_opt(worksheet, row, 2,"logo.png", &options);
row+=(options.height/worksheet->default_row_pixels);

Here I used the variable options.height to calculate How many rows the picture hold. The libxlsxwriter did read the height from the image file in pixels. It uses struct variable option only for read initialized variable it will never write anything in the set. But I did by adding the line user_options->height=options->height; in the function worksheet_insert_image_opt at worksheet.c.

 lxw_error worksheet_insert_image_opt(lxw_worksheet *self,
                           lxw_row_t row_num, lxw_col_t col_num,
                           const char *filename,
                           lxw_image_options *user_options)
{
    FILE *image_stream;
    char *short_name;
    lxw_image_options *options;

    if (!filename) {
        LXW_WARN("worksheet_insert_image()/_opt(): "
                 "filename must be specified.");
        return LXW_ERROR_NULL_PARAMETER_IGNORED;
    }

    /* Check that the image file exists and can be opened. */
    image_stream = fopen(filename, "rb");
    if (!image_stream) {
        LXW_WARN_FORMAT1("worksheet_insert_image()/_opt(): "
                         "file doesn't exist or can't be opened: %s.",
                         filename);
        return LXW_ERROR_PARAMETER_VALIDATION;
    }

    /* Get the filename from the full path to add to the Drawing object. */
    short_name = lxw_basename(filename);
    if (!short_name) {
        LXW_WARN_FORMAT1("worksheet_insert_image()/_opt(): "
                         "couldn't get basename for file: %s.", filename);
        fclose(image_stream);
        return LXW_ERROR_PARAMETER_VALIDATION;
    }

    /* Create a new object to hold the image options. */
    options = calloc(1, sizeof(lxw_image_options));
    if (!options) {
        fclose(image_stream);
        return LXW_ERROR_MEMORY_MALLOC_FAILED;
    }

    if (user_options) {
        memcpy(options, user_options, sizeof(lxw_image_options));
        options->url = lxw_strdup(user_options->url);
        options->tip = lxw_strdup(user_options->tip);
    }

    /* Copy other options or set defaults. */
    options->filename = lxw_strdup(filename);
    options->short_name = lxw_strdup(short_name);
    options->stream = image_stream;
    options->row = row_num;
    options->col = col_num;

    if (!options->x_scale)
        options->x_scale = 1;

    if (!options->y_scale)
        options->y_scale = 1;

    if (_get_image_properties(options) == LXW_NO_ERROR) {
        user_options->height=options->height;
        STAILQ_INSERT_TAIL(self->image_data, options, list_pointers);
        return LXW_NO_ERROR;
    }
    else {
        free(options);
        return LXW_ERROR_IMAGE_DIMENSIONS;
    }
}

this how I was calcuating rows. If there is a better way please let me know.

"It uses struct variable option only for read initialized variable it will never write anything in the set". That is correct. Libxlsxwriter never modifies any structs passed by the user. I am the author btw. Maybe libxlsxwriter should expose the functions to calculate the image metrics required by Excel. However, adding them back into the user struct, like you did, is simpler and probably cleaner. I'll think about it. It might be a good idea to open a feature request for this. — jmcnamara, Oct 09 '17 at 09:10

How do I print image on their original size in Excel using libxlsxwriter?

2 Answers2