CODEWITHSUNDEEP
rexcelloopsworksheet
Samantha Sullivan
Samantha Sullivan

Reputation: 5

Create new worksheet for each column within one excel file in R

I am trying to take an excel file I have (let's say it has 4 columns named "one","two","three","four" and the excel file is called "test") and make a separate worksheet for each column. So within in my "test" file, I would have 4 worksheets. In the first worksheet would be the all of the values in column "one", the next worksheet would be all of the values in column "two", etc.

I tried using a loop like this:

for(i in 1:ncol(test)){
write.xlsx(test[i,,], file="filename.xlsx", sheetName=names(test)[i])
}

But it didn't work.

Any help is appreciated!!!

Upvotes: 0

Views: 1375

Answers (1)

Rick Pack
Rick Pack

Reputation: 1074

One concern is you have two commas in the bracketed data frame reference, and the first bracketed argument will grab the row not the column. Also, unlike data frames, vectors do not have column names:

colnames(mtcars[,1])
NULL

First, I'll show the openxlsx code I used to produce what I believe you wanted. Then I show code with the xlsx package given the corruption error you are seeing with openxlsx. I hope the comments express sufficiently what the code does.

openxlsx package version

library(openxlsx)
# Create the workbook data structure as wb
wb <- createWorkbook()
for(i in 1:ncol(mtcars)){
# shtname will be the ith column name in mtcars  
  shtname = names(mtcars)[i]
# add a sheet to wb that is named the ith column name
  addWorksheet(wb, shtname)
# Turn the ith column vector of mtcars into a dataframe 
# so we can give the object a column name
  mtcars_col_frm <-  data.frame(mtcars[,i])
  colnames(mtcars_col_frm) <- shtname
# write into that sheet the ith column of mtcars
  writeData(wb, i, mtcars_col_frm, colNames = TRUE)
}
# save all of the created sheets into a workbook
# Note that I used overwrite = TRUE to avoid 
# "File already exists" error you may experience
saveWorkbook(wb, file="filename.xlsx", overwrite = TRUE)

xlsx package version

library(xlsx)
# Create the workbook data structure as wb
wb <- xlsx::createWorkbook()
for(i in 1:ncol(mtcars)){
  # shtname will be the ith column name in mtcars  
  shtname = names(mtcars)[i]
  # add a sheet to wb that is named the ith column name
  sht <- xlsx::createSheet(wb, shtname)
  # Turn the ith column vector of mtcars into a dataframe 
  # so we can give the object a column name
  mtcars_col_frm <-  data.frame(mtcars[,i])
  colnames(mtcars_col_frm) <- shtname
  # write into that sheet the ith column of mtcars
  xlsx::addDataFrame(x = mtcars_col_frm, sheet = sht, row.names = FALSE)
}
# save all of the created sheets into a workbook
xlsx::saveWorkbook(wb, file="filename.xlsx")

Upvotes: 1

Related Questions