iterative color each character conditionaly (1 xlsx column)

[Marco2]

I want to color each character conditionaly in every cell of column.
I can imagine what to do partialy (newbie in R):

1. open xlsx table or txt and change it to xlsx
2. iterate through column (threat cell as vector)
3. iterate through each vector (characters) and change color conditionally
 (and through regex find lines which will be colored - sequences)
4. save to xlsx

But I do not know how to color items in xlsx (and which lib) and how to save file with this change.

Sample data:

    >>f_2;hypothetical protein L_2128 [Legionella] {gene:L_2128}_start=1;end=300;length=300;source_length=320
    LAKELTYTDIINLKDSGLISNSEALCSIDFSERNSCTLINCKKLIIIEASQESSKIQLSILPFTKAGTELLAFTNPTSNNEYIMKLCNLVKASKARIHVADIEKIVGDKISYKNKNVISG
    &~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    5 | -0.368E+01 >>_vfdb.0002001_ VFG001328(gi:21283614) (sak) Staphylokinase precursor [Staphylokinase (VF0021)] [Staphylococcus aureus subsp. aureus MW2] :_: Length: 163
    ss1 HHHHHHHHHEEEETTCCCCCCCHHHEEEHTTTTTTTH-HHHHHHEEEEHHHHHHTHEEEEEECTCCCCHHHEEECCCCCCTTEHHHHHHHHHHH
    #1  LAKELTYTDIINLKDSGLISNSEALCSIDFSERNSCT-LINCKKLIIIEASQESSKIQLSILPFTKAGTELLAFTNPTSNNEYIMKLCNLVKAS
    #c  ----------------------+---------------+----+-----------+------+-+--+-----------+--------------
    #2  VEFPIKPGTTLTKEK--IEYYVEWALDATAYKEFRVVELDTSAKIEVTYYDKNKKKEETKSFPITEKGFVVPDLSEHIKNPGFNLITKVVIEKK
    ss2 EEETTCCTCCCHHHH--HHHHHHHHHHHHHHHHHHHHHHHHHHHHEEHHHHHHHHHHHHHHCHHHTTTEECHHHHHTTCCTTTCEEEHHHHHHH
    pseudoscore: 8.51
    1st sequence starts at 1
    2nd sequence starts at 72
    
    >>f_1; hypothetical protein L_2128 [Legionella] {gene:L_2128}_start=201;end=320;length=120;source_length=320
    LAKELTYTDIINLKDSGLISNSEALCSIDFSERNSCTLINCKKLIIIEASQESSKIQLSILPFTKAGTELLAFTNPTSNNEYIMKLCNLVKASKARIHVADIEKIVGDKISYKNKNVISG
    &~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    5 | -0.368E+01 >>_vfdb.0002001_ VFG001328(gi:21283614) (sak) Staphylokinase precursor [Staphylokinase (VF0021)] [Staphylococcus aureus subsp. aureus MW2] :_: Length: 163
    ss1 HHHHHHHHHEEEETTCCCCCCCHHHEEEHTTTTTTTH-HHHHHHEEEEHHHHHHTHEEEEEECTCCCCHHHEEECCCCCCTTEHHHHHHHHHHH
    #1  LAKELTYTDIINLKDSGLISNSEALCSIDFSERNSCT-LINCKKLIIIEASQESSKIQLSILPFTKAGTELLAFTNPTSNNEYIMKLCNLVKAS
    #c  ----------------------+---------------+----+-----------+------+-+--+-----------+--------------
    #2  VEFPIKPGTTLTKEK--IEYYVEWALDATAYKEFRVVELDTSAKIEVTYYDKNKKKEETKSFPITEKGFVVPDLSEHIKNPGFNLITKVVIEKK
    ss2 EEETTCCTCCCHHHH--HHHHHHHHHHHHHHHHHHHHHHHHHHHHEEHHHHHHHHHHHHHHCHHHTTTEECHHHHHTTCCTTTCEEEHHHHHHH
    pseudoscore: 8.51
    1st sequence starts at 1
    2nd sequence starts at 72

My code:

    # xlsx files
    setwd('D:/Dropbox/color_ffas_results')
    library(xlsx)
    wb     <- loadWorkbook("sample.xlsx")
    sheet1 <- getSheets(wb)[[1]]
    # get all rows
    rows  <- getRows(sheet1)
    cells <- getCells(rows)
    # look at the values
    sapply(cells, getCellValue)
    
    cellColor <- function(style) {
      SET COLOR HERE
    }


    #sequence_pattern <- str_detect("^#\d .*\n")
    
    
   

[FJCC]

As I understand your question, you want to change the font color of some, but not all, of the characters within an Excel cell. Is that correct?

If so, I do not see a way to do that with the packages I have used to work with Excel files in R. However, I am no expert, so others may know a way.

[Marco2]

Yes I do. It is correct.

Hmm maybe you know - some other way - is there is a possibility to make this from txt to format like eps or pdf where it be colorized? cheers

[FJCC]

I have not used Microsoft Office recently and I do not have access to it. I do have OpenOffice, which is free and open software. If I had to color part of the text in a Calc spreadsheet (similar to Excel), I would copy and Paste Special the cells into a Writer (similar to Word) document, pasting them as unformatted text. I would then use Find and Replace with regular expressions to find the text and then directly apply the color. The modified text could then be copied back into the spreadsheet if needed. If I needed to save the file in the xlsx format, I would use LibreOffice. Using R to format a spreadsheet seems like the wrong tool to me.