writing data in to excel with title above header - R

Question

Here is how my excel output look like in one sheet,

Total Expenses

Region Jan-18 Feb-18 Mar-18 
Reg1   32     65     56
Reg2   24     45     89
Reg3   15     78     23

Average Expenses

Region Jan-18 Feb-18 Mar-18
Reg1    12    14     13
Reg2    13    15     14
Reg3    11    14     12

I am constructing this in R using rbind by first adding the title with the corresponding dataset, then adding empty line and then the datasets. However by doing this, I am loosing the numeric nature of the actual data. Can we do it while preserving the numeric datatype for my numbers ?

Note : Above is sample data to be written in one sheet of excel, similarly I am constructing 4 different sheets as list of data frames and using write.xlsx to output them.

Not so easy to get a perfect solution: with [THIS IDEA](https://stackoverflow.com/questions/18511249/excel-cell-coloring-using-xlsx) and some research [HERE](https://cran.r-project.org/web/packages/xlsx/xlsx.pdf) about `DataFormat()` and `CellStyle` you can archieve something. — Andre Elrico, Apr 30 '18 at 07:15

digEmAll · Accepted Answer · 2018-04-30T08:38:41.267

Here's a working example using xlsx package :

totExpenses <- data.frame('Region'=c('Reg1','Reg2','Reg3'),
                          'Jan-18'=c(32,24,15),
                          'Feb-18'=c(65,45,78),
                          'Mar-18'=c(56,89,23),check.names=FALSE)
avgExpenses <- data.frame('Region'=c('Reg1','Reg2','Reg3'),
                          'Jan-18'=c(12,13,11),
                          'Feb-18'=c(14,15,14),
                          'Mar-18'=c(13,14,12),check.names=FALSE)


library(xlsx)

wb <- createWorkbook()
sh <- createSheet(wb,"mySheet")
row <- 1
addDataFrame(data.frame("Total Expenses"=double(),check.names=FALSE),
             sheet = sh,startRow=row,row.names=FALSE)
row <- row + 2
addDataFrame(totExpenses,sheet=sh,startRow=row,row.names=FALSE)
row <- row + nrow(totExpenses) + 2
addDataFrame(data.frame("Average Expenses"=double(),check.names=FALSE),
             sheet=sh,startRow=row,row.names=FALSE)
row <- row + 2
addDataFrame(avgExpenses,sheet=sh,startRow=row,row.names=FALSE)
saveWorkbook(wb,file='myfile.xlsx')

Result :

Here's a custom function that takes a named list of data.frame's and writes them to a excel sheet with the desired structure :

writeListOfDFToSheet <- function(lst,workBook,sheetName){

  sh <- createSheet(workBook,sheetName)

  # force names in list if not present
  if(is.null(names(lst))) 
    names(lst) <- rep.int("",length(lst))
  names(lst) <- ifelse(names(lst) == "",paste("data.frame",1:length(lst)),names(lst))

  row <- 1
  for(i in seq_len(length(lst))){
    DFName <- names(lst)[i]
    DF <- as.data.frame(lst[[i]])

    # header
    addDataFrame(setNames(data.frame(x=logical()),DFName),
                 sheet=sh,startRow=row,row.names=FALSE)
    row <- row + 2

    # data.frame
    addDataFrame(DF,
                 sheet=sh,startRow=row,row.names=FALSE)
    row <- row + nrow(DF) + 2
  }

  invisible(NULL)
}

Example of usage (assuming the previous 2 data.frame's being defined) :

# create a workbook 
wb <- createWorkbook()

# write to "sheet 1" 
lst1 <- list("Total Expenses"=totExpenses,"Average Expenses"=avgExpenses)
writeListOfDFToSheet(lst1,wb,"sheet 1")

# write to "sheet 2" 
# (I'm using the same data.frame's but it's just an example)
lst2 <- list("Total Expenses"=totExpenses,"Average Expenses"=avgExpenses)
writeListOfDFToSheet(lst2,wb,"sheet 2")

# save excel file
saveWorkbook(wb,file='myfile.xlsx')

Thanks for your help on this. I need to write some data frames in one sheet, some in to another sheet, etc.. How can I add multiple sheet in this custom function. ? — ds_user, Apr 30 '18 at 08:14
@ds_user : changed the function. Anyway, the idea is that you create a workbook, create a sheet and you write to it using `addDataFrame` — digEmAll, Apr 30 '18 at 08:39

score 2 · Answer 2 · answered Apr 30 '18 at 07:01

2

Convert your df to lists and you can use this code :

install.packages("devtools")
library(devtools)

# Install package from github
install_github('d-notebook/sheetr')
library(sheetr)

# Create a list of dataframes
iris_dataframe = list()
iris_dataframe[["Setosa subset"]] = head(iris[iris$Species == "setosa",])
iris_dataframe[["Versicolor subset"]] = head(iris[iris$Species == "versicolor",])

# Write the list of dataframes to CSV file
write_dataframes_to_csv(iris_dataframe, "exmaple_csv.csv")

answered Apr 30 '18 at 07:01

anup

465
3
18

Hi. Thanks for your help. Can he have multiple sheets in this? Some data frames in one sheet, some in other sheet, etc ? – ds_user Apr 30 '18 at 08:30
No, you cannot have multiple sheets in a `csv` format. You can however have multiple sheets for `.xlsx` format files. @ds_user – anup Apr 30 '18 at 08:36
Yes. I need to write it in xlsx only. Can this solution be extended for excel? – ds_user Apr 30 '18 at 08:37
1

This should work. I am not sure. I have not tested it. `write.xlsx(df, file = 'file.xlsx', sheetName = 'sheet 1',append=TRUE) ` @ds_user – anup Apr 30 '18 at 08:38
Solution from @digEmAll works perfectly. Thanks for your help anyways. :) – ds_user Apr 30 '18 at 08:45

Andre Elrico · Answer 3 · 2018-04-30T08:11:09.060

I found out something interesting that is worth sharing here:

You can archive your desired result by adding the column as list with the right types:

look at this example:

df       <- data.frame(a=1:5)
df$normal<- c(1,"two",3,4,5)
#add Column as list
df$new   <- list("title",NA,1,2,"three")
#look at your xlsx-file. Column "new" has the right types!
xlsx::write.xlsx(x = df,file = "new.df.xlsx")

So here is an example that matches your case

df <- rbind(c("title",rep(NA,4)),rep(NA,5),mtcars[1:5,1:5],c("title2",rep(NA,4)),rep(NA,5),mtcars[1:5,1:5])
xlsx::write.xlsx(x = df,file = "stupidResult.xlsx")

df.list <-lapply(matrix(df),function(x)lapply(x,function(i){type.convert(as.character(i), as.is = TRUE)}))

df.new<-data.frame(dummy=1:nrow(df)) 
for(i in seq_along(df)) {df.new[[i]]<-df.list[[i]]}
names(df.new) <- names(df)
xlsx::write.xlsx(x = df.new,file = "superResult.xlsx")

Simply replace df with your data.frame

Thanks for your time and help. :) – ds_user Apr 30 '18 at 08:43 — ds_user, Apr 30 '18 at 08:43

writing data in to excel with title above header - R

3 Answers3