Openpyxl技术求助：实现用户数据计算并填充Excel表格

Hey there! Let's get your pipe elevation calculation and Excel table working properly. You've already nailed the user input part—great job! Now let's fix the calculation logic and automate filling those table rows instead of hardcoding them.

First, Let's Correct the Core Calculations #

Your original code has a couple of small logic issues:

• Slope percentage conversion: Since y is a percentage (e.g., 2 for 2%), you need to divide by 100 to get a decimal before calculating fall.
• Elevation direction: Pipe fall means elevation decreases over length, so ending elevation should be starting elevation - total fall, not addition.
• Per-segment fall: You can calculate the 10-foot segment fall directly as (y / 100) * 10 instead of deriving it from total fall and segments.

Automate Table Row Generation #

Instead of using a bunch of if statements to set row labels and elevations, we'll use a loop to handle any length of run. This makes your code flexible for runs of any size, not just up to 700 feet.

Modified Full Code #

Here's the updated code with explanations in comments:

import openpyxl

# Load the workbook (fixed unnecessary import typo)
wb = openpyxl.load_workbook('slope.xlsx', data_only=True)
ws1 = wb['cover']

# Get user inputs (switched to float() for safer input handling)
print('What is the Starting Elevation?')
starting_elev = float(input())

print('What % slope do you wish to hold?')
slope_percent = float(input())

print('What is the Length of the Run?')
run_length = float(input())

# Calculate core values correctly
total_fall = (slope_percent / 100) * run_length  # Convert percentage to decimal first
ending_elev = starting_elev - total_fall  # Elevation decreases with pipe fall
num_segments = int(run_length // 10)  # Number of full 10-foot segments
fall_per_10ft = (slope_percent / 100) * 10  # Fall per single 10-foot pipe stick

# Fill summary cells (standardized cell reference case to avoid errors)
ws1['C8'] = starting_elev
ws1['K8'] = slope_percent
ws1['N8'] = run_length
ws1['H8'] = ending_elev
ws1['K10'] = total_fall
ws1['N10'] = num_segments
ws1['Q9'] = fall_per_10ft

# Automate filling the elevation table
starting_row = 14  # First row of your table (A14, C14)
for segment in range(num_segments + 1):
    # Generate station label (e.g., 0+00, 1+00, 2+00)
    station = f"{segment}+00"
    # Calculate elevation for current segment
    current_elev = starting_elev - (segment * fall_per_10ft)
    
    # Write values to worksheet
    ws1[f'A{starting_row + segment}'] = station
    ws1[f'C{starting_row + segment}'] = round(current_elev, 2)  # Round to 2 decimals for readability

# Save the final results
wb.save('slope_results5.xlsx')
print("Calculations complete! Check the saved Excel file for your elevation table.")

Key Improvements Explained #

• Safer input handling: Used float() instead of eval() to avoid potential security risks from untrusted user input.
• Dynamic table filling: The loop generates exactly as many rows as needed based on the run length, so it works for 50 feet or 1000 feet alike.
• Correct elevation math: Fixed the fall calculation to account for percentage conversion and downward elevation change.
• Consistent cell references: Used uppercase letters for cell columns (e.g., A14 instead of a14) to avoid case sensitivity issues.

Testing Tips #

• Try a small test case: Starting elevation 100, slope 2%, run length 50 feet. You should get elevations 100, 99.8, 99.6, 99.4, 99.2, 99.0 for stations 0+00 to 5+00.
• Verify the ending elevation matches manual calculation: 100 - (0.02 * 50) = 99.

内容的提问来源于stack exchange，提问作者user9274908