1
wrbk = xlrd.open_workbook("D:Book1.xlsx")
idx = 0

book_1 = xlrd.open_workbook("D:Book2.xlsx")
sh_1 = book_1.sheet_by_name('Sheet4')

i = 0
for x in range(sh_1.nrows):
    i = i + 1
    if i >= sh_1.nrows:
        break
    if sh_1.cell(i, 2).value:
        concat = sh_1.cell(i, 2).value
        for y in range(len(wrbk.sheets())):
            sht = wrbk.sheet_by_index(y)
            for j in range(sht.ncols):
                for cell in range(sht.nrows):
                    list = str(sht.cell(cell, j).value)
                    if list.__contains__(concat):
                        print(sh_1.cell(i, 2).value)

Im using this code to find a value in a workbook and then search that value in another workbook.
I'm using xlrd, the output is fine so far but i can't read and write with xlrd.
i need suggestions to change this code from xlrd to openpyxl.

kar_n
  • 78
  • 7
  • Are the values on Sheet4 of Book2 to be searched for in Column C, and each value is to be searched for in all the sheets of Book1. If found you just want to print, no updating of sheets ? – CDP1802 Mar 17 '20 at 13:56
  • @CDP1802 yes, values on Sheet4 of Book2 to be searched for in Column C and each value is to be searched for in all the sheets of Book1. Instead of `print(sh_1.cell(i, 2).value)` i will be writing a text in the cell(i,3) then i have to save the worksheet. – kar_n Mar 17 '20 at 14:01

1 Answers1

1

This defines a function to do the search and uses a Regular Expression to do the 'contains' match. Change the print to suit.

from openpyxl import load_workbook
import re

# open workbook 
excel_file1 = 'D:Book1.xlsx'
wb1 = load_workbook(excel_file1) # wrbk
ws1 = wb1["Sheet1"]

excel_file2 = 'D:Book2.xlsx'
wb2 = load_workbook(excel_file2) # book_1
ws2 = wb2["Sheet4"] # sh_1

# fn to search all sheets in workbook
def myfind(wb,s):
    for ws in wb.worksheets:       
        for c in range(1,ws.max_column+1):
            for r in range(1,ws.max_row+1):
                txt = ws.cell(r,c).value 
                if txt is None:
                    pass
                elif re.search(s,txt):
                    print("Found",s,txt,ws,r,c)

# scan col C
for r in range(1,ws2.max_row+1):
    s = ws2.cell(r, 3).value 
    if s is None: 
        pass
    else: 
        print(r,s)
        myfind(wb1,s)
CDP1802
  • 13,871
  • 2
  • 7
  • 17
  • next to `print("Found",s,txt,ws,r,c)` can I use `ws.cell(row=r, column=4).value = 'found'` to wirte a value in a cell, next column but with same row number – kar_n Mar 18 '20 at 07:40
  • column 4 value has to be empty.`print("Found",s,txt,ws,r,c)` - i have to fill the cell next to the cell which has the 'Found' value – kar_n Mar 18 '20 at 07:55