Guide sur les feuilles de calcul Excel en Python avec openpyxl – Real Python

Les feuilles de calcul Excel font partie des problèmes auxquels vous devrez peut-être faire face à un moment donné. C’est soit parce que votre patron les aime, soit parce que le marketing en a besoin, vous devrez peut-être apprendre à travailler avec des feuilles de calcul. openpyxl est très pratique!

Les feuilles de calcul constituent un moyen très intuitif et convivial de manipuler de grands ensembles de données sans arrière-plan technique préalable. C’est pourquoi ils sont encore si couramment utilisés de nos jours.

Dans cet article, vous apprendrez à utiliser openpyxl pour:

• Manipuler des feuilles de calcul Excel en toute confiance
• Extraire des informations à partir de feuilles de calcul
• Créez des feuilles de calcul simples ou plus complexes, notamment en ajoutant des styles, des graphiques, etc.

Cet article est destiné aux développeurs intermédiaires connaissant assez bien les structures de données Python, telles que les dictés et les listes, mais se sentant également à l'aise autour de la POO et de sujets de niveau intermédiaire.

Avant que tu commences

Si vous êtes invité à extraire des données d'une base de données ou d'un fichier journal dans une feuille de calcul Excel, ou si vous devez souvent convertir une feuille de calcul Excel en une forme programmatique plus utilisable, ce didacticiel est fait pour vous. Sautons dans le openpyxl caravane!

Cas d'utilisation pratique

Tout d’abord, quand auriez-vous besoin d’un paquet tel que openpyxl dans un scénario du monde réel? Vous en trouverez quelques exemples ci-dessous, mais en réalité, il existe des centaines de scénarios possibles dans lesquels ces connaissances pourraient vous être utiles.

Importation de nouveaux produits dans une base de données

Vous êtes responsable de la technologie dans une entreprise de magasin en ligne, et votre patron ne veut pas payer pour un système CMS cool et coûteux.

Chaque fois qu'ils souhaitent ajouter de nouveaux produits à la boutique en ligne, ils vous adressent une feuille de calcul Excel comportant quelques centaines de lignes et, pour chacune d'entre elles, vous avez le nom du produit, sa description, son prix, etc.

Maintenant, pour importer les données, vous devrez parcourir chaque ligne de la feuille de calcul et ajouter chaque produit à la boutique en ligne.

Exportation de données de base de données dans une feuille de calcul

Supposons que vous ayez une table de base de données dans laquelle vous enregistrez toutes les informations de vos utilisateurs, y compris le nom, le numéro de téléphone, l’adresse e-mail, etc.

L'équipe marketing souhaite désormais contacter tous les utilisateurs pour leur proposer des offres ou des promotions à prix réduits. Cependant, ils n’ont pas accès à la base de données ou ne savent pas comment utiliser SQL pour extraire facilement ces informations.

Que pouvez-vous faire pour aider? Eh bien, vous pouvez faire un script rapide en utilisant openpyxl qui parcourt chaque enregistrement utilisateur et met toutes les informations essentielles dans une feuille de calcul Excel.

Cela vous rapportera une part de gâteau supplémentaire lors de la prochaine fête d’anniversaire de votre entreprise!

Ajout d'informations à une feuille de calcul existante

Vous devrez peut-être également ouvrir une feuille de calcul, lire les informations qu’elle contient et, selon certaines logiques d’entreprise, y ajouter des données.

Par exemple, en utilisant à nouveau le scénario de la boutique en ligne, supposons que vous obteniez une feuille de calcul Excel avec une liste d'utilisateurs et que vous deviez ajouter à chaque ligne le montant total dépensé dans votre boutique.

Ces données sont dans la base de données et, pour ce faire, vous devez lire la feuille de calcul, parcourir chaque ligne, extraire le montant total dépensé de la base de données, puis écrire dans la feuille de calcul.

Pas de problème pour openpyxl!

Apprentissage de la terminologie de base Excel

Voici une liste rapide de termes de base que vous verrez lorsque vous travaillez avec des feuilles de calcul Excel:

Débuter avec openpyxl

Maintenant que vous connaissez les avantages d’un outil tel que openpyxl, allons-y et commençons par installer le paquet. Pour ce tutoriel, vous devez utiliser Python 3.7 et openpyxl 2.6.2. Pour installer le package, vous pouvez effectuer les opérations suivantes:

Après avoir installé le paquet, vous devriez pouvoir créer une feuille de calcul très simple avec le code suivant:

de openpyxl importation Cahier de travail

classeur = Cahier de travail()
drap = classeur.actif

drap[[[["A1"] = "Bonjour"
drap[[[["B1"] = "monde!"

classeur.enregistrer(nom de fichier="hello_world.xlsx")

Le code ci-dessus devrait créer un fichier appelé hello_world.xlsx dans le dossier que vous utilisez pour exécuter le code. Si vous ouvrez ce fichier avec Excel, vous devriez voir quelque chose comme ceci:

Woohoo, votre premier tableur créé!

Lecture de feuilles de calcul Excel avec openpyxl

Commençons par la tâche la plus essentielle d’une feuille de calcul: lisez-la.

Vous allez passer d’une approche simple à la lecture d’une feuille de calcul à des exemples plus complexes dans lesquels vous lisez les données et les convertissez en structures Python plus utiles.

Jeu de données pour ce tutoriel

Avant de vous plonger dans certains exemples de code, vous devriez télécharger cet exemple de données et le stocker quelque part comme sample.xlsx:

C’est l’un des jeux de données que vous utiliserez tout au long de ce didacticiel. Il s’agit d’un tableur contenant un échantillon de données réelles tirées d’évaluations de produits en ligne d’Amazon. Cet ensemble de données ne représente qu'une infime partie de ce qu'Amazon fournit, mais à des fins de test, il est plus que suffisant.

Une approche simple pour lire une feuille de calcul Excel

Enfin, commençons à lire des feuilles de calcul! Pour commencer, ouvrez notre exemple de feuille de calcul:

>>> de openpyxl importation load_workbook
>>> classeur = load_workbook(nom de fichier="sample.xlsx")
>>> classeur.noms de feuille
['Sheet 1']

>>> drap = classeur.actif
>>> drap


>>> drap.Titre
'Feuille 1'

Dans le code ci-dessus, vous ouvrez d'abord la feuille de calcul sample.xlsx en utilisant load_workbook ()et puis vous pouvez utiliser classeur pour voir toutes les feuilles dont vous disposez pour travailler. Après ça, classeur.active sélectionne la première feuille disponible et, dans ce cas, vous pouvez voir qu'elle sélectionne Feuille 1 automatiquement L’utilisation de ces méthodes est le moyen par défaut d’ouvrir une feuille de calcul et vous le verrez plusieurs fois au cours de ce didacticiel.

Maintenant, après avoir ouvert une feuille de calcul, vous pouvez facilement en récupérer les données comme ceci:

>>> drap[[[["A1"]


>>> drap[[[["A1"].valeur
«marché»

>>> drap[[[["F10"].valeur
"Montre de sport grise pour hommes G-Shock"

Pour renvoyer la valeur réelle d'une cellule, vous devez faire .valeur. Sinon, vous obtiendrez le principal Cellule objet. Vous pouvez également utiliser la méthode .cellule() pour récupérer une cellule en utilisant la notation d'index. N'oubliez pas d'ajouter .valeur pour obtenir la valeur réelle et non un Cellule objet:

>>> drap.cellule(rangée=dix, colonne=6)


>>> drap.cellule(rangée=dix, colonne=6).valeur
"Montre de sport grise pour hommes G-Shock"

Vous pouvez constater que les résultats obtenus sont les mêmes, quelle que soit la méthode choisie. Cependant, dans ce tutoriel, vous utiliserez principalement la première approche: ["A1"].

Ce qui précède vous indique le moyen le plus rapide d’ouvrir une feuille de calcul. Toutefois, vous pouvez transmettre des paramètres supplémentaires pour modifier le mode de chargement d'une feuille de calcul.

Options de lecture supplémentaires

Il y a quelques arguments que vous pouvez transmettre à load_workbook () cela change la façon dont une feuille de calcul est chargée. Les plus importants sont les deux booléens suivants:

1. lecture seulement charge une feuille de calcul en mode lecture seule, ce qui vous permet d'ouvrir de très gros fichiers Excel.
2. data_only ignore les formules de chargement et ne charge que les valeurs résultantes.

Importation de données à partir d'une feuille de calcul

Maintenant que vous avez appris les bases du chargement d’une feuille de calcul, il est temps de passer à la partie amusante: l'itération et l'utilisation réelle des valeurs dans la feuille de calcul.

Dans cette section, vous apprendrez toutes les façons de parcourir les données, mais également de convertir ces données en un élément utilisable et, plus important encore, de le faire de manière pythonique.

Itérer dans les données

Selon vos besoins, vous pouvez parcourir différentes données de différentes manières.

Vous pouvez découper les données avec une combinaison de colonnes et de lignes:

>>> drap[[[["A1: C2"]
((, , ),
    (, , ))

Vous pouvez obtenir des plages de lignes ou de colonnes:

>>> # Obtenir toutes les cellules de la colonne A
>>> drap[[[["UNE"]
(,
 ,
    ...
 ,
 )

>>> # Obtenir toutes les cellules pour une plage de colonnes
>>> drap[[[["UN B"]
((,
  ,
        ...
  ,
  ),
    (,
  ,
        ...
  ,
  ))

>>> # Obtenir toutes les cellules de la rangée 5
>>> drap[[[[5]
(,
 ,
    ...
 ,
 )

>>> # Obtenir toutes les cellules pour une plage de lignes
>>> drap[[[[5:6]
((,
  ,
        ...
  ,
  ),
    (,
  ,
        ...
  ,
  ))

Vous remarquerez que tous les exemples ci-dessus renvoient une tuple. Si vous voulez rafraîchir votre mémoire sur la façon de gérer tuples en Python, consultez l'article sur Listes et Tuples en Python.

Il existe également de nombreuses manières d’utiliser des générateurs Python normaux pour parcourir les données. Les principales méthodes que vous pouvez utiliser pour y parvenir sont:

• .iter_rows ()
• .iter_cols ()

Les deux méthodes peuvent recevoir les arguments suivants:

• min_row
• max_row
• min_col
• max_col

Ces arguments sont utilisés pour définir les limites de l'itération:

>>> pour rangée dans drap.iter_rows(min_row=1,
...                            max_row=2,
...                            min_col=1,
...                            max_col=3):
...     impression(rangée)
(, , )
(, , )


>>> pour colonne dans drap.iter_cols(min_row=1,
...                               max_row=2,
...                               min_col=1,
...                               max_col=3):
...     impression(colonne)
(, )
(, )
(, )

Vous remarquerez que dans le premier exemple, lors d’une itération dans les lignes à l’aide de .iter_rows (), vous en obtenez un tuple élément par ligne sélectionnée. En utilisant .iter_cols () et en parcourant les colonnes, vous obtiendrez un tuple par colonne à la place.

Un argument supplémentaire que vous pouvez transmettre aux deux méthodes est le booléen. valeurs_seulement. Quand il est réglé sur Vrai, les valeurs de la cellule sont renvoyées au lieu de Cellule objet:

>>> pour valeur dans drap.iter_rows(min_row=1,
...                              max_row=2,
...                              min_col=1,
...                              max_col=3,
...                              valeurs_seulement=Vrai):
...     impression(valeur)
('marketplace', 'customer_id', 'review_id')
('US', 3653882, 'R3O9SGZBVQBV76')

Si vous souhaitez parcourir l'ensemble du jeu de données, vous pouvez également utiliser les attributs. .rows ou .columns directement, qui sont des raccourcis pour utiliser .iter_rows () et .iter_cols () sans aucun argument:

>>> pour rangée dans drap.rangées:
...     impression(rangée)
(, , 
...
, , )

Ces raccourcis sont très utiles lorsque vous parcourez l’ensemble du jeu de données.

Manipuler des données en utilisant les structures de données par défaut de Python

Maintenant que vous connaissez les bases de l'itération dans les données d'un classeur, voyons des moyens intelligents de convertir ces données en structures Python.

Comme vous l'avez vu plus tôt, le résultat de toutes les itérations se présente sous la forme: tuples. Cependant, depuis tuple n'est rien d'autre qu'un immuable liste, vous pouvez facilement accéder à ses données et les transformer en d’autres structures.

Par exemple, supposons que vous souhaitiez extraire des informations sur le produit du sample.xlsx feuille de calcul et dans un dictionnaire où chaque clé est un ID de produit.

Une façon simple de procéder consiste à parcourir toutes les lignes, à sélectionner les colonnes liées aux informations sur le produit, puis à les stocker dans un dictionnaire. Laissez-nous coder ceci!

Tout d’abord, jetez un coup d’œil aux en-têtes et voyez les informations qui vous intéressent le plus:

>>> pour valeur dans drap.iter_rows(min_row=1,
...                              max_row=1,
...                              valeurs_seulement=Vrai):
...     impression(valeur)
('marketplace', 'customer_id', 'review_id', 'product_id', ...)

Ce code retourne une liste de tous les noms de colonnes que vous avez dans la feuille de calcul. Pour commencer, prenez les colonnes avec les noms:

• product_id
• produit_parent
• product_title
• catégorie de produit

Heureusement pour vous, les colonnes dont vous avez besoin sont toutes juxtaposées, vous pouvez donc utiliser les min_column et max_column pour obtenir facilement les données que vous voulez:

>>> pour valeur dans drap.iter_rows(min_row=2,
...                              min_col=4,
...                              max_col=7,
...                              valeurs_seulement=Vrai):
...     impression(valeur)
('B00FALQ1ZC', 937001370, 'Invicta Women ' s '15150 "Angel" 18k jaune ...)
('B00D3RGO20', 484010722, "KC4944 féminin de Kenneth Cole New York ...)
...

Agréable! Maintenant que vous savez comment obtenir toutes les informations importantes sur le produit dont vous avez besoin, plaçons ces données dans un dictionnaire:

importation JSON
de openpyxl importation load_workbook

classeur = load_workbook(nom de fichier="sample.xlsx")
drap = classeur.actif

des produits = 

# Utilisation de values_only car vous voulez renvoyer les valeurs des cellules
pour rangée dans drap.iter_rows(min_row=2,
                           min_col=4,
                           max_col=7,
                           valeurs_seulement=Vrai):
    product_id = rangée[[[[0]
    produit = 
        "parent": rangée[[[[1],
        "Titre": rangée[[[[2],
        "Catégorie": rangée[[[[3]
    
    des produits[[[[product_id] = produit

# Utiliser json ici pour pouvoir formater la sortie pour l'afficher plus tard
impression(JSON.décharges(des produits))

Le code ci-dessus renvoie un JSON semblable à ceci:

  "B00FALQ1ZC": 
    "parent": 937001370,
    "Titre": "Invicta 15150 femmes ...",
    "Catégorie": "Montres"
  ,
  "B00D3RGO20": 
    "parent": 484010722,
    "Titre": "Kenneth Cole New York ...",
    "Catégorie": "Montres"
  

Ici, vous pouvez voir que la sortie est limitée à 2 produits seulement, mais si vous exécutez le script tel quel, vous devriez obtenir 98 produits.

Convertir des données en classes Python

Pour finaliser la section de lecture de ce didacticiel, explorons les classes Python et voyons comment vous pouvez améliorer l’exemple ci-dessus et structurer davantage les données.

Pour cela, vous utiliserez les nouvelles classes de données Python disponibles à partir de Python 3.7. Si vous utilisez une version plus ancienne de Python, vous pouvez utiliser les classes par défaut à la place.

Commençons par examiner vos données et décider de ce que vous souhaitez stocker et de la manière dont vous souhaitez les stocker.

Comme vous l'avez vu au tout début, ces données proviennent d'Amazon et constituent une liste de critiques de produits. Vous pouvez consulter la liste de toutes les colonnes et leur signification sur Amazon.

Vous pouvez extraire deux éléments importants des données disponibles:

1. Des produits
2. Avis

UNE Produit a:

le La revue a quelques champs de plus:

• ID
• N ° de client
• Étoiles
• Gros titre
• Corps
• Rendez-vous amoureux

Vous pouvez ignorer quelques champs de révision pour simplifier un peu les choses.

Ainsi, une implémentation directe de ces deux classes pourrait être écrite dans un fichier séparé classes.py:

importation date / heure
de classes de données importation classe de données

@dataclass
classe Produit:
    identifiant: str
    parent: str
    Titre: str
    Catégorie: str

@dataclass
classe La revue:
    identifiant: str
    N ° de client: str
    étoiles: int
    gros titre: str
    corps: str
    rendez-vous amoureux: date / heure.date / heure

Après avoir défini vos classes de données, vous devez convertir les données de la feuille de calcul en ces nouvelles structures.

Avant de procéder à la conversion, il convient de consulter à nouveau notre en-tête et de créer un mappage entre les colonnes et les champs dont vous avez besoin:

>>> pour valeur dans drap.iter_rows(min_row=1,
...                              max_row=1,
...                              valeurs_seulement=Vrai):
...     impression(valeur)
('marketplace', 'customer_id', 'review_id', 'product_id', ...)

>>> # Ou une alternative
>>> pour cellule dans drap[[[[1]:
...     impression(cellule.valeur)
marché
N ° de client
review_id
product_id
produit_parent
...

Créons un fichier mapping.py où vous avez une liste de tous les noms de champs et leur emplacement de colonne (index zéro) sur la feuille de calcul:

# Champs de produit
PRODUCT_ID = 3
PRODUCT_PARENT = 4
PRODUCT_TITLE = 5
CATÉGORIE DE PRODUIT = 6

# Champs d'examen
REVIEW_ID = 2
REVIEW_CUSTOMER = 1
REVIEW_STARS = 7
REVIEW_HEADLINE = 12
REVIEW_BODY = 13
DATE DE RÉVISION = 14

Vous n’avez pas nécessairement à faire la cartographie ci-dessus. C’est davantage pour la lisibilité lors de l’analyse des données de ligne, de sorte que vous ne vous retrouvez pas avec beaucoup de nombres magiques qui traînent.

Enfin, examinons le code nécessaire pour analyser les données de la feuille de calcul dans une liste d’objets de produit et de révision:

de date / heure importation date / heure
de openpyxl importation load_workbook
de Des classes importation Produit, La revue
de cartographie importation PRODUCT_ID, PRODUCT_PARENT, PRODUCT_TITLE, 
    CATÉGORIE DE PRODUIT, DATE DE RÉVISION, REVIEW_ID, REVIEW_CUSTOMER, 
    REVIEW_STARS, REVIEW_HEADLINE, REVIEW_BODY

# Utilisation de la méthode read_only car vous n'allez pas modifier la feuille de calcul
classeur = load_workbook(nom de fichier="sample.xlsx", lecture seulement=Vrai)
drap = classeur.actif

des produits = []
avis = []

# Utilisation de values_only car vous souhaitez simplement renvoyer la valeur de la cellule
pour rangée dans drap.iter_rows(min_row=2, valeurs_seulement=Vrai):
    produit = Produit(identifiant=rangée[[[[PRODUCT_ID],
                      parent=rangée[[[[PRODUCT_PARENT],
                      Titre=rangée[[[[PRODUCT_TITLE],
                      Catégorie=rangée[[[[CATÉGORIE DE PRODUIT])
    des produits.ajouter(produit)

    # Vous devez analyser la date de la feuille de calcul dans un format datetime
    spread_date = rangée[[[[DATE DE RÉVISION]
    parsed_date = date / heure.temps de repos(spread_date, "% Y-% m-%ré")

    la revue = La revue(identifiant=rangée[[[[REVIEW_ID],
                    N ° de client=rangée[[[[REVIEW_CUSTOMER],
                    étoiles=rangée[[[[REVIEW_STARS],
                    gros titre=rangée[[[[REVIEW_HEADLINE],
                    corps=rangée[[[[REVIEW_BODY],
                    rendez-vous amoureux=parsed_date)
    avis.ajouter(la revue)

impression(des produits[[[[0])
impression(avis[[[[0])

Après avoir exécuté le code ci-dessus, vous devriez obtenir une sortie comme celle-ci:

Produit(identifiant='B00FALQ1ZC', parent=937001370, ...)
La revue(identifiant='R3O9SGZBVQBV76', N ° de client=3653882, ...)

C'est tout! Vous devriez maintenant avoir les données dans un format de classe très simple et compréhensible, et vous pouvez commencer à penser à les stocker dans une base de données ou tout autre type de stockage de données que vous aimez.

L'utilisation de ce type de stratégie POO pour analyser des feuilles de calcul simplifie la gestion des données beaucoup plus facilement par la suite.

Ajout de nouvelles données

Avant de commencer à créer des feuilles de calcul très complexes, jetez un coup d'œil à un exemple montrant comment ajouter des données à une feuille de calcul existante.

Retournez au premier exemple de feuille de calcul que vous avez créé (hello_world.xlsx) et essayez de l’ouvrir et d’y ajouter des données, comme ceci:

de openpyxl importation load_workbook

# Commencez par ouvrir la feuille de calcul et sélectionnez la feuille principale.
classeur = load_workbook(nom de fichier="hello_world.xlsx")
drap = classeur.actif

# Écrivez ce que vous voulez dans une cellule spécifique
drap[[[["C1"] = "l'écriture ;)"

# Sauvegarder la feuille de calcul
classeur.enregistrer(nom de fichier="hello_world_append.xlsx"

Et voilà, si vous ouvrez le nouveau hello_world_append.xlsx feuille de calcul, vous verrez le changement suivant:

Remarquez le supplément l'écriture 😉 sur cellule C1.

Écrire des feuilles de calcul Excel avec openpyxl

Vous pouvez écrire sur une feuille de calcul de nombreuses choses différentes, du simple texte ou des valeurs numériques aux formules complexes, graphiques ou même images.

Commençons par créer des feuilles de calcul!

Créer une feuille de calcul simple

Auparavant, vous avez vu un exemple très rapide expliquant comment écrire «Hello world!» Dans un tableur. Vous pouvez donc commencer par cela:

    1 de openpyxl importation Cahier de travail
    2 
    3 nom de fichier = "hello_world.xlsx"
    4 
    5 classeur = Cahier de travail()
    6 drap = classeur.actif
    7 
    8 drap[[[["A1"] = "Bonjour"
    9 drap[[[["B1"] = "monde!"
dix 
11 classeur.enregistrer(nom de fichier=nom de fichier)

Les lignes en surbrillance dans le code ci-dessus sont les plus importantes pour l’écriture. Dans le code, vous pouvez voir que:

• Ligne 5 vous montre comment créer un nouveau classeur vide.
• Lignes 8 et 9 vous montrer comment ajouter des données à des cellules spécifiques.
• Ligne 11 vous montre comment enregistrer la feuille de calcul lorsque vous avez terminé.

Même si ces lignes ci-dessus peuvent être simples, il est toujours bon de bien les connaître lorsque la situation devient un peu plus compliquée.

Une chose que vous pouvez faire pour vous aider avec les exemples de code à venir est d’ajouter la méthode suivante à votre console ou votre fichier Python:

>>> def print_rows():
...     pour rangée dans drap.iter_rows(valeurs_seulement=Vrai):
...         impression(rangée)

Il est plus facile d’imprimer toutes les valeurs de votre feuille de calcul en appelant simplement print_rows ().

Opérations de base sur les feuilles de calcul

Avant de vous lancer dans des sujets plus avancés, il est bon que vous sachiez comment gérer les éléments les plus simples d’une feuille de calcul.

Ajout et mise à jour de valeurs de cellules

Vous avez déjà appris comment ajouter des valeurs à une feuille de calcul comme ceci:

>>> drap[[[["A1"] = "valeur"

Pour ce faire, vous pouvez également sélectionner une cellule, puis en modifier la valeur:

>>> cellule = drap[[[["A1"]
>>> cellule


>>> cellule.valeur
'Bonjour'

>>> cellule.valeur = "Hey"
>>> cellule.valeur
'Hey'

La nouvelle valeur est uniquement stockée dans la feuille de calcul lorsque vous appelez. classeur.save ().

le openpyxl crée une cellule lors de l'ajout d'une valeur, si cette cellule n'existait pas auparavant:

>>> # Avant, notre feuille de calcul n'a qu'une seule ligne
>>> print_rows()
('Bonjour le monde!')

>>> # Essayez d'ajouter une valeur à la ligne 10
>>> drap[[[["B10"] = "tester"
>>> print_rows()
('Bonjour le monde!')
(Aucun, aucun)
(Aucun, aucun)
(Aucun, aucun)
(Aucun, aucun)
(Aucun, aucun)
(Aucun, aucun)
(Aucun, aucun)
(Aucun, aucun)
(Aucun, 'test')

Comme vous pouvez le voir, lorsque vous essayez d'ajouter une valeur à une cellule B10, vous vous retrouvez avec un tuple avec 10 lignes, juste pour que vous puissiez avoir ça tester valeur.

Gestion des lignes et des colonnes

L'ajout ou la suppression de lignes et de colonnes est l'une des tâches les plus courantes que vous devez effectuer lors de la manipulation de feuilles de calcul. le openpyxl package vous permet de le faire de manière très simple en utilisant les méthodes suivantes:

• .insert_rows ()
• .delete_rows ()
• .insert_cols ()
• .delete_cols ()

Chacune de ces méthodes peut recevoir deux arguments:

1. idx
2. montant

En utilisant notre base hello_world.xlsx Encore un exemple, voyons comment ces méthodes fonctionnent:

>>> print_rows()
('Bonjour le monde!')

>>> # Insérer une colonne avant la colonne 1 existante ("A")
>>> drap.insert_cols(idx=1)
>>> print_rows()
(Aucun, 'bonjour', 'monde!')

>>> # Insérer 5 colonnes entre la colonne 2 ("B") et 3 ("C")
>>> drap.insert_cols(idx=3, montant=5)
>>> print_rows()
(Aucun, 'bonjour', aucun, aucun, aucun, aucun, aucun, aucun, aucun, 'monde!')

>>> # Supprimer les colonnes créées
>>> drap.delete_cols(idx=3, montant=5)
>>> drap.delete_cols(idx=1)
>>> print_rows()
('Bonjour le monde!')

>>> # Insérer une nouvelle ligne au début
>>> drap.insert_rows(idx=1)
>>> print_rows()
(Aucun, aucun)
('Bonjour le monde!')

>>> # Insère 3 nouvelles lignes au début
>>> drap.insert_rows(idx=1, montant=3)
>>> print_rows()
(Aucun, aucun)
(Aucun, aucun)
(Aucun, aucun)
(Aucun, aucun)
('Bonjour le monde!')

>>> # Supprimer les 4 premières lignes
>>> drap.delete_rows(idx=1, montant=4)
>>> print_rows()
('Bonjour le monde!')

La seule chose à retenir est que lors de l'insertion de nouvelles données (lignes ou colonnes), l'insertion a lieu avant le idx paramètre.

Donc, si vous le faites insert_rows (1), il insère une nouvelle ligne avant la première rangée existante.

C’est la même chose pour les colonnes: quand vous appelez insert_cols (2), il insère une nouvelle colonne à droite avant la deuxième colonne déjà existante (B).

Cependant, lors de la suppression de lignes ou de colonnes, .effacer_... supprime les données partant de l'index passé en argument.

Par exemple, en faisant delete_rows (2) il supprime la ligne 2et en faisant delete_cols (3) il supprime la troisième colonne (C).

Gestion des feuilles

La gestion des feuilles est également l’une des choses que vous pourriez avoir besoin de savoir, même si c’est quelque chose que vous n’utilisez pas souvent.

Si vous regardez les exemples de code de ce tutoriel, vous remarquerez le code récurrent suivant:

C'est le moyen de sélectionner la feuille par défaut dans une feuille de calcul. Toutefois, si vous ouvrez une feuille de calcul avec plusieurs feuilles, vous pouvez toujours en sélectionner une spécifique, comme ceci:

>>> # Disons que vous avez deux feuilles: "Produits" et "Ventes de l'entreprise"
>>> classeur.noms de feuille
['Products', 'Company Sales']

>>> # Vous pouvez sélectionner une feuille en utilisant son titre
>>> fiche_produits = classeur[[[["Des produits"]
>>> sales_sheet = classeur[[[["Ventes de l'entreprise"]

Vous pouvez également changer le titre d'une feuille très facilement:

>>> classeur.noms de feuille
['Products', 'Company Sales']

>>> fiche_produits = classeur[[[["Des produits"]
>>> fiche_produits.Titre = "Nouveaux produits"

>>> classeur.noms de feuille
['New Products', 'Company Sales']

Si vous souhaitez créer ou supprimer des feuilles, vous pouvez également le faire avec .create_sheet () et .retirer():

>>> classeur.noms de feuille
['Products', 'Company Sales']

>>> feuille_opérations = classeur.create_sheet("Opérations")
>>> classeur.noms de feuille
['Products', 'Company Sales', 'Operations']

>>> # Vous pouvez également définir la position pour créer la feuille à
>>> hr_sheet = classeur.create_sheet("HEURE", 0)
>>> classeur.noms de feuille
['HR', 'Products', 'Company Sales', 'Operations']

>>> # Pour les supprimer, il suffit de passer la feuille comme argument à la .remove ()
>>> classeur.retirer(feuille_opérations)
>>> classeur.noms de feuille
['HR', 'Products', 'Company Sales']

>>> classeur.retirer(hr_sheet)
>>> classeur.noms de feuille
['Products', 'Company Sales']

Une autre chose que vous pouvez faire est de dupliquer une feuille en utilisant copy_worksheet ():

>>> classeur.noms de feuille
['Products', 'Company Sales']

>>> fiche_produits = classeur[[[["Des produits"]
>>> classeur.copy_worksheet(fiche_produits)


>>> classeur.noms de feuille
['Products', 'Company Sales', 'Products Copy']

Si vous ouvrez votre feuille de calcul après avoir enregistré le code ci-dessus, vous remarquerez que la feuille Copie de produits est un duplicata de la feuille Des produits.

Gel des rangées et des colonnes

Lorsque vous travaillez avec de grandes feuilles de calcul, vous pouvez également geler quelques lignes ou colonnes afin qu'elles restent visibles lorsque vous faites défiler vers la droite ou vers le bas.

Le gel des données vous permet de garder un œil sur les lignes ou les colonnes importantes, quel que soit l’endroit où vous faites défiler la feuille de calcul.

Encore, openpyxl a également un moyen d'y parvenir en utilisant la feuille de travail freeze_panes attribut. Pour cet exemple, retournez à notre sample.xlsx feuille de calcul et essayez ce qui suit:

>>> classeur = load_workbook(nom de fichier="sample.xlsx")
>>> drap = classeur.actif
>>> drap.freeze_panes = "C2"
>>> classeur.enregistrer("sample_frozen.xlsx")

Si vous ouvrez le sample_frozen.xlsx feuille de calcul dans votre éditeur de feuille de calcul préféré, vous remarquerez que 1 et des colonnes UNE et B sont figés et sont toujours visibles quel que soit l'endroit où vous naviguez dans la feuille de calcul.

Cette fonctionnalité est pratique, par exemple, pour que les en-têtes restent visibles, de sorte que vous sachiez toujours ce que chaque colonne représente.

Voici à quoi cela ressemble dans l’éditeur:

Remarquez comment vous vous trouvez à la fin de la feuille de calcul. Pourtant, vous pouvez voir les deux lignes. 1 et des colonnes UNE et B.

Ajout de filtres

Vous pouvez utiliser openpyxl ajouter des filtres et des tris à votre feuille de calcul. Toutefois, lorsque vous ouvrez la feuille de calcul, les données ne seront pas réorganisées en fonction de ces tris et filtres.

Au début, cela peut sembler une fonctionnalité plutôt inutile, mais lorsque vous créez par programme une feuille de calcul qui sera envoyée et utilisée par quelqu'un d'autre, il est toujours agréable de créer au moins les filtres et de permettre aux utilisateurs de l'utiliser ultérieurement.

Le code ci-dessous est un exemple de la manière dont vous ajouteriez des filtres à notre sample.xlsx tableur:

>>> # Vérifiez l'espace utilisé dans la feuille de calcul à l'aide de l'attribut "dimensions"
>>> drap.dimensions
'A1: O100'

>>> drap.filtre automatique.ref = "A1: O100"
>>> classeur.enregistrer(nom de fichier="sample_with_filters.xlsx")

Vous devriez maintenant voir les filtres créés lors de l’ouverture de la feuille de calcul dans votre éditeur:

Vous n'êtes pas obligé d'utiliser feuille.dimensions si vous savez précisément à quelle partie de la feuille de calcul vous souhaitez appliquer des filtres.

Ajout de formules

Formules (ou formules) sont l’une des fonctionnalités les plus puissantes des feuilles de calcul.

Ils vous donnent le pouvoir d'appliquer des équations mathématiques spécifiques à une plage de cellules. Utiliser des formules avec openpyxl est aussi simple que l'édition de la valeur d'une cellule.

Vous pouvez voir la liste des formules supportées par openpyxl:

>>> de openpyxl.utils importation Formules
>>> Formules
frozenset ('ABS',
                                            'ACCRINT',
                                            'ACCRINTM',
                                            'ACOS',
                                            'ACOSH',
                                            'AMORDEGRC',
                                            'AMORLINC',
                                            'ET',
                                            ...
                                            'YEARFRAC',
                                            'RENDEMENT',
                                            'YIELDDISC',
                                            'YIELDMAT',
                                            'ZTEST')

Ajoutons quelques formules à notre sample.xlsx tableur.

Commençons par quelque chose de facile, vérifions le nombre moyen d'étoiles des 99 avis figurant dans le tableur:

>>> # Le nombre d'étoiles est la colonne "H"
>>> drap[[[["P2"] = "= MOYENNE (H2: H100)"
>>> classeur.enregistrer(nom de fichier="sample_formulas.xlsx")

Si vous ouvrez la feuille de calcul maintenant et allez dans la cellule P2, vous devriez voir que sa valeur est: 4.18181818181818. Jetez un coup d'œil dans l'éditeur:

Vous pouvez utiliser la même méthodologie pour ajouter des formules à votre feuille de calcul. Par exemple, comptons le nombre de commentaires ayant suscité des votes utiles:

>>> # Les votes utiles sont comptés dans la colonne "I"
>>> drap[[[["P3"] = '= COUNTIF (I2: I100, "> 0")'
>>> classeur.enregistrer(nom de fichier="sample_formulas.xlsx")

Vous devriez obtenir le numéro 21 sur votre P3 cellule de feuille de calcul comme suit:

Vous devez vous assurer que les chaînes d'une formule sont toujours entre guillemets. Vous devez donc utiliser des guillemets simples autour de la formule, comme dans l'exemple ci-dessus, ou vous devrez échapper les guillemets à l'intérieur de la formule: "= COUNTIF (I2: I100, "> 0 ")".

Il existe une tonne d'autres formules que vous pouvez ajouter à votre feuille de calcul en utilisant la même procédure que celle décrite précédemment. Essayez vous-même!

Ajout de styles

Même si le style d’une feuille de calcul n’est pas quelque chose que vous feriez tous les jours, il est toujours bon de savoir comment le faire.

En utilisant openpyxl, vous pouvez appliquer plusieurs options de style à votre feuille de calcul, notamment les polices, les bordures, les couleurs, etc. Regardez le openpyxl documentation pour en savoir plus.

Vous pouvez également choisir d'appliquer un style directement à une cellule ou de créer un modèle et de le réutiliser pour appliquer des styles à plusieurs cellules.

Commençons par examiner le style simple des cellules, en utilisant notre sample.xlsx à nouveau comme feuille de calcul de base:

>>> # Importer les classes de style nécessaires
>>> de openpyxl.styles importation Police de caractère, Couleur, Alignement, Frontière, Côté, couleurs

>>> # Créer quelques styles
>>> caractère gras = Police de caractère(audacieux=Vrai)
>>> big_red_text = Police de caractère(Couleur=couleurs.ROUGE, Taille=20)
>>> center_aligned_text = Alignement(horizontal="centre")
>>> double_border_side = Côté(style de bordure="double")
>>> square_border = Frontière(Haut=double_border_side,
...                        droite=double_border_side,
...                        bas=double_border_side,
...                        la gauche=double_border_side)

>>> # Style des cellules!
>>> drap[[[["A2"].Police de caractère = caractère gras
>>> drap[[[["A3"].Police de caractère = big_red_text
>>> drap[[[["A4"].alignement = center_aligned_text
>>> drap[[[["A5"].frontière = square_border
>>> classeur.enregistrer(nom de fichier="sample_styles.xlsx")

Si vous ouvrez votre feuille de calcul maintenant, vous devriez voir pas mal de styles différents sur les 5 premières cellules de la colonne UNE:

Voilà. Vous avez:

• A2 avec le texte en gras
• A3 avec le texte en rouge et une taille de police plus grande
• A4 avec le texte centré
• A5 avec une bordure carrée autour du texte

Vous pouvez également combiner des styles en les ajoutant simplement à la cellule en même temps:

>>> # Réutilisation des mêmes styles de l'exemple ci-dessus
>>> drap[[[["A6"].alignement = center_aligned_text
>>> drap[[[["A6"].Police de caractère = big_red_text
>>> drap[[[["A6"].frontière = square_border
>>> classeur.enregistrer(nom de fichier="sample_styles.xlsx")

Regarde la cellule A6 ici:

Lorsque vous souhaitez appliquer plusieurs styles à une ou plusieurs cellules, vous pouvez utiliser un StyleNommé class à la place, qui est comme un modèle de style que vous pouvez utiliser encore et encore. Regardez l'exemple ci-dessous:

>>> de openpyxl.styles importation StyleNommé

>>> # Créons un modèle de style pour la ligne d'en-tête
>>> entête = StyleNommé(prénom="entête")
>>> entête.Police de caractère = Police de caractère(audacieux=Vrai)
>>> entête.frontière = Frontière(bas=Côté(style de bordure="mince"))
>>> entête.alignement = Alignement(horizontal="centre", verticale="centre")

>>> # Appliquons maintenant ceci à toutes les cellules de la première ligne (en-tête)
>>> header_row = drap[[[[1]
>>> pour cellule dans header_row:
...     cellule.style = entête

>>> classeur.enregistrer(filename="sample_styles.xlsx")

If you open the spreadsheet now, you should see that its first row is bold, the text is aligned to the center, and there’s a small bottom border! Have a look below:

As you saw above, there are many options when it comes to styling, and it depends on the use case, so feel free to check openpyxl documentation and see what other things you can do.

Conditional Formatting

This feature is one of my personal favorites when it comes to adding styles to a spreadsheet.

It’s a much more powerful approach to styling because it dynamically applies styles according to how the data in the spreadsheet changes.

In a nutshell, mise en forme conditionnelle allows you to specify a list of styles to apply to a cell (or cell range) according to specific conditions.

For example, a widespread use case is to have a balance sheet where all the negative totals are in red, and the positive ones are in green. This formatting makes it much more efficient to spot good vs bad periods.

Without further ado, let’s pick our favorite spreadsheet—sample.xlsx—and add some conditional formatting.

You can start by adding a simple one that adds a red background to all reviews with less than 3 stars:

>>> de openpyxl.styles importation PatternFill, couleurs
>>> de openpyxl.styles.differential importation DifferentialStyle
>>> de openpyxl.formatting.rule importation Rule

>>> red_background = PatternFill(bgColor=couleurs.RED)
>>> diff_style = DifferentialStyle(remplir=red_background)
>>> règle = Rule(type="expression", dxf=diff_style)
>>> règle.formule = [[[["$H1<3"]
>>> drap.conditional_formatting.ajouter("A1:O100", règle)
>>> classeur.enregistrer("sample_conditional_formatting.xlsx.xlsx")

Now you’ll see all the reviews with a star rating below 3 marked with a red background:

Code-wise, the only things that are new here are the objects DifferentialStyle et Rule:

• DifferentialStyle is quite similar to NamedStyle, which you already saw above, and it’s used to aggregate multiple styles such as fonts, borders, alignment, and so forth.
• Rule is responsible for selecting the cells and applying the styles if the cells match the rule’s logic.

Utilisant un Rule object, you can create numerous conditional formatting scenarios.

However, for simplicity sake, the openpyxl package offers 3 built-in formats that make it easier to create a few common conditional formatting patterns. These built-ins are:

• ColorScale
• IconSet
• DataBar

le ColorScale gives you the ability to create color gradients:

>>> de openpyxl.formatting.rule importation ColorScaleRule
>>> color_scale_rule = ColorScaleRule(start_type="min",
...                                   start_color=couleurs.RED,
...                                   end_type="max",
...                                   end_color=couleurs.GREEN)

>>> # Again, let's add this gradient to the star ratings, column "H"
>>> drap.conditional_formatting.ajouter("H2:H100", color_scale_rule)
>>> classeur.enregistrer(filename="sample_conditional_formatting_color_scale.xlsx")

Now you should see a color gradient on column H, from red to green, according to the star rating:

You can also add a third color and make two gradients instead:

>>> de openpyxl.formatting.rule importation ColorScaleRule
>>> color_scale_rule = ColorScaleRule(start_type="num",
...                                   start_value=1,
...                                   start_color=couleurs.RED,
...                                   mid_type="num",
...                                   mid_value=3,
...                                   mid_color=couleurs.YELLOW,
...                                   end_type="num",
...                                   end_value=5,
...                                   end_color=couleurs.GREEN)

>>> # Again, let's add this gradient to the star ratings, column "H"
>>> drap.conditional_formatting.ajouter("H2:H100", color_scale_rule)
>>> classeur.enregistrer(filename="sample_conditional_formatting_color_scale_3.xlsx")

This time, you’ll notice that star ratings between 1 and 3 have a gradient from red to yellow, and star ratings between 3 and 5 have a gradient from yellow to green:

le IconSet allows you to add an icon to the cell according to its value:

>>> de openpyxl.formatting.rule importation IconSetRule

>>> icon_set_rule = IconSetRule("5Arrows", "num", [[[[1, 2, 3, 4, 5])
>>> drap.conditional_formatting.ajouter("H2:H100", icon_set_rule)
>>> classeur.enregistrer("sample_conditional_formatting_icon_set.xlsx")

You’ll see a colored arrow next to the star rating. This arrow is red and points down when the value of the cell is 1 and, as the rating gets better, the arrow starts pointing up and becomes green:

le openpyxl package has a full list of other icons you can use, besides the arrow.

Finally, the DataBar allows you to create progress bars:

>>> de openpyxl.formatting.rule importation DataBarRule

>>> data_bar_rule = DataBarRule(start_type="num",
...                             start_value=1,
...                             end_type="num",
...                             end_value="5",
...                             Couleur=couleurs.GREEN)
>>> drap.conditional_formatting.ajouter("H2:H100", data_bar_rule)
>>> classeur.enregistrer("sample_conditional_formatting_data_bar.xlsx")

You’ll now see a green progress bar that gets fuller the closer the star rating is to the number 5:

As you can see, there are a lot of cool things you can do with conditional formatting.

Here, you saw only a few examples of what you can achieve with it, but check the openpyxl documentation to see a bunch of other options.

Adding Images

Even though images are not something that you’ll often see in a spreadsheet, it’s quite cool to be able to add them. Maybe you can use it for branding purposes or to make spreadsheets more personal.

To be able to load images to a spreadsheet using openpyxl, you’ll have to install Oreiller:

Apart from that, you’ll also need an image. For this example, you can grab the Real Python logo below and convert it from .webp à .png using an online converter such as cloudconvert.com, save the final file as logo.png, and copy it to the root folder where you’re running your examples:

Afterward, this is the code you need to import that image into the hello_word.xlsx spreadsheet:

de openpyxl importation load_workbook
de openpyxl.drawing.image importation Image

# Let's use the hello_world spreadsheet since it has less data
classeur = load_workbook(filename="hello_world.xlsx")
drap = classeur.actif

logo = Image("logo.png")

# A bit of resizing to not fill the whole spreadsheet with the logo
logo.la taille = 150
logo.largeur = 150

drap.add_image(logo, "A3")
classeur.enregistrer(filename="hello_world_logo.xlsx")

You have an image on your spreadsheet! Here it is:

The image’s left top corner is on the cell you chose, in this case, A3.

Adding Pretty Charts

Another powerful thing you can do with spreadsheets is create an incredible variety of charts.

Charts are a great way to visualize and understand loads of data quickly. There are a lot of different chart types: bar chart, pie chart, line chart, and so on. openpyxl has support for a lot of them.

Here, you’ll see only a couple of examples of charts because the theory behind it is the same for every single chart type:

For any chart you want to build, you’ll need to define the chart type: BarChart, LineChart, and so forth, plus the data to be used for the chart, which is called Référence.

Before you can build your chart, you need to define what data you want to see represented in it. Sometimes, you can use the dataset as is, but other times you need to massage the data a bit to get additional information.

Let’s start by building a new workbook with some sample data:

    1 de openpyxl importation Cahier de travail
    2 de openpyxl.chart importation BarChart, Référence
    3 
    4 classeur = Cahier de travail()
    5 drap = classeur.actif
    6 
    7 # Let's create some sample sales data
    8 rangées = [[[[
    9     [[[["Product", "Online", "Store"],
dix     [[[[1, 30, 45],
11     [[[[2, 40, 30],
12     [[[[3, 40, 25],
13     [[[[4, 50, 30],
14     [[[[5, 30, 25],
15     [[[[6, 25, 35],
16     [[[[7, 20, 40],
17 ]
18 
19 pour rangée dans rangées:
20     drap.ajouter(rangée)

Now you’re going to start by creating a diagramme à bandes that displays the total number of sales per product:

22 graphique = BarChart()
23 Les données = Référence(feuille de travail=drap,
24                  min_row=1,
25                  max_row=8,
26                  min_col=2,
27                  max_col=3)
28 
29 graphique.add_data(Les données, titles_from_data=True)
30 drap.add_chart(graphique, "E2")
31 
32 classeur.enregistrer("chart.xlsx")

There you have it. Below, you can see a very straightforward bar chart showing the difference between en ligne product sales online and in-store product sales:

Like with images, the top left corner of the chart is on the cell you added the chart to. In your case, it was on cell E2.

Try creating a graphique en ligne instead, changing the data a bit:

    1 importation au hasard
    2 de openpyxl importation Cahier de travail
    3 de openpyxl.chart importation LineChart, Référence
    4 
    5 classeur = Cahier de travail()
    6 drap = classeur.actif
    7 
    8 # Let's create some sample sales data
    9 rangées = [[[[
dix     [[[["", "January", "February", "March", "April",
11     "May", "June", "July", "August", "September",
12      "October", "November", "December"],
13     [[[[1, ],
14     [[[[2, ],
15     [[[[3, ],
16 ]
17 
18 pour rangée dans rangées:
19     drap.ajouter(rangée)
20 
21 pour rangée dans drap.iter_rows(min_row=2,
22                            max_row=4,
23                            min_col=2,
24                            max_col=13):
25     pour cellule dans rangée:
26         cellule.valeur = au hasard.randrange(5, 100)

With the above code, you’ll be able to generate some random data regarding the sales of 3 different products across a whole year.

Once that’s done, you can very easily create a line chart with the following code:

28 graphique = LineChart()
29 Les données = Référence(feuille de travail=drap,
30                  min_row=2,
31                  max_row=4,
32                  min_col=1,
33                  max_col=13)
34 
35 graphique.add_data(Les données, from_rows=True, titles_from_data=True)
36 drap.add_chart(graphique, "C6")
37 
38 classeur.enregistrer("line_chart.xlsx")

Here’s the outcome of the above piece of code:

One thing to keep in mind here is the fact that you’re using from_rows=True when adding the data. This argument makes the chart plot row by row instead of column by column.

In your sample data, you see that each product has a row with 12 values (1 column per month). That’s why you use from_rows. If you don’t pass that argument, by default, the chart tries to plot by column, and you’ll get a month-by-month comparison of sales.

Another difference that has to do with the above argument change is the fact that our Référence now starts from the first column, min_col=1, instead of the second one. This change is needed because the chart now expects the first column to have the titles.

There are a couple of other things you can also change regarding the style of the chart. For example, you can add specific categories to the chart:

chats = Référence(feuille de travail=drap,
                 min_row=1,
                 max_row=1,
                 min_col=2,
                 max_col=13)
graphique.set_categories(chats)

Add this piece of code before saving the workbook, and you should see the month names appearing instead of numbers:

Code-wise, this is a minimal change. But in terms of the readability of the spreadsheet, this makes it much easier for someone to open the spreadsheet and understand the chart straight away.

Another thing you can do to improve the chart readability is to add an axis. You can do it using the attributes x_axis et y_axis:

graphique.x_axis.Titre = "Months"
graphique.y_axis.Titre = "Sales (per unit)"

This will generate a spreadsheet like the below one:

As you can see, small changes like the above make reading your chart a much easier and quicker task.

There is also a way to style your chart by using Excel’s default ChartStyle propriété. In this case, you have to choose a number between 1 and 48. Depending on your choice, the colors of your chart change as well:

# You can play with this by choosing any number between 1 and 48
graphique.style = 24

With the style selected above, all lines have some shade of orange:

There is no clear documentation on what each style number looks like, but this spreadsheet has a few examples of the styles available.

importation au hasard
de openpyxl importation Cahier de travail
de openpyxl.chart importation LineChart, Référence

classeur = Cahier de travail()
drap = classeur.actif

# Let's create some sample sales data
rangées = [[[[
    [[[["", "January", "February", "March", "April",
    "May", "June", "July", "August", "September",
     "October", "November", "December"],
    [[[[1, ],
    [[[[2, ],
    [[[[3, ],
]

pour rangée dans rangées:
    drap.ajouter(rangée)

pour rangée dans drap.iter_rows(min_row=2,
                           max_row=4,
                           min_col=2,
                           max_col=13):
    pour cellule dans rangée:
        cellule.valeur = au hasard.randrange(5, 100)

# Create a LineChart and add the main data
graphique = LineChart()
Les données = Référence(feuille de travail=drap,
                           min_row=2,
                           max_row=4,
                           min_col=1,
                           max_col=13)
graphique.add_data(Les données, titles_from_data=True, from_rows=True)

# Add categories to the chart
chats = Référence(feuille de travail=drap,
                 min_row=1,
                 max_row=1,
                 min_col=2,
                 max_col=13)
graphique.set_categories(chats)

# Rename the X and Y Axis
graphique.x_axis.Titre = "Months"
graphique.y_axis.Titre = "Sales (per unit)"

# Apply a specific Style
graphique.style = 24

# Save!
drap.add_chart(graphique, "C6")
classeur.enregistrer("line_chart.xlsx")

There are a lot more chart types and customization you can apply, so be sure to check out the package documentation on this if you need some specific formatting.

Convert Python Classes to Excel Spreadsheet

You already saw how to convert an Excel spreadsheet’s data into Python classes, but now let’s do the opposite.

Let’s imagine you have a database and are using some Object-Relational Mapping (ORM) to map DB objects into Python classes. Now, you want to export those same objects into a spreadsheet.

Let’s assume the following data classes to represent the data coming from your database regarding product sales:

de dataclasses importation dataclass
de dactylographie importation liste

@dataclass
classe Vente:
    identifiant: str
    quantité: int

@dataclass
classe Produit:
    identifiant: str
    prénom: str
    Ventes: liste[[[[Vente]

Now, let’s generate some random data, assuming the above classes are stored in a db_classes.py file:

    1 importation au hasard
    2 
    3 # Ignore these for now. You'll use them in a sec ;)
    4 de openpyxl importation Cahier de travail
    5 de openpyxl.chart importation LineChart, Référence
    6 
    7 de db_classes importation Produit, Vente
    8 
    9 des produits = []
dix 
11 # Let's create 5 products
12 pour idx dans intervalle(1, 6):
13     Ventes = []
14 
15     # Create 5 months of sales
16     pour _ dans intervalle(5):
17         vente = Vente(quantité=au hasard.randrange(5, 100))
18         Ventes.ajouter(vente)
19 
20     produit = Produit(identifiant=str(idx),
21                       prénom="Product %s" % idx,
22                       Ventes=Ventes)
23     des produits.ajouter(produit)

By running this piece of code, you should get 5 products with 5 months of sales with a random quantity of sales for each month.

Now, to convert this into a spreadsheet, you need to iterate over the data and append it to the spreadsheet:

25 classeur = Cahier de travail()
26 drap = classeur.actif
27 
28 # Append column names first
29 drap.ajouter([[[["Product ID", "Product Name", "Month 1",
30               "Month 2", "Month 3", "Month 4", "Month 5"])
31 
32 # Append the data
33 pour produit dans des produits:
34     Les données = [[[[produit.identifiant, produit.prénom]
35     pour vente dans produit.Ventes:
36         Les données.ajouter(vente.quantité)
37     drap.ajouter(Les données)

C'est tout. That should allow you to create a spreadsheet with some data coming from your database.

However, why not use some of that cool knowledge you gained recently to add a chart as well to display that data more visually?

All right, then you could probably do something like this:

38 graphique = LineChart()
39 Les données = Référence(feuille de travail=drap,
40                  min_row=2,
41                  max_row=6,
42                  min_col=2,
43                  max_col=7)
44 
45 graphique.add_data(Les données, titles_from_data=True, from_rows=True)
46 drap.add_chart(graphique, "B8")
47 
48 chats = Référence(feuille de travail=drap,
49                  min_row=1,
50                  max_row=1,
51                  min_col=3,
52                  max_col=7)
53 graphique.set_categories(chats)
54 
55 graphique.x_axis.Titre = "Months"
56 graphique.y_axis.Titre = "Sales (per unit)"
57 
58 classeur.enregistrer(filename="oop_sample.xlsx")

Nous parlons maintenant! Here’s a spreadsheet generated from database objects and with a chart and everything:

That’s a great way for you to wrap up your new knowledge of charts!

Bonus: Working With Pandas

Even though you can use Pandas to handle Excel files, there are few things that you either can’t accomplish with Pandas or that you’d be better off just using openpyxl directly.

For example, some of the advantages of using openpyxl are the ability to easily customize your spreadsheet with styles, conditional formatting, and such.

But guess what, you don’t have to worry about picking. In fact, openpyxl has support for both converting data from a Pandas DataFrame into a workbook or the opposite, converting an openpyxl workbook into a Pandas DataFrame.

First things first, remember to install the pandas package:

Then, let’s create a sample DataFrame:

    1 importation pandas comme pd
    2 
    3 Les données = 
    4     "Product Name": [[[["Product 1", "Product 2"],
    5     "Sales Month 1": [[[[dix, 20],
    6     "Sales Month 2": [[[[5, 35],
    7 
    8 df = pd.DataFrame(Les données)

Now that you have some data, you can use .dataframe_to_rows() to convert it from a DataFrame into a worksheet:

dix de openpyxl importation Cahier de travail
11 de openpyxl.utils.dataframe importation dataframe_to_rows
12 
13 classeur = Cahier de travail()
14 drap = classeur.actif
15 
16 pour rangée dans dataframe_to_rows(df, indice=Faux, entête=True):
17     drap.ajouter(rangée)
18 
19 classeur.enregistrer("pandas.xlsx")

You should see a spreadsheet that looks like this:

If you want to add the DataFrame’s index, you can change index=True, and it adds each row’s index into your spreadsheet.

On the other hand, if you want to convert a spreadsheet into a DataFrame, you can also do it in a very straightforward way like so:

importation pandas comme pd
de openpyxl importation load_workbook

classeur = load_workbook(filename="sample.xlsx")
drap = classeur.actif

valeurs = drap.valeurs
df = pd.DataFrame(valeurs)

Alternatively, if you want to add the correct headers and use the review ID as the index, for example, then you can also do it like this instead:

importation pandas comme pd
de openpyxl importation load_workbook
de cartographie importation REVIEW_ID

classeur = load_workbook(filename="sample.xlsx")
drap = classeur.actif

Les données = drap.valeurs

# Set the first row as the columns for the DataFrame
cols = suivant(Les données)
Les données = liste(Les données)

# Set the field "review_id" as the indexes for each row
idx = [[[[rangée[[[[REVIEW_ID] pour rangée dans Les données]

df = pd.DataFrame(Les données, indice=idx, des colonnes=cols)

Using indexes and columns allows you to access data from your DataFrame easily:

>>> df.des colonnes
Index(['marketplace''customer_id''review_id''product_id'['marketplace''customer_id''review_id''product_id'['marketplace''customer_id''review_id''product_id'['marketplace''customer_id''review_id''product_id'
                            'product_parent', 'product_title', 'product_category', 'star_rating',
                            'helpful_votes', 'total_votes', 'vine', 'verified_purchase',
                            'review_headline', 'review_body', 'review_date'],
                        dtype='object')

>>> # Get first 10 reviews' star rating
>>> df[[[["star_rating"][:[:[:[:dix]
R3O9SGZBVQBV76    5
RKH8BNC3L5DLF     5
R2HLE8WKZSU3NL    2
R31U3UH5AZ42LL    5
R2SV659OUJ945Y    4
RA51CP8TR5A2L     5
RB2Q7DLDN6TH6     5
R2RHFJV0UYBK3Y    1
R2Z6JOQ94LFHEP    5
RX27XIIWY5JPB     4
Name: star_rating, dtype: int64

>>> # Grab review with id "R2EQL1V1L6E0C9", using the index
>>> df.loc[[[["R2EQL1V1L6E0C9"]
marketplace               US
customer_id         15305006
review_id     R2EQL1V1L6E0C9
product_id        B004LURNO6
product_parent     892860326
review_headline   Five Stars
review_body          Love it
review_date       2015-08-31
Name: R2EQL1V1L6E0C9, dtype: object

There you go, whether you want to use openpyxl to prettify your Pandas dataset or use Pandas to do some hardcore algebra, you now know how to switch between both packages.

Conclusion

Phew, after that long read, you now know how to work with spreadsheets in Python! You can rely on openpyxl, your trustworthy companion, to:

• Extract valuable information from spreadsheets in a Pythonic manner
• Create your own spreadsheets, no matter the complexity level
• Add cool features such as conditional formatting or charts to your spreadsheets

There are a few other things you can do with openpyxl that might not have been covered in this tutorial, but you can always check the package’s official documentation website to learn more about it. You can even venture into checking its source code and improving the package further.

Feel free to leave any comments below if you have any questions, or if there’s any section you’d love to hear more about.