Home > R > Write data (frame) to Excel file using R package xlsx

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.

About these ads
Categories: R Tags: , , , ,
  1. Robert
    February 13, 2012 at 23:20

    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

    • February 14, 2012 at 09:48

      I’m on 64 bit and it works just fine. I think you have problems with Java/rJava and not xlsx.

  2. Roger J. Bos
    February 16, 2012 at 13:50

    How does xlsx compare to XLConnect,which I have started using?

  3. February 16, 2012 at 14:28

    @Roger, they appear to be quite similar. XLConnect does have an extensive vignette, which is definitely a plus in my book.

  4. Måns
    February 16, 2012 at 17:23

    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

  5. Ree
    March 20, 2012 at 14:22

    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?

    • James M. Ward
      August 11, 2012 at 06:10

      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!

  6. Dennis
    June 6, 2012 at 23:10

    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

  7. June 6, 2012 at 23:14

    @Dennis, works for me. You may have a wild java install…

  8. abu
    August 17, 2012 at 08:42

    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

    • August 17, 2012 at 09:10

      You need to specify a different sheetName argument value. If you’re writing to the same sheet, you need to say TRUE to append.

      • abu
        August 17, 2012 at 21:58

        I said False and it still overwrite my whole sheets. I do not want the other sheets overwritten

  9. August 19, 2012 at 03:00

    abu :
    I said False and it still overwrite my whole sheets. I do not want the other sheets overwritten

    Shouldn’t you use TRUE?

    • Felipe Freitas
      January 29, 2013 at 05:52

      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?

      • January 29, 2013 at 11:23

        What code did you use? Can you provide a reproducible example?

  10. January 17, 2013 at 15:31

    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!

  11. abu
    January 29, 2013 at 14:50

    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.

    • Luiz Felipe Freitas
      January 29, 2013 at 21:45

      Thank you @abu!

  12. Francisco
    February 24, 2013 at 05:30

    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.

  13. Reani
    June 3, 2013 at 10:32

    Hi,

    Thanx for this code. I try use in R-studio but code did not work (rjava problem), but in R perfectly. Thanks again.

  14. martyz
    October 24, 2013 at 12:58

    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

  15. Jiac
    November 20, 2013 at 16:15

    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.

  1. January 1, 2014 at 06:22

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: