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?

Can you open the generated file, `Output_File.xlsx`? – Panagiotis Kanavos Sep 09 '20 at 10:34 — Panagiotis Kanavos, Sep 09 '20 at 10:34
yes @Panagiotis Kanavos i'm able to open the file in local. – Zakir Hussain Sep 09 '20 at 10:46 — Zakir Hussain, Sep 09 '20 at 10:46

Bruno Tremblay · Answer 1 · 2020-09-09T12:40:10.050

3

using next release 1.0.0

library(xlsx)
library(plumber)

#* @get /excel
function(req, res){
  filename <- file.path(tempdir(), "Output_File.xlsx")
  on.exit(unlink(filename))
  write.xlsx2(iris, filename, row.names = FALSE)
  as_attachment(readBin(filename, "raw", file.info(filename)$size), basename(filename))
}

edited Sep 09 '20 at 12:40

answered Sep 09 '20 at 10:30

Bruno Tremblay

756
4
9

score 2 · Accepted Answer · answered Oct 12 '20 at 11:55

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
}

R plumber getting as excel (xlsx)

2 Answers2