-2
from openpyxl import load_workbook
wb = load_workbook("C:\op.xlsx")
ws = wb.active

Here is the code I have so far. Basically, I want to create a program in which python asks for a string and finds that string in the excel file given. Then it prints that string in another sheet.

Shrahbeel
  • 21
  • 6

1 Answers1

1

Basically, iterate in the excel sheets, in each row, and each cell, and look for the pattern. Then write one string per row in the output file.

from openpyxl import load_workbook, Workbook


def find(wb, string):
    res = []
    for ws in wb:
        for row in ws.values:
            for value in row:
                if value is not None and string in str(value):
                    res.append(value)
    return res


if __name__ == '__main__':
    wb = load_workbook("C:\op.xlsx")
    values = find(wb, "findme")  # Replace findme with the string to find

    wb = Workbook()
    ws = wb.active
    for value in values:
        ws.append([value])
    wb.save(filename="out.xlsx")  # change out.xlsx with the output file name
Rodrigo Llanes
  • 613
  • 2
  • 10
  • Can you pls show me how to print the values onto another excel workbook. And where to insert the values in the code you provided.Pls help me and thankyou in advanced. – Shrahbeel Jul 14 '22 at 14:23
  • 1
    @Shrahbeel, I modified de answer to do what you said, but I highly recomend you to look the [docs](https://openpyxl.readthedocs.io/en/stable/usage.html) and some tutorials, to be able to modify and adapt the code. – Rodrigo Llanes Jul 14 '22 at 14:46
  • Sir there is this problem now: if value is not None and string in value: TypeError: argument of type 'datetime.datetime' is not iterable – Shrahbeel Jul 14 '22 at 14:51
  • Sir pls answer my comment pls sir pls – Shrahbeel Jul 14 '22 at 15:16
  • @Shrahbeel, change ```string in value``` to ```string in str(value)```, but as i said, read the docs, and do the modifications by yourself. – Rodrigo Llanes Jul 14 '22 at 16:24