P242-3-单元格翻转

课后题3

# 单元格翻转
import openpyxl

wb=openpyxl.load_workbook('tmp.xlsx')
print(wb.sheetnames)
sheetOld=wb['Sheet1']
sheetNew=wb.create_sheet(title='new2')

for rowNum in range(1,sheetOld.max_row+1):
    for ColumnNum in range(1,sheetOld.max_column+1):
        print(sheetOld.cell(row=rowNum,column=ColumnNum).value)
        sheetNew.cell(row=ColumnNum,column=rowNum).value=sheetOld.cell(row=rowNum,column=ColumnNum).value

wb.save('tmp2.xlsx')

 

# -*- coding: utf-8 -*- """ 办公室平面图生成器 - 左上角为原点版本 功能:基于 seat.xlsx,在 a-r 行 × 每行 22 座位的固定布局中, 绘制完整工位图,已分配高亮,未分配灰显,且 a 行在最上方。 """ import os import pandas as pd import matplotlib.pyplot as plt from matplotlib.patches import Rectangle plt.rcParams[&#39;font.sans-serif&#39;] = [&#39;SimHei&#39;] plt.rcParams[&#39;font.family&#39;] = &#39;sans-serif&#39; plt.rcParams[&#39;axes.unicode_minus&#39;] = False # ------------------------------- # 1. 检查输入文件是否存在 # ------------------------------- if not os.path.exists("seat.xlsx"): raise FileNotFoundError("请确保当前目录下有 &#39;seat.xlsx&#39; 文件!") # ------------------------------- # 2. 读取 Excel 数据并构建映射 # ------------------------------- try: df = pd.read_excel("seat.xlsx") if len(df.columns) < 3: raise ValueError("Excel 文件必须至少包含三列。") df = df.iloc[:, :3] df.columns = [&#39;seat&#39;, &#39;emp_id&#39;, &#39;dept&#39;] except Exception as e: raise RuntimeError(f"无法读取 seat.xlsx: {e}") # 清理数据 df.dropna(subset=[&#39;seat&#39;], inplace=True) df[&#39;seat&#39;] = df[&#39;seat&#39;].astype(str).str.upper().str.strip() df[&#39;dept&#39;] = df[&#39;dept&#39;].astype(str).str.upper().str.strip() assigned_seats = df.set_index(&#39;seat&#39;).T.to_dict() pillar_color = &#39;#555555&#39; # 柱子颜色(深灰) w, h = 0.9, 0.8 # 座位矩形宽高 zorder_base = 10 # 层级顺序基础值 # ------------------------------- # 3. 定义完整布局:a - r 行,每行 1 - 22 号 # ------------------------------- rows = [chr(i) for i in range(ord(&#39;c&#39;), ord(&#39;r&#39;) + 1)] # c 到 r 共 16 行 cols = list(range(1, 23)) # 1 到 22 # 映射字母到 y 坐标:a -> 0, b -> 1, ..., r -> 17 row_map = {letter: idx for idx, letter in enumerate(rows)} col_map = {num: num - 1 for num in cols} # 数字→索引(等距排列) use_numeric_x = False # 推荐 False:等间距更整齐 # ------------------------------- # 4. 预生成所有座位的位置 # ------------------------------- seat_positions = {} for letter in rows: for num in cols: seat_id = f"{letter.upper()}{num:02d}" y = row_map[letter] # a=0, b=1, ..., r=17 → 将通过 invert_yaxis() 变成顶部到底部 x = num if use_numeric_x else col_map[num] seat_positions[seat_id] = (x, y) x_vals = [pos[0] for pos in seat_positions.values()] y_vals = [pos[1] for pos in seat_positions.values()] x_min, x_max = min(x_vals), max(x_vals) y_min, y_max = min(y_vals), max(y_vals) # ------------------------------- # 5. 颜色设置 # ------------------------------- dept_colors = { &#39;S19&#39;: &#39;#FF6B6B&#39;, &#39;S13&#39;: &#39;#4ECDC4&#39;, &#39;HR&#39;: &#39;#E57777&#39;, &#39;S17&#39;: &#39;#77C7E5&#39;, &#39;S11&#39;: &#39;#E5BC77&#39;, &#39;QM&#39;: &#39;#E5DF77&#39;, &#39;RD&#39;: &#39;#C2E577&#39;, &#39;先进制程技术&#39;: &#39;#7DE577&#39;, &#39;IT&#39;: &#39;#77E5B3&#39;, &#39;资材&#39;: &#39;#77E5D6&#39;, &#39;厂务&#39;: &#39;#E59977&#39;, &#39;总经理室&#39;: &#39;#77A4E5&#39;, &#39;环安&#39;: &#39;#A977E5&#39;, &#39;外包&#39;: &#39;#E577D6&#39;, &#39;客戶服務課&#39;: &#39;#E577B3&#39;, &#39;财务&#39;: &#39;#E57790&#39;, &#39;外派(非AUS)&#39;: &#39;#E57777&#39; } pillars = [&#39;G02&#39;, &#39;G06&#39;, &#39;G11&#39;, &#39;G14&#39;, &#39;G18&#39;, &#39;G22&#39;, &#39;M02&#39;, &#39;M06&#39;, &#39;M11&#39;, &#39;M14&#39;, &#39;M18&#39;, &#39;M22&#39;] valid_pillars = [p for p in pillars if p in seat_positions] # ------------------------------- # 6. 创建绘图 # ------------------------------- fig_width = (x_max - x_min + 2) * 0.6 fig_height = (y_max + 2) * 0.5 fig, ax = plt.subplots(1, figsize=(fig_width, fig_height)) # ------------------------------- # 7. 遍历所有座位绘制 # ------------------------------- for seat_id, (x, y) in seat_positions.items(): rect_center_x = x + 0.5 * w if use_numeric_x else x + 0.5 rect_y = y + 0.1 # 矩形底部 y 坐标 # 判断是否为柱子 is_pillar = seat_id in pillars is_occupied = seat_id in assigned_seats and not is_pillar if is_pillar: color = pillar_color hatch = &#39;xxxx&#39; zorder = zorder_base + 3 elif is_occupied: dept = assigned_seats[seat_id][&#39;dept&#39;] color = dept_colors.get(dept, &#39;#CCCCCC&#39;) hatch = None zorder = zorder_base + 2 else: color = &#39;#FFFFFF&#39; hatch = None zorder = zorder_base + 1 # 绘制矩形 rect = Rectangle((x, y), w, h, facecolor=color, edgecolor=&#39;black&#39;, lw=0.8, hatch=hatch, zorder=zorder) ax.add_patch(rect) # 添加座位编号(所有位置都显示) ax.text( rect_center_x, rect_y + h * 0.4, seat_id, ha=&#39;center&#39;, va=&#39;center&#39;, fontsize=7, color=&#39;black&#39;, style=&#39;italic&#39;, alpha=0.9, zorder=zorder + 1 ) # 如果已分配且不是柱子,才显示员工信息 if is_occupied: person = assigned_seats[seat_id] raw_id = str(person[&#39;emp_id&#39;]).strip() if raw_id and raw_id[0].isalpha(): formatted_emp_id = raw_id[0].upper() + raw_id[1:] else: formatted_emp_id = raw_id ax.text( rect_center_x, rect_y + h * 0.75, f"{formatted_emp_id}", ha=&#39;center&#39;, va=&#39;center&#39;, fontsize=8, weight=&#39;bold&#39;, color=&#39;black&#39;, zorder=zorder + 1 ) # ------------------------------- # 8. 设置坐标轴范围与标签 # ------------------------------- ax.set_xlim(x_min, x_max + 1) ax.set_ylim(y_min - 1, y_max + 1) # Y 轴标签:a 在顶部 ax.set_yticks([i + 0.5 for i in range(len(rows))]) ax.set_yticklabels([r.upper() for r in rows]) # X 轴标签 if use_numeric_x: ax.set_xticks(range(1, 23)) else: ax.set_xticks([col_map[n] + 0.5 for n in cols]) ax.set_xticklabels(cols) # ax.set_xlabel("Seat Number", fontsize=12, labelpad=10) # ax.set_title( # "🏢 Office Floor Plan\nOrigin: Top-Left | Rows a–r (a at top), Seats 01–22", # fontsize=16, pad=25, weight=&#39;bold&#39; # ) # ------------------------------- # 翻转 Y 轴,使 a 行在最上方 # ------------------------------- ax.invert_yaxis() # ------------------------------- # 9. 图例 # ------------------------------- used_depts = set(assigned_seats[s][&#39;dept&#39;] for s in assigned_seats if &#39;dept&#39; in assigned_seats[s]) legend_handles = [plt.Rectangle((0,0),1,1, color=dept_colors[d]) for d in used_depts if d in dept_colors] legend_labels = [d.upper() for d in used_depts if d in dept_colors] if legend_handles: ax.legend( legend_handles, legend_labels, loc=&#39;upper right&#39;, ncol=15, fontsize=10, title_fontsize=11 ) # ------------------------------- # 10. 框 # ------------------------------- ax.spines[&#39;top&#39;].set_visible(False) ax.spines[&#39;right&#39;].set_visible(False) ax.spines[&#39;left&#39;].set_visible(False) ax.spines[&#39;bottom&#39;].set_visible(False) ax.tick_params(left=False, bottom=False, labelleft=True, labelbottom=True) # ------------------------------- # 11. 调整布局 # ------------------------------- plt.subplots_adjust(left=0, right=1, top=1, bottom=0) # ------------------------------- # 12. 保存输出 # ------------------------------- output_pdf = "T1.pdf" # output_png = "office_floor_plan_top_left.png" plt.savefig(output_pdf, dpi=200, bbox_inches=&#39;tight&#39;) # plt.savefig(output_png, dpi=200, bbox_inches=&#39;tight&#39;) print(f"{output_pdf}") # ------------------------------- # 13. 显示图像 # ------------------------------- # plt.show() 某几个位置为空白不填充任何内容
10-28
你遇到的问题是: > **“某几个位置为空白不填充任何内容”** 结合你的代码分析,这是一个典型的 **布局错位 + 坐标映射错误** 导致的视觉“空白”问题。 --- ## 🔍 一、根本原因分析 ### ❌ 关键问题出在:`seat_positions` 的生成逻辑与绘图坐标未对齐 ```python for letter in rows: for num in cols: seat_id = f"{letter.upper()}{num:02d}" y = row_map[letter] # 正确 x = num if use_numeric_x else col_map[num] seat_positions[seat_id] = (x, y) ``` 然后你用 `(x, y)` 绘制矩形: ```python rect = Rectangle((x, y), w, h, ...) ``` 但这里有个致命细节: 👉 **`Rectangle((x,y), w, h)` 是以左下角为锚点绘制的!** 而你在 `ax.text(...)` 中使用了偏移(如 `y + h*0.4`),这说明你想让文字在格子内居中 —— 可是你没有统一地处理这个“单元格”的定位方式。 --- ## ✅ 正确做法:每个座位应占据一个固定“格子”,无论 `w`, `h` 多大 你应该基于“网格索引”来画,而不是直接拿 `x` 和 `y` 当坐标。 --- ## 🛠️ 二、修复方案(完整修改建议) ### ✅ 修改 1:确保所有座位都按等间距排列(推荐) 将 `use_numeric_x = False` 固定,并修正 X/Y 对齐逻辑。 #### ✅ 替换 `#7. 遍历所有座位绘制` 部分为以下代码: ```python # ------------------------------- # 7. 遍历所有座位绘制(修复版) # ------------------------------- for seat_id, (grid_x, grid_y) in seat_positions.items(): # 所有元素基于 grid_x, grid_y 构建(即列号和行号作为索引) # 判断是否为柱子或已分配 is_pillar = seat_id in valid_pillars is_occupied = seat_id in assigned_seats and not is_pillar # 设置颜色与图案 if is_pillar: color = pillar_color hatch = &#39;xxxx&#39; zorder = zorder_base + 3 elif is_occupied: dept = assigned_seats[seat_id][&#39;dept&#39;] color = dept_colors.get(dept, &#39;#CCCCCC&#39;) hatch = None zorder = zorder_base + 2 else: color = &#39;#EEEEEE&#39; # 空闲工位设为浅灰,避免纯白“像没画” hatch = None zorder = zorder_base + 1 # 绘制矩形(以 grid_x, grid_y 为左下角) rect = Rectangle( (grid_x, grid_y), width=w, height=h, facecolor=color, edgecolor=&#39;black&#39;, linewidth=0.8, hatch=hatch, zorder=zorder ) ax.add_patch(rect) # 添加座位编号(居中于格子) center_x = grid_x + w / 2 center_y = grid_y + h / 2 ax.text( center_x, center_y + 0.15, seat_id, ha=&#39;center&#39;, va=&#39;center&#39;, fontsize=6, color=&#39;darkblue&#39;, style=&#39;italic&#39;, alpha=0.8, zorder=zorder + 1 ) # 如果已分配且非柱子,显示员工 ID if is_occupied: emp_id = str(assigned_seats[seat_id][&#39;emp_id&#39;]).strip() if &#39;@&#39; in emp_id: display_id = emp_id.split(&#39;@&#39;)[0].upper() else: display_id = emp_id[:6].upper() ax.text( center_x, center_y - 0.1, display_id, ha=&#39;center&#39;, va=&#39;center&#39;, fontsize=8, weight=&#39;bold&#39;, color=&#39;white&#39;, zorder=zorder + 2, bbox=dict(boxstyle="round,pad=0.2", facecolor="black", edgecolor="none", alpha=0.7) ) ``` --- ### ✅ 修改 2:设置合适的坐标轴范围,防止裁剪 替换原来的 `set_xlim/set_ylim`: ```python # 设置坐标轴范围:覆盖所有格子 ax.set_xlim(-0.5, max(col_map.values()) + 1.5) ax.set_ylim(y_min - 0.5, y_max + 1.5) ``` 或者动态计算: ```python n_cols = len(cols) n_rows = len(rows) ax.set_xlim(-0.5, n_cols - 0.5 + 0.5) # 0 ~ 21 → + buffer ax.set_ylim(-0.5, n_rows - 0.5 + 1.0) ``` --- ### ✅ 修改 3:空闲工位不要用白色! 你现在把未分配的座位设成 `&#39;#FFFFFF&#39;` 白色,边框又细(`lw=0.8`),打印或缩放时看起来就像“空白”。 ✅ 改成浅灰色更清晰: ```python color = &#39;#EEEEEE&#39; # 替代之前的 &#39;#FFFFFF&#39; ``` --- ### ✅ 修改 4:确认 `valid_pillars` 是否正确过滤 你定义了: ```python pillars = [&#39;G02&#39;, &#39;G06&#39;, ...] valid_pillars = [p for p in pillars if p in seat_positions] ``` 请确认这些座位确实在 `seat_positions` 中存在。例如 `&#39;G02&#39;` 是否真的被生成? 你可以加个调试输出: ```python missing_pillars = [p for p in pillars if p not in seat_positions] if missing_pillars: print("⚠️ 以下柱子不在布局中:", missing_pillars) ``` --- ### ✅ 修改 5:关闭 tight_layout 并手动调整边距 你用了: ```python plt.subplots_adjust(left=0, right=1, top=1, bottom=0) ``` 这会导致标签被裁剪!特别是 Y 轴标签可能看不见。 ✅ 改为留出边距: ```python plt.subplots_adjust(left=0.05, right=0.98, top=0.95, bottom=0.1) ``` --- ## ✅ 三、最终建议配置总结 | 项目 | 推荐值 | |------|--------| | `use_numeric_x` | `False`(等间距更整齐) | | 未分配座位颜色 | `#EEEEEE` 浅灰 | | 文字居中基准 | 使用 `center_x = grid_x + w/2` | | 边框线 | 保留 `edgecolor=&#39;black&#39;` | | 字体大小 | 座位号 6pt,人名 8pt | | 图例位置 | `loc=&#39;lower center&#39;` 避免遮挡 | --- ## ✅ 四、如何验证是否还有“空白”? 运行后检查: 1. 是否所有 `(x,y)` 网格都被遍历? 2. 是否某些 `seat_id` 拼写错误导致无法匹配?比如 `F01` vs `F1` 3. 加一句测试:打印总共绘制了多少个矩形 ```python print(f"📊 共绘制 {len(seat_positions)} 个工位") print(f"📌 柱子数量: {len(valid_pillars)}") print(f"👥 已分配: {len([s for s in assigned_seats if s in seat_positions])}") ``` --- ###
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值