CODEWITHSUNDEEP
rexcel
Polar Bear
Polar Bear

Reputation: 731

R: Writing data frame into excel with large number of rows

I have a data frame (panel form) in R with 194498 rows and 7 columns. I want to write it to an Excel file (.xlsx) using function res <- write.xlsx(df, output) but R goes in the coma (keeps showing stop sign on the top left of console) without making any change in the targeted file(output). Finally shows following: Error in .jcheck(silent = FALSE) : Java Exception <no description because toString() failed>.jcall(row[[ir]], "Lorg/apache/poi/ss/usermodel/Cell;", "createCell", as.integer(colIndex[ic] - 1))<S4 object of class "jobjRef"> I have loaded readxl and xlsx packages. Please suggest to fix it. Thanks.

Upvotes: 5

Views: 9795

Answers (3)

Brigitta Rebane
Brigitta Rebane

Reputation: 3

  1. In addition to Pete's answer I wouldn't recommend write.csv because it takes or can take minutes to load. I used fwrite() (from data.table library) and it did the same thing in about 1-2 secs.

  2. The post author asked about large files. I dealt with a table about 2,3 million rows long and write.data (and frwrite) aren't able to write more than about 1 million rows. It just cuts the data away. So instead use write.table(Data, file="Data.txt"). You can open it in Excel and split the one column by your delimiter (use argument sep) and voila!

Upvotes: 0

Pete
Pete

Reputation: 168

This does not answer your question, but might be a solution to your problem.

Could save the file as a CSV instead like so:

write.csv(df , "df.csv")

open the CSV and then save as an Excel file.

I gave up on trying to import/export Excel files with R because of hassles like this.

Upvotes: 2

Sowmya S. Manian
Sowmya S. Manian

Reputation: 3843

Install and load package named 'WriteXLS' and try writing out your R object using function WriteXLS(). Make sure your R object is written in quotes like the one below "data".

 # Store your data with 194498 rows and 7 columns in a data frame named   'data'

 # Install package named WriteXLS
 install.packages("WriteXLS")

 # Loading package
 library(WriteXLS)

 # Writing out R object 'data' in an Excel file created namely data.xlsx 
 WriteXLS("data",ExcelFileName="data.xlsx",row.names=F,col.names=T)

Hope this helped.

Upvotes: 4

Related Questions