Python读取Excel时间类型数据并计算时间差写入新列求助

Hey there! As a Python newbie, it makes sense that handling Excel time data feels a bit tricky right now. Your current code uses xlrd, but let's note two key limitations first:

• xlrd can only read Excel files—it can't write back to them.
• Newer versions of xlrd don't support .xlsx files (only legacy .xls).

Let's walk through two straightforward solutions that will let you read time data, calculate differences, and write the results back to your Excel file.

Method 1: Using Pandas (Simplest for Beginners) #

Pandas is a powerful data analysis library that handles Excel files and time calculations with minimal code. Here's how to use it:

Step-by-Step Code #

import pandas as pd

# 1. Read the Excel file into a DataFrame
df = pd.read_excel("x1.xlsx")

# 2. Ensure time columns are recognized as time data
# Pandas usually auto-detects this, but force conversion if needed
df['start_time'] = pd.to_datetime(df['start_time'], format='%H:%M:%S').dt.time
df['end_time'] = pd.to_datetime(df['end_time'], format='%H:%M:%S').dt.time

# 3. Calculate time difference (convert time to datetime to compute delta)
# Add a dummy date since raw time objects can't be subtracted directly
start_dt = pd.to_datetime(df['start_time'].astype(str))
end_dt = pd.to_datetime(df['end_time'].astype(str))
df['time_diff'] = end_dt - start_dt

# 4. Write the updated data back to Excel
df.to_excel("x1_updated.xlsx", index=False)

Explanation #

• Pandas reads your Excel file into a DataFrame (a table-like structure) that makes data manipulation intuitive.
• Converting columns to datetime.time ensures we're working with proper time objects instead of strings.
• We convert times to full datetime objects (with a dummy date) because raw time types don't support subtraction. The result is a timedelta object showing hours/minutes/seconds between the two times.
• to_excel writes the updated data to a new file (you can overwrite the original if you're confident, but starting with a new file is safer for testing).

Method 2: Using OpenPyXL (More Control, No Pandas Needed) #

If you want to work directly with Excel files without pandas, openpyxl is a great choice—it supports reading and writing .xlsx files natively.

Step-by-Step Code #

from openpyxl import load_workbook
from datetime import datetime, timedelta

# 1. Load the workbook and select the first sheet
wb = load_workbook("x1.xlsx")
ws = wb.active

# 2. Add a header for the new time difference column
ws.cell(row=1, column=3, value="Time Difference")

# 3. Iterate through rows starting from row 2 (skip header)
for row in range(2, ws.max_row + 1):
    # Fetch time values from columns A and B (adjust column numbers as needed)
    start_time = ws.cell(row=row, column=1).value
    end_time = ws.cell(row=row, column=2).value

    # 4. Calculate time difference
    # Combine time with a dummy date to create subtractable datetime objects
    start_dt = datetime.combine(datetime.today(), start_time)
    end_dt = datetime.combine(datetime.today(), end_time)
    time_diff = end_dt - start_dt

    # 5. Write the result to column C
    ws.cell(row=row, column=3, value=str(time_diff))

# 6. Save the updated workbook
wb.save("x1_updated.xlsx")

Explanation #

• load_workbook opens your Excel file, and wb.active selects the first sheet.
• We add a header for the new column to keep your output organized.
• OpenPyXL returns datetime.time objects for cells formatted as Time in Excel—no extra conversion needed if your file is set up correctly.
• Combining time with a dummy date lets us subtract the values to get a timedelta, which we write as a string to the new column.

Quick Fixes for Your Original Code #

• Replace xlrd with either pandas or openpyxl since xlrd can't write to Excel and lacks .xlsx support in newer versions.
• Double-check that your Excel time columns are formatted as Time (not plain text)—otherwise, the libraries will read them as strings instead of time objects.

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