0

I'm looking for a solution to combine same text color excel cells into a single cell in a column. The following image shows the input_excel.xlsx :-

Input image to python code

The output_excel.xlsx should be returned by python code as follows:-

Output of python code

I looked into the following link: Subsetting a dataframe based on cell color and text color in excel sheet but not able to combine the cells that are having black text color.

The following code was implemented to detect red text and black text in an excel:- 

# importing openpyxl module 
import openpyxl 

# Give the location of the file 
path = "E:\\input_excel.xlsx"

# workbook object is created 
wb_obj = openpyxl.load_workbook(path) 

sheet_obj = wb_obj.active 
m_row = sheet_obj.max_row 
for i in range(1, m_row + 1): 
    cell_obj = sheet_obj.cell(row = i, column = 1).font.color
    if cell_obj is not None:    # to catch cells that do have a color object
        if cell_obj.rgb == "FFFF0000":
           print(cell_obj.rgb)
        elif cell_obj.rgb == "00000000":
           print(cell_obj.rgb)

The above code always returns the red text hex value as follows:-

FFFF0000
FFFF0000
FFFF0000
FFFF0000
FFFF0000

Why does openpyxl not detect black color text? How can the code be improved? Can anybody help in implementing the code needed for above shown images?

The Programmer
  • 23
  • 10
  • what happens if you place `print(cell_obj.rgb)` before `if cell_obj.rgb == "FFFF0000":` This should help debug what is actually assigned to `cell_obj.rgb` prior to matching it. Not sure why however you are using `if cell_obj is not None:` in this instance. – Gerhard Feb 28 '20 at 12:16
  • 1
    I believe black is the default color, so cells you call black are in fact cells without color attribute set; and your condition `cell_obj is not None` explicitly filters them out. – Błotosmętek Feb 28 '20 at 12:18
  • `print(cell_obj.rgb)` before `if cell_obj.rgb == "FFFF0000":` results into - **Values must be of type ** @GerhardBarnard – The Programmer Feb 29 '20 at 08:18
  • @GerhardBarnard Any further suggestions? – The Programmer Feb 29 '20 at 21:38
  • You could set the class to str prior to the print anyway just do another `elif` for when cell_obj is none. Those should then be the black ones you want. You could also set the class to `str` before the loop. – Gerhard Mar 01 '20 at 05:28

0 Answers0