xlsx : le meilleur package pour connecter Excel avec le logiciel R


Lire et écrire des fichiers Excel avec le logiciel R, package xlsx

Il existe plusieurs solutions pour importer et exporter des fichiers Excel en utilisant le Logiciel R.

Le package xlsx est l’un des plus performants packages R pour lire, écrire et formater des fichiers Excel. C’est une solution Java-dépendante qui est compatible Windows, Mac et Linux. Il fonctionne à la fois pour les formats de fichiers de types Excel 2007 et Excel 97/2000/XP/2003 (c’est à dire les formats xls et xlsx ).

Malheureusement le package xlsx n’est pas bien documenté.

Cet article est un guide de démarrage rapide pour manipuler des fichiers Excel dans R en utilisant le package xlsx.


Dans ce tutoriel vous allez apprendre comment :

  • Lire et écrire des fichiers Excel
  • Ajouter des tables de données et images (ou graphes) dans un classeur Excel
  • Formater l’apparence du fichier Excel en modifiant la police, les couleurs de textes et les bordures


Installer et charger le package xlsx

install.packages("xlsx") 
library("xlsx")

Notez que le package xlsx dépend des packages R rJava et xlsxjars.

Lire un fichier Excel

Les fonctions read.xlsx() et read.xlsx2() peuvent être utilisées pour lire le contenu d’un classeur Excel dans R.

La différence entre ces deux fonctions est :

  • read.xlsx préserve le type des données. Il essaye de deviner le type de classe (nombre, caractère, logique, …) de chaque variable correspondant à chaque colonne dans le classeur. A noter que la fonction read.xlsx est un peu plus lent pour lire des gros jeux de données ( c’est à dire une feuille Excel avec plus de 100 000 cellules).
  • La fonction read.xlsx2 est beaucoup plus rapide sur des gros fichiers Excel.

Le format simplifié de ces fonctions est :

read.xlsx(file, sheetIndex, header=TRUE, colClasses=NA)
read.xlsx2(file, sheetIndex, header=TRUE, colClasses="character")

  • file : le chemin du fichier à lire
  • sheetIndex : numéro du fichier à lire; i.e : utiliser sheetIndex=1 pour lire le premier fichier
  • header : une valeur logique. Si TRUE, la première ligne est prise comme nom des variables
  • colClasses : un vecteur de caractères contenant le type de classe de chaque colonne


Exemples :

library(xlsx)
file <- system.file("tests", "test_import.xlsx", package = "xlsx")
res <- read.xlsx(file, 1)  # Lire la prémière feuille
head(res[, 1:6])
##          NA. Population Income Illiteracy Life.Exp Murder
## 1    Alabama       3615   3624        2.1    69.05   15.1
## 2     Alaska        365   6315        1.5    69.31   11.3
## 3    Arizona       2212   4530        1.8    70.55    7.8
## 4   Arkansas       2110   3378        1.9    70.66   10.1
## 5 California      21198   5114        1.1    71.71   10.3
## 6   Colorado       2541   4884        0.7    72.06    6.8

Notez que les fonctions read.xlsx et read.xlsx2 peuvent être utilisées pour lire à la fois des fichiers de formats .xls et .xlsx.

Ecrire des données dans un fichier Excel

Les fonctions write.xlsx et write.xlsx2 peuvent être utilisées pour exporter des données de R vers Excel. En revanche la fonction write.xlsx2 est plus performante que write.xlsx pour les grosses tables de données (avec plus de 100 000 cases).

Le format simplifié de ces fonctions est :

write.xlsx(x, file, sheetName="Sheet1", 
  col.names=TRUE, row.names=TRUE, append=FALSE)
write.xlsx2(x, file, sheetName="Sheet1",
  col.names=TRUE, row.names=TRUE, append=FALSE)

  • x : le data.frame à écrire dans le fichier Excel
  • 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 :

library(xlsx)
write.xlsx(USArrests, file="myworkbook.xlsx", 
           sheetName="USA Arrests")

A noter que le code ci-dessus enregistre le fichier Excel dans le répertoire de travail courant de R.

Lire et écrire des fichiers Excel avec le logiciel R et le package xlsx

Il faudrait utiliser l’argument append = TRUE dans le but de pouvoir ajouter plusieurs data.frames dans le même classeur Excel. Ceci est illustré avec le code R suivant:

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

Lire et écrire des fichiers Excel avec le logiciel R et le package xlsx

Comme vous pouvez le constater sur l’image ci-dessus, il est possible d’ajouter plusieurs tables de données dans le même fichier Excel. Cependant, la méthode est très répétitive. Vous trouverez dans la section suivante, une fonction simple pour ajouter différents types de données en une seule ligne de code.

Un fonction R simple pour exporter rapidement plusieurs data sets dans le même classeur Excel

Cette partie propose une fonction R pour facilement exporter plusieurs tables de données en une seule ligne de commande. Les différentes tables sont écrites dans le même classeur Excel. Le nom des tables est utilisé pour nommer les différentes feuilles Excel.

Le code R de la fonction est la suivante :

#+++++++++++++++++++++++++++
# xlsx.writeMultipleData
#+++++++++++++++++++++++++++++
# file : le chemin du fichier résultat
# ... : une liste de tables à exporter vers Excel
xlsx.writeMultipleData <- function (file, ...)
  {
    require(xlsx, quietly = TRUE)
    objects <- list(...)
    fargs <- as.list(match.call(expand.dots = TRUE))
    objnames <- as.character(fargs)[-c(1, 2)]
    nobjects <- length(objects)
    for (i in 1:nobjects) {
        if (i == 1)
            write.xlsx(objects[[i]], file, sheetName = objnames[i])
        else write.xlsx(objects[[i]], file, sheetName = objnames[i],
            append = TRUE)
    }
  }

Cette fonction est inspirée de celle publiée sur le site statmethods

La fonction xlsx.writeMultipleData fonctionne pour les objets R de types data frames, matrices, séries temporelles et tables.

Exemples d’utilisation :

Utiliser le code R ci-dessous pour enregistrez les tables mtcars (une data frame), Titanic (une table), AirPassengers (une série temporelle) et state.x77 (une matrice) :

xlsx.writeMultipleData("myworkbook.xlsx",
        mtcars, Titanic, AirPassengers, state.x77)

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

Créer et formater un classeur Excel

L’objectif de cette section est de vous montrer comment créer un beau rapport Excel contenant des graphiques et des tables formatées.

Les étapes suivantes sont nécessaires :


  1. Créer un classeur Excel
  2. Définir des styles de cellules : Couleur et taille de la police, alignement du texte, bordure et data frame, …
  3. Ecrire une table dans une feuille Excel en utilisant les styles définis à l’étape 2.
  4. Enregistrez le classeur Excel
  5. Ouvrir et visualiser le fichier


Dans les prochaines sections, je vais vous montrer étape par étape comment changer l’apparence d’une feuille Excel dans R. Ceci n’est pas une tache simple avec le package xlsx et nécessite du “code pur et dur”. C’est pour cela que j’ai récemment implémenté le package r2excel qui dépend du package xlsx et il fournit des fonctions simples pour rapidement importer, exporter et formater des fichiers Excel. Le package r2excel est décris dans mon précédent article : r2excel : Créer et formater facilement un document Excel avec le logiciel R

Etape 1/5. Créer un nouveau classeur Excel

La fonction createWorkbook() peut être utilisée pour créer des fichiers xls ou xlsx :

# Créer un classeur Excel
wb<-createWorkbook(type="xlsx")

Etape 2/5. Définir des styles pour formater le classeur Excel

Nous allons définir des styles pour changer :

  • L’apparence du titre de la feuille
  • L’apparence des noms de lignes/colonnes de la table
  • L’alignement du texte du nom des colonnes des tables
  • Les bordures des cellules autour des noms des colonnes

La fonction CellStyle() peut être utilisée pour créer des styles de cellules. Un format simplifié est :

CellStyle(wb, dataFormat=NULL, alignment=NULL,
          border=NULL, fill=NULL, font=NULL)

  • wb : le classeur Excel (objet).
  • dataFormat : un objet DataFormat
  • alignment : un objet Alignment
  • border : un objet Border
  • font : un objet Font


# Définir quelques styles de cellules
#++++++++++++++++++++
# Titres et sous-titres
TITLE_STYLE <- CellStyle(wb)+ Font(wb,  heightInPoints=16, 
                            color="blue", isBold=TRUE, underline=1)
SUB_TITLE_STYLE <- CellStyle(wb) + 
                    Font(wb,  heightInPoints=14, 
                          isItalic=TRUE, isBold=FALSE)
# Styles pour les noms de lignes/colonnes
TABLE_ROWNAMES_STYLE <- CellStyle(wb) + Font(wb, isBold=TRUE)
TABLE_COLNAMES_STYLE <- CellStyle(wb) + Font(wb, isBold=TRUE) +
    Alignment(wrapText=TRUE, horizontal="ALIGN_CENTER") +
    Border(color="black", position=c("TOP", "BOTTOM"), 
           pen=c("BORDER_THIN", "BORDER_THICK")) 

  1. wb : un objet workbook (classeur Excel)
  2. Les arguments principaux pour la fonction Font() :
    • color : couleur de la police
    • heightInPoints : taille de la police. Valeurs habituelles 10, 12, 14, etc
    • isBold, isItalic : une valeur logique spécifiant si la police doit être en gras ou en italic
    • underline : un entier indiquant l’épaisseur du soulignement. Valeurs possibles : 0, 1, 2.
    • name : la police à utiliser; i.e: “Arial”.
  3. Les arguments principaux pour la fonction Alignment() :
    • wrapText : valeur logique indiquant l’utilisation du retour à la ligne automatique (utile si le texte est trop long).
    • horizontal : l’alignement horizontal du texte. Valeurs possibles : “ALIGN_CENTER”, “ALIGN_JUSTIFY”, “ALIGN_LEFT”, “ALIGN_RIGHT”.
    • vertical : l’alignemen vertical. Valeurs possibles : “VERTICAL_BOTTOM”, “VERTICAL_CENTER”, “VERTICAL_JUSTIFY”, “VERTICAL_TOP”
    • rotation : degré de rotation du texte dans une cellule. Valeur par défaut 0.
  4. Les arguments principaux pour la fonction Border() :
    • color : couleur des bordures; i.e : color=“red” ou color =“#FF0000”
    • position : la position de la bordure. Valeurs possibles : “BOTTOM”, “LEFT”, “TOP”, “RIGHT”
    • pen : le style du stylo. Valeurs possibles : “BORDER_DASH_DOT”, “BORDER_DASH_DOT_DOT”, “BORDER_DASHED”, “BORDER_DOTTED”, “BORDER_DOUBLE”, “BORDER_HAIR”, “BORDER_MEDIUM”, “BORDER_MEDIUM_DASH_DOT”, “BORDER_MEDIUM_DASH_DOT_DOT”, “BORDER_MEDIUM_DASHED”, “BORDER_NONE”, “BORDER_SLANTED_DASH_DOT”, “BORDER_THICK”, “BORDER_THIN”.


Etape 3/5. Ajouter des données et des graphiques dans le classeur

Créer une nouvelle feuille dans le classeur

Pour ajouter les données, la première étape est de créer une feuille dans le classeur pour contenir les données. Ceci peut être effectué en utilisant la fonction creatSheet() :

# Créer une nouvelle feuille dans le classeur
sheet <- createSheet(wb, sheetName = "US State Facts")

Ajouter un titre dans la feuille Excel

Pour ajouter un titre, la procédure est :

  1. créer une nouvelle ligne
  2. créer une cellule pour contenir le titre.
  3. ajouter du texte dans la cellule.

Pour simplifier le code R, j’ai écrit une fonction pour l’ajout des titres :

#++++++++++++++++++++++++
# Fonction helper pour ajouter des titres
#++++++++++++++++++++++++
# - sheet : la feuille Excel pour contenir le titre
# - rowIndex : numéro de la ligne pour contenir le titre 
# - title : texte du titre
# - titleStyle : l'objet style pour le titre
xlsx.addTitle<-function(sheet, rowIndex, title, titleStyle){
  rows <-createRow(sheet,rowIndex=rowIndex)
  sheetTitle <-createCell(rows, colIndex=1)
  setCellValue(sheetTitle[[1,1]], title)
  setCellStyle(sheetTitle[[1,1]], titleStyle)
}

Copier et coller le code de la fonction xlsx.addTitle (ci-dessus) dans la console R avant de poursuivre.

# Ajouter un titre
xlsx.addTitle(sheet, rowIndex=1, title="US State Facts",
      titleStyle = TITLE_STYLE)
# Ajouter un sous-titre
xlsx.addTitle(sheet, rowIndex=2, 
      title="Data sets related to the 50 states of USA.",
      titleStyle = SUB_TITLE_STYLE)

Ajouter une table dans le classeur

La fonction addDataframe() pour ajouter une table dans une nouvelle feuille Excel.

Le jeu de données state.x77 est utilisé dans les exemples suivants :

head(state.x77[, 1:6])
##            Population Income Illiteracy Life Exp Murder HS Grad
## Alabama          3615   3624        2.1    69.05   15.1    41.3
## Alaska            365   6315        1.5    69.31   11.3    66.7
## Arizona          2212   4530        1.8    70.55    7.8    58.1
## Arkansas         2110   3378        1.9    70.66   10.1    39.9
## California      21198   5114        1.1    71.71   10.3    62.6
## Colorado         2541   4884        0.7    72.06    6.8    63.9
# Ajouter une table
addDataFrame(state.x77, sheet, startRow=3, startColumn=1, 
             colnamesStyle = TABLE_COLNAMES_STYLE,
             rownamesStyle = TABLE_ROWNAMES_STYLE)
# Changer la largeur des colonnes
setColumnWidth(sheet, colIndex=c(1:ncol(state.x77)), colWidth=11)

  • Arguments pour la fonction addDataFrame() :
    • startRow, startColumn : nombre indiquant la ligne et la colonne de début de la table
    • colnameStyle, rownameStyle : un objet CellStyle pour personnaliser le nom des colonnes/lignes de la table
  • Arguments pour la fonction setColumnWidth() :
    • colIndex : un vecteur de type numeric spécifiant les colonnes dont la largeur doit être changée
    • colWidth : la largeur de la colonne

Ajouter un graphe dans une feuille Excel

# Créer un graphe en png
png("boxplot.png", height=800, width=800, res=250, pointsize=8)
boxplot(count ~ spray, data = InsectSprays,
         col = "blue")
dev.off()
# Créer une nouvelle feuille pour contenir le graphique
sheet <-createSheet(wb, sheetName = "boxplot")
# Ajouter un titre à la feuille
xlsx.addTitle(sheet, rowIndex=1, 
              title="Box plot using InsectSprays data",
              titleStyle = TITLE_STYLE)
# Ajouter le graphe créé précédemment
addPicture("boxplot.png", sheet, scale = 1, startRow = 4,
          startColumn = 1)
# Supprimer le graphe du disque dur
res<-file.remove("boxplot.png")

Etape 4/5. Enregistrer le classeur Excel

# Enregistrer le classeur dans un fichier
saveWorkbook(wb, "r-xlsx-report-example.xlsx")

Etape 5/5. Ouvrir et visualiser le fichier Excel

Ouvrez le répertoire courant de R et ouvrez le classeur Excel que vous venez de créer.

Lire et écrire des fichiers Excel avec le logiciel R et le package xlsx Lire et écrire des fichiers Excel avec le logiciel R et le package xlsx

Comme je l’ai mentionné plus haut, le formatage des feuilles Excel peut se faire simplement et rapidement en utilisant le package r2excel.

Le script complet pour créer et formmater un fichier Excel

library(xlsx)
# Créer un workbook (classeur)
#++++++++++++++++++++++++++++++++++++
# Valeurs possibles pour l'argument type : "xls" et "xlsx"
wb<-createWorkbook(type="xlsx")
# Definir quelques styles
#++++++++++++++++++++++++++++++++++++
# Titre et sous-titre
TITLE_STYLE <- CellStyle(wb)+ Font(wb,  heightInPoints=16, 
                                   color="blue", isBold=TRUE, underline=1)
SUB_TITLE_STYLE <- CellStyle(wb) + 
  Font(wb,  heightInPoints=14,
       isItalic=TRUE, isBold=FALSE)
# Styles pour le nom des lignes/colonnes
TABLE_ROWNAMES_STYLE <- CellStyle(wb) + Font(wb, isBold=TRUE)
TABLE_COLNAMES_STYLE <- CellStyle(wb) + Font(wb, isBold=TRUE) +
  Alignment(wrapText=TRUE, horizontal="ALIGN_CENTER") +
  Border(color="black", position=c("TOP", "BOTTOM"), 
         pen=c("BORDER_THIN", "BORDER_THICK")) 
# Créer une nouvelle feuille Excel
#++++++++++++++++++++++++++++++++++++
sheet <- createSheet(wb, sheetName = "US State Facts")
#++++++++++++++++++++++++
# Fonction helper pour ajouter des titres
#++++++++++++++++++++++++
# - sheet : la feuille Excel pour contenir le titre
# - rowIndex : numéro de la ligne pour contenir le titre 
# - title : texte du titre
# - titleStyle : l'objet style pour le titre
xlsx.addTitle<-function(sheet, rowIndex, title, titleStyle){
  rows <-createRow(sheet,rowIndex=rowIndex)
  sheetTitle <-createCell(rows, colIndex=1)
  setCellValue(sheetTitle[[1,1]], title)
  setCellStyle(sheetTitle[[1,1]], titleStyle)
}
# Ajouter des titres et sous-titres dans la feuille
#++++++++++++++++++++++++++++++++++++
# Ajouter un titre
xlsx.addTitle(sheet, rowIndex=1, title="US State Facts",
      titleStyle = TITLE_STYLE)
# Ajouter un sous-titre
xlsx.addTitle(sheet, rowIndex=2, 
      title="Data sets related to the 50 states of USA.",
      titleStyle = SUB_TITLE_STYLE)
# Ajouter une table
#++++++++++++++++++++++++++++++++++++
addDataFrame(state.x77, sheet, startRow=3, startColumn=1, 
             colnamesStyle = TABLE_COLNAMES_STYLE,
             rownamesStyle = TABLE_ROWNAMES_STYLE)
# Changer la largeur des colonnes
setColumnWidth(sheet, colIndex=c(1:ncol(state.x77)), colWidth=11)
# Ajouter un graphe
#++++++++++++++++++++++++++++++++++++
# Créer un graphe en png
png("boxplot.png", height=800, width=800, res=250, pointsize=8)
boxplot(count ~ spray, data = InsectSprays,
         col = "blue")
dev.off()
# Créer une nouvelle feuille pour contenir le graphe
sheet <-createSheet(wb, sheetName = "boxplot")
# Ajouter un titre
xlsx.addTitle(sheet, rowIndex=1, title="Box plot using InsectSprays data",
      titleStyle = TITLE_STYLE)
# Ajouter le graphe créé précédemment
addPicture("boxplot.png", sheet, scale = 1, startRow = 4,
           startColumn = 1)
# supprimer le graphe du disque
res<-file.remove("boxplot.png")
# Enregistrer le calsseur Excel...
#++++++++++++++++++++++++++++++++++++
saveWorkbook(wb, "r-xlsx-report-example.xlsx")

Infos

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









Want to Learn More on R Programming and Data Science?

Follow us by Email

by FeedBurner

On Social Networks:


 Get involved :
  Click to follow us on and Google+ :   
  Comment this article by clicking on "Discussion" button (top-right position of this page)
  Sign up as a member and post news and articles on STHDA web site.
Cette page a été vue 35782 fois