R plumber getting as excel (xlsx)

Question

I want to return or download excel for post response in plumber using R

I'm reading data frame and writing to xlsx:

FileName: sample.R

xlsx_df = read.xlsx(file="My_File.xlsx", sheetName="Overview", header=T, stringsAsFactors=F, encoding="UTF-8")
write.xlsx(xlsx_df, file="Output_File.xlsx", sheetName="Sample_Sheet", row.names=F, showNA=F)

#* @serializer contentType list(type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
#* @get/excel
function(req, res){
  filename <- file.path(tempdir(), "Output_File.xlsx")
  write.xlsx2(iris, filename, row.names = FALSE)
  include_file(filename, res, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
} 

FileName: Plumber.R

r <- plumb("sample.R")
r$run(port=8011)

When I look into response body I get some weird response, which is not readable. and if I try to open the excel I get error excel cannot open because file format or extension is invalid

Can you please help me where i'm going wrong in generation of excel sheet?

Answer 1

This worked for me, hoping it'll be helpful to someone.

library(plumber)
library(xlsx)

#* @serializer contentType list(type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
#* @get /report/
api_get_report <-function(req, res){
  
  df <- data.frame(CHAR = letters, NUM = rnorm(length(letters)), stringsAsFactors = F)
  filename <- file.path(tempdir(), "alphabet.xlsx")
  write.xlsx(df, filename, sheetName="Alphabets", append=TRUE)
  attachmentString = paste0("attachment; filename=Output_File.xlsx", filename)
  
  res$setHeader("Content-Disposition", attachmentString)
  
  # Read in the raw contents of the binary file
  bin <- readBin(filename, "raw", n=file.info(filename)$size)
  
  #Return the binary contents
  bin
}