Cet article est le premier d’une suite d’articles liés à la gestion, l’administration et le traitement des données via R. En effet, R pouvant être interfacé avec de nombreux logiciels très répandus dans le milieu professionnel ou universitaire, il peut s’avérer être un allié de choix pour des traitements laborieux et complexes.

Le tableur le plus connu, et sûrement le plus répandu, sur les ordinateurs de notre chère planète (même si la concurrence est grandissante depuis quelques années) est celui de Microsoft : Excel. Il était donc évident que cette série d’articles serait entamée par un petit tour d’horizon des choses qu’il est possible de faire en connectant R et Excel.

Les packages

Ce paragraphe a pour ambition de dresser une liste des packages qui sont actuellement disponibles pour créer une connexion entre R et Excel, et de montrer les avantages et les inconvénients de chacun d’entre eux. Cependant, le développement et l’entretien des packages étant assuré par la communauté des utilisateurs de R, il est fort probable que cette liste établie aujourd’hui en 2013, soit complètement dépassée dans quelques années voir quelques mois.

excel.link :

Packages plutôt complet, il permet la lecture et l’écriture sur des fichiers Excel (même la création de nouvelles feuilles dans un fichier). Cependant, il ne permet pas la création de nouveaux fichiers, vous serez donc obligé de les créer préalablement. Une version d’Excel doit obligatoirement être installée sur la machine, de plus il ne fonctionne que sur une version Windows de R pour le moment.

WriteXLS :

Ce package à l’intérêt d’être cross plateforme. il permet de créer des fichiers Excel (xls et xlsx) et d’y insérer des objets de type data.frame. Sa limite la plus fragrante est qu’il ne permet d’insérer qu’un seul data.frame par page.

XLConnect :

Il est également cross plateforme. Son plus gros intérêt est qu’il n’a pas besoin d’avoir Microsoft Excel d’installé sur la machine pour lire ou écrire dans un fichier Excel. C’est une librairie Java qui est fait le boulot, c’est pourquoi il est nécessaire d’avoir le Java RunTime Environment d’installé sur la machine (mais il l’est déjà sur de nombreuses machines).

Lien permettant de trouver un pdf résumant quelques fonction du packages XLConnect.

xlsx :

Mon préféré ! Dans un premier temps parce que je travaille exclusivement avec des fichiers de ce format…Puis pour sa simplicité et ses capacités. Il est cross-plateforme et permet la lecture et l’écriture de fichiers et la création de classeurs. Le fait qu’il soit cross-plateforme me permet d’utiliser mes scripts sur Mac et PC Windows, mes deux stations de travail. Mes exemples seront majoritairement issus de l’utilisation de ce package.

RExcel :

Ce packages est beaucoup plus complet que les autres mais ses ambitions sont également bien plus importantes. Ici, il ne s’agit plus seulement d’accéder à un fichier Excel pour écrire ou lire des données, mais bel et bien d’intégrer R (et d’autres composants) dans Excel, afin d’en améliorer les capacités.

Pour en savoir plus sur le sujet, c’est ici (en anglais seulement). Dans l’onglet « Download » vous pourrez télécharger tous les éléments dont vous avez besoin, mais également consulter la documentation pour tout mettre en place.

La philosophie de ce package étant différente de celle des autres que j’ai citée plus haut, il fera l’objet d’un article ultérieur plus complet.

Comment ça fonctionne ?

En gros, ces packages fonctionnent tous de la même manière. Concrètement, on accède au fichier Excel en donnant son chemin d’accès complet (bien sûr, la racine du fichier sera différente sur chaque plateforme : Windows, Mac OS ou Linux). Des informations complémentaires sont souvent nécessaires pour renseigner le format du fichier ou bien la page à laquelle on souhaite accéder.

Les données sont ensuite chargées en mémoire, en tant qu’objet « data.frame », et c’est à partir de là que vous pouvez jouer avec. Leur modification ne peut pas affecter le fichier source, à moins qu’une requête d’écriture sur le fichier ne soit lancée. Autrement dit, vous pouvez faire ce que vous désirez à ces données, sans vous soucier de l’intégrité de votre fichier Excel puisqu’il n’est plus en course.

Pour des opérations plus spécifiques, il faut revenir au manuel de chaque package, notamment sur la manière dont on gère les cellules ou le type des variables dans les cellules.

En voiture Simone !

• Lecture des données

Je ne peux pas reprendre un exemple pour chacun des packages qui existe, c’est pourquoi, je vais utiliser exclusivement deux packages qui me paraissent être les plus aboutis pour le moment.

Data<-read.xlsx(« C:/Users/Antoine/Desktop/Analyse.xlsx », sheetIndex=1) [package : xlsx]

data<-readWorksheetFromFile(« C:/Users/Antoine/Desktop/Analyse.xlsx », sheet=1, header=TRUE, startCol=1, startRow=1) [package : XLConnect]

Avec cette ligne, je charge les données de la feuille 1 de mon classeur Excel qui s’appelle « Analyse.xlsx ». Comme vous pouvez le voir, j’ai renseigné l’index d’une feuille de classeur, mais j’aurais pu lui demander de me charger les données d’une feuille spécifique en la nommant grâce à la requête « sheetName » à la place de « sheetIndex ». D’autres caractéristiques sont paramétrable, et pour plus d’information dessus, je vous invite à regarder la notice d’utilisation du package, via R directement ou sur le site du CRAN. Pour rappel, le fichier Excel n’a pas besoin d’être ouvert !

Lorsque le fichier est trop volumineux, on peut obtenir le message suivant :
java.lan.OutOfMemoryError : Java Heap Space.

Cela veut dire que votre ordinateur ne consacre pas, ou ne possède pas assez de mémoire pour importer vos données dans R. Dans ce cas là, on peut changer le montant de mémoire utilisable par le processus grâce à cette requête « options(java.parameters= « -Xmx1200m »), placée avant le chargement du package, mais ce n’est pas conseillé. Sinon on peut tout simplement « spliter » les données (en affectant les données de la colonne 1 à 50 à la variable x et de la colonne 51 à 100 à la variable y). Il ne faut pas oublier que ces packages vous permettent de sélectionner l’intégralité ou une région de votre choix, ce qui est bien sûr très pratique.

• Écriture des données

En termes de lecture des données, les packages que j’ai cité font à peu de choses près les mêmes choses, c’est plus sur l’écriture qu’ils se différencient le plus, dans tous les cas, pour être traité, un fichier doit être fermé (ce qui n’est pas le cas pour la lecture).

write.xlsx(Data,file= »C:/Users/antoine/Desktop/BlogDemo.xlsx », sheetName= »Data », col.names=TRUE,row.names=TRUE, append=FALSE) [package : xlsx]

Il est à noté que les données qui vous enverrez dans l’Excel doivent être au format « data.frame ».

Récemment, Mirai Solutions (concepteurs du package XLConnect) a amélioré les capacités du package en rajoutant des fonctions permettant de modifier l’aspect visuel des feuilles de calcul (coloration des pages, des cellules, fixation des volets, division d’écrans, etc), ce qui peut être très utile. Je vais reprendre des exemples ci-dessous.

Avec le script suivant, je change la couleur des feuilles du classeur Excel : [package : XLConnect] wb<-loadWorkbook(« C:/Users/Antoine/Desktop/BlogDemo.xlsx », create=FALSE)
setSheetColor(wb,1, XLC$COLOR.RED)
écriture équivalente = setSheetColor(wb, »Data », XLC$COLOR.RED))
saveWorkbook(wb)

Pour modifier la taille des colonnes que l’on désire :
setColumnWidth(wb,1,column=c(1,2))

Pour figer les volets :
wb<-loadWorkbook(« C:/Users/Antoine/Desktop/Analyse.xlsx », create=FALSE)
createFreezePane(wb, 1, « C », 3)
saveWorkbook(wb)

Comme vous pouvez le voir dans le code, les volets sont figés à partir de la colonne C et de la deuxième ligne.

Pour diviser la fenêtre :
createSplitPane(wb, 1, 5000, 5000, « F », 10)
(Pour comprendre le choix des variables dans la fonction, veuillez vous reporter au manuel d’utilisation)

Conclusion

R offre de nombreuses solutions (plus ou moins complexes) d’accès aux données stockées dans un fichier Excel. Si ces méthodes se ressemblent beaucoup, c’est parce que la plupart d’entre elles reposent sur la même technologie, un API Java (Apache POI) qui permet d’accéder à différent type de fichiers dont le format de base est l’Office Open XML (OOXML) ou le OLE2, tout deux créés par Microsoft pour améliorer l’interopérabilité dans les environnements bureautiques. Les dernières générations du pack office ont été créées sur ce modèle  à partir de 2007, ont les reconnais par leur extension .xlsx, .docx ou .pptx.

Le choix du package à utiliser dépend de vos besoins, mais je pense que celui qui en couvre le plus est « XLConnect » (« Qui peut le plus, peut le moins » Aristote). Dans cet article, je l’ai souvent comparé au package « xlsx » qui me paraît en revanche plus simple d’utilisation (en tout cas, plus concis). Ce sont les deux principaux packages que je conseille si vous souhaitez travailler des données d’Excel avec R.

 

• Quelques ressources :

Apache POI : http://poi.apache.org/

Mirai Solution WordPress : http://miraisolutions.wordpress.com/

D’autres exemples : rwiki.sciviews.org/doku.php?id=tips:data-io:ms_windows&s=excel#!