Inserting an image not working

CharlieC

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

2015-07-25T10:22:02+00:00

Mathieu reporter

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

2015-07-27T00:25:32+00:00

Mathieu reporter

Any clue why this is happening?

2015-07-27T20:06:35+00:00

CharlieC

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

2015-07-28T07:57:09+00:00

Mathieu reporter

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

2015-07-28T15:53:47+00:00

CharlieC

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

2015-07-28T16:47:43+00:00

Mathieu reporter

#!/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)

2015-07-28T18:36:37+00:00

Mathieu reporter

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

2015-07-28T18:36:59+00:00

CharlieC

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

2015-07-28T18:38:48+00:00

Mathieu reporter

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)

2015-07-28T18:42:16+00:00

Mathieu reporter

I am confused

2015-07-28T18:42:47+00:00

CharlieC

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.

2015-07-28T18:48:17+00:00

Mathieu reporter

"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?

2015-07-28T18:53:28+00:00

CharlieC

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.

2015-07-28T18:55:29+00:00

Mathieu reporter

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

2015-07-28T19:02:39+00:00

CharlieC

changed status to resolved

Set necessary attributes for images. Resolves ~~#500~~

→ <<cset e0ea42ae894d>>

2015-07-28T19:02:47+00:00

CharlieC

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.