Write data (frame) to Excel file using R package xlsx
Writing to Excel files comes up rather often, especially if you’re collaborating with non-OSS users. There are several options, but I like the xlsx package way of doing things. Authors use Java to write to Excel files, which are basically compressed XML files.
Alright, let’s get cracking.
First, let’s create some data.
sample.dataframe
If you don’t have the file created yet, you can just write the data into a new file.
library(xlsx) #load the package
write.xlsx(x = sample.dataframe, file = "test.excelfile.xlsx",
sheetName = "TestSheet", row.names = FALSE)
If you already have a file created, you can add data to a new sheet, or just add it to the existing one. Here’s how you would add a data.frame to columns D and E (result not shown).
workbook.sheets workbook.test addDataFrame(x = sample.dataframe, sheet = workbook.test, row.names = FALSE, startColumn = 4) # write data to sheet starting on line 1, column 4 saveWorkbook(workbook.sheets, "test.excelfile.xlsx") # and of course you need to save it.
You can now open the file in your WYSIWYG editor.
Dang, another error 
Unfortunately, this package doesn’t work under x64 windows. I always get a message:
Error : .onLoad failed in loadNamespace() for ‘rJava’, details:
call: stop(“No CurrentVersion entry in ‘”, key, “‘! Try re-installing Java and make sure R and Java have matching architectures.”)
error: object ‘key’ not found
Error: package ‘rJava’ could not be loaded
I’m on 64 bit and it works just fine. I think you have problems with Java/rJava and not xlsx.
How does xlsx compare to XLConnect,which I have started using?
@Roger, they appear to be quite similar. XLConnect does have an extensive vignette, which is definitely a plus in my book.
I got this:
Installing package(s) into ‘C:/Users/MM/Documents/R/win-library/2.13’
(as ‘lib’ is unspecified)
trying URL ‘http://ftp.sunet.se/pub/lang/CRAN/bin/windows/contrib/2.13/xlsx_0.4.1.zip’
Warning in install.packages :
cannot open: HTTP status was ‘404 Not Found’
Error in download.file(url, destfile, method, mode = “wb”, …) :
cannot open URL ‘http://ftp.sunet.se/pub/lang/CRAN/bin/windows/contrib/2.13/xlsx_0.4.1.zip’
Warning in install.packages :
download of package ‘xlsx’ failed
Have you tried other mirrors?
I have a problem with some cell contents that are similar to calender dates. For instance, the human gene SEPT11 changes to the date Sept-11 if I press F2 for any reason in excel. I would like to avoid this by specifying the cell content as text for the first columns but leave all other columns as numeric. Is there a way to do this while saving with the write.xlsx function?
Ree, I think the easiest fix might be the global Excel setting, to have it not auto-convert dates. There are lots of genes affected by that though, it’s frustrating! Sept5, March1, Oct4, Apr4, Dec1, etc. I don’t work with dates as my primary focus in Excel, so it helped me much more to make that change than not. Otherwise, try wrapping text strings in quotes?
I had to add this line to my .Rprofile file to ensure that no other rJava packages would mess up the Java mode:
options( java.parameters = c(“-Djava.awt.headless=true”, “-Xmx1g”, “-Xrs”) );
It sets “headless” mode, and gives 1 Gb memory.
Also I think the option “-Xrs” is very important, otherwise once you initialize any rJava session, any time you press Ctrl-C it will kill your whole R session! Ouch!
re: “Unfortunately, this package doesn’t work under x64 windows. I always get a message:”
I received the same error when loading the rJava package using R2.15.0 64 bit version x64 windows, but worked well using the 32 bit version of R2.15.0
@Dennis, works for me. You may have a wild java install…
Hi danganothererror, I tried xlsx and it works very fine to read excle files, the problem is that whenever I try to write to excel file using the write function it always overwrites it and you get only one sheet at the end. all the sheets are deleted and you remain with one sheet you write hhow do i write to new sheet in the same file wothout overwriting what is there? thanks
You need to specify a different sheetName argument value. If you’re writing to the same sheet, you need to say TRUE to append.
I said False and it still overwrite my whole sheets. I do not want the other sheets overwritten
Shouldn’t you use TRUE?
Hi
I set append to TRUE and I got the following error message
Error in .jcall(wb, “Lorg/apache/poi/ss/usermodel/Sheet;”, “createSheet”, :
java.lang.IllegalArgumentException: The workbook already contains a sheet of this name
Any idea of what’s going on?
What code did you use? Can you provide a reproducible example?
Hello! I’ve been reading your site for a long time now and finally got the bravery to go ahead and give you a shout out from Lubbock Texas! Just wanted to mention keep up the fantastic work!
I found a solution with working with R-> EXCEL
to import use library(xlsReadWrite)
to Export use library(XLConnect)
They work very well especially when you are using many several sheets of the same worksheet.
Secondly, they do not read data as they are unlike xlsx which read numeric as factors in some cases.
Thank you @abu!
Library(xlsReadWrite) does not work with the latest version of R. I have found a very simple way of dealing with the general problem of writing output to a file for subsequent use by Excel.
My output is a correlation matrix of a dataframe. Let us call the dataframe df2 and the output that we want to use in Excell is the correlation matrix that results from the following command:
cor(df2)
I first write this output as a csv table, as follows:
write.table(cor(df2),file=”cor.csv”,sep=”,”)
If you now go to your R workspace you will find a file named cor.csv. Just click on this file and it will be opened by Excel. You can then work on this file and save it like any other Excel file. Since each piece of data in your matrix is separated from the next one by a comma, you don’t need to worry about reformattng text.
Hi,
Thanx for this code. I try use in R-studio but code did not work (rjava problem), but in R perfectly. Thanks again.
I had the same problem, and found out you can use xlsx on windowsx64, by installing the x32 version of R, this way you dont get the rJava error
I’ve always got an error in R when I tried to load the package “xlsx”.
Loading required package: xlsxjars
Loading required package: rJava
Error : .onLoad failed in loadNamespace() for ‘rJava’, details:
call: fun(libname, pkgname)
error: JAVA_HOME cannot be determined from the Registry
In addition: Warning messages:
1: package ‘xlsx’ was built under R version 2.15.3
2: package ‘xlsxjars’ was built under R version 2.15.3
3: package ‘rJava’ was built under R version 2.15.3
Error: package ‘rJava’ could not be loaded
I’m using R version 2.15.2. Can you tell me what should I do? Thanks in advance.
Yes, upgrade to version 3.0 or higher.