Search code examples
rexcelxlsx

How to color some numbers in Excel cell using python or R

I have this example of Excel file where the data contain some random values. I generated this using RAND() function.

What I want to do is read this excel file using R so that I can color red and bold the number 9 wherever it appears in the cell. Is this possible to do?

I've been searching on Google a while but haven't been able to figure it out any other way other than using VBA. But it's not an option.

Does anybody have an example of how to achieve this?

enter image description here

Solution

  • What I wanted to do is not possible using any of the python packages - xlsxwriter can only do rich text like I wanted but only on new cell but cannot modify, openpyxl can do a lot of things but not rich text. Wasn't sure if it could be done using R or not, but seems like it's not possible to do what I want done. I saw a Google Group discussion here where they showed a potential method to perform what I wanted, but that method didn't work for me. It showed .jnew is not recognized.

    So, instead, what I did is created a function to add a color dot (image) to the cell to delineate that the cell contains the value I'm searching for - 9 in this case. The reason I can't use conditional formatting is because there's another conditional formatting that is applied for another logic.

    def __add_color_dots__(self, ws=None, excel_filename=None):
   
    from openpyxl.drawing.image import Image
    import os

    path = os.path.abspath('blue-dot.png')
    image = Image(path)

    image.anchor = 'C4'

    ws.add_image(image)

    return ws

    Hope this will help someone later and that this method maybe a useful workaround.