0

I'm relatively new to coding in python, hence stuck with a coding problem to merge similar font color text in a cell from an input excel and save the output back to an excel file. The input excel file looks as below:- 

------------
| John     |     #black color font text 
------------
| Charlie  |     #black color font text 
------------
| Sean     |     #red color font text 
------------
| Johnny   |     #red color font text
------------
| Tom      |     #black color font text
------------
| Ronny    |     #red color font text
------------
| Larry    |     #black color font text
------------

The black color font text should be merged together and the red color font text should be merged together. The output should be divided into blocks of black-red-black font text. The first Block-1 group of cells should have the values as follows: -

--------------
|John Charlie|    #black color font text
--------------
|Sean Johnny  |   #red color font text
--------------
|Tom          |   #black color font text
---------------

The second Block-2 group of cells to be added to output excel should have the last black color font text from Block-1 and the rest values should follow. 

---------------         
|Tom          |   #black color font text 
---------------
|Ronny        |   #red color font text 
---------------
|Larry        |   #black color font text
---------------

The final output excel should be the combination of Block-1 and Block-2 which means 'Tom' should be repeated twice in the output excel.

import xlrd
book = xlrd.open_workbook("input-out.xls", formatting_info=True)
def font_color(xf):
   font = book.font_list[xf.font_index]
   if not font:
      return None
   return get_color(font.colour_index)
def get_color(color_index):
   return book.colour_map.get(color_index)

red_rows = []
black_rows = []
sheets = book.sheet_names() 
for index, sh in enumerate(sheets):
   sheet = book.sheet_by_index(index)
   rows, cols = sheet.nrows, sheet.ncols  # 11, 1 - int type
   for row in range(1,rows):
      for col in range(cols):
          c = sheet.cell(row, col)
          xf = book.xf_list[c.xf_index]
          color = font_color(xf)
          if 255 not in color: # black rows
             black_rows.append(row)
          else:                # red rows
             red_rows.append(row)

Implemented above python code for detecting red and black color text font although I'm not able to proceed further on how to merge the cells. Is it possible to merge the cell values in a pandas data frame or is there any other python module which can successfully give the expected output?

user39602
  • 339
  • 2
  • 5
  • 13

1 Answers1

0

You can label the colours in excel in other column then you can read that excel file with pandas. The check of colours formula you can see Excel formula to get cell color. For other grouping, you can see Finding rows with same column values in pandas dataframe

Barış Can Tayiz
  • 87
  • 5
  • This is not what I expect from the code. The formula shared is for finding the cell color where as I’m looking for a solution to know the font color of the text inside a cell. Also, how the other shared link of finding rows with same column is going to work as the problem is to combine the previous black font text cell to the next red font cell. – user39602 Mar 01 '20 at 02:28