I am trying to format certain columns using openxlsx to write an r data frame to an excel file.
Here is a snippet of the R data frame:
The square bracket part in the "seed" column is used to superscript the excel output.
Here is the code I used to write the file:
openxlsx::addWorksheet(wb, sheetName = 'data') # add sheet
openxlsx::writeData(wb, sheet ='data',
x=df, xy=c(1, 1),withFilter = T) # write data on workbook
# make quality codes superscript
for(i in grep("\\_\\[([A-z0-9\\s]*)\\]", wb$sharedStrings)){
# if empty string in superscript notation, then just remove the superscript notation
if(grepl("\\_\\[\\]", wb$sharedStrings[[i]])){
wb$sharedStrings[[i]] <- gsub("\\_\\[\\]", "", wb$sharedStrings[[i]])
next # skip to next iteration
}
# insert additional formatting in shared string
wb$sharedStrings[[i]] <- gsub("<si>", "<si><r>", gsub("</si>", "</r></si>", wb$sharedStrings[[i]]))
# find the "_[...]" pattern, remove brackets and udnerline and enclose the text with superscript format
wb$sharedStrings[[i]] <- gsub("\\_\\[([A-z0-9\\s]*)\\]",
"</t></r><r><rPr><vertAlign val=\"superscript\"/></rPr><t xml:space=\"preserve\">\\1</t></r><r><t xml:space=\"preserve\">",
wb$sharedStrings[[i]])
}
openxlsx::modifyBaseFont(wb, fontSize = 10, fontName = 'Arial')
# right-justify data
openxlsx::addStyle(wb, sheet = 'data',
style = openxlsx::createStyle(halign = "right"), rows = 1:nrow(df)+1, cols = 3:12, gridExpand = TRUE)
#apply to rows with "All" in column B
openxlsx::conditionalFormatting(wb,sheet = 'data',
cols = 1:ncol(df),
rows = 1:nrow(df)+1,
rule = 'LEFT($B2,3)="ALL"',
style = openxlsx::createStyle(textDecoration = 'bold', bgFill = '#dad9d9'))
# format numbers
openxlsx::addStyle(wb = wb, sheet = 'data',
style = openxlsx::createStyle(numFmt = "#,###.0"),
rows = 1:nrow(df)+1, cols = c(5:7,10:12),gridExpand = T)
# write excel file
openxlsx::saveWorkbook(wb, file="file.xlsx",
overwrite = TRUE)
The output looks like this:
I am trying to conditionally format the numbers in the seed/harv/yield/prod columns to be numbers, but the "F" values are creating a mixed-class vector at best. (I need these Fs!)
Any ideas?
Thanks!