поиск в Excel-файле
Python

Python – search in excel file

Hello to all! An interesting question was asked to me on Telegram a few days ago. In general, it came down to a task that sounded like: search in an Excel file. Let’s try to solve it? 🙂

Actually, any Excel file is a certain number of rows and columns. We work, as always – using the Openpyxl library, and to work with each individual cell, we use the while loop.

In general, the algorithm of the code comes down to:
1. Open the file ( I wrote about this earlier )
2. Read the first cell in the row
3. Go to the next row in this column.
4. After you have reached the maximum in the number of cells in the column, go to the next column.
5. And so on until the file is completely processed.

Important : in order for the cycle to work, we need to know the number of cells in rows and columns. To do this, use the constructs in the form: 

  1. row_max = sheet_active.max_row  # Get the number of columns

and:

  1. column_max = sheet_active.max_column  # Get the number of columns

The code itself with comments is available below.

  1. import openpyxl
  2. from openpyxl.utils import get_column_letter
  3. import re
  4.  
  5. path_to_file = 'base_to_search.xlsx'
  6.  
  7. search_text = input(str('What text are we looking for: '))
  8. search_text = search_text.lower()
  9. print('Ищем:', search_text)
  10.  
  11. wb = openpyxl.load_workbook(path_to_file)  #Loaded our price list
  12. sheets_list = wb.sheetnames  # Get a list of all sheets in a file
  13. sheet_active = wb[sheets_list[0]]  # Getting started with the very first
  14. row_max = sheet_active.max_row  # Get the number of columns
  15. #print(type(row_max))
  16. column_max = sheet_active.max_column  #Get the number of rows
  17.  
  18. print('In file:', path_to_file, '\n Columns:', row_max, '\n rows:', column_max)
  19.  
  20. row_min = 1 #Variable for line number
  21. column_min = 1 #Column Number Variable
  22.  
  23. while column_min <= column_max:
  24.     row_min_min = row_min
  25.     row_max_max = row_max
  26.     while row_min_min <= row_max_max:
  27.         row_min_min = str(row_min_min)
  28.  
  29.         word_column = get_column_letter(column_min)
  30.         word_column = str(word_column)
  31.         word_cell = word_column + row_min_min
  32.  
  33.         data_from_cell = sheet_active[word_cell].value
  34.         data_from_cell = str(data_from_cell)
  35.         #print(data_from_cell)
  36.         regular = search_text
  37.         result = re.findall(regular, data_from_cell)
  38.         if len(result) > 0:
  39.             print('Нашли в ячейке:', word_cell)
  40.         row_min_min = int(row_min_min)
  41.         row_min_min = row_min_min + 1
  42.     column_min = column_min + 1

Of course, I will be glad to any comments and questions. To do this, use mail or Telegram.