Inserting an image not working

2.3.0-b1/usage.html#inserting-an-image

Not working

Can you please provide more information about what doesn't work?

The images are inserted successfully using version 2.2.5 but the merged cells border formatting is broken. Using 2.3.0b1, the borders are working properly but the images are not being inserted. Pillow is installed. See used code below:

openpyxl==2.2.5

img = Image('distributor_templates/services.png')
img2 = Image('distributor_templates/pjc.png')
img.anchor(wsFormulaire['B1'])
img2.anchor(wsFormulaire['Y1'])
wsFormulaire.add_image(img)
wsFormulaire.add_image(img2)
wb.save('test_copie.xlsm')

openpyxl==2.3.0b1

img = Image('distributor_templates/services.png')
img2 = Image('distributor_templates/pjc.png')
wsFormulaire.add_image(img,'B1')
wsFormulaire.add_image(img2,'Y1')
wb.save('test_copie.xlsm')

Thanks, Mat

Any clue why this is happening?

Sorry, but in the absence of any files and error messages I'm not sure what the problem is.

What's the next step to resolve this. What should I do to get or force an error message.

It would help if you just provide more information. The code samples you provide run fine.

#!/usr/bin/env python
# -*- coding: utf-8 -*-

from django.utils.safestring import mark_safe
from django.forms import widgets
from django.conf import settings
from django.utils.translation import ugettext as _

from .models import Format, DIN, Price, DistributionCenter, Product, Distributor, OrderedProduct
from custom_profiles.models import Province

from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.writer.excel import save_virtual_workbook
from openpyxl.utils import get_column_letter, column_index_from_string
from openpyxl.styles import Fill, Color, Style, PatternFill
from openpyxl.worksheet.datavalidation import DataValidation
from openpyxl.drawing.image import Image


def finalize_product(infos_din):
    # Pour chaque upc de ce DIN
    for upc in Format.objects.filter(infos_din__id=infos_din.id):
        # On créé un prix par province
        for province in Province.objects.all():
            Price.objects.get_or_create(format=upc, province=province)
        # On créé un Product par Distributor
        for distributor in Distributor.objects.all():
            Product.objects.get_or_create(format=upc, distributor=distributor)


def generate_jean_coutu_order(orders):
    ## OPENPYXL ###
    wb = load_workbook('distributor_templates/template_pjc.xlsm', keep_vba=True)
    ws = wb.active

    # On sélectionne la feuille "Données"
    wsDonnees = wb.get_sheet_by_name(name=u'Données')
    wsFormulaire = wb.get_sheet_by_name(name=u'Formulaire')
    #wsParametres = wb.get_sheet_by_name(name=u'Paramètres')

    # wsParam = wb.get_sheet_by_name(name=u'Paramètres')
    row = 7

    # On remplit le tableau des associations de produit/colonne
    products = {}
    prod_col = 'B'
    for ordered_product in OrderedProduct.objects.filter(order__in=orders):
        if not ordered_product.product.id in products:
            products[ordered_product.product.id] = prod_col
            prod_col = get_column_letter(column_index_from_string(prod_col) + 1)

    # On écrit la rangée des codes produits
    prod_row_index = row
    for product in products:
        cell = products[product] + str(prod_row_index)
        wsDonnees[cell] = Product.objects.get(id=product).code
        wsDonnees[cell].style = Style(fill=PatternFill(patternType='solid', fgColor=Color(rgb='03B0F0')))

    # Pour chaque commande, on insère une rangée avec la commande du client
    for order in orders:
        row += 1

        # On met le numéro de client(account number de la pharmacie dans A8)
        cell = 'A' + str(row)
        wsDonnees[cell] = order.account_number.account_number
        wsDonnees[cell].style = Style(fill=PatternFill(patternType='solid', fgColor=Color(rgb='03B0F0')))

        # Pour chaque produit du document, on met la quantité pour la commande courante
        for product in products:
            cell = products[product] + str(row)
            # Si ce produit existe dans cette commande, on met la quantité, sinon 0
            if order.ordered_products.filter(product_id=product):
                wsDonnees[cell] = order.ordered_products.get(product_id=product).quantity
            else:
                wsDonnees[cell] = 0

            wsDonnees[cell].style = Style(fill=PatternFill(patternType='solid', fgColor=Color(rgb='03B0F0')))

    #Data validation (Not needed, to be removed)
    #dv = DataValidation(type="list", formula1='"Dog,Cat,Bat"', allow_blank=True)
    #dv = DataValidation(type="list", formula1='TypeCommande')
    #wsFormulaire.add_data_validation(dv)
    #k3 = wsFormulaire["K3"]
    #k3.value = "<Type>"
    #dv.add(k3)

    # create image instances
    img = Image('distributor_templates/services.png')
    img2 = Image('distributor_templates/pjc.png')

    #Version 2.2.5 (Working but borders issues)
    #img.anchor(wsFormulaire['B1'])
    #img.anchor(wsFormulaire['Y1'])

    #wsFormulaire.add_image(img)
    #wsFormulaire.add_image(img)

    #Version 2.3.0b1 (Not working but borders ok)
    #wsFormulaire.add_image(img,'B1')
    #wsFormulaire.add_image(img2,'Y1')

    wb.save('template_pjc.xlsm')

    #os.rename('test_copie.xlsx', 'temp.xlsx')
    return save_virtual_workbook(wb)

Is this enough information? Or do you need the file?

What error are you getting? Does the code run okay?

If I comment out this code:

#Version 2.3.0b1 (Not working but borders ok)
    #wsFormulaire.add_image(img,'B1')
    #wsFormulaire.add_image(img2,'Y1')

The code runs ok but the image don't get inserted. If I comment out code below and install version 2.2.5. Code runs but merged cells borders are messed up and images get inserted.

#Version 2.2.5 (Working but borders issues)
    #img.anchor(wsFormulaire['B1'])
    #img.anchor(wsFormulaire['Y1'])

    #wsFormulaire.add_image(img)
    #wsFormulaire.add_image(img)

I am confused

I have recently come across an issue that Excel has with file properties but only when an image is inserted. I'll be committing a fix for this shortly.

The code for images and charts has been completely rewritten in 2.3 which is why it is a beta release. Once it's finished it should be much more robust and flexible but it's a huge part of the specification and not everything is properly documented.

All formatting for merged cells will be lost because the specification does not explain how it should work. See the related bugs on this.

Files with macros and other components like images and charts have always been a bit tricky. Very important to test in isolation and then in combination. Can't really do much without the files. Files with macros often include lots of other things.

"All formatting for merged cells will be lost because the specification does not explain how it should work. See the related bugs on this." Even in version 2.3? How can I get the images and the merged cells borders to work?

Can I pm you the file?

If something is undefined how can it be properly implemented?

One of the related bugs has some tips on how to format a group of merged cells manually.

Formatting borders around the merged cells? But what if the adjacent cells are merge as well

Set necessary attributes for images. Resolves #500

→ <<cset e0ea42ae894d>>

Don't ask me ask the standards committee. I have an open issue on it. Excel's current approach – to create dummy cells – is undocumented and fraught with difficulties.

Why did you set to resolved? The images are still not being inserted in version 2.3.0b1... Did you just commit your fix?

I obviously just committed to the 2.3 and 2.4 branches. You will need a checkout to use that code.

Obviously. Thanks.

How do I install the 2.4 branch from pip ? Thanks.

You should really work with a checkout in a virtualenv as described in the documentation. However, the following will also work:

bin/pip install -e hg+https://bitbucket.org/openpyxl/openpyxl@2.3#egg=openpyxl

Do not use the 2.4 branch as it is unstable.

Working with virtualenv. Thanks.

Removing version: 2.3.x (automated comment)