Search code examples
rpdf-generationlibreoffice-calc

Updating excel through xlsx and printing pdf through libreoffice. But content does not get updated

I am updating an excel template using xlsx package in R and then writing it as a pdf using system command in R. within the system command I am using libreoffice-calc (which is like excel). but the updates I make using R are updated in the file if I open it. but the pdf does not print it.

In this example, chk.xlsx is an excel sheet with two tabs the second tab needs to be printed.

library(xlsx)
wb<-loadWorkbook("chk.xlsx")
sheets <- getSheets(wb)
sheet2 <- sheets[["Sheet1"]]
a=c(1:5)
b=c(6:10)
x <- as.data.frame(cbind(a,b))
addDataFrame(x, sheet2, startRow=2, startColumn=1,col.names=F, row.names=F)

wb$setForceFormulaRecalculation(TRUE)
#setForceFormulaRecalculation(wb,"data",TRUE)

saveWorkbook(wb, "chk.xlsx")
system(paste0("libreoffice --convert-to pdf chk.xlsx")) # empty pdf
#system(paste0("libreoffice --headless --convert-to pdf chk.xlsx")) # empty pdf
#system(paste0("libreoffice --headless --invisible --convert-to pdf chk.xlsx")) # empty pdf

even when I try to run this from the command line (a terminal not Rstudio/R console) after running the R scripts I don't get the updated sheet's print.

 libreoffice --headless --convert-to pdf chk.xlsx
Solution

  • Finally, I got it resolved.

    The formulas in excel do not get updated automatically in LibreOffice. So, you will have to do this.

    Toos > Options > LibreOfficeCalc > Formula > recaclulation on file load

    set this to always recalculate. (Although it would be nice to set it via command line)

    enter image description here