Inserting an image not working

Issue #500 resolved
Mathieu
created an issue

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

Not working

Comments (24)

  1. 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

  2. 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)
    
  3. 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)
    
  4. 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.

  5. 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?

  6. 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.

  7. 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.

  8. CharlieC

    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.

  9. Log in to comment