Python Forum
find cell value with matching regular expression of a row in excel file
Thread Rating:
  • 1 Vote(s) - 1 Average
  • 1
  • 2
  • 3
  • 4
  • 5
find cell value with matching regular expression of a row in excel file
#1
Python -- 3.5 pip 8.1.2 openpyxl centos 7

I am trying to read an excel file using above specifications. So i loop through all rows in an excel file and find a cell value matching regular expression and if that cell value is found then I should check the next immediate row is empty and if its empty I should print the name of the sheet.

#!/usr/bin/python3.5

import os, sys, re
from re import *
from openpyxl import *

f = "/root/python/countsheets/sample.xlsx"
print (f)
wb = load_workbook(f)
for sheet in wb:
       print ("\n")
       print ("Checking Sheet:", sheet)
# looping through all rows and first column
   for row in sheet.iter_rows('A{}:A{}'.format(sheet.min_row,sheet.max_row)):
               for col in sheet.iter_cols(min_col=1,max_col=1):
                       for cell in row:
# printing all cell values from all rows and first column
                               cv = cell.value
# matching regular expression with cell value
                               m = re.search(r"^yang*type*", str(cv))
                               print (cell.row,cell.column)
                               if m == cv:
                                       print (cell.coordinate)
                               else:
                                       print ("Not found")
How can I get into next row after finding cell value and check if it is empty?
Reply
#2
Hi hruday,

If I understand correctly, you know the row number of the current row,
Quote:print (cell.row,cell.column)
 so can you not run the if test on the next row by using the row value of cell.row and adding a 1? so this would be "cell.row +1"

Or have I missed the point, let me  know.

Good Luck

Bass

"The good thing about standards is that you have so many to choose from" Andy S. Tanenbaum
Reply
#3
Hi Bass, thanks for the reply..

if i increment the row number then how can i loop through row values for that row number?
Reply
#4
Unless I've missed something, you would loop through the row in exactly the same manner, the difference being that when you access the new row (the one below) then you add a "+ 1" after the variable name. It depends on how your code is set up, but rather than increase the value of the row by (row += 1) which will change the value itself, I would suffix the variable row with a + 1. This leaves the actual value the same but allows you to manipulate and scan the row below or (5 rows below if you use a + 5 etc.!)

Does this makes sense? If not then maybe you could update the code, as far as you can and then we can look at it again?

Best,

Bass

"The good thing about standards is that you have so many to choose from" Andy S. Tanenbaum
Reply
#5
While your at it, you might want to upgrade 'pip' to the latest version (currently 9.0.1)

from the command line, type and enter:

pip install --upgrade pip
If it ain't broke, I just haven't gotten to it yet.
OS: Windows 10, openSuse 42.3, freeBSD 11, Raspian "Stretch"
Python 3.6.5, IDE: PyCharm 2018 Community Edition
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Matching string from a file tester_V 5 343 Mar-05-2024, 05:46 AM
Last Post: Danishhafeez
  Python openyxl not updating Excel file MrBean12 1 250 Mar-03-2024, 12:16 AM
Last Post: MrBean12
  Copy Paste excel files based on the first letters of the file name Viento 2 348 Feb-07-2024, 12:24 PM
Last Post: Viento
  data validation with specific regular expression shaheen07 0 296 Jan-12-2024, 07:56 AM
Last Post: shaheen07
  Search Excel File with a list of values huzzug 4 1,147 Nov-03-2023, 05:35 PM
Last Post: huzzug
  Updating sharepoint excel file odd results cubangt 1 756 Nov-03-2023, 05:13 PM
Last Post: noisefloor
  FileNotFoundError: [WinError 2] The system cannot find the file specified NewBiee 2 1,496 Jul-31-2023, 11:42 AM
Last Post: deanhystad
  Reading data from excel file –> process it >>then write to another excel output file Jennifer_Jone 0 1,048 Mar-14-2023, 07:59 PM
Last Post: Jennifer_Jone
  Cannot find py credentials file standenman 5 1,572 Feb-25-2023, 08:30 PM
Last Post: Jeff900
  Save and Close Excel File avd88 0 2,840 Feb-20-2023, 07:19 PM
Last Post: avd88

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020