R: Export data as "text" using openxlsx

Question

I am trying to use openxlsx (or xlsx 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)).

The openxlsx 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.

library(openxlsx)

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")

KenHBS · Accepted Answer · 2016-10-27 16:07:32Z

5

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")

answered Oct 27, 2016 at 16:07

KenHBS

7,2546 gold badges41 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nova Jul 11 at 18:00

I've followed this example and my text columns are still showing up as "error - number stored as text". I want it to be text...

Collectives™ on Stack Overflow

R: Export data as "text" using openxlsx

1 Answer 1

Sample code

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Sample code

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related