I have multiple excel files with 12 sheets in each file.

So, in each sheet, I have a fixed text like below - "Project has been blocked"

So, I would like to do the below

a) Find the text "Project has been blocked" wherever it appears and change it is formatting like below (with bold red color) as shown below

b) save the excel file as .xlsx

I tried the below

req_text = "Project has been blocked"

for a_cell in ws.used_range:
        if a_cell.value == req_text:
            print(a_cell.address)
            col = a_cell.address[0]
            ws[col].characters.font.bold = True  #how to get the start and end position of my text
            ws[col].characters.font.color = (255, 0, 0)

But this doesn't work correctly. As I am unable to get the start and end position of my text.

I expect my output to be like as below

CodePudding user response：

I have changed the code to include an Excel search for the text in the used data of the sheet, and then update that cell text with Bold-Red as desired per the original code.
I ended up having to use a while loop for the Excel search and break out when the search looped back to the first found cell. Therefore the code keeps track of the first_search_cell to compare with the next found cell in the while loop.
I've left the Excel search variables as constants so you know what the name and value is if you want to change the search options. Obviously you can delete the ones you don't want or use import from Xlwings constants.
Otherwise its pretty much the same.

...
# Excel Search constants
# class LookAt:
xlPart = 2  # from enum XlLookAt
xlWhole = 1  # from enum XlLookAt
# class FindLookIn:
xlComments = -4144  # from enum XlFindLookIn
xlFormulas = -4123  # from enum XlFindLookIn
xlValues = -4163  # from enum XlFindLookIn
# class SearchOrder:
xlByColumns = 2  # from enum XlSearchOrder
xlByRows = 1  # from enum XlSearchOrder
# class SearchDirection:
xlNext = 1  # from enum XlSearchDirection
xlPrevious = 2  # from enum XlSearchDirection


def find_next_cell(start_cell):
    found_cell = ws.api.UsedRange.Find(req_text,
                                       After=start_cell,
                                       LookIn=xlValues,
                                       SearchOrder=xlByRows,
                                       SearchDirection=xlNext,
                                       MatchCase=False)
    return found_cell


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

req_text = "Project has been blocked"

# First cell to start searching for req_text
search_from_cell = ws.api.Range('A1')

count = 0
first_search_cell = ''
while True:
    # Search for next cell to update
    update_cell = find_next_cell(search_from_cell)

    # Excel search will restart search again from the beginning after the last match
    # is found exit the loop when find the first match again
    if update_cell._inner.Address != first_search_cell:
        print(update_cell._inner.Address)
        # Set the address of the first found cell
        if count == 0:
            first_search_cell = update_cell._inner.Address

        cell_column = update_cell._inner.Column
        cell_row = update_cell._inner.Row

        text = ws.range(cell_row, cell_column).value
        len_req_text = len(req_text)

        # Create a List of the start position for all instances of the req_text
        # tsi = text position index
        tsi_list = [index for index in range(len(text)) if text.startswith(req_text, index)]

        index = 0
        if text.startswith(req_text, index):
            tsi_list.append()

        # Iterate the tsi list
        for i in range(len(tsi_list)):
            # Get the index of the text position, tps = text position start
            tps = tsi_list[i]
            # Use the tps as start of the character position of the req_text
            # and (tps   length of req_text) for the end character position
            ws.range(cell_row, cell_column).characters[tps:tps   len_req_text].font.bold = True
            ws.range(cell_row, cell_column).characters[tps:tps   len_req_text].font.color = (255, 0, 0)

        search_from_cell = ws.api.Range(update_cell._inner.Address.replace('$', ''))

        count  = 1

    else:
        break

wb.save('foo.xlsx')
...