Python Forum
help with changing values in an excel sheet and saving to a new file
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
help with changing values in an excel sheet and saving to a new file
#1
Hi guys,

It is my first post here and I am following a tutorial on youtube on how to learn python, I'm 4 hours in and I have had some issues in the past already getting the same results as the guy in the video and I almost posted here but I was able to figure out most of them, until we touched upon excel sheets.

Basically, we have an excel sheet with a list of transactions and prices for each of them, and the goal is to take the prices column and discount it by 10% (so multiply by 0.9) and paste the results in the next column. Column 3 being the originals, and column 4 being the adjusted prices. There are 4 row in total but the first 1 is just titles so no numbers there.

Here is the code:

import openpyxl as xl


wb = xl.load_workbook('transactions.xlsx')
sheet = wb['Sheet1']
cell = sheet['a1']
cell = sheet.cell(1, 1)
print(cell.value)

for row in range(2, sheet.max_row + 1):
    cell = sheet.cell(row, 3)
    corrected_price = cell.value * 0.9
    corrected_price_cell = sheet.cell(row, 4)
    corrected_price_cell.value = corrected_price
    print(corrected_price)


wb.save('transactions 2.xlsx')
And here is what happens when running it :

Error:
~$transactions.xlsx transaction_id 5.355 6.255 7.155 Traceback (most recent call last): File "C:/Users/Youssef/PycharmProjects/HelloWorld/app.py", line 12, in <module> corrected_price = cell.value * 0.9 TypeError: unsupported operand type(s) for *: 'NoneType' and 'float'
What I understand from this is that corrected_price is a Nonetype whereas cell is a float, so the * operator is invalid there, but isn't that line meant to turn corrected_price into a float ?

One thing I noticed is the multiplication is done just fine, the number 5.355 for example is 90% of 5.95 which is my price for row 2 column 3, so python is doing what I want it to with the mutiplication and printing it on pycharm but it's still giving me an error message and it is not saving the updated values new excel file called 'transactions 2.xlsx' like I am asking it to.

Tried replaying that part of the youtube video countless times - my code is the exact same as the youtube guy - and looking up the error on google, I can't figure it out guys... I am sure that the issue is something obvious that I'm just too stupid to realize. Another thing is the guy in the video uses 3.7 whereas I am using python 3.8, not sure if that makes a difference.

Please help me and thanks in advance
Reply
#2
Most likely you have a empty cell in transactions.xlsx.
Check cell after value 7.155.
To make the error.
>>> cell = None
>>> cell * 0.9
Traceback (most recent call last):
  File "<interactive input>", line 1, in <module>
TypeError: unsupported operand type(s) for *: 'NoneType' and 'float'
You can fix file bye add a value,or bypass(pass) the error as shown under.
import openpyxl as xl

wb = xl.load_workbook('transactions.xlsx')
sheet = wb['Sheet1']
cell = sheet['a1']
cell = sheet.cell(1, 1)
print(cell.value)

for row in range(2, sheet.max_row + 1):
    cell = sheet.cell(row, 3)
    try:
        corrected_price = cell.value * 0.9
    except TypeError:
        pass
    corrected_price_cell = sheet.cell(row, 4)
    corrected_price_cell.value = corrected_price
    print(corrected_price)

wb.save('transactions 2.xlsx')
Reply
#3
(Nov-18-2019, 11:31 AM)snippsat Wrote: Most likely you have a empty cell in transactions.xlsx.
Check cell after value 7.155.
To make the error.

You can fix file bye add a value,or bypass(pass) the error as shown under.
[/python]

Hey man it worked, thank you so much !

I wonder why the tutor didn't get this error though... Do you have any idea ?
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Python openyxl not updating Excel file MrBean12 1 250 Mar-03-2024, 12:16 AM
Last Post: MrBean12
  Copy Paste excel files based on the first letters of the file name Viento 2 348 Feb-07-2024, 12:24 PM
Last Post: Viento
  Search Excel File with a list of values huzzug 4 1,147 Nov-03-2023, 05:35 PM
Last Post: huzzug
  Updating sharepoint excel file odd results cubangt 1 755 Nov-03-2023, 05:13 PM
Last Post: noisefloor
  Python and pandas: Aggregate lines form Excel sheet Glyxbringer 12 1,696 Oct-31-2023, 10:21 AM
Last Post: Pedroski55
  Reading data from excel file –> process it >>then write to another excel output file Jennifer_Jone 0 1,046 Mar-14-2023, 07:59 PM
Last Post: Jennifer_Jone
  Save and Close Excel File avd88 0 2,840 Feb-20-2023, 07:19 PM
Last Post: avd88
  Trying to access excel file on our sharepoint server but getting errors cubangt 0 773 Feb-16-2023, 08:11 PM
Last Post: cubangt
  Import XML file directly into Excel spreadsheet demdej 0 801 Jan-24-2023, 02:48 PM
Last Post: demdej
  how to read txt file, and write into excel with multiply sheet jacklee26 14 9,515 Jan-21-2023, 06:57 AM
Last Post: jacklee26

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020