1

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])
kitchen800
  • 197
  • 1
  • 12
  • 36
  • 1
    Could you give values for `matrix`, so the problem can be reproduced? Also you can check what data type openpyxl thinks the cells should be by printing `ws.cell(column=col_num, row=row_num).data_type`. If it's a number, the value should be `n`. – fakedad Apr 20 '21 at 22:01
  • great thanks i have added more details so hopefully you are able to replicate it. when i check the `data_type` as you suggested it comes up as 's'. i guess that's good because we now just need to change that `data_type` to 's'. but i dont know how to do that haha. any ideas? – kitchen800 Apr 21 '21 at 06:50

1 Answers1

1

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)
Eric Jensen
  • 193
  • 11
  • thanks for trying but it doesn't seem to work. I tested the data type of each element in the matrix and they are all strings. `print(ws.cell(column=col_num, row=row_num, value=item).data_type)` dont know how to convert them to number format – kitchen800 Apr 21 '21 at 18:10
  • I think the `float()` should convert the string to number, but my if statement was wrong. I've added a new function I found on Stackoverflow here: https://stackoverflow.com/questions/354038/how-do-i-check-if-a-string-is-a-number-float – Eric Jensen Apr 22 '21 at 10:45