First, let me thank all the readers who sent me emails of hope and wishes for a quick recovery. They were very kind and helpful. I also want to thank Ronnie, our wonderful editor, for his support and patience during that painful period. I still have issues with sitting for long periods of time, so this is being done over the course of a number of days, so I hope the continuity that I’m trying for works. Now on with “the show”… Not too long ago, I was walking to the time clock and the General Manager of my “day job” called me into his office. Hoping it was just a “how’s it going” talk, I went in and sat down. He then started the meeting with “I’m having a problem with my spreadsheet program, and was hoping you could help me”. As my vision darkened and the three-note ominous orchestral string hits “Da Da DAAAAAAAAA” that we all know from the horror flicks of the 70’s and 80’s rang through my mind, rather than running screaming from the room, I innocently asked what was wrong. He responded that there was something wrong with one of the macros and “the thing just quits in the middle of the calculations”. As I whipped out my white cowboy hat, I said in my best hero voice “Don’t worry citizen. We’ll have you up and running in no time.” Within a short while, I discovered the reason the spreadsheet was unceremoniously crashing was that one cell in one of 35 workbooks was getting a divide by zero error due to an expected value not being entered in another cell in yet another one of the 35 workbooks. Let me make this perfectly clear, it was not my boss’s fault. All he had asked for was a simple way to get the higher-up values from the data. (The previous two sentences have absolutely nothing to do with the fact that my boss may read this article! Or maybe it does.)

Tout d'abord, permettez-moi de remercier tous les lecteurs qui m'ont envoyé des courriels d'espoir et des vœux de prompt rétablissement. C'était super gentil et d'une grande aide. Je tiens également à remercier Ronnie, notre merveilleux rédacteur en chef, pour son soutien et sa patience pendant cette période douloureuse. J'ai encore des problèmes pour rester longtemps assis et du coup je rédige ceci sur plusieurs jours ; j'espère arriver à garder une certaine continuité. Maintenant, place au « spectacle »…

Il n'y a pas très longtemps, j'allais pointer quand le directeur général de mon « travail de jour » m'a appelé dans son bureau. En espérant que c'était juste une conversation de style « comment ça va », je suis entré et me suis assis. Il a alors commencé par : « J'ai un problème avec mon tableur, et j'espérais que vous pourriez m'aider. »

Ma vision s'est obscurcie et les trois notes sinistres « Da Da DAAAAAAAAA » des films d'horreur des années 70 et 80 ont sonné dans ma tête, mais plutôt que de courir hors de la salle en hurlant, j'ai demandé innocemment quel était le problème exact. Il a répondu qu'il y avait un souci avec l'une des macros qui « s'arrêtait en plein milieu des calculs ». En revêtant mon chapeau blanc de cow-boy, j'ai dit dans ma meilleure voix de héro : « Ne vous inquiétez pas citoyen. Nous allons remettre ça en état en un rien de temps. » J'ai rapidement découvert que la raison pour laquelle la feuille de calcul plantait sans cérémonie était qu'une cellule dans l'un des 35 classeurs contenait une erreur de division par zéro parce qu'une valeur attendue n'était pas saisie dans une autre cellule dans un autre des 35 classeurs. Permettez-moi de souligner très clairement que ce n'était pas la faute de mon patron. Tout ce qu'il avait demandé était un moyen simple d'obtenir des valeurs représentatives à partir des données. (Les deux phrases précédentes n'ont absolument rien à voir avec le fait que mon patron peut lire cet article ! Ou peut-être que si.)

As I walked back to my work area, brushing the spurious bits of computer code from my white hat, I realized that this would be an excellent teaching moment. So, here we are. But first, let’s revert back to 1979 when Apple introduced Visicalc. That was the first “Free Form Calculation type system” to really make a hit in the marketplace. While there were many bugs in the software, the world loved the idea and clones (bugs and all) began to pop up on other computer systems, like the Commodore Pet and other Apple competitors (including Microsoft in 1981 with a program called Multiplan). Finally, in 1983, a company called Lotus Development Corp. introduced Lotus 1-2-3. While very close to Visicalc in many aspects, including the menu structure, it was written completely in x86 assembly language, which made it very fast, and many of the bugs of Visicalc were fixed. Lotus 1-2-3 was so popular that it became a common benchmark to test a machine for “PC Compatibility”. The advent of the Free Form Calculation systems, allowed the “normal” person to deal with numbers in a way that previously was in the realm of the programmer. Almost anyone could, in a few hours or so, make sense of numbers, create charts and graphs, and share that information with coworkers. Shortly after that, the ability to automate some portions of the spreadsheet through Macros and Basic-like embedded languages gave these non-programmer users even more power over their destiny. They could get the answers themselves, and pretty charts and graphs as well, without having to wait in the queue for I.T. assistance. However, as we all learned from Peter Parker’s uncle Ben… With great power, comes great responsibility.

En retournant à mon bureau, brossant les faux bits de code accrochés à mon chapeau blanc, j'ai réalisé que ce serait un excellent opportunité de faire un peu d'enseignement. Et nous y sommes. Mais d'abord, nous allons revenir à 1979, lorsque Apple a lancé Visicalc. C'était le premier « système de type formulaire libre de calcul » qui a eu un vrai succès sur le marché. Bien qu'il y ait de nombreux bogues dans le logiciel, le monde a adoré l'idée et les clones (bogues inclus) ont commencé à apparaître sur d'autres systèmes informatiques, comme le Commodore PET et d'autres concurrents d'Apple (y compris Microsoft en 1981 avec un programme appelé Multiplan). Enfin, en 1983, une société appelée Lotus Development Corp. a introduit Lotus 1-2-3. Bien que très proche de Visicalc sur de nombreux aspects, y compris la structure des menus, il était écrit entièrement en langage assembleur x86, ce qui le rendait très rapide, et de nombreux bogues de Visicalc ont été corrigés. Lotus 1-2-3 était si populaire qu'il est devenu un base de référence classique pour tester la « compatibilité PC » d'une machine.

L'avènement des systèmes de formulaires libres de calculs a permis à la personne « normale » de manipuler des nombres d'une manière qui était auparavant du domaine de la programmation. Presque n'importe qui pouvait, en quelques heures, donner un sens à des nombres, créer des tableaux et des graphiques, et partager cette information avec des collègues. Peu de temps après, la capacité d'automatiser certaines parties de la feuille de calcul grâce à des macros et des langages intégrés proches du Basic a donné à ces utilisateurs non-programmeurs encore plus de pouvoir sur leur destin. Ils pouvaient obtenir les réponses eux-mêmes, et également de jolis tableaux et graphiques, sans avoir à faire la queue en attendant l'aide des informaticiens. Cependant, comme nous l'avons tous appris de l'oncle Ben de Peter Parker…

Un grand pouvoir implique de grandes responsabilités.

Soon the spreadsheet was taken into areas that were better suited for databases than spreadsheets. We now had workbooks upon workbooks that relied on other workbooks, and if one little number along the way didn’t happen to get updated… well, we had the old “house of cards” effect. While I don’t think that every spreadsheet is evil, there are some (read this to say ‘many’) that should have been converted to databases many years ago. They just became too large and unwieldy for their own good. If someone had just sat down with the programmers and said, “Please help”, the world would be a kinder, gentler place. Now as I step down from my soapbox, we come to the real reason for this month’s article. Every good Python programmer should have a way to deal with spreadsheets in their arsenal of tools. You never know when you will be called upon to pull data from a spreadsheet and manipulate it. While there are many ways to get data from spreadsheets like using CSV files, which has its own drawbacks, sometimes you need to read and write directly from and to a ‘live’ spreadsheet. After looking around, I settled on a very nice library to access my boss’s problematical spreadsheet.

Bientôt la feuille de calcul a été utilisée dans des cas qui relevaient plutôt des bases de données que des feuilles de calcul. Nous avons maintenant des classeurs sur des classeurs qui dépendent d'autres classeurs, et si un petit nombre le long du chemin n'arrive pas à se mettre à jour… eh bien, nous obtenons le vieil effet « château de cartes ».

Je ne pense pas que toutes les feuilles de calcul soient mauvaises, mais certaines (lire ici « beaucoup ») auraient dû être converties en bases de données il y a de nombreuses années. Elles sont juste devenu trop grandes et lourdes pour leur propre bien. Si quelqu'un s'était assis avec des programmeurs et avait dit : « Je vous en prie, aidez-nous », le monde serait un endroit plus empathique et plus doux.

Maintenant, je descend de ma tribune, et nous arrivons à la véritable raison de l'article de ce mois-ci. Chaque bon programmeur Python devrait avoir parmi ses outils un moyen de traiter avec des feuilles de calcul. Vous ne savez jamais quand vous aurez besoin d'extraire des données d'une feuille de calcul pour les manipuler. Bien qu'il existe plusieurs façons de récupérer des données de feuilles de calcul, comme les fichiers CSV qui ont leurs propres inconvénients, vous avez parfois besoin de lire et d'écrire directement à partir et vers un tableur « actif ». Après avoir cherché, je suis tombé sur une très belle bibliothèque pour accéder à la feuille de calcul problématique de mon patron.

We will be adding the library called XLRD, which one might imagine stands for eXceL ReaD. This library allows us to easily read data from Excel files (.xls , .xlsx and .xlsm) from versions 2.0 onward. Let’s create an excel spreadsheet that we can use to examine the functionality of XLRD. Either open excel, or openoffice or libreoffice calc. In the first column (A), enter the numbers 1 to 5 going down. In the next column (B), enter 6 to 10. It should look something like this: Now save the spreadsheet as “example1.xls” in the folder you will use to save the test code. This way, we won’t have to worry about paths. Now download and install XLRD (https://pypi.python.org/pypi/xlrd). We can use it like is shown below. Save the file as example1.py in the same folder as the spreadsheet. Since the code is so short, we will simply discuss it here. Of course, the first line imports the library. Then we create a function called OpenFile and pass the name (and path if needed) of the spreadsheet to the function.

Nous allons ajouter la bibliothèque appelée XLRD, sans doute pour eXceL ReaD (lire Excel). Cette bibliothèque nous permet de lire facilement des données dans des fichiers Excel (.xls, .xlsx et .xlsm) à partir de la version 2.0.

Créons une feuille de calcul Excel pour examiner les fonctionnalités de XLRD. Ouvrez Excel ou OpenOffice ou LibreOffice Calc. Dans la première colonne (A), saisissez les chiffres de 1 à 5 en descendant. Dans la colonne suivante (B), saisissez 6 à 10. Cela devrait ressembler à ceci :   Maintenant, sauvegardez la feuille de calcul comme « exemple1.xls » dans le dossier que vous allez utiliser pour enregistrer le code de test. De cette façon, nous n'aurons pas à nous soucier de chemins.

Maintenant téléchargez et installez XLRD (https://pypi.python.org/pypi/xlrd). Nous pouvons l'utiliser comme illustré ci-dessous.

Enregistrez le fichier sous exemple1.py dans le même dossier que la feuille de calcul. Puisque le code est très court, nous allons tout simplement en discuter ici. Bien sûr, la première ligne importe la bibliothèque. Ensuite, nous créons une fonction appelée OuvrirFichier et passons le nom (et le chemin si nécessaire) de la feuille de calcul à la fonction.

Now we call the open_workbook method and get back a ‘book’ object. Then we use the nsheets attribute to return the number of ACTIVE workbooks. We can also get the name of the workbooks. In this case, they are the default. We use the sheet_by_index method to get Sheet1 into the first_sheet object. Now we can start getting data. We get the information from the cell at position (1,1) which translates to cell position B2 (it’s Zero based, so cell A1 would be (0,0)). We print the data from there, both what the cell contains and the value, so we could use it in a calculation if we wish. That was really easy, wasn’t it? Now, let’s do something a bit more useful. Enter the code shown on the next page (top right) and save it as ‘example2.py’. This example will print out the contents of the workbook. Since we already used the first four lines of code in the first example, we’ll skip them. By using the ‘sheet.nrows’ and ‘sheet.ncols’ attributes, we get the number of rows and columns. This can be helpful, not only so we know what we are dealing with; we can write “generic” routines that use those values in our calculations as you will see. In fact, we use ‘rows’ in a for loop to obtain each row’s information.

Maintenant, nous appelons la méthode open_workbook et récupérons un objet « classeur ». Ensuite, nous utilisons l'attribut nsheets qui retourne le nombre de feuilles actives. Nous pouvons également obtenir le nom des feuilles. Dans ce cas, ce sont ceux par défaut. Nous utilisons la méthode sheet_by_index pour obtenir la Feuille1 dans l'objet premiere_feuille. Maintenant, nous pouvons commencer à récupérer des données. Nous récupérons l'information de la cellule à la position (1,1) qui correspond à la cellule B2 (on compte à partir de 0, donc la cellule A1 serait (0,0)). Nous écrivons les données à partir de là, à la fois ce que contient la cellule et la valeur, pour que nous puissions l'utiliser dans un calcul si l'on veut.

C'était vraiment facile, non ? Maintenant, nous allons faire quelque chose d'un peu plus utile. Entrez le code indiqué sur la page suivante (en haut à droite) et enregistrez-le comme « exemple2.py ». Cet exemple permet d'afficher le contenu du classeur.

Nous avons déjà utilisé les quatre premières lignes de code dans le premier exemple, nous les laisserons de côté. En utilisant les attributs « sheet.nrows » et « sheet.ncols », on obtient le nombre de lignes et de colonnes. Cela peut être utile, et pas seulement pour savoir à quoi nous avons affaire ; nous pouvons écrire des routines « génériques » qui utilisent ces valeurs dans nos calculs, comme vous le verrez. En fait, nous utilisons « lignes » dans une boucle for pour obtenir les informations de chaque ligne.

Notice the line that has ‘first_sheet.row_slice’. This gets a block of cells of a given row. The syntax is as follows: X = first_sheet.row_slice(RowInQuestion, Start_Column, End_Column) So we have used the number of rows and the number of columns in calculations. The output from our program should look something like this… There are 5 rows in this workbook. There are 2 cols in this workbook. [number:1.0, number:6.0] [number:2.0, number:7.0] [number:3.0, number:8.0] [number:4.0, number:9.0] [number:5.0, number:10.0] Press any key to continue . . . We’ll do one more example before we end this month’s article. Go to the spreadsheet and in column C put some dates. Here’s what my spreadsheet looks like now:

Remarquez la ligne qui contient « premiere_feuille.row_slice ». Elle récupère un bloc de cellules d'une ligne donnée. La syntaxe est la suivante :

X = premiere_feuille.row_slice(Ligne_Concernée, Colonne_Départ, Colonne_Fin)

Nous avons donc utilisé le nombre de lignes et le nombre de colonnes dans les calculs. La sortie de notre programme devrait ressembler à quelque chose comme çeci…

Il y a 5 lignes dans cette feuille. Il y a 2 colonnes dans cette feuille. [number:5.0, number:6.0] [number:4.0, number:7.0] [number:3.0, number:8.0] [number:2.0, number:9.0] [number:1.0, number:10.0] Appuyez sur une touche pour continuer…

Nous allons voir un exemple de plus avant de terminer cet article. Allez sur la feuille de calcul et placez quelques dates dans la colonne C. Voici à quoi ma feuille de calcul ressemble maintenant :

You can use any dates you like. Now let’s re-run our example2.py program. Here is the output from mine. There are 5 rows in this workbook. There are 3 cols in this workbook. [number:1.0, number:6.0, xldate:41649.0] [number:2.0, number:7.0, xldate:42109.0] [number:3.0, number:8.0, xldate:31587.0] [number:4.0, number:9.0, xldate:23284.0] [number:5.0, number:10.0, xldate:36588.0] Press any key to continue … Well, that’s not what we expected. It seems that excel holds dates as a value that is simply formatted for whatever we ask it to. This might be helpful for sorting and calculations, but, for showing the actual data, this won’t do. Luckily, the writers of the library already thought of this. Delete the line that says “print cells” and replace it with the code shown below.

Vous pouvez utiliser les dates que vous voulez. Maintenant, relancez le programme exemple2.py. Voici la sortie du mien.

Il y a 5 lignes dans cette feuille. Il y a 3 colonnes dans cette feuille. [number:5.0, number:6.0, xldate:41649.0] [number:4.0, number:7.0, xldate:42109.0] [number:3.0, number:8.0, xldate:31587.0] [number:2.0, number:9.0, xldate:23284.0] [number:1.0, number:10.0, xldate:36588.0] Appuyez sur une touche pour continuer…

Eh bien, ce n'est pas ce que nous attendions. Il semble qu'Excel stocke les dates comme des valeurs qui sont simplement formatées comme nous leur demandons. Ceci peut être utile pour le tri et les calculs, mais, pour afficher les données réelles, cela ne convient pas. Heureusement, les auteurs de la bibliothèque ont déjà pensé à cela. Supprimez la ligne « print cellules » et remplacez-la par le code ci-dessous.

Here, we go through each cell in the cells list and check the type of the cell to see if it is considered a XL_CELL_DATE. If it is, then we convert it to a tuple. It is stored as YYYY,MM,DD. We simply pretty it up to print it as MM/DD/YYYY. Here is the output of our new program… There are 5 rows in this workbook. There are 3 cols in this workbook. 1.0 6.0 1/10/2014 2.0 7.0 4/15/2015 3.0 8.0 6/24/1986 4.0 9.0 9/30/1963 5.0 10.0 3/3/2000 Press any key to continue …

Ici, nous parcourons chaque cellule dans la liste des cellules et vérifions le type de la cellule pour voir si elle est considérée comme un XL_CELL_DATE. Si c'est le cas, alors nous la convertissons en un tuple. Il est stocké sous la forme AAAA, MM, JJ. Nous le rendons alors présentable pour l'afficher sous la forme JJ/MM/AAAA. Voici la sortie de notre nouveau programme…

Il y a 5 lignes dans cette feuille. Il y a 3 colonnes dans cette feuille. 5.0 6.0 1/10/2014 4.0 7.0 4/15/2015 3.0 8.0 6/24/1986 2.0 9.0 9/30/1963 1.0 10.0 3/3/2000 Appuyez sur une touche pour continuer…

Just for your information, there is a library from the same wonderful people called XLWT, which allows you to write to excel files. There is a wonderful tutorial and documentation on these two libraries at http://www.python-excel.org/. The source code for example3.py is on pastebin at http://pastebin.com/bWz7beBw. Hopefully, I’ll see you next month.

Juste pour information, les mêmes merveilleuses personnes ont fait une autre bibliothèque appelée XLWT, qui vous permet d'écrire dans des fichiers Excel. Il y a un tutoriel merveilleux et une documentation sur ces deux bibliothèques ici : http://www.python-excel.org/.   Le code source de exemple3.py est sur pastebin : http://pastebin.com/EciU3Fak.

J'espère que je vous verrai le mois prochain.

import xlrd def OuvrirFichier(chemin):

   # Ouvre et lit un fichier Excel
   classeur = xlrd.open_workbook(chemin)
   # Recupere le nombre de feuilles actives
   print "Nombre de feuilles : ",classeur.nsheets
   # Recupere le nom des ces feuilles
   print "Noms des feuilles : ",classeur.sheet_names()
   premiere_feuille = classeur.sheet_by_index(0)
   cellule = premiere_feuille.cell(1,1)
   print "Cellule en 1,1: ",cellule
   print "Valeur de la cellule en 1,1: ",cellule.value
   

if name == “main”:

   chemin = "exemple1.xls"
   OuvrirFichier(chemin)

(attention dans la VO il y a un problème d'indentation sur les 2 lignes qui suivent la boucle for : elles doivent être décalées vers la droite (avec quelques espaces, comme ci-dessous) sinon ça ne fonctionne pas)

import xlrd def OuvrirFichier(chemin):

   classeur = xlrd.open_workbook(chemin)
   premiere_feuille = classeur.sheet_by_index(0)
   # recupere le nombre de lignes dans cette feuille
   lignes = premiere_feuille.nrows
   # recupere le nombre de colonnes dans cette feuille
   cols = premiere_feuille.ncols
   print "Il y a %d lignes dans cette feuille." % lignes
   print "Il y a %d colonnes dans cette feuille." % cols
   for l in range(0,lignes):
      cellules = premiere_feuille.row_slice(rowx=l,start_colx=0,end_colx=cols)
      print cellules

if name == “main”:

   chemin = "exemple1.xls"
   OuvrirFichier(chemin)

attention là aussi à l'indentation qui est mauvaise dans la VO

 for c in cellules:
    if c.ctype == xlrd.XL_CELL_DATE:
       valeur_date = xlrd.xldate_as_tuple(c.value,classeur.datemode)
       dt = str(valeur_date[1]) + "/" + str(valeur_date[2]) + "/" + str(valeur_date[0])
       print dt
    else:
       print c.value