[FIXED] How to detect and filter text/numbers with strikethrough in a cell containing both struck and unstruck text in Python (openpyxl)?

Issue

Hi, I am trying to filter out the struck texts/numbers in xlsx file using python using openpyxl. I am able to remove the cells containing struck texts and numbers. However, when there are two values in a cell, one with struck and the other unstruck, I am unable to do it. I tried splitting and then doing it but couldn't do it. I have been using cell.font.strikethrough to detect.

My code:

import openpyxl as op
wb=op.load_workbook("Some_Workbook.xlsx")
ws=wb['Sheet1']

for row in ws.iter_rows(max_row=1,max_col=2):
    for cell in row:
        if cell.font.strike:
            print('struck',cell.value)
        else:
            print('unstruck',cell.value)

wb.close()

I used the above code to find if the cell had values that were struck.

Please help.

Solution

XLWINGS requires a local installation of Excel so means OS is either Windows or MacOS and not every function necesarily works on macOS (I can only be sure this works on win). XLWINGS however has a character class which allows you to maniplulate individual characters in a cell so it can do what you want.
Firstly if a cell has all text with strikethrough font then the attrib

cell.font.impl.xl.Strikethrough

is True, if cells are a mixture of normal and strikethrough it's None and doesn't exist otherwise.


The code below is an example to show the strikethrough detection and deletion of that text. The image shows the input cells and subsequent output using this example code.

import xlwings as xw


wb = xw.Book('foo1.xlsx')
ws = wb.sheets('Sheet1')

for cell in ws.range('A1:B3'):
    cell_coord = str(cell.address.replace('$', ''))
    print('Cell: ' + cell_coord + ', Cell value: ' + str(cell.value), end=', ')

    st = cell.font.impl.xl.Strikethrough
    print('ST value: ' + str(st), end=' ')
    if st:
        print(', Cell has strikethrough font.')
        cell.clear()
    elif st is None:
        print(', Cell has mixed font.')
        num_chars = len(cell.value)
        print(cell_coord + ' Text length: ' + str(num_chars) + ' characters.')
        print("Individual character font strikethrough?")
        char_position = 0
        while True:
            cur_text_value = cell.characters[char_position].text
            if cur_text_value == '':
                break
            else:
                print("'" + cur_text_value + "'", end=' ')
                char_is_st_font = cell.characters[char_position].font.impl.xl.Strikethrough
                print(char_is_st_font)

                if char_is_st_font:
                    cell.characters[char_position].api.Delete()
                else:
                    char_position += 1

    else:
        print(', Cell has NO strikethrough font.')

wb.save('foo2.xlsx')
wb.close()

xlwings

Answered By - moken