Jon Chung Jon Chung - 1 year ago 199
R Question

R: Export data as "text" using openxlsx

I am trying to use

or other package) package to export data frames to excel spreadsheets. One problem that I have is that I want to set certain columns to "text" rather than "general" because Excel has a tendency to automatically format gene names (i.e
SEPT16 -> 16-Sep
(date format)).

documentation has some examples for setting column classes to "currency", "accounting", "hyperlink", "percentage", or "scientific", but not explicitly to "text". I've tried setting the class to "text" or "character", but the output Excel column is still "general". Initially, the correct text is there, but if I edit anything in the cell, Excel automatically formats these cells.


df <- data.frame(gene = c("SEPT16", "MARCH10", "GATA4"),
pvalue = c(0.0123, 0.2315, 0.00001),
stringsAsFactors = FALSE)

class(df$gene) <- "text" # Doesn't work
class(df$pvalue) <- "scientific"

wb <- openxlsx::createWorkbook()
sheet <- openxlsx::addWorksheet(wb, "test")
openxlsx::writeDataTable(wb = wb,
sheet = "test",
x = df)
openxlsx::saveWorkbook(wb, "example_table.xlsx")

Answer Source

openxlsx does allow for text formatting, but it requires a couple of steps:

  • Create a workbook-object wb and create a tab (sheet) for it
  • Create the cell formatting (cell style) you wish to use. For text formatting: use numFmt = '@'.
  • Assign (write) some data to a sheet in your workbook-object wb
  • Identify the cell ranges for each cell style and assign the cell style to them

Sample code

# Create workbook & sheet:
wb    <- openxlsx::createWorkbook()
sheet <- openxlsx::addWorksheet(wb, "test")

# Create the cell style
textstyle <- openxlsx::createStyle(fontName = "Arial", fontSize = 7, 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,3,5), col = c(1,2,3,4,5))

# Then assign 'textstyle' to the 'textcells-range':
openxlsx::addStyle(wb = wb, sheet = "test", 
                   rows = textcells$row, cols = textcells$col, 
                   style = textstyle) 

# Save the workbook
openxlsx::saveWorkbook(wb, "example_table.xlsx")
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download