[R] openxlsx: No Formatting of Numbers (TEXT ONLY)

G.Maubach at weinwolf.de G.Maubach at weinwolf.de
Mon Dec 5 14:00:59 CET 2016


Hi All,
Dear Readers,

I am using openxlsx to export data to Microsoft Excel 2013, 32-Bit, German 
Version:

--- schnipp ---

library("openxlsx")

dataset <- structure(
  list(
    a = c(1126039.81, 45636.44, 14847.41),
    b = c(1194447.5,
          88310.53, 18699.68),
    c = c(1560307.73, 34203.73, 24755.99),
    d = c(1068790.67,
          67581.86, 12378.55)
  ),
  .Names = c("a", "b", "c", "d"),
  row.names = c(NA,
                3L),
  class = "data.frame"
)

xlsx_workbook <- openxlsx::createWorkbook()
openxlsx::addWorksheet(
  wb = xlsx_workbook,
  sheetName = "Numbers")

openxlsx::writeData(
  wb = xlsx_workbook,
  sheet = "Numbers",
  x = dataset,
  rowNames = TRUE,
  colNames = TRUE,
  startRow = 2,
  startCol = 2,
  borders = c("surrounding")
)

myStyle <- openxlsx::createStyle(numFmt = "###.###.##0")

openxlsx::addStyle(wb = xlsx_workbook,
                   sheet = "Numbers",
                   style = myStyle,
                   rows = 1:1,
                   cols = 10:10,
                   gridExpand = TRUE,
                   stack = TRUE)

openxlsx::saveWorkbook(
  wb = xlsx_workbook,
  file = "C:/temp/openxlsx_example.xlsx",
  overwrite = TRUE
)

--- schnipp ---

The problem with this is, that it does not apply the number formats to the 
Excel cell on the sheet. Also, sometimes the boarder of the data on the 
Excel sheet is delete. I could not find out yet what the cause for this 
behaviour is.

My sessionInfo() output is:

R version 3.3.2 (2016-10-31)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 7 x64 (build 7601) Service Pack 1

locale:
[1] LC_COLLATE=German_Germany.1252 
[2] LC_CTYPE=German_Germany.1252 
[3] LC_MONETARY=German_Germany.1252
[4] LC_NUMERIC=C 
[5] LC_TIME=German_Germany.1252 

attached base packages:
[1] tools     stats     graphics  grDevices utils 
[6] datasets  methods   base 

other attached packages:
[1] tidyr_0.5.1    stringr_1.1.0  reshape2_1.4.1
[4] openxlsx_3.0.0 dplyr_0.5.0 

loaded via a namespace (and not attached):
[1] lazyeval_0.2.0 plyr_1.8.4     magrittr_1.5 
[4] R6_2.2.0       assertthat_0.1 DBI_0.4-1 
[7] tibble_1.1     Rcpp_0.12.5    stringi_1.1.1 

I do not want to round the numbers in R, cause my clients would like to 
use them as they are in further calculations.

How can I export a dataframe to Excel, print a border around the complete 
table/dataset (not the single cells) and format the numbers like 
123.456.789 (thousand delimiter dot ".", all numbers without decimals)?

Kind regards

Georg



More information about the R-help mailing list