[R] openxlsx: No Formatting of Numbers
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.1stringr_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.5stringi_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]]
__
R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
[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.1stringr_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.5stringi_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
__
R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
