How to find all cells across an entire excel workbook that are in a regular expression?

Question

I am having trouble figuring out the correct way to write my code to find all the cells that fit my regular expression. This is what I have so far. I know i cant just equate the cell value to my regular expression but i do not know how to go about doing so.

import openpyxl
import re

date = re.compile(r'\d{1,2}[\/-]\d{1,2}[\/-]18-\d{1,2}[\/-]\d{1,2}[\/-](18|19)')
   for sheet in wb:
      for row in sheet.iter_rows():
         for cell in row:
           if cell.value == date:
             print (cell.value)

Examples:

          IN: mo = date.search('Support Term 3/7/18-3/6/18')
          IN: mo.group()
          OUT: '3/7/18-3/6/18'

(i tested my regex and it works how i want it to but my for loop needs some work obviously)

Can you provide some examples of what you're trying to match? You seem to want to match some date values but if your regex pattern doesn't work, then reading it probably won't give us an accurate demonstration of what you're trying to achieve. — 41686d6564 stands w. Palestine, Apr 07 '19 at 04:26
Yes, your regex seems fine to me (although it'll match invalid dates as well, but that aside...) I don't really know much Python but shouldn't `if cell.value == date` be replaced with something like `if date.match(cell.value)` or `if date.search(cell.value)`? — 41686d6564 stands w. Palestine, Apr 07 '19 at 04:46
yes its data that i need regardless if its bad or not and i did try the date.search() method but it returns an error because it wants a string or bytes like object — jwrig, Apr 07 '19 at 04:54

PineconeDude · Accepted Answer · 2019-04-07T05:01:24.560

1

I think what you want to use is the search method of the compiled object:

import openpyxl
import re

date = re.compile(r'\d{1,2}[\/-]\d{1,2}[\/-]18-\d{1,2}[\/-]\d{1,2}[\/-](18|19)')
for sheet in wb:
    for row in sheet.iter_rows():
       for cell in row:
         if date.search(str(cell.value)):
           print(cell.value)

edited Apr 07 '19 at 05:01

answered Apr 07 '19 at 04:46

PineconeDude

98
5

TypeError Traceback (most recent call last) in 2 for row in sheet.iter_rows(): 3 for cell in row: ----> 4 if date.search(cell.value): 5 print (cell.value) TypeError: expected string or bytes-like object – jwrig Apr 07 '19 at 04:51
yeah i tried that too but it seems to want a string or "bytes-like object" in the date.search() method – jwrig Apr 07 '19 at 04:52
Again, I don't know python but it looks like `cell.value` is not a string (which actually makes sense because Cell.Value is a variant in Excel VBA and usually an object when read by other programming languages) so it probably needs to be converted. See [this](https://stackoverflow.com/q/3204614/4934172). – 41686d6564 stands w. Palestine Apr 07 '19 at 04:53
Yes! that worked thank you. I didn't realize it wasn't a string type to begin with. Thank you both for helping. I figured it was something simple but didn't think it would be this simple. – jwrig Apr 07 '19 at 05:08

How to find all cells across an entire excel workbook that are in a regular expression?

1 Answers1