Hi.,
I have data in sheet1 of excel, in tabular column format.
i want to read the first data row, and perform calculation and output in the second sheet.
The data deonotes the size of the cabinet. ( width, height and depth)
From the data the panel sizes has to be calculated based on the formula.
For Example, the left panel size is the width = (cabinet Height) and depth = (cabinet depth).
Shelf size is width= (Cabinet width - thickness of the panel*2 ) and depth = (cabinet depth - 50 mm)
How to perfom the calculation in the python
The sample file is shared here
Hey there a little producible code to use, cause I couldn’t get how you were performing the calculations. You can adjust accordingly to your needs cheers.
import openpyxl # so we import the necessary lib to handle the excel, others you can use like pandas
try:
# loading and opening the excel workbook,
workbook = openpyxl.load_workbook('tabular.xlsx') # esnsure this script is in the same directory as your excel file
workbook_sheetName = workbook['Sheet1'] # the sheetname containing your data
except FileNotFoundError as error:
print(f'{error} file not found, check the name or path of the file')
for row in workbook_sheetName.iter_rows(min_row=2, values_only=True): # iterating through the rows available, assuming you have headers
width, height, length = row[0], row[1], row[2] # assigining the varibles
# formula for calculating volume just an example
volume = length * width * height
print(f'Volume is {volume}') # to test the functionality by printing on the terminal each voulme
workbook_sheetName.cell(row[0], column=4, value=volume) # row[0] is the current row with each iteration and incrementing
# column=4 is the column where the volume results are going to be placed
workbook.save('updated_tabular.xlsx') # esnure to save the workbook after perfoming operations
workbook.close()
Thanks @kyle, it is starting point toward the goal. Thanks @MRAB
@kyle , i modified the code, but the values are not updating in the excel
import openpyxl
try:
workbook = openpyxl.load_workbook('J:\\python\\test\\sample_01.xlsx')
workbook_sheetName = workbook['Sheet1']
except FileNotFoundError as error:
print(f'{error} file not found, check the name or path of the file')
for row in workbook_sheetName.iter_rows(min_row=2, values_only=True):
g1_value = workbook_sheetName.cell(row=row[0], column=7).value
h1_value = workbook_sheetName.cell(row=row[0], column=8).value
if g1_value == 3.5 and h1_value == 1:
# Code block to execute if conditions are met for case 1
#print("Case 1 executed")
# Add your code here
# assigining the varibles
width_value = workbook_sheetName.cell(row=row[0], column=3).value
height_value = workbook_sheetName.cell(row=row[0], column=4).value
depth_value = workbook_sheetName.cell(row=row[0], column=5).value
shelf_value=workbook_sheetName.cell(row=row[0], column=3).value-36
# formula for calculating volume just an example
#Left Panel details
height = height_value
depth=depth_value
shelf_width= shelf_value
workbook_sheetName.cell(row[0], column=10, value=height) # row[0] is the current row with each iteration and incrementing &# column=10 is the column where the volume results are going to be placed
workbook_sheetName.cell(row[0], column=11, value=depth)
workbook_sheetName.cell(row[1], column=10, value=shelf_width)
workbook_sheetName.cell(row[1], column=11, value=500)
elif g1_value == 4 and h1_value == 2:
# Code block to execute if conditions are met for case 2
print("Case 2 executed")
# Add your code here
else:
# Code block to execute if none of the conditions are met
print("Default case executed")
# Add your code here
workbook.save('J:\\python\\test\\sample_01.xlsx')
workbook.close()
Ummh why but I can already see an indentation problem in your code here
workbook_sheetName.cell(row[0], column=10, value=height) # row[0] is the current row with each iteration and incrementing &# column=10 is the column where the volume results are going to be placed
workbook_sheetName.cell(row[0], column=11, value=depth)
workbook_sheetName.cell(row[1], column=10, value=shelf_width)
workbook_sheetName.cell(row[1], column=11, value=500)
Hey did it work or you’ve not tried it. Nywy just realized the indent was brought up by how you pasted the code in here and tends to dent it a little. I found another problem that the last row is not processed was this the problem. Try this out and klemme know… though am still not sure your intention is but this will ensure all the specified cell are captured correctly. I’ve replaced the row[0] with current row to store current row index on each iteration
import openpyxl
try:
workbook = openpyxl.load_workbook('test3.xlsx')
workbook_sheetName = workbook['Sheet3']
except FileNotFoundError as error:
print(f'{error} file not found, check the name or path of the file')
# initailzing the current row index
current_row = 2 # commencing at 2 to skip the header
for row in workbook_sheetName.iter_rows(min_row=2, values_only=True):
g1_value = row[6] # Assuming G1 is in the sixth column (column index 6) numbering is zero-based indexing
h1_value = row[7]
width_value = row[2] # Assuming Height is in the c column (column index 2)
height_value = row[3] # Assuming Height is in the D column (column index 3)
depth_value = row[4]
# shelf_value = row[5]
if g1_value == 3.5 and h1_value == 1:
# Code block to execute if conditions are met for case 1
print("Case 1 executed")
# Add your code here
print(f'shelf height {height_value}')
# formula for calculating volume just an example
#Left Panel details
height = height_value # optional to assign as this seems redundant to assign the same value to a new variable
print(height)
shelf_value = width_value - 36 # new shelf value
workbook_sheetName.cell(row=current_row, column=10, value=width_value)
workbook_sheetName.cell(row=current_row, column=11, value=height_value)
workbook_sheetName.cell(row=current_row, column=12, value=depth_value)
workbook_sheetName.cell(row=current_row, column=13, value=shelf_value)
elif g1_value == 4 and h1_value == 2:
# Code block to execute if conditions are met for case 2
print("Case 2 executed")
# Add your code here
workbook_sheetName.cell(row=current_row, column=10, value=width_value)
workbook_sheetName.cell(row=current_row, column=11, value=height_value)
else:
# Code block to execute if none of the conditions are met
print("Default case executed")
# Add your code here
current_row += 1 # increments after each iteration updating our current row
workbook.save('u.xlsx')
workbook.close()
am not sure about notepad++ but VS has highlighting, so every indentation error will be noted and highlighted.You need to install Python intellisense I guess if am not wrong but some extension and enable it. Just go to extensions in VS and search for it.