R2XLSX : lire, ?crire et formater un document Excel dans R

kassambara | 28/07/2013 | 8115 | R | R et excel, RExcel, R2XLSX, xlsx

R2XLSX est un fichier contenant un ensemble de fonctions R, que j'ai ?crite, permettant de lire et ?crire un fichier Excel dans R de mani?re simple et rapide. Il offre ?galement la possibilit? d'ajouter des graphiques et de formater les donn?es.

Installation

Pour pouvoir utiliser les fonctions contenues dans le fichier R2XLSX, vous devez ex?cuter le code suivant ? chaque session R. Les fonctions d?pendent du package xlsx. Le package xlsx est automatiquement install? par le script dans le cas o? il n'existe pas.

Code R :

 
source("http://www.sthda.com/french/upload/r2xlsx.r")

Les fonctions disponibles

xlsx.addPlot pour ajouter un graphique.
xlsx.addParagraph pour ajouter un paragraphe de textes
xlsx.addTable pour ajouter des tables.
xlsx.addHeader pour ajouter des titres.
xlsx.addLineBreak pour ajouter un saut de ligne.
xlsx.addHyperlink pour ajouter un lien hypertext.
xlsx.readFile pour lire un fichier excel.
xlsx.writeFile pour ?crire un fichier excel.
xlsx.openFile pour ouvrir le fichier excel.

Votre premier document Excel

Code R :

 
source("http://www.sthda.com/french/upload/r2xlsx.r")
 
#create an Excel workbook. Both .xls and .xlsx file formats can be used.
filename <- "Example1.xlsx"
wb <- createWorkbook(type="xlsx")
#Create a sheet in that workbook to contain the table
sheet <- createSheet(wb, sheetName = "example1")
 
#Add header
#+++++++++++++++++++++++++++++++
xlsx.addHeader(wb, sheet, value="Add table",level=1, color="black", underline=1)
xlsx.addLineBreak(sheet, 1)
 
#Add Text :Author
#+++++++++++++++++++++++++++++++
xlsx.addText(wb, sheet, 
             value="Author : Alboukadel KASSAMBARA. \n@:alboukadel.kassambara@gmail.com.\n Website : http://ww.sthda.com",
             isItalic=TRUE, colSpan=5, rowSpan=3, fontColor="darkgray", fontSize=14)
xlsx.addLineBreak(sheet, 3)
 
#Add table
#+++++++++++++++++++++++++++++++
xlsx.addTable(wb, sheet, head(iris), startCol=2)
xlsx.addLineBreak(sheet, 2)
 
#saving a workbook to an Excel file and writes the file to disk.
saveWorkbook(wb, filename)
 
#View the file
xlsx.openFile(filename)

Ajout de titre

Code R :

 
#......
#Don't forget to create a workbook and a sheet as previously
#......
#Header
#**************************************
#header level 1
xlsx.addHeader(wb, sheet, value="Header 1",level=1, color="black")
#header level 2
xlsx.addHeader(wb, sheet, value="Header 2",level=2, color="black")
#header level 3
xlsx.addHeader(wb, sheet, value="Header 3",level=3, color="black")
#header level 4
xlsx.addHeader(wb, sheet, value="Header 4",level=4, color="black")
#header level 5
xlsx.addHeader(wb, sheet, value="Header 5",level=5, color="black")
#header level 6
xlsx.addHeader(wb, sheet, value="Header 6",level=6, color="black")
 
#......
#Don't forget to save the workbook
#......

Ajout de textes

Code R :

 
#......
#Don't forget to create a workbook and a sheet as previously
#......
 
# Add texts
#**************************************
#Add Text :Author
#+++++++++++++++++++++++++++++++
xlsx.addHeader(wb, sheet, value="Author",level=2, color="black", underline=1)
xlsx.addText(wb, sheet, value="Author : Alboukadel KASSAMBARA. \n@:alboukadel.kassambara@gmail.com.\n Website : http://ww.sthda.com",
             isItalic=TRUE, colSpan=5, rowSpan=3, fontColor="darkgray", fontSize=14)
xlsx.addLineBreak(sheet, 3)# Three line breaks
 
#Add paragraph
#+++++++++++++++++++++++++++++
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."
#customized
xlsx.addParagraph(wb, sheet, paragraph, fontSize=14, isItalic=TRUE, 
                  fontColor="darkred", backGroundColor="gray")
xlsx.addLineBreak(sheet, 2)
#......
#Don't forget to save the workbook
#......

Ajouter un hyperlien

Code R :

 
#......
#Don't forget to create a workbook and a sheet as previously
#......
#Header
#**************************************
 
#Add Hyperlink
#+++++++++++++++++++++++++++++
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)
 
#......
#Don't forget to save the workbook
#......

Ajouter une table

Code R :

 
#......
#Don't forget to create a workbook and a sheet as previously
#......
 
#Add table
#+++++++++++++++++++++++++++++
#add iris data using default settings
data(iris)
#header
xlsx.addHeader(wb, sheet, value="Add table",level=2, color="black", underline=1)
xlsx.addLineBreak(sheet, 1)
 
#header
xlsx.addHeader(wb, sheet, value="Simple Table",level=3, color="black")
xlsx.addLineBreak(sheet, 1)
xlsx.addTable(wb, sheet, head(iris), startCol=2)
xlsx.addLineBreak(sheet, 2)
 
#Customized table : column data format
xlsx.addHeader(wb, sheet, value="Customized table : font color and size",
               level=3, color="black")
xlsx.addLineBreak(sheet, 1)
xlsx.addTable(wb, sheet, data= head(iris),
              fontColor="darkblue", fontSize="16"             
)
xlsx.addLineBreak(sheet, 2)
#......
#Don't forget to save the workbook
#......

Ajouter un graphique

Boxplot basic

Code R :

 
#......
#Don't forget to create a workbook and a sheet as previously
#......
#Add Plot
#+++++++++++++++++++++++++++++
#boxplot
data(ToothGrowth)
xlsx.addHeader(wb, sheet, " Add Plot", level=2, underline=1)
xlsx.addLineBreak(sheet, 1)
plotFunction<-function(){boxplot(len ~ dose, data = ToothGrowth,
                                 subset = supp == "VC", col = "yellow",
                                 main = "Guinea Pigs' Tooth Growth",
                                 xlab = "Vitamin C dose mg", ylab = "tooth length",
                                 xlim = c(0.5, 3.5), ylim = c(0, 35), yaxs = "i")
}
xlsx.addPlot(wb, sheet, plotFunction())
#......
#Don't forget to save the workbook
#......

GGPLOT2 : Courbe de densit?

Code R :

 
#......
#Don't forget to create workbook and sheet as previously
#......
#Add Plot
#+++++++++++++++++++++++++++++
#ggplot2
rquery.loadPackages("ggplot2")#for plot
set.seed(1234)
df <- data.frame(cond = factor( rep(c("A","B"), each=200) ), 
                 rating = c(rnorm(200),rnorm(200, mean=.8)))
 
# Density plots with semi-transparent fill
xlsx.addHeader(wb, sheet, "Density plots with semi-transparent fill", level=2, underline=1)
xlsx.addLineBreak(sheet, 1)
 
gg.p<-ggplot(df, aes(x=rating, fill=cond)) + geom_density(alpha=.3)
xlsx.addPlot(wb, sheet, function(){print(gg.p)})
#......
#Don't forget to save the workbook
#......

Lire un fichier Excel

Code R :

 
file <- system.file("tests", "test_import.xlsx", package = "xlsx")
res <- xlsx.readFile(file, 1)  # read first sheet
head(res)

Ecrire un fichier Excel

LA fonction suivante permet d'?crire une table de donn?es dans un fichier excel.

Code R :

 
res <- xlsx.writeFile(USArrests, file="USArrests.xlsx", sheetName="USArrests")

Un document Excel complet

Le script complet utilis? pour cr?e le document word suivant est t?l?chargeable ici : howToUse_R2XLSX.R

Le fichier Excel est t?l?chargeable en cliquant sur le lien suivant:R2XLSX_Example1.xlsx

Document Excel complet

Utilisation avanc?e - prototype des fonctions

Les prototypes des fonctions sont montr?es ci-dessous pour une utilisation avanc?e.

xlsx.addHeader

Code R :

 
#****************************************
#Add header :sheet title and subtitle
#****************************************
#wb :workbook object
#sheet : sheet object
#value : your header value
#level : header level; possible value (1 to 6)
#startRow : row index to start to write
#startCol : col index to start to write
#rowBreak : number of rows to jump before appending the data
#underline : a numeric value specifying the thickness of the underline.
#Allowed values are 0, 1, 2.
xlsx.addHeader(wb, sheet, value="Header", level=1, color="#FFFFFF",
                         startRow=NULL, startCol=2, underline=c(0,1,2))

xlsx.addText

Code R :

 
#****************************************
#Add Text : add a simple line text
#****************************************
#the function is similar to xlsx.addParagraph
#wb :workbook object
#sheet : sheet object
#value : paragraph text
#fontColor: text color. default is black. value : all colors returned by colors() can be used
#backGroundColor : fill color of the cell
#fontSize : text size
#isBold : if TRUE, the text is written in bold format
#isItalic : if TRUE, the text is written in italic format
#startRow : row index to start to write
#startCol : col index to start to write
#colSpan : number of column to be merged (paragraph : width)
#rowSpan : number of rows to be merged corresponding to paragrath height
xlsx.addText(wb,sheet, value, fontColor="#FFFFFF", fontSize=12, backGroundColor="#FFFFFF",
                            isBold=FALSE, isItalic=FALSE,
                            startRow=NULL, startCol=2, colSpan=1, rowSpan=1)

xlsx.addParagraph

Code R :

 
#****************************************
#Add paragraph
#****************************************
#wb :workbook object
#sheet : sheet object
#value : paragraph text
#fontColor: text color. default is black. value : all colors returned by colors() can be used
#fontSize : text size
#backGroundColor : fill color of the cell
#isBold : if TRUE, the text is written in bold format
#isItalic : if TRUE, the text is written in italic format
#startRow : row index to start to write
#startCol : col index to start to write
#colSpan : number of column to be merged (paragraph : width)
#rowSapn : number of rows to be merged corresponding to paragrath height
xlsx.addParagraph(wb,sheet, value, fontColor="#FFFFFF", fontSize=12, backGroundColor="#FFFFFF",
                            isBold=FALSE, isItalic=FALSE,
                            startRow=NULL, startCol=2, colSpan=10, rowSpan=5)

xlsx.addHyperlink

Code R :

 
#****************************************
#Add Hyperlink
#****************************************
#wb :workbook object
#sheet : sheet object
#value : paragraph text
#fontColor: text color. default is black. value : all colors returned by colors() can be used
#fontSize : text size
#isBold : if TRUE, the text is written in bold format
#isItalic : if TRUE, the text is written in italic format
#startRow : row index to start to write
#startCol : col index to start to write
xlsx.addHyperlink(wb,sheet, address, friendlyName, 
                            fontColor="blue", fontSize=12,
                            isBold=FALSE, isItalic=FALSE, startRow=NULL, startCol=2)

xlsx.addLineBreak

Code R :

 
#****************************************
#Add Line break to the sheet
#****************************************
#wb :workbook object
#sheet : sheet object
#value : your header value
#level : header level; possible value (1 to 6)
#startRow : row index to start to write
#startCol : col index to start to write
#numberOfLine : number of rows to jump before appending the data
xlsx.addLineBreak(sheet, numberOfLine=1)

xlsx.addTable

Code R :

 
#****************************************
#add table 
#****************************************
#wb :workbook object
#sheet : sheet object
#data : data frame
#level : header level; possible value (1 to 6)
#startRow : row index to start to write
#startCol : col index to start to write
#rowBreak : nimber of rows to jum before appending the data
#if col.names and row.names : column and row names  are written
#fontColor : color of text
#fontSize : text size
#rownamesFill: background color of table row names
#colnamesFill : background color of table column names
#rowFill : background color of pair rows (i.e : 2, 4, 6). For table styling.
#mergedColumnForRownames : number of column to merge for rownames. 
#Be carrefull : using mergedColumnForRownames is a  very slow process
xlsx.addTable(wb, sheet, data, startRow=NULL,startCol=2,
                        col.names=TRUE, row.names=TRUE, columnWidth=14,
                        fontColor="#FFFFFF", fontSize=12, rownamesFill="white", colnamesFill="white", rowFill="white",
                        mergedColumnForRownames=1,   ...)

xlsx.addPlot

Code R :

 
#****************************************
#add Plot 
#****************************************
#wb :workbook object
#sheet : sheet object
#startRow : row index to start to write
#startCol : col index to start to write
#plotFunction: plot function
xlsx.addPlot( wb, sheet, plotFunction, startRow=NULL,startCol=2,
               width=480, height=480,... )

xlsx.writeFile

Code R :

 
#****************************************
#Write a data.frame to an Excel workbook
#****************************************
#data : data frame
#file : output file name
#if col.names and row.names : column and row names  are written
#append :if TRUE data should be appended to an existing file. 
#usage : res <- xlsx.writeFile(USArrests, file="USArrests.xlsx", sheetName="USArrests")
xlsx.writeFile(data, file, sheetName="Sheet1",
               col.names=TRUE, row.names=TRUE, append=FALSE, ...)

xlsx.readFile

Code R :

 
#****************************************
#Read a data.frame from an Excel workbook
#****************************************
#file :the path to the file to read
#sheetIndex: a number representing the sheet index in the workbook.
#startRow : index of starting row
#colIndex: a numeric vector indicating the cols you want to extract. If NULL, all columns found will be extracted
#endRow: The index of the last row to pull. If NULL, read all the rows in the sheet.
#header : if TRUE, the first row is considered as row names
#usage : 
#file <- system.file("tests", "test_import.xlsx", package = "xlsx")
#res <- xlsx.readFile(file, 1)  # read first sheet
#head(res)
xlsx.readFile(file, sheetIndex=1, startRow=1, colIndex=NULL, endRow=NULL, header=TRUE,...)

xlsx.openFile

Code R :

 
#++++++++++++++++++++++++++++
#Open file
#++++++++++++++++++++++++++++
xlsx.openFile(filename)

Voir aussi

XLConnect: lire, ?crire et manipuler des fichiers Microsoft Excel ? partir de R

Source(s) : 1

Dernière modification : 19/03/2015

0 Note

Enjoyed this article? Give us 5 stars (just above this text block)! Reader needs to be STHDA member for voting. 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? Donnez nous 5 étoiles (juste au dessus de ce block)! Vous devez être membre pour voter. 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!