TAG
作者主题
- 使用 Openpyxl 合并数据时出现 Excel 错误
- 如何根据 ansible 中的条件仅使用 3 个变量中的一个?
- 通过 Helm 使用自定义 Dockerfile 在本地部署 Airflow
- 如何构建 html
使用[仅] angular.js 模板来获得不连贯的 json?
作者最近主题:
我写了这个脚本:import openpyxlfrom openpyxl.styles import Alignmentfrom datetime import datetime, timedeltaDays = [\'Monday\', \'Tuesday\', \'Wednesday\', \'Th...
我写了这个脚本:
import openpyxl
from openpyxl.styles import Alignment
from datetime import datetime, timedelta
Days = ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Sunday","Monday1", "Tuesday1", "Wednesday1", "Thursday1", "Friday1", "Sunday1"]
occupied_days = ["Saturday"]
ranges = [
[(600, 730), (800, 1200), (1300, 1900), (2130, 2300)],
[(600, 730), (1300, 1900), (2130, 2300)],
[(600, 730), (1300, 1900), (2130, 2300)],
[(600, 730), (1300, 1900), (2130, 2300)],
[(600, 730), (1300, 1900), (2130, 2300)],
[(1500, 2200)],
[(600, 730), (800, 1200), (1300, 1900), (2130, 2300)],
[(600, 730), (1300, 1900), (2130, 2300)],
[(600, 730), (1300, 1900), (2130, 2300)],
[(600, 730), (1300, 1900), (2130, 2300)],
[(600, 730), (1300, 1900), (2130, 2300)],
[(1500, 2200)]
]
def export_schedule_to_excel(schedule):
wb = openpyxl.Workbook()
ws = wb.active
earlies = []
lates = []
for x in schedule.values():
if len(x) > 0:
earlies.append(min(x, key=lambda s: s[2][0]))
lates.append(max(x, key=lambda s: s[2][1]))
earliest = min(earlies, key=lambda s: s[2][0])[2][0]
latest = max(lates, key=lambda s: s[2][1])[2][1]
time_5 = [x for x in range(earliest, latest + 5, 5)] # Ensure the range includes the latest time
for i, t in enumerate(time_5, start=2):
ws.cell(row=i, column=1, value=t)
for i, d in enumerate(Days):
ws.cell(row=1, column=i+2, value=d)
r, c = 2, 2
for course, assignments in schedule.items():
for assignment in assignments:
name, day, (start, end) = assignment
r1 = time_5.index(start) + r
r2 = time_5.index(end) + r
day_col = Days.index(day) + c
print(r2,r1,day_col)
# Assign value to the top-left cell before merging
ws.merge_cells(start_row=r1, start_column=day_col, end_row=r2, end_column=day_col)
cell = ws.cell(row=r1, column=day_col, value=f"{course}:{name}")
cell.alignment = Alignment(horizontal='center', vertical='center')
wb.save("schedule.xlsx")
courses_hom = {
"Algebra 2": {"Chapter 1 Review in Textbook": 200, "Read: Linear Equations in One Variable": 200,
"Read: An Introduction to Problem Solving": 200, "Read: Formulas and Problem Solving": 200},
"Biology": {"Taxonomy & Scientific Method Recorded Lesson Check": 125, "Water Investigation Post Lab": 200},
"English 3": {"Eng 3 Orientation Worksheet": 100, "Read: Reading Deeper, Writing Stronger (RDWS)": 150,
"Rhetorical Appeals Worksheet": 150, "Finding the Message Chart": 400,
'Read: Staples "Just Walk on By"': 400, '"Just Walk on By" Quiz': 400,
'Read: Mairs, "On Being a Cripple"': 400, '"On Being a Cripple" Quiz': 400},
"History": {'Read: WH-What is History?': 300, 'Read: MWR-Introduction': 300, 'What is History?-Questions': 300,
'MWR Intro-Questions': 300, 'Read: Skill Building: CEA Writing Strategy': 300,
'Read: Skill Building: Chicago/Turabian Style for Research Papers': 300},
"Java": {"Read: Inputs and Outputs Reading": 130, "Print a Poem with Java": 30}
}
def distribute_homework(Days, occupied_days, ranges, courses_hom):
schedule = {}
day_index = 0
used_time_slots = {day: [] for day in Days}
for course, assignments in courses_hom.items():
schedule[course] = []
for assignment, duration in assignments.items():
while duration > 0:
if Days[day_index] not in occupied_days:
for time_range in ranges[day_index]:
start, end = time_range
available_time = end - start
if available_time >= duration and (start, end) not in used_time_slots[Days[day_index]]:
schedule[course].append((assignment, Days[day_index], (start, start + duration)))
used_time_slots[Days[day_index]].append((start, start + duration))
duration = 0
break
elif available_time < duration and (start, end) not in used_time_slots[Days[day_index]]:
schedule[course].append((assignment, Days[day_index], (start, end)))
used_time_slots[Days[day_index]].append((start, end))
duration -= available_time
day_index = (day_index + 1) % len(Days)
return schedule
totals = 0
for x in ranges:
for m in x:
totals+=m[1]-m[0]
print(totals)
totals_hw = 0
for x in courses_hom:
for m in courses_hom[x].values():
totals_hw+=m
print(totals_hw)
if totals_hw>totals:
print("impossible!")
schedule = distribute_homework(Days, occupied_days, ranges, courses_hom)
print(schedule)
export_schedule_to_excel(schedule)
并且它工作正常,没有报告任何错误。
然而,当我尝试使用 Excel 打开工作簿时,它给出一个警告,要求我恢复工作表,因为似乎存在一些错误的格式。
我尝试调试,发现如果我写
ws.merge_cells(start_row=r1, start_column=day_col, end_row=r1+20, end_column=day_col)
或者 r1+a number
Excel 工作簿可以正常打开。
如果数量超过一定数量,则会出现错误,因为时间会有重叠。
但原始代码运行良好,没有任何错误。