Export Both Image and Data from R to an Excel Spreadsheet

Export both Image and Data from R to an Excel spreadsheet

I would use the xlsx package. Check out the addPicture function and the addDataFrame function. I found this package fairly easy to work with.

R: save ggplot2 plots in different excel sheets

From the help of insertPlot: The current plot is saved to a temporary image file using dev.copy. This file is then written to the workbook using insertImage.

This means that you need to print the k-th plot before each insertPlot.

library(openxlsx)
# A test data set
set.seed(1)
n <- 1000
df <- data.frame(Perc=runif(n), Quintiles=runif(n), 
                 Answer=sample(1:2, size=n, replace=T), 
                 Group =sample(1:5, size=n, replace=T))

List1 <- split(df, df$Group) 
ListGraphs <- lapply(List1, function(x) {
     ggplot(x, aes(x=Quintiles, y=Perc, group=Answer, color=as.factor(Answer))) +
     geom_line(size=2) +
     theme(legend.title=element_blank(), legend.position="bottom", 
           legend.key = element_blank())
}) 
wb <- createWorkbook() 
for (k in seq_along(ListGraphs)) {
  name <- addWorksheet(wb, names(ListGraphs)[k]) 
  plot(ListGraphs[[k]])   # Plot the k-th graph before insertPlot
  insertPlot(wb, sheet=name)                     
} 
saveWorkbook(wb,"a.xlsx", overwrite = TRUE) 

Exporting Decomposed Data from R into Excel

An option with tidyverse

library(dplyr)
library(readr)
bind_cols(A.modwt) %>%
      write_csv(result, 'result.csv')

Easy way to export multiple data.frame to multiple Excel worksheets

You can write to multiple sheets with the xlsx package. You just need to use a different sheetName for each data frame and you need to add append=TRUE:

library(xlsx)
write.xlsx(dataframe1, file="filename.xlsx", sheetName="sheet1", row.names=FALSE)
write.xlsx(dataframe2, file="filename.xlsx", sheetName="sheet2", append=TRUE, row.names=FALSE)

Another option, one that gives you more control over formatting and where the data frame is placed, is to do everything within R/xlsx code and then save the workbook at the end. For example:

wb = createWorkbook()

sheet = createSheet(wb, "Sheet 1")

addDataFrame(dataframe1, sheet=sheet, startColumn=1, row.names=FALSE)
addDataFrame(dataframe2, sheet=sheet, startColumn=10, row.names=FALSE)

sheet = createSheet(wb, "Sheet 2")

addDataFrame(dataframe3, sheet=sheet, startColumn=1, row.names=FALSE)

saveWorkbook(wb, "My_File.xlsx")

In case you might find it useful, here are some interesting helper functions that make it easier to add formatting, metadata, and other features to spreadsheets using xlsx:
http://www.sthda.com/english/wiki/r2excel-read-write-and-format-easily-excel-files-using-r-software

how to export dataframes from R to excel and separate them by an empty row

Using openxlsx, which I hope works better for you than the other packages you mentioned, you can do:

library(openxlsx)

# create your workbook
mywb <- createWorkbook() 

# create the sheets you need based on the first list of tables
for (sheetName in names(listofdfs)){
    addWorksheet(mywb , sheetName )
}

# get all your lists of tables in a single list
l_listOfDF <- mget(ls(pattern="listofdf"))

# initiate the index of the row where you will want to start writing (one per sheet)
startR <- rep(1, length(listofdfs)) ; names(startR) <- names(listofdfs)

# loop over the lists of tables using index and then over the elements / sheets using their names
for(N_myListOfDF in seq(l_listOfDF)){

  for(pageName in names(l_listOfDF[[N_myListOfDF]])){

    # write the name/number of your table in the correct sheet, at the correct row
    writeData(mywb, sheet=pageName, startRow=startR[pageName], paste0("Table ", N_myListOfDF, "."))

    # write your data in the correct sheet at the correct row (the one after the name)
    writeData(mywb, sheet=pageName, startRow=startR[pageName]+1, l_listOfDF[[N_myListOfDF]][[pageName]])
    
    # update the row number (the + 3 is to leave space for name of table, headers and blank row
    startR[pageName] <- startR[pageName]+nrow(l_listOfDF[[N_myListOfDF]][[pageName]]) + 3

  }
}

# save your workbook in a file
saveWorkbook(mywb , "myfile.xlsx")

Output file:

How to export an Excel sheet range to a picture, from within R

Consider having R do exactly as VBA does in your macro: making a COM interface to the Excel object library. You can do so with the RDCOMClient package, retaining nearly same code as macro in the R syntax.

library(RDCOMClient)

xlApp <- COMCreate("Excel.Application")
xlWbk <- xlApp$Workbooks()$Open("C:\\Path\\To\\test_import.xlsx")
xlScreen = 1
xlBitmap = 2

xlWbk$Worksheets("deletedFields")$Range("A8:J36")$CopyPicture(xlScreen, xlBitmap)

xlApp[['DisplayAlerts']] <- FALSE

oCht <- xlApp[['Charts']]$Add()
oCht$Paste()
oCht$Export("C:\\Temp\\SavedRange.jpg", "JPG")
oCht$Delete()

# CLOSE WORKBOOK AND APP
xlWbk$Close(FALSE)
xlApp$Quit()

# RELEASE RESOURCES
oCht <- xlWbk <- xlApp <- NULL    
rm(oCht, xlWbk, xlApp)
gc()

Output (random data/chart)

export a huxtable data and a ggplot into one excel file

Here is a potential work flow which could be adapted. Check out the package documentation for options, as the answer below only uses minimal arguments and all the packages used offer lots of options.

Updated following OP's inclusion of formatted huxtable.

library(openxlsx)
library(huxtable)
library(ggplot2)

# create workbook
wb <- createWorkbook()

#create sheet for plot
addWorksheet(wb, sheetName = "plot")

# create plot
p <- 
  ggplot(mtcars)+
  geom_histogram(aes(x = mpg))

p  

# insert plot inserts the current plot into the worksheet
insertPlot(wb, sheet = "plot")

# create huxtable with formatting
hx <- as_huxtable(mtcars[1:10,])

for (i in 1:length(hx) ) {
  
  if  (i  == 1){
    hx<-set_background_color(hx, row = i, everywhere, "yellow")  
  }
  
  
  else{
    hx<-set_background_color(hx, row = i, everywhere, "red")
  }
  
}

hx

# use huxtable::as_Workbook function to convert table for export into excel workbook
as_Workbook(hx, Workbook = wb, sheet = "table")

## Save workbook
saveWorkbook(wb, "eg_table_plot.xlsx", overwrite = TRUE)

^{Created on 2021-12-02 by the reprex package (v2.0.1)}

---

Related Topics