I am trying to save a R dataframe to excel with openxlsx. Some columns contain text, other contain numbers.
I have borrowed below scrip from elsewhere (thanks) and it works in principle. Below shown is the case with a very small example dataframe.
My problem is the numberformat to put with numFmt.
In my orignal data I have a mix of intergers and decimal numbers in a column, the R data type is numeric.
When I export I want these to show "as they are" in excel.
library("openxlsx")
df <- data.frame(ID = c("a","b","c"), Value = c(12345, 12.52, 0.0001))
# Create workbook & sheet:
wb <- openxlsx::createWorkbook()
sheet <- openxlsx::addWorksheet(wb, "test")
# Create the cell style
textstyle <- openxlsx::createStyle(numFmt = "@")
numericstyle <- openxlsx::createStyle(numFmt = "######.######")
# Assign df to workbook
openxlsx::writeDataTable(wb = wb, sheet = "test", x = df)
# First identify the range of the 'text cells':
textcells <- expand.grid(row = c(1:(nrow(df)+1)), col = c(1))
numericcells <- expand.grid(row=c(1:(nrow(df)+1)), col = c(2))
# Then assign 'textstyle' to the 'textcells-range':
openxlsx::addStyle(wb = wb, sheet = "test",
rows = textcells$row, cols = textcells$col,
style = textstyle)
openxlsx::addStyle(wb = wb, sheet = "test",
rows = numericcells$row, cols = numericcells$col,
style = numericstyle)
# Save the workbook
openXL(wb)
I cannot find the right numFmt to put into the line
numericstyle <- openxlsx::createStyle(numFmt = "######.######")
12345 should show as 12345
12.52 should show as 12.52
0.001 should show as 0.001
The above "#####.#####" gives me in Excel:
12345.
12.52
.0001
I dont want the comma after the integer(s), and the .0001 shoudl show as 0.0001.
When I export I want the numebers to show "as they are in R" in excel.
I have tried various options, trying to google examples etc…
numFmt = "#.0"
gives me in Excel:
12345.0
12.5
.0
Appologies if this is a stupid question or if this is answered elsewhere… could not find a solution for me.
P.S: I am using the latest R 4.3.1, the latest RStudio 2023.06.0 Build 421 and latest openxlsx package (4.2.5.2).
>Solution :
Try using numFmt = "GENERAL"
library("openxlsx")
df <- data.frame(ID = c("a","b","c"), Value = c(12345, 12.52, 0.0001))
# Create workbook & sheet:
wb <- openxlsx::createWorkbook()
sheet <- openxlsx::addWorksheet(wb, "test")
textstyle <- openxlsx::createStyle(numFmt = "TEXT")
numericstyle <- openxlsx::createStyle(numFmt = "GENERAL")
# Assign df to workbook
openxlsx::writeDataTable(wb = wb, sheet = "test", x = df)
# First identify the range of the 'text cells':
textcells <- expand.grid(row = c(1:(nrow(df)+1)), col = c(1))
numericcells <- expand.grid(row=c(1:(nrow(df)+1)), col = c(2))
# Then assign 'textstyle' to the 'textcells-range':
openxlsx::addStyle(wb = wb, sheet = "test",
rows = textcells$row, cols = textcells$col,
style = textstyle)
openxlsx::addStyle(wb = wb, sheet = "test",
rows = numericcells$row, cols = numericcells$col,
style = numericstyle)
# Save the workbook
openXL(wb)
Which give you:
