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
[[alternative HTML version deleted]]