trouble writing rich text in R package xlsx

[yuryb...@gmail.com]

Hi,

I am trying to use this package to export some data from R to xlsx. One of the things I need to do is bold certain words inside strings.  However, I get an error when trying to do this:


########################################################################################################
## Test code
# make a new workbook with one sheet
> wb <- createWorkbook()
> sheet <- createSheet(wb, "Sheet1")
> rows <- createRow(sheet, rowIndex=1:5)
> cells <- createCell(rows, colIndex=1:5)

# Set cell value to a string
> setCellValue(cells[[1,1]],"test {\b bold} word") # no error, but no bold word either

# Set cell value to an RTF string
> setCellValue(cells[[1,1]],"test {\b bold} word", richTextString=T) # fails:
Error in .jnew("org/apache/poi/sf/usermodel/RichTextString", as.character(value)) :
java.lang.ClassNotFoundException
Calls: setCellValue -> .jnew -> .External


My R installation:

> sessionInfo()
R version 3.0.2 (2013-09-25)
Platform: x86_64-pc-linux-gnu (64-bit)
[...]
xlsx_0.5.5 xlsxjars_0.5.0 rJava_0.9-6


My system is Debian 6.0.8 (squeeze); Linux 2.6.32-5-amd64.

The same happens on Windows:

> # Set cell value to an RTF string
> setCellValue(cells[[1,1]],"test {\b bold} word", richTextString=T) # fails:
Error in .jnew("org/apache/poi/sf/usermodel/RichTextString", as.character(value)) : 
  java.lang.ClassNotFoundException
> sessionInfo()
R version 3.0.2 (2013-09-25)
Platform: x86_64-w64-mingw32/x64 (64-bit)

locale:
[1] LC_COLLATE=English_United States.1252  LC_CTYPE=English_United States.1252    LC_MONETARY=English_United States.1252
[4] LC_NUMERIC=C                           LC_TIME=English_United States.1252    

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] xlsx_0.5.5      xlsxjars_0.5.0  gage_2.12.1     Rcolombos_1.4.1 RJSONIO_1.0-3   RCurl_1.95-4.1  bitops_1.0-6   
[8] limma_3.18.11   rJava_0.9-6    

loaded via a namespace (and not attached):
 [1] BiocGenerics_0.8.0 Biostrings_2.30.1  digest_0.6.4       graph_1.40.1       httr_0.2           IRanges_1.20.6    
 [7] KEGGREST_1.2.0     parallel_3.0.2     png_0.1-7          stats4_3.0.2       stringr_0.6.2      tools_3.0.2       
[13] XVector_0.2.0     

Is this a bug or am I missing something?  I couldn't find any information on the web that would help someone who is not a Java developer.

Thanks.

Yury

[Adrian Dragulescu]

Hi Yuri, 

Here is an example on how to do what you want.  Note that although it's called Rich Text I don't think it's the same as the Microsoft format.  You can look through the Java doc to see what you can do. 

wb <- createWorkbook()

sheet <- createSheet(wb, "Sheet1")

rows <- createRow(sheet, rowIndex=1:24)

cells <- createCell(rows, colIndex=1:8)

# see https://poi.apache.org/apidocs/index.html?org/apache/poi/xssf/usermodel/XSSFRichTextString.html

rs <- .jnew("org/apache/poi/xssf/usermodel/XSSFRichTextString",

"test red bold words." )

.jcall(rs, "V", "applyFont", 5L, 13L, Font(wb, color="red", isBold=TRUE)$ref)

.jcall(cells[[2,1]], "V", "setCellValue",

.jcast(rs, "org/apache/poi/ss/usermodel/RichTextString"))

#saveWorkbook(wb, file=fileOut)

[yuryb...@gmail.com]

Yes, Adrian, this is what I needed.  Many thanks.

Yury

[minh....@gmail.com]

Hello, Adrian

I do not understand this line:

.jcall(rs, "V", "applyFont", 5L, 13L, Font(wb, color="red", isBold=TRUE)$ref)

What are 5L and 13L stand for? I have tried to modify these two values. But it seems like this line only makes the text red and bold from the end of the string back to the index 5 regardless of what value the field "13L" is.