CODEWITHSUNDEEP
rexcel
Zaheer Abbas
Zaheer Abbas

Reputation: 103

How to Export Large data into Excel

I am using sink() function for printing R output to excel/text file. my program output is too large. I found at the end of Excel/text output file a message 

[ reached getOption("max.print") -- omitted 5326 rows ]

Also I have more than one large outputs to print. I also want them on separate (different) sheets of one excel file .

Actually, the output comes in matrix form, as the value of loop increases, the rows and columns of square matrices also increases. the function append does not work due to difference of rows and columns number. to avoid this error I used if condition and deal separately using rbind for similar matrices. But there is more than 5 thousand iteration through double loop (and the method also repeats), so it is impossible to write 1000's of if and rbind conditions and save them into separate sheets and files.

Upvotes: 2

Views: 6496

Answers (2)

user1317221_G
user1317221_G

Reputation: 15441

if you don't want a csv you can use package xlsx and it is pretty easy to save multiple sheets e.g.

df1 <- data.frame(x=1:100, y=1:100)   # make data
df2 <- data.frame(x=1:1000, y=1:1000) 

library(xlsx)

wb <- createWorkbook()         # create blank workbook
sheet1 <- createSheet(wb, sheetName="mysheet1") # create different sheets
sheet2 <- createSheet(wb, sheetName="mysheet2")
addDataFrame(df1, sheet1)  # add data to the sheets
addDataFrame(df2, sheet2)
saveWorkbook(wb, "mysheets.xlsx")  # write the file with multiple sheets

Upvotes: 11

Aviram Segal
Aviram Segal

Reputation: 11120

You can write your data frame to a csv file and then open it in excel using write.csv (Read its documentation using ?write.csv):

## To write a CSV file for input to Excel one might use
x <- data.frame(a = I("a \" quote"), b = pi)
write.table(x, file = "foo.csv", sep = ",", col.names = NA,
            qmethod = "double")

Upvotes: 5

Related Questions