r2excel : Créer et formater facilement un document Excel avec le logiciel R


Plusieurs solutions existent pour écrire et lire un fichier Excel avec R

Lire et écrire un fichier Excel avce le logiciel R, le package r2excel

Les fichiers Excel sont utilisés par des millions de personnes pour sauvegarder et analyser leurs données.

Différents packages R et solutions pour importer des données d’Excel vers R et d’en exporter de R vers Excel :

  • La connection ODBC: Pour Windows seulement et nécessite un driver ODBC pour Excel
  • Le package gdata: Existe pour Windows, Mac et Linux mais nécessite l’installation de librairies du langage Perl
  • Le package xlsReadWrite : Pour Windows seulement mais il n’y a plus de version mise à jour
  • Le logiciel RExcel : Un outil performant permettant d’exécuter directement du code R à partir d’Excel. Cependant, il ne peut être utilisé que sur Windows
  • Le package XLConnect : XLConnect est une solution java-dépendante qui est compatible Windows, Mac et Linux. Il est un peu lent lorsque le jeu de données est important
  • Le package xlsx: Ceci est mon package favoris pour lire, écrire et formater des fichiers Excel à partir du Logiciel R. C’est une solution java-dépendante et elle peut être utilisée pour lire et écrire à la fois les formats de fichiers xls et xlsx.

Comme indiqué ci-dessus, il existe plusieurs méthodes pour connecter R et Excel, mais beaucoup d’entre elles sont :

  • difficiles à utiliser
  • ou compatibles seulement avec Windows
  • ou nécessitent des drivers additionnels
  • ou marchent seulement avec des versions anciennes d’Excel (.xls mais pas .xlsx)

Les 3 packages à connaitre pour gagner du temps

Les trois packages que vous devez connaitre pour importer et exporter des données à partir/vers Excel sont xlsx, XLConnect et r2excel.

Lire ou écrire des fichiers Excel est devenu une tache facile maintenant grâce aux packages xlsx et XLConnect.

Formater un fichier Excel est aussi possible. Cependant, ceci nécessite du “Hard coding”. C’est la raison pour laquelle, j’ai implémenté le package r2excel contenant des fonctions pour rapidement et facilement importer, écrire et personnaliser des fichiers Excel.

L’objectif de cet article est de vous montrer comment utiliser le package r2excel.

Le package r2excel

Installation et chargement

Pour l’installation, utiliser le code R suivant :

install.packages("devtools")
devtools::install_github("kassambara/r2excel")

Charger le package avec le code ci-dessous :

library(r2excel)

Les fonctions disponibles

La liste des fonctions disponibles dans le package r2excel est :

  • xlsx.addHeader pour ajouter des titres
  • xlsx.addPlot pour ajouter des graphiques
  • xlsx.addParagraph pour ajouter des paragraphes de textes
  • xlsx.addTable pour ajouter des data.frames.
  • xlsx.addLineBreak pour ajouter un saut de ligne.
  • xlsx.addHyperlink pour ajouter un hyper lien.
  • xlsx.readFile pour lire un fichier Excel.
  • xlsx.writeFile pour écrire une table de données dans un fichier Excel.
  • xlsx.writeMultipleData pour exporter rapidement plusieurs tables de données dans le même classeur Excel.
  • xlsx.openFile pour ouvrir et voir un fichier Excel.

Ces fonctions sont décrites dans les sections suivantes

Créer votre premier fichier Excel

library("r2excel")
# Créer un classeur Excel. 
# Les formats .xls et .xlsx peuvent être utilisés
filename <- "r2excel-example1.xlsx"
wb <- createWorkbook(type="xlsx")
# Créer une feuille dan le classeur Excel pour contenir les données 
sheet <- createSheet(wb, sheetName = "example1")
 
# Ajouter un titre
xlsx.addHeader(wb, sheet, value="Add table",level=1, 
               color="black", underline=1)
xlsx.addLineBreak(sheet, 1)
 
# Ajouter un paragraph : Autheur
author=paste("Author : Alboukadel KASSAMBARA. \n",
             "@:alboukadel.kassambara@gmail.com.",
             "\n Website : http://ww.sthda.com", sep="")
xlsx.addParagraph(wb, sheet,value=author, isItalic=TRUE, colSpan=5, 
                  rowSpan=4, fontColor="darkgray", fontSize=14)
xlsx.addLineBreak(sheet, 3)
 
# Ajouter une table : data.frame
xlsx.addTable(wb, sheet, head(iris), startCol=2)
xlsx.addLineBreak(sheet, 2)
 
# Enregistrer le classeur dans un fichier
saveWorkbook(wb, filename)
xlsx.openFile(filename)# Voir le fichier

L’image du fichier Excel créé par le code ci-dessus est :

Lire et écrire un fichier Excel avec le logicel R

Ajouter des titres

Pour ajouter un titre, la fonction xlsx.addHeader() peut être utilisée. Un format simplifié est :

xlsx.addHeader(wb, sheet, value="Header", level=1, 
               color="#FFFFFF", underline=c(0,1,2))

  • wb : classeur Excel(workbook en anglais)
  • sheet : la feuille Excel
  • value : le text à ajouter comme titre
  • level : le niveau du titre; valeurs possibles 1 à 6 (pour titre1, titre2)
  • color : couleur à utiliser pour le titre
  • underline : un nombre indiquant s’il faut souligner ou pas le titre. Valeurs possibles : 0 (par défaut, texte non souligné), 1 (texte souligné avec un trait), 2 (texte souligné avec 2 traits)


Exemples :

# créer le classeur et la feuille
wb <- createWorkbook(type="xlsx")
sheet <- xlsx::createSheet(wb, sheetName = "example1")
#Titre
#**************************************
# Titre de niveau 1
xlsx.addHeader(wb, sheet, value="Header 1",level=1, color="black")
# Titre de niveau 2
xlsx.addHeader(wb, sheet, value="Header 2",level=2, color="black")
# Titre de niveau 3
xlsx.addHeader(wb, sheet, value="Header 3",level=3, color="black")
# Titre de niveau 4
xlsx.addHeader(wb, sheet, value="Header 4",level=4, color="black")
# Titre de niveau 5
xlsx.addHeader(wb, sheet, value="Header 5",level=5, color="black")
# Titre de niveau 56
xlsx.addHeader(wb, sheet, value="Header 6",level=6, color="black")
 
# Ecrire le classeur Excel dans un fichier
saveWorkbook(wb, "examples_add_header.xlsx")
xlsx.openFile("examples_add_header.xlsx")# view the file

L’image du fichier Excel créé par le code ci-dessus est :

Lire et écrire un fichier Excel avec le logicel R

Ajouter un paragraph de textes

La fonction xlsx.addParagraph() peut être utilisée. Un format simplifié est :

xlsx.addParagraph(wb, sheet, value, fontColor="#FFFFFF", 
    fontSize=12, isBold=FALSE, isItalic=FALSE, 
    colSpan=10, rowSpan=5)

  • wb : classeur Excel
  • sheet : la feuille Excel
  • value : le texte à écrire
  • fontColor : couleur du texte
  • fontSize : taille du texte
  • isBold : si TRUE, le texte est écrit en gras
  • isItalic : si TRUE, le texte est écrit en italic
  • colSpan : nombre de colonnes à fusionner (correspond à la largeur du paragraphe)
  • rowSpan : nombre de lignes à fusionner (hauteur du paragraphe)


Exemples :

# Créer un classeur et une feuille Excel
wb <- createWorkbook(type="xlsx")
sheet <- createSheet(wb, sheetName = "example1")
 
# Ajouter un paragraphe
xlsx.addHeader(wb, sheet, " Add paragraph", level=2, underline=1)
xlsx.addLineBreak(sheet, 2)
paragraph="Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged."
xlsx.addParagraph(wb, sheet, paragraph, fontSize=14, isItalic=TRUE, 
                  fontColor="darkred", colSpan=10, rowSpan=10)
# Enregistrer le classeur Excel dans un fichier
saveWorkbook(wb, "examples_add_paragraph.xlsx")
xlsx.openFile("examples_add_paragraph.xlsx")# view the file

Lire et écrire un fichier Excel avec le logicel R, ajouter un paragraph

Ajouter un lien

La fonction xlsx.addHyperlink() peut être utilisée. Un format simplifié est :

xlsx.addHyperlink(wb,sheet, address, friendlyName) 

  • wb : le classeur Excel
  • sheet : la feuille Excel
  • address : l’adresse url
  • friendlyName : texte indiquant le nom du lien


Exemples :

# Classeur et feuille Excel
wb <- createWorkbook(type="xlsx")
sheet <- createSheet(wb, sheetName = "example1")
 
# Ajouter un lien
#+++++++++++++++++++++++++++++
xlsx.addHeader(wb, sheet, " Add Hyperlink", level=2, underline=1)
xlsx.addLineBreak(sheet, 1)
xlsx.addHyperlink(wb, sheet, "http://www.sthda.com", "Click-me!", fontSize=12)
xlsx.addLineBreak(sheet, 2)
# Enregistrer le classeur dans un fichier
saveWorkbook(wb, "examples_add_hyperlink.xlsx")
xlsx.openFile("examples_add_hyperlink.xlsx")# Voir le fichier

Lire et écrire un fichier Excel avec le logicel R, ajouter un lien

Ajouter une table de données de type data.frame

La fonction xlsx.addTable peut être utilisée. Un format simplifié est :

xlsx.addTable(wb, sheet, data, col.names=TRUE, row.names=TRUE,
     fontColor="#FFFFFF", fontSize=12, rowFill=c("white", "white"))

  • wb : classeur Excel
  • sheet : feuille Excel
  • data : une data.frame
  • col.names, row.names : une valeur logique indiquant si le nom des colonnes/lignes doit être écrit dans le fichier. La valeur par défaut est TRUE
  • font.color : couleur du texte
  • font.size : taille du texte
  • rowFill : un vecteur contenant deux couleurs correspondant aux couleurs des lignes impaires et paires. Valeur par défaut : c(“white”, “white”)


# Créer un classeur et une feuille Excel
wb <- createWorkbook(type="xlsx")
sheet <- createSheet(wb, sheetName = "example1")
# ajouter la table iris avec les paramètres par défaut
data(iris)
xlsx.addHeader(wb, sheet, 
     value="Add iris table using default settings")
xlsx.addLineBreak(sheet, 1)
xlsx.addTable(wb, sheet, head(iris))
xlsx.addLineBreak(sheet, 2)
 
# Personnaliser la table
xlsx.addHeader(wb, sheet, value="Customized table")
xlsx.addLineBreak(sheet, 1)
xlsx.addTable(wb, sheet, data= head(iris),
              fontColor="darkblue", fontSize=14,
              rowFill=c("white", "lightblue")
)
xlsx.addLineBreak(sheet, 2)
# Enregistrer le classeur dans un fichier
saveWorkbook(wb, "examples_add_table.xlsx")
xlsx.openFile("examples_add_table.xlsx")# voir le fichier

Lire et écrire un fichier Excel avec le logicel R, ajouter une table

Ajouter un graphe

La fonction xlsx.addPlot() peut être utilisée. Un format simplifié :

xlsx.addPlot(wb, sheet, plotFunction,
            width = 480, height = 480, ...)

  • wb : classeur Excel
  • sheet : Feuille Excel
  • plotFunction : la fonction R pour créer le graphe
  • width, height : taille et hauteur du graphique
  • … : d’autres arguments à passer à la fonction png()


Exemples :

# Créer un classeur et une feuille Excel
wb <- createWorkbook(type="xlsx")
sheet <- createSheet(wb, sheetName = "example1")
 
# Box plot basic
data(ToothGrowth)
xlsx.addHeader(wb, sheet, "Basic box plot")
xlsx.addLineBreak(sheet, 1)
plotFunction<-function(){boxplot(len ~ dose, data = ToothGrowth, col = 1:3)}
xlsx.addPlot(wb, sheet, plotFunction())
# ggplot2
# vous devez installer le package ggplot2
library("ggplot2")
xlsx.addHeader(wb, sheet, "ggplot2")
xlsx.addLineBreak(sheet, 1)
plotFunction<-function(){
  p<-qplot(mpg, wt, data=mtcars)
  print(p)
  }
xlsx.addPlot(wb, sheet, plotFunction())
# Enregistrer le fichier Excel.
saveWorkbook(wb, "examples_add_plot.xlsx")
xlsx.openFile("examples_add_plot.xlsx")# voir le fichier

Lire et écrire un fichier Excel avec le logicel R, ajouter un graphique Lire et écrire un fichier Excel avec le logicel R, ajouter un graphique

Lire un fichier Excel

La fonction xlsx.readFile() peut être utilisée. Un format simplifié est :

xlsx.readFile(file, sheetIndex = 1, header = TRUE)

  • file : le chemin du fichier à lire
  • sheetIndex : le numéro de la feuille à lire dans le classeur Excel
  • header : valeur logique. Si TRUE, la première ligne est prise comme noms des variables


Exemples :

file <- system.file("tests", "test_import.xlsx", package = "xlsx")
res <- xlsx.readFile(file, 1)  # Lire la prémière feuille
head(res)
##   Population Income Illiteracy Life.Exp Murder HS.Grad Frost   Area
## 1       3615   3624        2.1    69.05   15.1    41.3    20  50708
## 2        365   6315        1.5    69.31   11.3    66.7   152 566432
## 3       2212   4530        1.8    70.55    7.8    58.1    15 113417
## 4       2110   3378        1.9    70.66   10.1    39.9    65  51945
## 5      21198   5114        1.1    71.71   10.3    62.6    20 156361
## 6       2541   4884        0.7    72.06    6.8    63.9   166 103766

Ecrire un fichier Excel

La fonction xlsx.writeFile peut être utilisée. Un format simplifié est :

xlsx.writeFile(data, file, sheetName = "Sheet1",
  col.names = TRUE, row.names = TRUE, append = FALSE)

  • data : une data.frame à écrire dans le classeur
  • file : chemin du fichier résultat
  • sheetName : texte indiquant le nom de la feuille Excel
  • col.names, row.names : une valeur logique indiquant si le nom des colonnes/lignes doit être écrit dans le fichier
  • append : une valeur logique indiquant si les données doivent être ajoutée dans un fichier déjà existant.


Exemples :

xlsx.writeFile(USArrests, file="myworkbook.xlsx", 
               sheetName="USA Arrests")
xlsx.openFile("myworkbook.xlsx")# Voir le fichier

Lire et écrire un fichier Excel en utilisant le logiciel R et le package xlsx

Utiliser l’argument append = TRUE pour ajouter plusieurs data.frames dans le même classeur Excel :

# Ecrire la prémiere table dans un nouveau classeur
xlsx.writeFile(USArrests, file="myworkbook.xlsx", 
            sheetName="USA-ARRESTS", append=FALSE)
# Ajouter une deuxième table
xlsx.writeFile(mtcars, file="myworkbook.xlsx",
          sheetName="MTCARS", append=TRUE)
# Ajouter une troisième table
xlsx.writeFile(Titanic, file="myworkbook.xlsx", 
          sheetName="TITANIC", append=TRUE)
xlsx.openFile("myworkbook.xlsx")# voir le fichier

Lire et écrire un fichier Excel en utilisant le logiciel R et le package r2excel

La méthode ci-dessus est très répétitive. Vous pouvez utiliser la fonction xlsx.writeMultipleData() pour ajouter plusieurs data sets dans le même classeur en une seule ligne de code.

xlsx.writeMultipleData("myworkbook.xlsx", 
          mtcars, Titanic, AirPassengers, state.x77)
xlsx.openFile("myworkbook.xlsx")# view the file

Lire et écrire un fichier Excel en utilisant le logiciel R, plusieurs tables dans le même classeur Excel

La fonction xlsx.writeMultipleData marche pour les données de types data frames (mtcars), matrices (state.x77), séries temporelles (AirPassengers) et tables (Titanic).

Un rapport Excel complet avec le logiciel R

Le code R suivant peut être utilisé :

# Créer un classeur Excel. 
# Les formats .xls et .xlsx peut être utilisés
filename<-"r2excel-example.xlsx"
wb <- createWorkbook(type="xlsx")
# Créer une feuille dans le classeur
sheet <- xlsx::createSheet(wb, sheetName = "example1")
# Ajouter un titre
#+++++++++++++++++++++++++++++++
# Titre et sous-titre de la feuille Excel
xlsx.addHeader(wb, sheet, value="Excel file written with r2excel packages",
            level=1, color="darkblue", underline=2)         
xlsx.addLineBreak(sheet, 2)
# Ajouter un paragraphe : Autheur
author=paste("Author : Alboukadel KASSAMBARA. \n",
             "@:alboukadel.kassambara@gmail.com.",
             "\n Website : http://ww.sthda.com", sep="")
xlsx.addParagraph(wb, sheet,value=author, isItalic=TRUE, colSpan=5, 
                  rowSpan=4, fontColor="darkgray", fontSize=14)
xlsx.addLineBreak(sheet, 3)
# Ajouter une table
#+++++++++++++++++++++++++++++
# ajouter la table iris avec les paramètres par défaut
data(iris)
xlsx.addHeader(wb, sheet, value="Add iris table using default settings", level=2)
xlsx.addLineBreak(sheet, 1)
xlsx.addTable(wb, sheet, head(iris), startCol=2)
xlsx.addLineBreak(sheet, 2)
 
# Table personnalisée
xlsx.addHeader(wb, sheet, value="Customized table", level=2)
xlsx.addLineBreak(sheet, 1)
xlsx.addTable(wb, sheet, data= head(iris),
              fontColor="darkblue", fontSize=14,
              rowFill=c("white", "lightblue")
)
xlsx.addLineBreak(sheet, 2)
# Ajouter un paragraphe
#+++++++++++++++++++++++++++++
xlsx.addHeader(wb, sheet, "Add paragraph", level=2)
xlsx.addLineBreak(sheet, 2)
paragraph="Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged."
xlsx.addParagraph(wb, sheet, paragraph, fontSize=14, isItalic=TRUE, 
                  fontColor="darkred", backGroundColor="gray")
xlsx.addLineBreak(sheet, 2)
# Ajouter un lien
#+++++++++++++++++++++++++++++
xlsx.addHeader(wb, sheet, " Add Hyperlink", level=2)
xlsx.addLineBreak(sheet, 1)
xlsx.addHyperlink(wb, sheet, "http://www.sthda.com", "Click-me!!", fontSize=12)
xlsx.addLineBreak(sheet, 2)
# Ajouter un box plot
#+++++++++++++++++++++++++++++
data(ToothGrowth)
xlsx.addHeader(wb, sheet, " Add Plot", level=2)
xlsx.addLineBreak(sheet, 1)
plotFunction<-function(){boxplot(len ~ dose, data = ToothGrowth, col = 1:3)}
xlsx.addPlot(wb, sheet, plotFunction())
# Enregistrez le classeur Excel
xlsx::saveWorkbook(wb, filename)
xlsx.openFile(filename) # open file

Infos

Cette analyse a été réalisée avec R (ver. 3.1.0).


Enjoyed this article? I’d be very grateful if you’d help it spread by emailing it to a friend, or sharing it on Twitter, Facebook or Linked In.

Show me some love with the like buttons below... Thank you and please don't forget to share and comment below!!
Avez vous aimé cet article? Je vous serais très reconnaissant si vous aidiez à sa diffusion en l'envoyant par courriel à un ami ou en le partageant sur Twitter, Facebook ou Linked In.

Montrez-moi un peu d'amour avec les like ci-dessous ... Merci et n'oubliez pas, s'il vous plaît, de partager et de commenter ci-dessous!





Cette page a été vue 9912 fois