patelbg2001
asked on
Using Python to search & append identified Excel cell values.
Hi I'm seeking help to create an if statement in python
Three problems
1. if the cell in column B contains a dash '-', and the adjacent cells in columns 'C', 'D', 'E', have values then, I wanted to replace the dash with an average from the three cells.
Example from picture.
Row 2 Column B contains a dash, but data is present in Columns C, D and E
2. If the cell is populated with a number and not a formula then I wanted to append the cell, and add an additional three zeros
Example from picture.
Row 2 Column B now contains a formula, but data is present in Columns C, D and E
The number in Row 2, Column C is 42,746,000. I would like to update the value with an extra three zeros e.g. ',000'.
This will update the value in Row 2, Column C is 42,746,000,000
3. In Columns B, C, D and E, Rows 3, 4 and 5. I wanted to format the number according to either a positive or negative number
If a positive number then the cell format is updated to [<999950] 0.0,"K";[<999950000]0.0,,"
If a negative number then the cell format is updated to [>-999950]0.0,"K";[>-99995
Three problems
1. if the cell in column B contains a dash '-', and the adjacent cells in columns 'C', 'D', 'E', have values then, I wanted to replace the dash with an average from the three cells.
Example from picture.
Row 2 Column B contains a dash, but data is present in Columns C, D and E
2. If the cell is populated with a number and not a formula then I wanted to append the cell, and add an additional three zeros
Example from picture.
Row 2 Column B now contains a formula, but data is present in Columns C, D and E
The number in Row 2, Column C is 42,746,000. I would like to update the value with an extra three zeros e.g. ',000'.
This will update the value in Row 2, Column C is 42,746,000,000
3. In Columns B, C, D and E, Rows 3, 4 and 5. I wanted to format the number according to either a positive or negative number
If a positive number then the cell format is updated to [<999950] 0.0,"K";[<999950000]0.0,,"
If a negative number then the cell format is updated to [>-999950]0.0,"K";[>-99995
from openpyxl import Workbook
from openpyxl.styles import Color, PatternFill, Font, Border
from openpyxl.styles import colors
from openpyxl.cell import Cell
import os
xlfile = "/Users/User1/Documents/NewFolder/Test.xlsx"
if os.path.exists(xlfile):
os.remove(xlfile)
wb = Workbook()
filepath = "Test.xlsx"
if you have a small example .xls file, then you would save us a lot of time. We wouldn't have to create an xls file to test
and we'd be sure, that everything works on this file.
Additionally for that example you could us also give the expected answers for a few cells.
General tips:
you have to open the work book, then you have to select the sheet (or all sheets if your changes apply to all sheets in the work book)
and then you have to go through all rows. and do your manipulations.
I Fully agree with David about saving the results in a new file and not overwriting the original file. This avoids loosing date.
and we'd be sure, that everything works on this file.
Additionally for that example you could us also give the expected answers for a few cells.
General tips:
you have to open the work book, then you have to select the sheet (or all sheets if your changes apply to all sheets in the work book)
and then you have to go through all rows. and do your manipulations.
I Fully agree with David about saving the results in a new file and not overwriting the original file. This avoids loosing date.
Kind of curious, why are you deleting a file here?
Isn't 'Test.xlsx' the file you want to carry out the steps you describe on?
xlfile = "/Users/User1/Documents/NewFolder/Test.xlsx"
if os.path.exists(xlfile):
os.remove(xlfile)Isn't 'Test.xlsx' the file you want to carry out the steps you describe on?
ASKER
Hi All,
I'm pulling the information from a data source thats how its presented, so I'm just over writing the file, not the code used to generate it.
test.xlsx
I'm pulling the information from a data source thats how its presented, so I'm just over writing the file, not the code used to generate it.
test.xlsx
ASKER
Still working through this, and appreciate the support and help.
Error
File "test1.py", line 31
if len(str(cellObj)) > max_length
^
SyntaxError: invalid syntax
Screen-Shot-2019-04-21-at-10.25.58.png
from bs4 import BeautifulSoup
from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.styles import Color, PatternFill, Font, Border
from openpyxl.styles import colors
from openpyxl.cell import Cell
import os
wb = Workbook()
xlfile = "Test.xlsx"
wb = load_workbook(xlfile)
for xws in wb.sheetnames:
worksheet = wb[xws]
tuple(worksheet['B3':'E5'])
for rowOfCellObjects in worksheet['B3':'E5']:
for cellObj in rowOfCellObjects:
print(cellObj.coordinate, cellObj.value)
print('--- END OF ROW ---')
for xws in wb.sheetnames:
worksheet = wb[xws]
max_length = 3
tuple(worksheet['B3':'E5'])
for rowOfCellObjects in sheet['B3':'E5']:
for cellObj in rowOfCellObjects:
if len(str(cellObj)) > max_length
x = (cellObj.value)
y = str(x) + "000"
z = (format(int(y),","))
cellObj = 'z'
print(cellObj.coordinate, cellObj.value)
print('--- END OF ROW ---')[embed=file 1418545]Error
File "test1.py", line 31
if len(str(cellObj)) > max_length
^
SyntaxError: invalid syntax
Screen-Shot-2019-04-21-at-10.25.58.png
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You code will be...
1) Open the file.
2) Read the entire file.
3) Make any changes to the in memory copy of the file, using all the logic outlined in the initial question.
4) Write a new file, so don't overwrite the original file, in case your code has bugs.
Maybe I'm missing something.
Looks like you have all your logic laid out, so now you just have to write the code to implement your logic.