How to export R results to Excel

Hello,
I am a new user and need help to export the estimated results to Excel. Many thanks.

mydata<- read.csv("Benthic_final.csv", TRUE, ",")
mydata=as.matrix(data)
mydata[,sapply(mydata,is.numeric)]
data=mydata[,sapply(mydata,is.numeric)]

library("Hmisc")
my_data=as.matrix(data)
rcorr(my_data, type="pearson")

There are several libraries that allows you to write xls files, one of those is xls,you can do something like this.

library("Hmisc")
library(xlsx)
mydata <- read.csv("Benthic_final.csv", TRUE, ",")
mydata <- as.matrix(data)
data <- mydata[,sapply(mydata,is.numeric)]
my_data <- as.matrix(data)
results <- rcorr(my_data, type="pearson")
write.xlsx(as.data.frame(result$r), file = "results.xlsx")
3 Likes

Dear @andresrcs
Thanks for the reply.
I got this error when run your suggested codes:
write.xlsx(as.data.frame(result$r), file = "results.xlsx")
Error in write.xlsx(as.data.frame(result$r), file = "results.xlsx") :
could not find function "write.xlsx"

You have to install and load xlsx package first

install.packages('xlsx') 
library(xlsx)

Dear @andresrcs
The package was already installed. If you don't mind could you kindly download the R project from the below link (which also has the data file) to test the codes.

https://drive.google.com/drive/folders/1k1As_pptboA5LfpUNA7WfpNKGAZi5BZn?usp=sharing

This works for my with your data on rstudio server linux version, but If you are on windows you might be having problems with xlsx package and your Java installation, if you don't mind the .csv extension you can use readr::write_excel_csv() instead.

library(Hmisc)
library(xlsx)

my_data <- data.frame(
    Annelida. = c(-0.131778305, -0.030880161, 3.995076341, 0.045341,
                  -0.161345401, -0.045219353, 3.977922041, -0.262785757,
                  -0.206141008, -0.160736468, 3.711160423, -0.223712782,
                  -0.189363136, 1.002411927, 1.979772939, 1.684596561),
    Arthropoda = c(-0.094260992, 0.769075751, -0.130124631, 0.255073383,
                   -0.204691037, -0.12415447, -0.174578416, -0.083796551,
                   -0.223197192, -0.210846358, -0.247095059, -0.123972223,
                   -0.259272506, -0.344794918, 0.211751103, -0.427072885),
    Cnidaria = c(-0.259337169, -0.31086473, -0.264059728, -0.282365849,
                 -0.248036673, -0.262290926, -0.281878169, -0.262785757,
                 -0.23684214, -0.24553782, -0.318802586, -0.243660894,
                 -0.259272506, -0.344794918, -0.426701227, -0.427072885),
    Echinodermata = c(-0.259337169, -0.31086473, -0.264059728, -0.282365849,
                      -0.248036673, -0.208023032, -0.271148194, -0.262785757,
                      -0.233430903, -0.24553782, -0.333144092, -0.248647922,
                      -0.259272506, 0.318116387, -0.181142639, 1.409161416),
    Mollusca = c(4.002629584, 3.871761896, 0.046669696, 3.964714909,
                 4.006027853, 4.000205413, 0.233160647, 4.00312365,
                 4.006736539, 4.006093603, 1.287446015, 4.005286922, 4.005199083,
                 3.760978323, 3.305789316, 3.092376191),
    Nemertea. = c(-0.214316393, -0.299436788, -0.264059728, -0.269257575,
                  -0.229459972, -0.257357481, -0.206768342, -0.262785757,
                  -0.219785955, -0.230119392, -0.304461081, -0.233686838,
                  -0.212666259, -0.195105268, -0.37758951, -0.21284555),
    Nemotoda = c(-0.259337169, -0.31086473, -0.264059728, -0.295474123,
                 -0.248036673, -0.257455656, -0.281878169, -0.262785757,
                 -0.243664613, -0.24553782, -0.289413973, -0.248647922,
                 -0.259272506, -0.366179153, -0.426701227, -0.45767679),
    H = c(-0.258516586, -0.310350875, -0.263423863, -0.29396544,
          -0.247400525, -0.261868287, -0.280743747, -0.260208491,
          -0.243258602, -0.245235106, -0.331887246, -0.24807019,
          -0.257924494, -0.364629858, -0.423752036, -0.453681971),
    Dep = c(-0.249243246, -0.305147795, -0.255658461, -0.27724616,
            -0.233637653, -0.253873975, -0.257683464, -0.19672979,
            -0.232120043, -0.235579408, -0.282339984, -0.232898389,
            -0.150736596, -0.288020707, -0.180680989, -0.308416517),
    Temp = c(-0.247196727, -0.298691082, -0.256405238, -0.281995094,
             -0.239577871, -0.252169008, -0.266459571, -0.231883693,
             -0.240116893, -0.239676451, -0.312819187, -0.243582205,
             -0.228055451, -0.345390349, -0.362254684, -0.427232027),
    Salinity = c(-0.244958746, -0.302530531, -0.253542958, -0.269790375,
                 -0.236221853, -0.25506751, -0.260803529, -0.204114523,
                 -0.236931466, -0.239851823, -0.304918276, -0.238847442,
                 -0.214986029, -0.33439863, -0.329341801, -0.397285512),
    DO = c(-0.250882094, -0.307038446, -0.258031226, -0.279260683,
           -0.24076242, -0.258905194, -0.269893382, -0.225880927,
           -0.23958563, -0.242759749, -0.317169419, -0.242460379,
           -0.232914871, -0.348470706, -0.374009308, -0.419875019),
    Chl_a = c(-0.25732373, -0.310325051, -0.26261275, -0.292426781,
              -0.246143724, -0.261578666, -0.279225632, -0.255764008,
              -0.242803163, -0.24457332, -0.33029591, -0.247627663,
              -0.252735595, -0.362899326, -0.418711673, -0.451419975)
)

result <- rcorr(as.matrix(my_data), type="pearson")
write.xlsx(as.data.frame(result$r), file = "results.xlsx")

Created on 2019-01-06 by the reprex package (v0.2.1)

1 Like

Dear @andresrcs
Still I didn't get anything. I also run only library(xlsx) and got the below error:

library(xlsx)
Error: package or namespace load failed for ‘xlsx’:
.onLoad failed in loadNamespace() for 'rJava', details:
call: fun(libname, pkgname)
error: JAVA_HOME cannot be determined from the Registry
In addition: Warning message:
package ‘xlsx’ was built under R version 3.5.2

PS: how to complete the "readr::write_excel_csv()" for my datat?

For working with excel, you have also those :package:

  • writexl (only writing)
  • readxl (only reading)
  • openxlsx (allows advanced operation)

The advantage of these ones is that they do not have a Java dependency.

You have several discussions here on this topic


4 Likes

Dear @cderv
How can I use it for for my data.
Could you kindly download the R project from the below link (which also has the data file) to test the codes.

https://drive.google.com/drive/folders/1k1As_pptboA5LfpUNA7WfpNKGAZi5BZn?usp=sharing

The three packages listed by @cderv are your best bet, for sure.

Use this syntax to install and load the package:

install.packages("writexl")
library(writexl)

you can read about writexl from the package documentation

3 Likes

Dear @jdlong,
I installed the suggested package, but still need your help to complete the below code to export the estimated results in excel etc. file format. Thank you for the help.

mydata<- read.csv("Benthic_final.csv", TRUE, ",")
mydata=as.matrix(data)
mydata[,sapply(mydata,is.numeric)]
data=mydata[,sapply(mydata,is.numeric)]
library(writexl)
library("Hmisc")
my_data=as.matrix(data)
rcorr(my_data, type="pearson")

jdlong already told you how you can learn to do that, we are not here to do your job for you, we are here to point you in the right direction.

If you can't implement yourself the solutions we already gave you, maybe you need to work a little more in your r basic skills, this online book by Roger Peng is a very good starting point.

4 Likes

What have you tried?

What result did you get?

What is your specific question?

2 Likes

Dear @jdlong
Thanks for the kind reply. I run the code like this:

mydata<- read.csv("Benthic_final.csv", TRUE, ",")
mydata=as.matrix(data)
mydata[,sapply(mydata,is.numeric)]
data=mydata[,sapply(mydata,is.numeric)]
library(writexl)
library("Hmisc")
my_data=as.matrix(data)
rcorr(my_data, type="pearson")

write.xlsx(as.data.frame(result$r), file = "results.xlsx")

and got this error again:
write.xlsx(as.data.frame(result$r), file = "results.xlsx")
Error in write.xlsx(as.data.frame(result$r), file = "results.xlsx") :
could not find function "write.xlsx"

PS: Dear @andresrcs, I try my self but still got the error. I am going to read Sir. Roger Peng' book and just downloaded it. But right now I need to find a quick way to export the estimated results in excel format, so I can summarize the findings and write them into my research report. Thanks for your helps.

If you read the package documentation you will see that in this particular package the function is written with an underscore instead of a dot

write_xlsx(as.data.frame(result$r), path = "results.xlsx")
1 Like

Dear @andresrcs
This time, I got this error:

write_xlsx(as.data.frame(result$r), file = "results.xlsx")
Error in write_xlsx(as.data.frame(result$r), file = "results.xlsx") :
unused argument (file = "results.xlsx")

With that library the correct parameter name is path

write_xlsx(as.data.frame(result$r), path = "results.xlsx")
1 Like

Dear @andresrcs
Sir this time the error is like:

write_xlsx(as.data.frame(result$r), path = "results.xlsx")
Error in as.data.frame(result$r) : object 'result' not found

You are missing this

result <- rcorr(my_data, type="pearson")
1 Like

Dear @andresrcs
This time it works perfectly and my estimated results are exported as xls file into my directory. But my estimated results had two section:

  1. Results of correlation (already exported, no problem)
  2. Results of significant test, p value (whether the correlation between two parameters is significant or not)

So how I can also export the results about p value?