CODEWITHSUNDEEP
pythonexcelopenpyxl
L.P
L.P

Reputation: 43

Openpyxl - Unable to delete 'none' cell values from list

I am using openpyxl to get values of cells in a specific column. I am using

load_workbook(path, data_only=True)

because some cells have formulas in them.

However, for cells that are empty in excel, I am confused as to how to either delete them from my list or not append them in the first place.

This is because I am unable to target these empty cells. Indeed, using type() it appears that these cells are returned as strings (not NoneType). I've tried using None, "", "none" to skip these values from being appended, with no success.

Here is my code so far:

import openpyxl
import json
import requests

mypath = "XXX/XXXXXX/XXXXXX/XXX"
filename = "file"


def get_column_number(ws, header_name, max_header_row):
    for index, row in enumerate(ws, start=1):
        if index > max_header_row:
            break

        for cell in row:
            if cell.value:
                if str(cell.value).rstrip() == header_name:
                    return cell.col_idx

def get_location_list(wb,ws):
    location_list = []
    for row in range(2, ws.max_row):
        row = row + 1
        cell_value = str(ws.cell(row=row, column=get_column_number(ws, "Location literal", 1)).value).lower()
        print(cell_value)
        if cell_value is None:
            print("blank")
            pass
        else:
            location_list.append(cell_value)

    print(location_list)

for i in list_of_fdims:
    wb = openpyxl.load_workbook(mypath+filename+".xlsx", data_only=True)
    ws = wb['test_cases']
    print(i)
    get_location_list(wb,ws)

Here is how the empty cell values are returned :

In a list :

['none']

Using print() :

none

If anyone can help me I would really appreciate it. Thank you very much in advance !

Upvotes: 0

Views: 1663

Answers (1)

L.P
L.P

Reputation: 43

After some trial and error i simply had to put :

if cell_value == "none":
        continue
    else:
        location_list.append(cell_value)

This fixed my problem

Upvotes: 0

Related Questions