PROYECTO FINAL
Luis Alejandro Cruz Lemus
13/10/2017
Openxlsx para R
El objeto de este documento es el de establecer un pequeño manual para poder trabajar el programa openxlsx en R.
El progarma Openxlsx simplifica la creación de Excel, toda vez que trabaja con archivos con terminación .xlsx, creando una interface para escribir, estilizar y editar hojas de trabajo. A través del uso de ‘Rcpp’, los tiempos de lectura y escritura pueden ser comparables a los de paquetes ‘xlsx’ y ‘XLConnect’ con el beneficio adicional de no depender de la utilización del programa de Java.
Para iniciar se tiene que llevar a cabo la instalación del programa openxlsx, para lo cual es necesario seleccionar en RStudio con el cursos la pestaña de Packages y en el botón Install se busca el programa openxlsx, y se procede con la descarga del mismo.
library (nombre del programa a utilizar)
Una vez instalado el programa openxlsx en R, se utiliza el comando “library”, para que dicho programa se carge en R.
library(openxlsx)Con esta instrucción R carga el programa openxlsx, y en ese momento se pueden emplear los comando de openXlsx en R.
Help (Ayuda)
El programa openxlsx incluye una forma de ayuda, en la cual se puden consultar algunos comandos básicos de dicho programa, asimismo adiciona algunos documentos en PDF, los cuales incluyen instrucciones para la su instalación y algunos ejemplos para practicar.
help("openxlsx-package")## starting httpd help server ... doneOtra manera de obtener ayuda, para poder tratar de entender openxlsx, es ir a la pestaña de packages de RStudio, buscar el programa y dar click con el cursor sobre el nombre del programa, para que de manera automática despliega hipervínculos, que contienen documentación en PDF y algunos ejemplos, así como algunas páginas de ayuda.
Un ejemplo básico para entender Openxlsx tomado del paquete de Openxlxs
El programa openxlsx se emplea principalmente para generar libros de trabajo o workbook.
La manera más simple para escribir un libro de trabajo es con el comando write.xlsx().
El comando write.xlsx se llama writeData. Si la asTable es cierta (= TRUE) write.xlsx deberá escribir x como una tabla de Excel.
A fin de poder entender mejor, desarrollaremos el ejemplo que se encuentra en la ayuda de openxlsx.
Hoja de trabajo como una tabla de Excel.
Con este ejemplo comprenderemos la manera de trabajar una hoja de trabajo como una tabla de Excel.
"S1"=iris
"S2"=mtcars
wb<-createWorkbook("PruebaTrabajoFinal1")
addWorksheet(wb,"S1")
addWorksheet(wb, "S2")
addWorksheet(wb, "S3")
writeDataTable(wb, "S1", x=iris)
writeDataTable(wb, "S2", x=mtcars, xy=c("B",3), rowNames = TRUE,tableStyle = "TableStyleLight9")
df<-data.frame("Date"=Sys.Date()-0:19,
"LogicalT"=TRUE,
"Time"=Sys.time()-0:19*60*60,
"Cash"=paste("$",1:20), "Cash2"=31:50,
"hLink"="https://CRAN.R-project.org/",
"Percentage"=seq(0,1,length.out = 20),
"TinyNumbers"=runif(20)/ 1E9, stringAsFactors=FALSE)
class(df$Cash)<-c(class(df$Cash), "currency")
class(df$Cash2)<-c(class(df$Cash2), "accounting")
class(df$hLink)<-c(class(df$hLink), "hyperlink")
class(df$Percentage)<-c(class(df$Percentage), "percentage")
class(df$TinyNumbers)<-c(class(df$TinyNumbers), "scientific")
writeDataTable(wb, "S3", x=df, startRow = 4,rowNames = TRUE, tableStyle = "TableStyleMedium9")
writeDataTable(wb, sheet=1, x=iris,startCol = 7, headerStyle = createStyle(textRotation = 45), withFilter = FALSE)
openXL(wb)
saveWorkbook(wb, "writeDataTebleExample.xlsx", overwrite = TRUE)Directorio de trabajo.
Es importente mencionar, que como dato curioso cuando se escribe el comando write.xlsx (iris), el programa R nos indica que se trata de un dataset de Fisher y Anderson que proporciona las medidas en centimetros de las variables de la cepa de tres especies de flores Iris.
options("openxlsx.borderColour"="#4F80BD")
write.xlsx(iris, file = "writeXLSX1.xlsx", colNames=TRUE, borders="columns")
write.xlsx(iris, file = "writeXLSC2.xlsx", colNames=TRUE, borders="surrounding")
hs<-createStyle(textDecoration = "BOLD", fontColour = "#FFFFFF", fontSize = 12,
fontName = "Arial Narrow",fgFill = "#4F80BD")
write.xlsx(iris,file="writeXLSX3.xlsx", colNames=TRUE, borders="rows", headerStyle=hs)Directorio de trabajo con variables.
#Escribir el directorio de trabajo.
write.xlsx(iris, file = "writeXLSX1.xlsx")
write.xlsx(iris, file= "writeXLSXTable1.xlsx", asTable = FALSE)
#Escribir una lista de data.frames para hojas de trabajo individual.
list("IRIS" = iris)## $IRIS
## Sepal.Length Sepal.Width Petal.Length Petal.Width Species
## 1 5.1 3.5 1.4 0.2 setosa
## 2 4.9 3.0 1.4 0.2 setosa
## 3 4.7 3.2 1.3 0.2 setosa
## 4 4.6 3.1 1.5 0.2 setosa
## 5 5.0 3.6 1.4 0.2 setosa
## 6 5.4 3.9 1.7 0.4 setosa
## 7 4.6 3.4 1.4 0.3 setosa
## 8 5.0 3.4 1.5 0.2 setosa
## 9 4.4 2.9 1.4 0.2 setosa
## 10 4.9 3.1 1.5 0.1 setosa
## 11 5.4 3.7 1.5 0.2 setosa
## 12 4.8 3.4 1.6 0.2 setosa
## 13 4.8 3.0 1.4 0.1 setosa
## 14 4.3 3.0 1.1 0.1 setosa
## 15 5.8 4.0 1.2 0.2 setosa
## 16 5.7 4.4 1.5 0.4 setosa
## 17 5.4 3.9 1.3 0.4 setosa
## 18 5.1 3.5 1.4 0.3 setosa
## 19 5.7 3.8 1.7 0.3 setosa
## 20 5.1 3.8 1.5 0.3 setosa
## 21 5.4 3.4 1.7 0.2 setosa
## 22 5.1 3.7 1.5 0.4 setosa
## 23 4.6 3.6 1.0 0.2 setosa
## 24 5.1 3.3 1.7 0.5 setosa
## 25 4.8 3.4 1.9 0.2 setosa
## 26 5.0 3.0 1.6 0.2 setosa
## 27 5.0 3.4 1.6 0.4 setosa
## 28 5.2 3.5 1.5 0.2 setosa
## 29 5.2 3.4 1.4 0.2 setosa
## 30 4.7 3.2 1.6 0.2 setosa
## 31 4.8 3.1 1.6 0.2 setosa
## 32 5.4 3.4 1.5 0.4 setosa
## 33 5.2 4.1 1.5 0.1 setosa
## 34 5.5 4.2 1.4 0.2 setosa
## 35 4.9 3.1 1.5 0.2 setosa
## 36 5.0 3.2 1.2 0.2 setosa
## 37 5.5 3.5 1.3 0.2 setosa
## 38 4.9 3.6 1.4 0.1 setosa
## 39 4.4 3.0 1.3 0.2 setosa
## 40 5.1 3.4 1.5 0.2 setosa
## 41 5.0 3.5 1.3 0.3 setosa
## 42 4.5 2.3 1.3 0.3 setosa
## 43 4.4 3.2 1.3 0.2 setosa
## 44 5.0 3.5 1.6 0.6 setosa
## 45 5.1 3.8 1.9 0.4 setosa
## 46 4.8 3.0 1.4 0.3 setosa
## 47 5.1 3.8 1.6 0.2 setosa
## 48 4.6 3.2 1.4 0.2 setosa
## 49 5.3 3.7 1.5 0.2 setosa
## 50 5.0 3.3 1.4 0.2 setosa
## 51 7.0 3.2 4.7 1.4 versicolor
## 52 6.4 3.2 4.5 1.5 versicolor
## 53 6.9 3.1 4.9 1.5 versicolor
## 54 5.5 2.3 4.0 1.3 versicolor
## 55 6.5 2.8 4.6 1.5 versicolor
## 56 5.7 2.8 4.5 1.3 versicolor
## 57 6.3 3.3 4.7 1.6 versicolor
## 58 4.9 2.4 3.3 1.0 versicolor
## 59 6.6 2.9 4.6 1.3 versicolor
## 60 5.2 2.7 3.9 1.4 versicolor
## 61 5.0 2.0 3.5 1.0 versicolor
## 62 5.9 3.0 4.2 1.5 versicolor
## 63 6.0 2.2 4.0 1.0 versicolor
## 64 6.1 2.9 4.7 1.4 versicolor
## 65 5.6 2.9 3.6 1.3 versicolor
## 66 6.7 3.1 4.4 1.4 versicolor
## 67 5.6 3.0 4.5 1.5 versicolor
## 68 5.8 2.7 4.1 1.0 versicolor
## 69 6.2 2.2 4.5 1.5 versicolor
## 70 5.6 2.5 3.9 1.1 versicolor
## 71 5.9 3.2 4.8 1.8 versicolor
## 72 6.1 2.8 4.0 1.3 versicolor
## 73 6.3 2.5 4.9 1.5 versicolor
## 74 6.1 2.8 4.7 1.2 versicolor
## 75 6.4 2.9 4.3 1.3 versicolor
## 76 6.6 3.0 4.4 1.4 versicolor
## 77 6.8 2.8 4.8 1.4 versicolor
## 78 6.7 3.0 5.0 1.7 versicolor
## 79 6.0 2.9 4.5 1.5 versicolor
## 80 5.7 2.6 3.5 1.0 versicolor
## 81 5.5 2.4 3.8 1.1 versicolor
## 82 5.5 2.4 3.7 1.0 versicolor
## 83 5.8 2.7 3.9 1.2 versicolor
## 84 6.0 2.7 5.1 1.6 versicolor
## 85 5.4 3.0 4.5 1.5 versicolor
## 86 6.0 3.4 4.5 1.6 versicolor
## 87 6.7 3.1 4.7 1.5 versicolor
## 88 6.3 2.3 4.4 1.3 versicolor
## 89 5.6 3.0 4.1 1.3 versicolor
## 90 5.5 2.5 4.0 1.3 versicolor
## 91 5.5 2.6 4.4 1.2 versicolor
## 92 6.1 3.0 4.6 1.4 versicolor
## 93 5.8 2.6 4.0 1.2 versicolor
## 94 5.0 2.3 3.3 1.0 versicolor
## 95 5.6 2.7 4.2 1.3 versicolor
## 96 5.7 3.0 4.2 1.2 versicolor
## 97 5.7 2.9 4.2 1.3 versicolor
## 98 6.2 2.9 4.3 1.3 versicolor
## 99 5.1 2.5 3.0 1.1 versicolor
## 100 5.7 2.8 4.1 1.3 versicolor
## 101 6.3 3.3 6.0 2.5 virginica
## 102 5.8 2.7 5.1 1.9 virginica
## 103 7.1 3.0 5.9 2.1 virginica
## 104 6.3 2.9 5.6 1.8 virginica
## 105 6.5 3.0 5.8 2.2 virginica
## 106 7.6 3.0 6.6 2.1 virginica
## 107 4.9 2.5 4.5 1.7 virginica
## 108 7.3 2.9 6.3 1.8 virginica
## 109 6.7 2.5 5.8 1.8 virginica
## 110 7.2 3.6 6.1 2.5 virginica
## 111 6.5 3.2 5.1 2.0 virginica
## 112 6.4 2.7 5.3 1.9 virginica
## 113 6.8 3.0 5.5 2.1 virginica
## 114 5.7 2.5 5.0 2.0 virginica
## 115 5.8 2.8 5.1 2.4 virginica
## 116 6.4 3.2 5.3 2.3 virginica
## 117 6.5 3.0 5.5 1.8 virginica
## 118 7.7 3.8 6.7 2.2 virginica
## 119 7.7 2.6 6.9 2.3 virginica
## 120 6.0 2.2 5.0 1.5 virginica
## 121 6.9 3.2 5.7 2.3 virginica
## 122 5.6 2.8 4.9 2.0 virginica
## 123 7.7 2.8 6.7 2.0 virginica
## 124 6.3 2.7 4.9 1.8 virginica
## 125 6.7 3.3 5.7 2.1 virginica
## 126 7.2 3.2 6.0 1.8 virginica
## 127 6.2 2.8 4.8 1.8 virginica
## 128 6.1 3.0 4.9 1.8 virginica
## 129 6.4 2.8 5.6 2.1 virginica
## 130 7.2 3.0 5.8 1.6 virginica
## 131 7.4 2.8 6.1 1.9 virginica
## 132 7.9 3.8 6.4 2.0 virginica
## 133 6.4 2.8 5.6 2.2 virginica
## 134 6.3 2.8 5.1 1.5 virginica
## 135 6.1 2.6 5.6 1.4 virginica
## 136 7.7 3.0 6.1 2.3 virginica
## 137 6.3 3.4 5.6 2.4 virginica
## 138 6.4 3.1 5.5 1.8 virginica
## 139 6.0 3.0 4.8 1.8 virginica
## 140 6.9 3.1 5.4 2.1 virginica
## 141 6.7 3.1 5.6 2.4 virginica
## 142 6.9 3.1 5.1 2.3 virginica
## 143 5.8 2.7 5.1 1.9 virginica
## 144 6.8 3.2 5.9 2.3 virginica
## 145 6.7 3.3 5.7 2.5 virginica
## 146 6.7 3.0 5.2 2.3 virginica
## 147 6.3 2.5 5.0 1.9 virginica
## 148 6.5 3.0 5.2 2.0 virginica
## 149 6.2 3.4 5.4 2.3 virginica
## 150 5.9 3.0 5.1 1.8 virginicawrite.xlsx(iris, file = "writeXLXS2.xlsx")
write.xlsx(iris, file = "writeXLSX2Table2.xlsx", asTable = FALSE)
options("openxlsx.borderColour"="#4F80BD")
options("openxlsx.borderStyle"="thin")
options("openxlsx.dateFormat"="mm/dd/yyyy")
options("openxlsx.datetimeFormat"="yyyy-mm-dd hh:mm:ss")
options("openxlsx.numFmt"=NULL)
df<-data.frame("Date"=Sys.Date()-0:19,
"LogicalT"=TRUE,
"Time"=Sys.time()-0:19*60*60,
"Cash"=paste("$",1:20), "Cash2"=31:50,
"hLink"="https://CRAN.R-project.org/",
"Percentage"=seq(0,1,length.out = 20),
"TinyNumbers"=runif(20)/ 1E9, stringAsFactors=FALSE)
class(df$Cash)<-c(class(df$Cash), "currency")
class(df$Cash2)<-c(class(df$Cash2), "accounting")
class(df$hLink)<-c(class(df$hLink), "hyperlink")
class(df$Percentage)<-c(class(df$Percentage), "percentage")
class(df$TinyNumbers)<-c(class(df$TinyNumbers), "scientific")
write.xlsx(df, "writeXLSX3.xlsx")
write.xlsx(df, file = "writeXLSX3Table3.xlsx", asTable = FALSE)
#Se puede agregar estilo adicional.
hs<-createStyle(fontColour = "#ffffff", fgFill = "#4F80BD",
halign = "center", valign = "center", textDecoration = "Bold",
border = "TopBottomLeftRight", textRotation = 45)
write.xlsx(iris,file = "writeXLSX4.xlsx", borders="rows",headerStyle=hs)
write.xlsx(iris,file = "writeXLSX5.xlsx", borders="columns", headerStyle=hs)
write.xlsx(iris, "writeXLSXTable4.xlsx", asTable = FALSE)
headerStyle= createStyle(textRotation = 45)
#Cuando escribes una lista, el estilo aplica a todos los elementos de la misma.
list("IRIS"=iris, "colClasses"=df)## $IRIS
## Sepal.Length Sepal.Width Petal.Length Petal.Width Species
## 1 5.1 3.5 1.4 0.2 setosa
## 2 4.9 3.0 1.4 0.2 setosa
## 3 4.7 3.2 1.3 0.2 setosa
## 4 4.6 3.1 1.5 0.2 setosa
## 5 5.0 3.6 1.4 0.2 setosa
## 6 5.4 3.9 1.7 0.4 setosa
## 7 4.6 3.4 1.4 0.3 setosa
## 8 5.0 3.4 1.5 0.2 setosa
## 9 4.4 2.9 1.4 0.2 setosa
## 10 4.9 3.1 1.5 0.1 setosa
## 11 5.4 3.7 1.5 0.2 setosa
## 12 4.8 3.4 1.6 0.2 setosa
## 13 4.8 3.0 1.4 0.1 setosa
## 14 4.3 3.0 1.1 0.1 setosa
## 15 5.8 4.0 1.2 0.2 setosa
## 16 5.7 4.4 1.5 0.4 setosa
## 17 5.4 3.9 1.3 0.4 setosa
## 18 5.1 3.5 1.4 0.3 setosa
## 19 5.7 3.8 1.7 0.3 setosa
## 20 5.1 3.8 1.5 0.3 setosa
## 21 5.4 3.4 1.7 0.2 setosa
## 22 5.1 3.7 1.5 0.4 setosa
## 23 4.6 3.6 1.0 0.2 setosa
## 24 5.1 3.3 1.7 0.5 setosa
## 25 4.8 3.4 1.9 0.2 setosa
## 26 5.0 3.0 1.6 0.2 setosa
## 27 5.0 3.4 1.6 0.4 setosa
## 28 5.2 3.5 1.5 0.2 setosa
## 29 5.2 3.4 1.4 0.2 setosa
## 30 4.7 3.2 1.6 0.2 setosa
## 31 4.8 3.1 1.6 0.2 setosa
## 32 5.4 3.4 1.5 0.4 setosa
## 33 5.2 4.1 1.5 0.1 setosa
## 34 5.5 4.2 1.4 0.2 setosa
## 35 4.9 3.1 1.5 0.2 setosa
## 36 5.0 3.2 1.2 0.2 setosa
## 37 5.5 3.5 1.3 0.2 setosa
## 38 4.9 3.6 1.4 0.1 setosa
## 39 4.4 3.0 1.3 0.2 setosa
## 40 5.1 3.4 1.5 0.2 setosa
## 41 5.0 3.5 1.3 0.3 setosa
## 42 4.5 2.3 1.3 0.3 setosa
## 43 4.4 3.2 1.3 0.2 setosa
## 44 5.0 3.5 1.6 0.6 setosa
## 45 5.1 3.8 1.9 0.4 setosa
## 46 4.8 3.0 1.4 0.3 setosa
## 47 5.1 3.8 1.6 0.2 setosa
## 48 4.6 3.2 1.4 0.2 setosa
## 49 5.3 3.7 1.5 0.2 setosa
## 50 5.0 3.3 1.4 0.2 setosa
## 51 7.0 3.2 4.7 1.4 versicolor
## 52 6.4 3.2 4.5 1.5 versicolor
## 53 6.9 3.1 4.9 1.5 versicolor
## 54 5.5 2.3 4.0 1.3 versicolor
## 55 6.5 2.8 4.6 1.5 versicolor
## 56 5.7 2.8 4.5 1.3 versicolor
## 57 6.3 3.3 4.7 1.6 versicolor
## 58 4.9 2.4 3.3 1.0 versicolor
## 59 6.6 2.9 4.6 1.3 versicolor
## 60 5.2 2.7 3.9 1.4 versicolor
## 61 5.0 2.0 3.5 1.0 versicolor
## 62 5.9 3.0 4.2 1.5 versicolor
## 63 6.0 2.2 4.0 1.0 versicolor
## 64 6.1 2.9 4.7 1.4 versicolor
## 65 5.6 2.9 3.6 1.3 versicolor
## 66 6.7 3.1 4.4 1.4 versicolor
## 67 5.6 3.0 4.5 1.5 versicolor
## 68 5.8 2.7 4.1 1.0 versicolor
## 69 6.2 2.2 4.5 1.5 versicolor
## 70 5.6 2.5 3.9 1.1 versicolor
## 71 5.9 3.2 4.8 1.8 versicolor
## 72 6.1 2.8 4.0 1.3 versicolor
## 73 6.3 2.5 4.9 1.5 versicolor
## 74 6.1 2.8 4.7 1.2 versicolor
## 75 6.4 2.9 4.3 1.3 versicolor
## 76 6.6 3.0 4.4 1.4 versicolor
## 77 6.8 2.8 4.8 1.4 versicolor
## 78 6.7 3.0 5.0 1.7 versicolor
## 79 6.0 2.9 4.5 1.5 versicolor
## 80 5.7 2.6 3.5 1.0 versicolor
## 81 5.5 2.4 3.8 1.1 versicolor
## 82 5.5 2.4 3.7 1.0 versicolor
## 83 5.8 2.7 3.9 1.2 versicolor
## 84 6.0 2.7 5.1 1.6 versicolor
## 85 5.4 3.0 4.5 1.5 versicolor
## 86 6.0 3.4 4.5 1.6 versicolor
## 87 6.7 3.1 4.7 1.5 versicolor
## 88 6.3 2.3 4.4 1.3 versicolor
## 89 5.6 3.0 4.1 1.3 versicolor
## 90 5.5 2.5 4.0 1.3 versicolor
## 91 5.5 2.6 4.4 1.2 versicolor
## 92 6.1 3.0 4.6 1.4 versicolor
## 93 5.8 2.6 4.0 1.2 versicolor
## 94 5.0 2.3 3.3 1.0 versicolor
## 95 5.6 2.7 4.2 1.3 versicolor
## 96 5.7 3.0 4.2 1.2 versicolor
## 97 5.7 2.9 4.2 1.3 versicolor
## 98 6.2 2.9 4.3 1.3 versicolor
## 99 5.1 2.5 3.0 1.1 versicolor
## 100 5.7 2.8 4.1 1.3 versicolor
## 101 6.3 3.3 6.0 2.5 virginica
## 102 5.8 2.7 5.1 1.9 virginica
## 103 7.1 3.0 5.9 2.1 virginica
## 104 6.3 2.9 5.6 1.8 virginica
## 105 6.5 3.0 5.8 2.2 virginica
## 106 7.6 3.0 6.6 2.1 virginica
## 107 4.9 2.5 4.5 1.7 virginica
## 108 7.3 2.9 6.3 1.8 virginica
## 109 6.7 2.5 5.8 1.8 virginica
## 110 7.2 3.6 6.1 2.5 virginica
## 111 6.5 3.2 5.1 2.0 virginica
## 112 6.4 2.7 5.3 1.9 virginica
## 113 6.8 3.0 5.5 2.1 virginica
## 114 5.7 2.5 5.0 2.0 virginica
## 115 5.8 2.8 5.1 2.4 virginica
## 116 6.4 3.2 5.3 2.3 virginica
## 117 6.5 3.0 5.5 1.8 virginica
## 118 7.7 3.8 6.7 2.2 virginica
## 119 7.7 2.6 6.9 2.3 virginica
## 120 6.0 2.2 5.0 1.5 virginica
## 121 6.9 3.2 5.7 2.3 virginica
## 122 5.6 2.8 4.9 2.0 virginica
## 123 7.7 2.8 6.7 2.0 virginica
## 124 6.3 2.7 4.9 1.8 virginica
## 125 6.7 3.3 5.7 2.1 virginica
## 126 7.2 3.2 6.0 1.8 virginica
## 127 6.2 2.8 4.8 1.8 virginica
## 128 6.1 3.0 4.9 1.8 virginica
## 129 6.4 2.8 5.6 2.1 virginica
## 130 7.2 3.0 5.8 1.6 virginica
## 131 7.4 2.8 6.1 1.9 virginica
## 132 7.9 3.8 6.4 2.0 virginica
## 133 6.4 2.8 5.6 2.2 virginica
## 134 6.3 2.8 5.1 1.5 virginica
## 135 6.1 2.6 5.6 1.4 virginica
## 136 7.7 3.0 6.1 2.3 virginica
## 137 6.3 3.4 5.6 2.4 virginica
## 138 6.4 3.1 5.5 1.8 virginica
## 139 6.0 3.0 4.8 1.8 virginica
## 140 6.9 3.1 5.4 2.1 virginica
## 141 6.7 3.1 5.6 2.4 virginica
## 142 6.9 3.1 5.1 2.3 virginica
## 143 5.8 2.7 5.1 1.9 virginica
## 144 6.8 3.2 5.9 2.3 virginica
## 145 6.7 3.3 5.7 2.5 virginica
## 146 6.7 3.0 5.2 2.3 virginica
## 147 6.3 2.5 5.0 1.9 virginica
## 148 6.5 3.0 5.2 2.0 virginica
## 149 6.2 3.4 5.4 2.3 virginica
## 150 5.9 3.0 5.1 1.8 virginica
##
## $colClasses
## Date LogicalT Time Cash Cash2
## 1 2017-10-16 TRUE 2017-10-16 22:40:49 $ 1 31
## 2 2017-10-15 TRUE 2017-10-16 21:40:49 $ 2 32
## 3 2017-10-14 TRUE 2017-10-16 20:40:49 $ 3 33
## 4 2017-10-13 TRUE 2017-10-16 19:40:49 $ 4 34
## 5 2017-10-12 TRUE 2017-10-16 18:40:49 $ 5 35
## 6 2017-10-11 TRUE 2017-10-16 17:40:49 $ 6 36
## 7 2017-10-10 TRUE 2017-10-16 16:40:49 $ 7 37
## 8 2017-10-09 TRUE 2017-10-16 15:40:49 $ 8 38
## 9 2017-10-08 TRUE 2017-10-16 14:40:49 $ 9 39
## 10 2017-10-07 TRUE 2017-10-16 13:40:49 $ 10 40
## 11 2017-10-06 TRUE 2017-10-16 12:40:49 $ 11 41
## 12 2017-10-05 TRUE 2017-10-16 11:40:49 $ 12 42
## 13 2017-10-04 TRUE 2017-10-16 10:40:49 $ 13 43
## 14 2017-10-03 TRUE 2017-10-16 09:40:49 $ 14 44
## 15 2017-10-02 TRUE 2017-10-16 08:40:49 $ 15 45
## 16 2017-10-01 TRUE 2017-10-16 07:40:49 $ 16 46
## 17 2017-09-30 TRUE 2017-10-16 06:40:49 $ 17 47
## 18 2017-09-29 TRUE 2017-10-16 05:40:49 $ 18 48
## 19 2017-09-28 TRUE 2017-10-16 04:40:49 $ 19 49
## 20 2017-09-27 TRUE 2017-10-16 03:40:49 $ 20 50
## hLink Percentage TinyNumbers stringAsFactors
## 1 https://CRAN.R-project.org/ 0.00000000 4.760662e-10 FALSE
## 2 https://CRAN.R-project.org/ 0.05263158 6.375701e-10 FALSE
## 3 https://CRAN.R-project.org/ 0.10526316 8.777271e-10 FALSE
## 4 https://CRAN.R-project.org/ 0.15789474 4.173912e-10 FALSE
## 5 https://CRAN.R-project.org/ 0.21052632 7.910187e-10 FALSE
## 6 https://CRAN.R-project.org/ 0.26315789 4.508706e-11 FALSE
## 7 https://CRAN.R-project.org/ 0.31578947 6.478571e-10 FALSE
## 8 https://CRAN.R-project.org/ 0.36842105 3.395236e-10 FALSE
## 9 https://CRAN.R-project.org/ 0.42105263 2.873550e-11 FALSE
## 10 https://CRAN.R-project.org/ 0.47368421 7.634587e-10 FALSE
## 11 https://CRAN.R-project.org/ 0.52631579 5.399706e-10 FALSE
## 12 https://CRAN.R-project.org/ 0.57894737 7.241445e-10 FALSE
## 13 https://CRAN.R-project.org/ 0.63157895 4.562718e-10 FALSE
## 14 https://CRAN.R-project.org/ 0.68421053 6.808689e-10 FALSE
## 15 https://CRAN.R-project.org/ 0.73684211 7.338542e-10 FALSE
## 16 https://CRAN.R-project.org/ 0.78947368 8.772301e-10 FALSE
## 17 https://CRAN.R-project.org/ 0.84210526 5.669493e-10 FALSE
## 18 https://CRAN.R-project.org/ 0.89473684 8.122619e-10 FALSE
## 19 https://CRAN.R-project.org/ 0.94736842 1.021806e-10 FALSE
## 20 https://CRAN.R-project.org/ 1.00000000 2.436771e-10 FALSEwrite.xlsx(iris,file = "writeXLSX6.xlsx", borders="columns",headerStyle=hs)
write.xlsx(iris,file = "writeXLSXTable6.xlsx", asTable = FALSE, tableStyle="TableStyleMedium9")
openXL(wb)
wb<-write.xlsx(iris,"writeXLSX6.xlsx")
setColWidths(wb,sheet = 1, cols = 1:5, widths = 20)
saveWorkbook(wb, "writeXLSX6.xlsx", overwrite = TRUE)Otro ejemplo, desde un libro de trabajo básico.
Para este ejemplo necesitaremos utilizar el paquete ggplot2, para poder graficar los datos utilizados, para lo cual empelamos el comando library (nombre del programa), con la finalidad de que el paquete ggplot2 se cargue en R y podamos hacer uso de sus comando para elaborar la grafica correspondiente.
library(ggplot2)
#Estableciendo el color de los bordes y el estilo.
wb<-createWorkbook()
options("openxlsx.borderColour"="#4F80BD")
options("openxlsc.borderStyle"="thin")
modifyBaseFont(wb, fontSize = 10, fontName = "Arial Narrow")
addWorksheet(wb, sheetName = "Motor Trend Car Road Test", gridLines = FALSE)
addWorksheet(wb, sheetName = "Iris", gridLines = FALSE)
#Hoja 1
freezePane(wb, sheet = 1, firstRow = TRUE, firstCol = TRUE) ## freeze first row and column
writeDataTable(wb,sheet = 1,x=mtcars, colNames = TRUE, rowNames = TRUE)
setColWidths(wb,sheet = 1, cols = "A", widths = 18)
writeDataTable(wb,sheet = 2,iris,startCol = "L",startRow = 2)
qplot(data = iris,x=Sepal.Length,y=Sepal.Width,colour=Species)
insertPlot(wb,2,xy=c("B",16))
means<-aggregate(x=iris [,-5],by=list(iris$Species), FUN=mean)
vars<-aggregate(x=iris[,-5],by=list(iris$Species),FUN=var)
openXL(wb)
saveWorkbook(wb,"basics.xlsx", overwrite = TRUE)Media, desviación estandar, y correlación
Con los datos de nuestro ejemplo, podemos utilizarlos para obtener la media, desviación estandar y la correlación.
attach(iris)
# Statistics for the four datasets:
mean(Sepal.Length); mean(Sepal.Width); mean(Petal.Length); mean(Petal.Width)## [1] 5.843333## [1] 3.057333## [1] 3.758## [1] 1.199333sd(Sepal.Length); sd(Sepal.Width);sd(Petal.Length);sd(Petal.Width)## [1] 0.8280661## [1] 0.4358663## [1] 1.765298## [1] 0.7622377cor(Sepal.Length,Sepal.Width);cor(Petal.Length,Petal.Width)## [1] -0.1175698## [1] 0.9628654medias<-c(mean(Sepal.Length), mean(Petal.Length))Leer un documento de Excel.
Con el programa openlxsx se pueden leer documentos de Excel y convertirlos en un data.frame.
xlsxFile<-system.file("readTest.xlsx", package = "openxlsx")
df1<-read.xlsx(xlsxFile = xlsxFile, sheet = 1,skipEmptyRows = FALSE)
sapply(df1,class)## Var1 Var2 Var3 Var4 Var5 Var6
## "logical" "numeric" "numeric" "character" "numeric" "character"
## Var7
## "numeric"df2<-read.xlsx(xlsxFile = xlsxFile, sheet = 3, skipEmptyRows = TRUE)
df2$Date<-convertToDate(df2$Date)
sapply(df2,class)## Date value word bool wordZ2
## "Date" "numeric" "character" "logical" "character"df2<-read.xlsx(xlsxFile = xlsxFile,sheet = 3,skipEmptyRows = TRUE,
detectDates = TRUE)
sapply(df2,class)## Date value word bool wordZ2
## "Date" "numeric" "character" "logical" "character"head(df2)## Date value word bool wordZ2
## 1 2014-04-28 0.8390764 N-U-B-R-A FALSE FALSE-Z
## 2 2014-04-27 0.8863800 N-Z-P-S-Y TRUE TRUE-Z
## 3 2014-04-26 0.5741314 C-G-D-X-H TRUE TRUE-Z
## 4 2014-04-25 0.1366065 <NA> FALSE FALSE-Z
## 5 2014-04-24 0.3692582 B-K-A-O-W TRUE TRUE-Z
## 6 2014-04-23 NA H-P-G-O-K TRUE TRUE-Zwb<-loadWorkbook(system.file("readTest.xlsx", package = "openxlsx"))
df3<-read.xlsx(wb,sheet = 2,skipEmptyRows = FALSE,colNames = TRUE)
df4<-read.xlsx(xlsxFile,sheet=2,skipEmptyRows = FALSE,colNames = TRUE)
all.equal(df3,df4)## [1] TRUEwb<-loadWorkbook(system.file("readTest.xlsx",package = "openxlsx"))
df3<-read.xlsx(wb,sheet = 2,skipEmptyRows = FALSE,
cols = c(1,4), rows = c(1,3,4))
openXL(wb)
saveWorkbook(wb, "readTest.xlsx", overwrite = TRUE)Extraer un documento de Excel desde una URL.
Con este ejemplo se logra traer un documento de Excel desde una URL, para poder trabajarlo con los comandos del programa openxlsx.
xlsxFile<- "https://github.com/awalker89/openxlsx/raw/master/inst/readTest.xlsx"
head(read.xlsx(xlsxFile))## Var1 Var2 Var3 Var4 Var5 Var6 Var7
## 1 TRUE 1 1.00 a 42042 3209324 This NA
## 2 TRUE NA NA b 42041 <NA> NA
## 3 TRUE 2 1.34 c 42040 <NA> NA
## 4 FALSE 2 NA <NA> NA <NA> NA
## 5 FALSE 3 1.56 e NA <NA> NA
## 6 FALSE 1 1.70 f 42037 <NA> NAxlsxFile<-system.file("readTest.xlsx", package = "openxlsx")
df1<-read.xlsx(xlsxFile = "readTest.xlsx", sheet = 1,skipEmptyRows = FALSE)
sapply(df1,class)## Var1 Var2 Var3 Var4 Var5 Var6
## "logical" "numeric" "numeric" "character" "numeric" "character"
## Var7
## "numeric"df2<-read.xlsx(xlsxFile = xlsxFile, sheet = 3, skipEmptyRows = TRUE)
df2$Date<-convertToDate(df2$Date)
sapply(df2,class)## Date value word bool wordZ2
## "Date" "numeric" "character" "logical" "character"df2<-read.xlsx(xlsxFile = xlsxFile,sheet = 3,skipEmptyRows = TRUE,
detectDates = TRUE)
sapply(df2,class)## Date value word bool wordZ2
## "Date" "numeric" "character" "logical" "character"head(df2)## Date value word bool wordZ2
## 1 2014-04-28 0.8390764 N-U-B-R-A FALSE FALSE-Z
## 2 2014-04-27 0.8863800 N-Z-P-S-Y TRUE TRUE-Z
## 3 2014-04-26 0.5741314 C-G-D-X-H TRUE TRUE-Z
## 4 2014-04-25 0.1366065 <NA> FALSE FALSE-Z
## 5 2014-04-24 0.3692582 B-K-A-O-W TRUE TRUE-Z
## 6 2014-04-23 NA H-P-G-O-K TRUE TRUE-Zwb<-loadWorkbook(system.file("readTest.xlsx", package = "openxlsx"))
df3<-read.xlsx(wb,sheet = 2,skipEmptyRows = FALSE,colNames = TRUE)
df4<-read.xlsx(xlsxFile,sheet=2,skipEmptyRows = FALSE,colNames = TRUE)
all.equal(df3,df4)## [1] TRUEwb<-loadWorkbook(system.file("readTest.xlsx",package = "openxlsx"))
df3<-read.xlsx(wb,sheet = 2,skipEmptyRows = FALSE,
cols = c(1,4), rows = c(1,3,4))
openXL(wb)
saveWorkbook(wb, "readTest.xlsx2", overwrite = TRUE)