0

How can i append my R outputs in a single sheet of xlsx file? I am currently working on web crawling wherein i need to scrap the user reviews from website and save it in my deskstop in xlsx format. I need to every time change the website url(as user reviews are in different pages) in my code and save the output in one sheet of xlsx file.

Can you please help me with the code of appending outputs in a single sheet of xlsx file? Below is the code which i am using: Every time i need to change the website url and run the same below code and save the corresponding output in a single sheet of mydata.xlsx 

library("rvest")
htmlpage <- html("http://www.glassdoor.com/GD/Reviews/Symphony-Teleca-Reviews-E28614_P2.htm?sort.sortType=RD&sort.ascending=false&filter.employmentStatus=REGULAR&filter.employmentStatus=PART_TIME&filter.employmentStatus=UNKNOWN")
proshtml <- html_nodes(htmlpage, ".pros")
pros <- html_text(proshtml)
pros

data=data.frame(pros)

library(xlsx)
write.xlsx(data, "D:/mydata.xlsx", append=TRUE)
Ronak Kumar
  • 69
  • 1
  • 1
  • 7

2 Answers2

1

A trivial, but super-slow way:

If you only need to add (a few) row(s) to an existing Excel file, and it only has one sheet to which you want to append, you can just do a simple read => overwrite step:

SHEET.NAME <- '...' # fill in with yours
existing.data <- read.xlsx(file, sheetName = SHEET.NAME)
new.data <- rbind(existing.data, data)
write.xlsx(new.data, file, sheetName = SHEET.NAME, row.names = F, append = F)

Note:

  • It's quite slow in general, will work only for small scale
  • read.xlsx is a slow function. Try read.xlsx2 to make it much faster (see the difference in the docs)
  • If your R process is run once and keeps working for a long time, obviously don't do it this way (reading and overwriting a file is ridiculous in that case)
Jakub P.
  • 5,416
  • 2
  • 21
  • 21
-2

look at package xlsx.

?write.xlsx will show you what you want. append=TRUE is the key.

========= EDIT TO CORRECT =========

As @Jakub pointed out, append=TRUE adds another worksheet to the file.

========= EDIT TO ADD: ANOTHER METHOD ==========

Another method is to save the data to a .csv file, which could easily open from excel. In this case, the append=T works as expected (adding to the existing sheet):

write.table(df,"D:/MyFile.csv",append=T,sep=",")
PavoDive
  • 6,322
  • 2
  • 29
  • 55
  • I tried this but getting error: Error in .jcall(wb, "Lorg/apache/poi/ss/usermodel/Sheet;", "createSheet", : java.lang.IllegalArgumentException: The workbook already contains a sheet of this name – Ronak Kumar Jul 15 '15 at 12:04
  • give us some more information so we can help. Please provide a small toy sample of the data you intend to save, and the code you're using to accomplish it. Please do it **by editting your question**. – PavoDive Jul 15 '15 at 12:05
  • Thanks. i have updated my question with an example code. Please refer to it. – Ronak Kumar Jul 15 '15 at 12:18
  • 2
    Param append = TRUE does not work as suggested. It adds a NEW sheet into the Excel file! It does not append rows to existing sheet. – Jakub P. Jul 15 '15 at 12:25
  • Thanks Jakub. Is there any way to have my outputs appended in a single row to existing sheet of xlsx file? – Ronak Kumar Jul 15 '15 at 12:31