CODEWITHSUNDEEP
pythonpython-3.xopenpyxl
kitchen800
kitchen800

Reputation: 227

setting openpyxl format from text to number

I have an excel file and i am using openpyxl to input some data. the data is currently being entered as text. I am trying to do a loop so that my matrix can be inputted into an excel number type. The matrix is added into the excel no problem however the values are stored as text and not numbers. how can i change this? I thought by setting number_format to 0 it would work but it doesn't seem to.

for row_num, row in enumerate(matrix, 3):
    for col_num, item in enumerate(row, col_start - 12):
        ws.cell(column=col_num, row=row_num, value=item).number_format = "0"
wb.save(excel_file_path_from_db)

The matrix is made from user input values from dropdown options:

<td id="row_1_col_3">
               <center>
                   <select style="background-color: #ceedd0;" id="row_1_select_3" onchange="changeSelect('row_1','_col_3','_select_3')" name="filter_for">
             <option value="1">1</option>
             <option value="P">P</option>
             <option value="0">-</option>
             <option selected="selected" value="0">0</option>
</select></center>

I then access the values by:

matrix = ast.literal_eval(list(request.GET)[0])

Upvotes: 0

Views: 1586

Answers (1)

Eric Jensen
Eric Jensen

Reputation: 303

My guess would be that the datatype of item is String or a more general type. I would suggest:

def is_number(s):
    try:
        float(s)
        return True
    except ValueError:
        return False

for row_num, row in enumerate(matrix, 3):
    for col_num, item in enumerate(row, col_start - 12):
        if is_number(item):
            ws.cell(column=col_num, row=row_num, value=float(item))
            ws.cell(column=col_num, row=row_num).number_format = "0"
        else:
            ws.cell(column=col_num, row=row_num, value=item)
wb.save(excel_file_path_from_db)

Upvotes: 1

Related Questions