saving multiple tables in xlsx files

[ashley21]

do we have any functionality or function which can paste multiple tables in single sheet of xlsx files.

lets say i have n number of tables and all tables have different size and format. now i want to save all the tables in xlsx file in one single sheet. with a specific space between them.

library(openxlsx)
library(fs)
library(writexl)

l <- list(IRIS = iris, MTCARS = mtcars)
write.xlsx(l, file = "writeXLSX2.xlsx")
write.xlsx(l, file = "writeXLSXTable2.xlsx", asTable = TRUE)

My output is like the output of many tables 
t1 = mtcars

t2 = mtcars[,1:5]

t3= IRIS

t4 = IRIS[1:8,]


at last it should create a list of all the tables above and then paste in xlsx file with specific space.

tried this way but its creating multiple sheets rather than single sheet.

[mattwarkentin]

I think you can do this using the addDataFrame() function in the {xlsx} package. The downside is that the {xlsx} package has system dependencies (i.e. Java) compared to {openxlsx}.

You can use the startRow argument to control where the data are placed and how much space is between each data frame.

[dvetsch75]

You can do this by keeping track of the column you are in and incrementing the value of startCol on each iteration through the list of tables you have. Something like this would work - with some slight modifications to your data to make it easier to loop over:

library(openxlsx)

# Add Dataframes to this list as needed
l = list(
    'mtcars' = mtcars,
    'iris' = iris,
    't2' = mtcars[, 1:5],
    't4' = iris[1:8, ]
)


wb <- createWorkbook()
addWorksheet(wb, 'foo') # You can replace 'foo' with whatever you want
start_col <- 1
cols_between_data <- 3 # If you want to vary the space between dataframes,
# you would do that by changing this variable

for(df in l) {
    writeData(
        wb,
        x = df,
        sheet = 1,
        startCol = start_col
    )
    start_col <- start_col + ncol(df) + cols_between_data
}

saveWorkbook(wb, 'test.xlsx', overwrite = T, returnValue = T)

Note the use of writeData instead of write.xlsx - this is because write.xlsx makes some assumptions about how you want your spreadsheet to look, and it sounds like you don't want those assumptions here.