0

We would like to remove grid lines in xlsx files with R. This works fine. However, formats are changed. What can we do that the formats are not changed?

#packages
    library(zip)
    library(openxlsx)

#input
    pathInput <- "https://www.stadt-zuerich.ch/content/dam/stzh/prd/Deutsch/Statistik/Themen/Bevoelkerung/BEV322T3220_Auslaenderanteil_Bevoelkerung_nach-Herkunft-Geschlecht.xlsx"
    wb <- loadWorkbook(pathInput)
    names(wb)

#no grid lines
    showGridLines(wb, "1901 – 2019", showGridLines = FALSE)

#output
    pathOutput <- "H:/temp/Output.xlsx"
    saveWorkbook(wb, pathOutput, overwrite = TRUE)

Input

Output

KlemensRosin
  • 1
  • 1
  • Could you please be explicit about which formats have changed with reference to sheet and cell ids? State what you are expecting and what you actually get. Have you confirmed the formatting problem does not occur when `showGridLines = TRUE` or if you just upload the file and save as an xlsx file. – Peter May 11 '20 at 07:03
  • Thanks for the comment. The output problem (format change) also occurs when showGridLines = TRUE. So it might come from loadWorkbook or saveWorkbook. Example for the format change: in sheet '1901 - 2019', column D, the format before the import is # ### ##0;– # ### ##0;0;– This format should not be changed. However, it is #\ ###\ ##0;\–\ #\ ###\ ##0;0;\– after the export – KlemensRosin May 11 '20 at 07:32
  • That's helpful. Your comments might be usefully included in the question. – Peter May 11 '20 at 07:59

0 Answers0