8wDlpd.png
8wDFp9.png
8wDEOx.png
8wDMfH.png
8wDKte.png

使用 Openpyxl 合并数据时出现 Excel 错误

JaredPar 2月前

22 0

我写了这个脚本: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 工作簿可以正常打开。
如果数量超过一定数量,则会出现错误,因为时间会有重叠。

但原始代码运行良好,没有任何错误。

帖子版权声明 1、本帖标题:使用 Openpyxl 合并数据时出现 Excel 错误
    本站网址:http://xjnalaquan.com/
2、本网站的资源部分来源于网络,如有侵权,请联系站长进行删除处理。
3、会员发帖仅代表会员个人观点,并不代表本站赞同其观点和对其真实性负责。
4、本站一律禁止以任何方式发布或转载任何违法的相关信息,访客发现请向站长举报
5、站长邮箱:yeweds@126.com 除非注明,本帖由JaredPar在本站《python》版块原创发布, 转载请注明出处!
最新回复 (0)
  • Ruwi 2月前 0 只看Ta
    引用 2

    这段代码几乎无法调试。你用了某种生成器吗?如果你想创建一个时间表,你可能需要考虑使用线性求解器。

  • 出现此问题的原因是时间冲突导致合并重叠。

    例如,“代数 2”将这些时间设置为第一个主题

    ('Chapter 1 Review in Textbook', 'Monday', (800, 870)),
    ('Read: Linear Equations in One Variable', 'Tuesday', (1300, 1370)), 
    

    然后当您进入“英语 3”主题时,这些时间与现有的“代数 2”时间重叠,从而导致合并范围重叠。

    ('Read: Mairs, "On Being a Cripple"', 'Monday', (800, 1200)), 
    ('"On Being a Cripple" Quiz', 'Tuesday', (1300, 1700))], 
    

    可能还有其他重叠,但是一旦第一个重叠用英语 3 写入,工作簿(实际上是 Sheet)就会损坏。

    然而
    最终看起来您的 distribute_homework 功能需要彻底改革。
    鉴于它返回时间表如下

    ('Chapter 1 Review in Textbook', 'Monday', (800, 870)),
    

    开始时间 800, 结束时间 870
    如果选择的第一个时间段没有涵盖所需的时间长度,则还会为一个作业返回两个时间表,这可能是设计使然。

    {
    'Algebra 2': [
    ('Chapter 1 Review in Textbook', 'Monday', (600, 730)), 
    ('Chapter 1 Review in Textbook', 'Monday', (800, 870)), 
    ('Read: Linear Equations in One Variable', 'Tuesday', (600, 730)), 
    ('Read: Linear Equations in One Variable', 'Tuesday', (1300, 1370)), 
    ...
    

    我不知道您对设置时间表的要求是什么,但它似乎不正确。
    使用“代数 2”的第一个作业
    "Chapter 1 Review in Textbook": 200 我假设您想要分配 200 的时间(2 小时还是 200 分钟?),它分配第一个范围(600,730),然后将下一个时间范围分配为(800,870),70 是 200(持续时间) - 130(730-600)的差值。

    考虑到您正在安排时间,并且时间段是小时或半小时,您最好将范围设置为 30 或 15 分钟间隔,并使用时间计算准确地在范围中添加/减去时间,而不是使用这些无意义的时间如 800 - 870。

  • 更具体地说,Excel 不允许将单元格合并到不同的范围中。如果您查看合并的单元格,您会看到几个范围重叠:ws.merged_cells.sorted()。您有像 H2:H22 和 H2:H28 这样的范围。

返回
作者主题