Openpyxl Check for Empty Cell

Checking for empty cells with OpenPyXl

What Charlie mentions is of course correct.

Non empty strings in Python evaluate to True, so you are actually testing if cell value is None or if bool('None'), and the later is always True, hence your condition always evaluates to True.

To address your specific case I'm not sure why you are trying to test for an empty cell with 'None'.

In case you really want to do this your condition should look like this:

if sheet.cell(row=i, column=1).value in [None,'None']

If you want test for None or an empty string, so None or '' then of course your condition should be:

if sheet.cell(row=i, column=1).value in [None,'']

Hope this gets you on the right track..

Python openpyxl read until empty cell

Try with max_row to get the maximum number of rows.

from openpyxl import Workbook
from openpyxl import load_workbook

wb = load_workbook('exc_file.xlsx')
ws1 = wb['Sheet1']
for row in range(1,ws1.max_row):
    if(ws1.cell(row,1).value is not None):
            print(ws1.cell(row,1).value)

OR if you want to stop reading when it reaches an empty value you can simply:

from openpyxl import Workbook
from openpyxl import load_workbook

wb = load_workbook('exc_file.xlsx')
ws1 = wb['Sheet1']
for row in range(1,ws1.max_row):
    if(ws1.cell(row,1).value is None):
        break
    print(ws1.cell(row,1).value)

how to check if a cell is empty in openpyxl python

You compare prevsymbol with str "None", not None object. Try

if prevsymbol == None:

Also here

prevsymbol = readex.cell(row = looprow,column=getnewhighcolumn).value

you use looprow as row index. And you increment looprow only if cell.value is not empty. Here

newstocks.append(prevsymbol)

you use newstocks instead of newlist. Try this code

newlist = []
print ("Highest col",readex.get_highest_column())
getnewhighcolumn = readex.get_highest_column()        
for i in range(0, lengthofdict):
    prevsymbol = readex.cell(row = i+1,column=getnewhighcolumn).value
    if prevsymbol is not None:
        newlist.append(prevsymbol)
print(newlist)

Python: Openpyxl outputs "None" for empty cells

OpenPyXl doesn’t store empty cells (empty means without value, font, border, and so on). If you get a cell from a worksheet, it dynamically creates a new empty cell with a None value.

The current implementation (v2.4.0) of Worksheet.iter_rows() use Worksheet.cell() method which calls Cell() constructor with no value.

You need to change your code to handle "empty" cells:

for rownum in sh.iter_rows():
    values = [(u"" if cell.value is None else unicode(cell.value))
              for cell in rownum]
    wr.writerow([value.encode('ascii', 'ignore') for value in rownum])

Note: since you export your data to a CSV file for, presumably Windows users, you may choose a more useful encoding like: cp1252.

Openpyxl - Merge Empty Column Cells Delimited by String

wb = openpyxl.load_workbook("Test.xlsx")  # Load Workbook
ws = wb["Sheet1"]                         # Load Worksheet

    total_rows = []  # Used to enumerate the total number of rows
                     # in the Worksheet

    for i in range (1,20000):
        if ws["B" + str(i)].value != None:
            total_rows.append(i)  # If the cell has a string, the
                                  # cell row number is appended to 
                                  # total_rows.  Indexing the last 
                                  # number is total_rows will give
                                  # you the total number of rows
    
    cells_with_strings = []
    for i in range (1,(total_rows[-1])):
        if ws["A" + str(i)].value != None:
            cells_with_strings.append(int(i))
            # Iterates through each cell in column A, and appends
            # the row numbers of cells containing text to cells_with_strings
    
    merge_cell_range = len(cells_with_strings) - 1
    for i in range (0, (merge_cell_range)):
        ws.merge_cells("A" + str(cells_with_strings[i]) + ":" + "A" + str((cells_with_strings[(i+1)])-1))
        # Using the values in cell_with_strings, it merges the cells in the rows
        # represented by the values in cells_with_strings.  This works for all 
        # merges except for the last merge.
    
    final_merge = []
    for i in range ((cells_with_strings[-1]), ((cells_with_strings[-1]) + 9999)):
        if ws["B" + str(i)].value != None:
            final_merge.append(int(i))
    ws.merge_cells("A" + str(final_merge[0]) + ":" + "A" + str(final_merge[-1]))
    # The last row merge requires different code to the iteration merge.

wb.save("Test.xlsx")

Get first empty row of sheet in Excel file with Python

This way your loop will stop if it encounters any empty cell in a row.
If you want the row wo be completely empty you can use all.

book = load_workbook("myfile.xlsx")
ws = book.worksheets[0]
for cell in ws["C"]:
    if cell.value is None:
        print cell.row
        break
else:
    print cell.row + 1

Update to the question in the comments:

ws["C"] will get a slice from C1:CX where X is the last filled cell in any column. So if the C column happens to be the longest column and every entry is filled you will only get cells with cell is not None so you won't break out of the loop. If you didn't break out of the loop you will enter the else block and since you looped till the last filled row, the first empty row will be the next one.

---

Related Topics