Get data from excel cells which have green color as background

[MnvS]

This is the code used to extract data in cells with green background color.

from StyleFrame import StyleFrame
import sys
import pandas
import openpyxl

print("Python version: ", sys.version)
print ("Pandas version: ", pandas.__version__)
print("openpyxl version: ", openpyxl.__version__)
'''
print("StyleFrame version: ", StyleFrame.__version__) ## __version__ not defined
(base) C:\Anaconda\Lib\site-packages>pip show StyleFrame
Name: StyleFrame
Version: 1.6.1
'''
sf = StyleFrame.read_excel('filename.xlsx', read_style=True, use_openpyxl_styles=False)

Output is:

C:\Anaconda\python.exe C:/Python_Projects/read_excel_colored_cell.py
Python version:  3.6.4 |Anaconda, Inc.| (default, Jan 16 2018, 10:22:32) [MSC v.1900 64 bit (AMD64)]
Pandas version:  0.22.0
openpyxl version:  2.2.5

Traceback (most recent call last):
File "C:/Python_Projects/read_excel_colored_cell.py", line 16, in <module>
sf = StyleFrame.read_excel('filename.xlsx', read_style=True, use_openpyxl_styles=False)
File "C:\Anaconda\lib\site-packages\StyleFrame\deprecations.py", line 22, in inner
return func(*args, **kwargs)
File "C:\Anaconda\lib\site-packages\StyleFrame\style_frame.py", line 220, in read_excel
_read_style()
File "C:\Anaconda\lib\site-packages\StyleFrame\style_frame.py", line 209, in _read_style
read_comments and current_cell.comment)
File "C:\Anaconda\lib\site-packages\StyleFrame\styler.py", line 127, in from_openpyxl_style
font_color = theme_colors[openpyxl_style.font.color.theme]
TypeError: list indices must be integers or slices, not Integer

File (attached).
filename.xlsx

[DeepSpace2]

The issue was that this file contains some theme colors that caused an error that wasn't dealt with properly. I released version 1.6.2 that solves this issue (on my machine at least 🤓). It should be available on pip soon.

[MnvS]

Thanks @DeepSpace2. It is not giving any error now but giving empty dataframe with below code. If color coding is wrong where can I find the documentation regarding utils.colors code?

from StyleFrame import StyleFrame, utils
import numpy as np

def only_cells_with_green_background(cell):
    return cell if cell.style.bg_color in {utils.colors.green, '#00FF00'} else np.nan

sf = StyleFrame.read_excel('filename.xlsx', read_style=True, use_openpyxl_styles=False)
sf_3 = StyleFrame(sf.applymap(only_cells_with_green_background).dropna(axis=(0, 1), how='all'))
print(sf_3)

Output is:

Empty DataFrame
Columns: []
Index: []

[DeepSpace2]

@MnvS Don't use '#' when checking bg_color. {utils.colors.green, '#00FF00'} should be {utils.colors.green, '00FF00'}.

Anyway, utils.colors is using the same hex values as openpyxl.
Docs : https://styleframe.readthedocs.io/en/latest/api_documentation.html#utils-colors
Actual code:

StyleFrame/StyleFrame/utils.py

Lines 42 to 55 in 548bf68

	class colors(BaseDefClass):
	white = op_colors.WHITE
	blue = op_colors.BLUE
	dark_blue = op_colors.DARKBLUE
	yellow = op_colors.YELLOW
	dark_yellow = op_colors.DARKYELLOW
	green = op_colors.GREEN
	dark_green = op_colors.DARKGREEN
	black = op_colors.BLACK
	red = op_colors.RED
	dark_red = op_colors.DARKRED
	purple = '800080'
	grey = 'D3D3D3'

You can add print(cell.value, cell.style.bg_color) in only_cells_with_green_background to see what cell has what hex value as background color.

[MnvS]

I tried printing bgcolor but still giving empty frame:

from StyleFrame import StyleFrame, utils
import numpy as np

def only_cells_with_green_background(cell):
    if(cell.value == 'sh'):
         print(cell.value, cell.style.bg_color)
   return cell if cell.style.bg_color in {utils.colors.green, '#FFCCFFFF'} else np.nan

sf = StyleFrame.read_excel('filename.xlsx', read_style=True, use_openpyxl_styles=False)
sf_3 = StyleFrame(sf.applymap(only_cells_with_green_background).dropna(axis=(0, 1), how='all'))
print(sf_3)

Output:

 sh FFCCFFFF
 sh FFCCFFFF
 Empty DataFrame
 Columns: []
 Index: []

[DeepSpace2]

Note that you don't need the '#' as also explained in my previous comment.

[MnvS]

Not sure why but odd output: I have only few cells with green background all have pattern as "ab = cd" except 1st green cell "sh". Also I think dropna is not working correctly. Can I change StyleFrame object to pandas's dataframe?

from StyleFrame import StyleFrame, utils
import numpy as np

def only_cells_with_green_background(cell):
    if(cell.value == 'sh'):
         print(cell.value, cell.style.bg_color)
    return cell if cell.style.bg_color in {utils.colors.green, 'FFCCFFFF'} else np.nan

sf = StyleFrame.read_excel('filename.xlsx', read_style=True, use_openpyxl_styles=False)
sf_3 = StyleFrame(sf.applymap(only_cells_with_green_background).dropna(axis=(0, 1), how='all'))
print(sf_3)

Output:

C:\Anaconda\python.exe C:/Python_Projects/read_excel_colored_cell.py
sh FFCCFFFF
sh FFCCFFFF
Unnamed: 0 Unnamed: 3     ...     Unnamed: 39 Unnamed: 42
0           sh        nan     ...             nan         nan
7          nan        nan     ...             nan         nan
10         nan        nan     ...             nan         nan
13         nan        nan     ...             nan         nan
16         nan        nan     ...             nan         nan
19         nan        nan     ...             nan         nan
20         nan        nan     ...             nan         nan
21         nan        nan     ...             nan         nan
...
257        nan        nan     ...             nan         nan
279        nan        nan     ...             nan         nan
282        nan        nan     ...             nan         nan

[78 rows x 9 columns]

[MnvS]

I think its giving color of previous cell.

Its giving 'sh' cell color as "FFCCFFFF" which is color of previous cell (A,2).

[MnvS]

from StyleFrame import StyleFrame, utils
import numpy as np

def only_cells_with_green_background(cell):
    if(cell.value == 'ST name = 53'):
        print(cell.value, cell.style.bg_color)
    return cell if cell.style.bg_color in {utils.colors.green, 'FFCCFFFF'} else np.nan

sf = StyleFrame.read_excel('filename.xlsx', read_style=True, use_openpyxl_styles=False)
sf_3 = StyleFrame(sf.applymap(only_cells_with_green_background).dropna(axis=(0, 1), how='all'))

Output:

C:\Anaconda\python.exe C:/Python_Projects/read_excel_colored_cell.py
ST name = 53 FFCCFFCC

Please refer the color in previous cell of "ST name = 53"

[DeepSpace2]

1. You can get to the underlying dataframe with the data_df attribute: df = sf.data_df

2. I think its giving color of previous cell.

   This is because pandas.read_excel ignores all empty rows before the first non-empty row.
   If you put StyleFrame aside for a moment and just use pandas:

   df = pd.read_excel('filename.xlsx')
   print(df)
   
       Unnamed: 0       Unnamed: 1     ...     Unnamed: 43 Unnamed: 44
   0           sh  data is arrived     ...             nan         nan

The first row in df is sh, data is arrived, ..., In the file this is the first non-empty row so it is the first row that pandas reads (and in the file the background is green), but StyleFrame does not know that pandas made this shift. When StyleFrame tries to match the cells in the file to the cells in the dataframe, it does not know that pandas ignored some emptry rows at the start of the file.

I don't have a workaround at the moment. I'm going to open a new issue about this in order to keep track of it.

[MnvS]

Is it posible to get location of the cell. So I can take previous cell value?