0

I have a Microsoft Excel spreadsheet (screenshot) that I'm trying to format using the pandas library in Python, but I can't seem to find any way that I can select only the cells that have a specific colour (blue, for instance). So far, I've tried using both the styleframe and openpyxl library but none have worked for me without any errors.

With styleframe (using this implementation), I believe I can only find specific basic colours that the library supports in its utils module (here). However, my spreadsheet has more advanced colour codes, which styleframe is unable to find, giving me an empty DataFrame as the output.

Empty DataFrame
Columns: []    
Index: []

Code:

def find_bs_cs_2021(cell):
        return cell if cell.style.bg_color in {utils.colors.dark_yellow, 'FFB740'} else np.nan

def main():

    styleframe_dataframe=StyleFrame.read_excel('TimeTable, FSC, Fall-2022.xlsx', sheet_name='Monday', read_style=True, use_openpyxl_styles=False)
    find=StyleFrame(styleframe_dataframe.applymap(find_bs_cs_2021).dropna(how='all').dropna(how='all', axis=1))
    print(find)

Is there any way to select these cells that styleframe doesn't support, either by using the library or any other library? Eventually, what I want to do is find all the values in the spreadsheet with a specific colour, along with their indexes and column names. I'd highly appreciate any assistance regarding this! :)

Huzaifa Khan
  • 39
  • 7
  • Are the colours applied manually or with conditional formatting? – BigBen Sep 22 '22 at 14:31
  • @BigBen The colours of the cells are already present inside the spreadsheet. I can't change them manually. – Huzaifa Khan Sep 22 '22 at 14:52
  • Maybe my question was unclear. How were the colours applied in the spreadsheet originally? Were they applied manually, or are they the result of a conditional formatting rule? Also likely pandas is overkill for this. – BigBen Sep 22 '22 at 14:53
  • Regarding your implementation of `find_bs_cs_2021`, it's possible that you are just using the wrong color. First make sure that this is the correct color by reading that cell only. If you want more specific help you'll need to provide the excel file or a demo file with similar structure/colors. – DeepSpace Sep 22 '22 at 15:21
  • @BigBen The spreadsheet was provided to me as is. How the colours were implemented into it, I'm not sure. As for using pandas, it's what I'm restricted to as part of my assignment. – Huzaifa Khan Sep 22 '22 at 15:40
  • @DeepSpace I checked the hexadecimal colour code for the colour of the cell from within Microsoft Excel, as well as by using the eyedropper tool in Adobe Photoshop. Both gave me the same hexadecimal colour code, which I then passed as the argument to styleframe. – Huzaifa Khan Sep 22 '22 at 15:42

0 Answers0