Articles - R

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

|    |   8232  |  Poster commentaire  |  R


    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("https://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("https://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, "https://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
    Licence - Pas d?Utilisation Commerciale - Partage dans les M?mes Conditions
    Licence Creative Commons