R wrapper function to aid reading in .xlsx worksheets and writing to original position in the workbook

read.csv, part of the base package in R, provides native support to read in .csv and .txt documents, with the column in a dataframe assigned with high accuracy to the "true" class. The XLConnect package allows the user to read in sheets from .xls and .xlsx workbooks, but the column classes are typically all assigned as factor, or must be manually re-assigned by the user. The below is a wrapper function to allow the user to quickly read in a .xlsx file to R, while leveraging the strengths of read.csv.

xlsx.out allows the user to take data (the intention is data read in from xlsx.in) and overwrite a worksheet in a workbook with that data.



## this is a wrapper function to aid reading in xlsx sheets with a better approximation of the correct column class than is natively provided by XLConnect. Allows the user to do this from a workbook without manual export to csv.

####with XLConnect


library(XLConnect)

file.names<-list.files(path='./', full.names=TRUE)
file.names
#load a file by the number in file.names
wb<-loadWorkbook(file.names[filenumber])
data.frame(getSheets(yoursheetnumber))

xlsx.in<-function(file, sheet = 1){
temp<-loadWorkbook(file, create=FALSE)
temp.test <-readWorksheet(temp, sheet)
write.csv(temp.test, "temp.csv")
out<-read.csv("temp.csv")
file.remove("temp.csv")
return(out)
}


#make sure your file is versioned first- this will overwrite the original
xlsx.out<-function(file, sheet = 1, datas){
temp<-loadWorkbook(file, create=FALSE)
clearSheet(temp, sheet)
datas<data.frame(datas)
writeWorksheet(temp,datas,sheet)
saveWorkbook(temp)
}