CODEWITHSUNDEEP
rexcel
sgp667
sgp667

Reputation: 1875

build xls files in R with a header on top of column names

I have several hundred xls files that have incorrect data in them.

I need to open them make corrections and save them.

Making corrections is a trivial matter so I already wrote the code for that, trick is that each sheet has one table which starts on row 3 and first two rows contain legal header.

I am accustomed to using readxl package but it does not provide tools for saving spreadsheets. So today I have been experimenting with xslx package but I'm not quite sure how to make it work.

In readr I was able to use following to write csv files with a disclaimer:

write_csv(Disclaimer, filepath,col_names = FALSE)
write_csv(my.data.frame,filepath, col_names = TRUE,append =TRUE)

In xlsx this doesn't work:

write.xlsx(filepath,Disclaimer,"Sheet1",col.names = FALSE)
write.xlsx(filepath,my.data.frame,"Sheet1",col.names = TRUE,append=TRUE)

This yeilds a java error:

java.lang.IllegalArgumentException: The workbook already contains a sheet of this name

So my question is how can you write a xls file(alternatively xlsx but not csv), such that it has a header above the acutual table.

For the record my workbooks have two sheets, both sheets have the disclaimer, but they contain different tables.

I really wish I could move away from excel files but I need to maintain original format.

Also I'm open to using other packages but I'm not familiar with others(reasearching XLConnect as we speak).

Upvotes: 0

Views: 1297

Answers (1)

sgp667
sgp667

Reputation: 1875

I figured it out!!!

xlsx has other low level functions that let you build each sheet piece by piece.

See my test code bellow:

wb <- createWorkbook(type = "xls")
sh1 <- createSheet(wb,sheetName = "Sheet1")
addDataFrame(data.frame("Disclaimer"=c("Disclaimer")),sheet = sh1,row.names = FALSE,startRow = 1,col.names = FALSE)
addDataFrame(data.frame("Col1" = c(1,2,3),"Col2"=4:6),sheet = sh1,row.names = FALSE,startRow = 2)

sh2 <- createSheet(wb,sheetName = "Sheet2")
addDataFrame(data.frame("Disclaimer"=c("Disclaimer")),sheet = sh2,row.names = FALSE,startRow = 1,col.names = FALSE)
addDataFrame(data.frame("Col1" = c(1,2,3),"Col2"=4:6),sheet = sh2,row.names = FALSE,startRow = 2)

saveWorkbook(wb,"test_wb.xls")

Upvotes: 2

Related Questions