G.Maubach at weinwolf.de
2016-Dec-05 13:00 UTC
[R] openxlsx: No Formatting of Numbers (TEXT ONLY)
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