Search code examples
rdataframexlconnect

How can I remove the character ' when writing with XLConnect?

I want to export a data frame to a xls file. I found the library XLConnect, which is easy to use, but I would not mind trying something else.

The problem I have is in the output (xls file). Since my data frame contains strings like 'A_B', the output contains the character ' before each integer. This is a problem for me because I need OpenOffice to detect column with integers to be able to apply formulas later on.

Here is a small example without any problems, meaning each entry in the xls file is an integer.

library(XLConnect)
test = as.data.frame(matrix(c(0,0),nrow=1),stringsAsFactors = FALSE)
wb =loadWorkbook('file.xls',create=TRUE)
createSheet(wb, name = "test")
writeWorksheet(wb,test,'test',header=FALSE)
saveWorkbook(wb)

The problem occurs in the following example.

library(XLConnect)
test = as.data.frame(matrix(c(0,'A_B'),nrow=1),stringsAsFactors = FALSE)
wb =loadWorkbook('file.xls',create=TRUE)
createSheet(wb, name = "test")
writeWorksheet(wb,test,'test',header=FALSE)
saveWorkbook(wb)

In this last example, the first entry in the xls file is '0, which is not detected as an integer in OpenOffice.

Is there a way to remove the character ' from the output data ?

Thank you

Solution

  • In XLConnect, the column types of a data.frame determine the resulting cell types. E.g. character columns result in string/text cells while numeric columns result in numeric cells.

    In your example, the expression c(0, 'A_B') results in a character vector (R automatically coerces your 0 to character) and as such the resulting cells written will be string/text cells (see str(test) to investigate your data.frame). As such, you should be fine by simply constructing your data.frame differently, e.g. by doing the following:

    test = data.frame(NumericColumn = 0, TextColumn = "A_B", stringsAsFactors = FALSE) str(test)

    Looking at the output of str(test) you should now see that "NumericColumn" is a numeric column and "TextColumn" is a character column. When writing that data.frame with XLConnect, you will see that the resulting cells will have the appropriate cell type.