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)


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 33908 fois
Licence - Pas d?Utilisation Commerciale - Partage dans les M?mes Conditions
Licence Creative Commons