+
    <%j                     N    R t ^ RIt^ RIHtHtHtHtHt ^ RIH	t	 ^ RI
t
^ RIt]P                  ! R4       RtRRR] R	2R
RR^] R2^] R2^] R2^] R2/RRR^	R^/RRR] R2R
RR^] R2^] R2^] R2^] R2/RRR^R^/RRR] R2R
RR^] R2^] R2^] R 2^] R!2/RRR^R^/.t0 RmtR# tR$ tR% tRR& lt]! R'4       / t]! 4       t] EF  t]! R(R 24       ]! R)]R,           R*24       ]! ]R,          ]R
,          4      t]! R+]! ]4       R,]P7                  4        U Uu/ uF  w  rV ]! V^4      bK  	  upp  24       ]P7                  4        Fg  w  tt]]9  d+   R-]R,          /]]&   ]! ^^4       F  t R.]],          ] &   K  	  ]]],          ^&   ]R,          ]],          R-&   ]PC                  ]4       Ki  	  ]! ^^4       F  t"]R,          ]",          t#]! ]#]R,          ]R,          ]R,          4      t$]! R/]" R0]! ]$4       R,]$P7                  4        U Uu/ uF  w  rV ]! V^4      bK  	  upp  24       ]$P7                  4        Fg  w  tt]]9  d+   R-]R,          /]]&   ]! ^^4       F  t R.]],          ] &   K  	  ]]],          ]"&   ]R,          ]],          R-&   ]PC                  ]4       Ki  	  K  	  EK  	  ]! R1]! ]4       R224       / t%]&! ]PO                  4       4       Fc  t]PQ                  R34      ^ ,          PQ                  R44      ^ ,          PS                  4       t*]*]%9  d   . ]%]*&   ]%]*,          PW                  ]4       Ke  	  ]%P7                  4        EF  w  t*t,]! ],4      ^8  g   K  ],^ ,          t-],R5,           F  t.R6].9  g   K  ].t- M	  ], Fi  t.].]-8X  d   K  ]P_                  ].R4      t0]0'       g   K(  ]! ^^4       F4  t ]1! ]]-,          ] ,          ]0Pe                  ] ^ 4      4      ]]-,          ] &   K6  	  Kk  	  ]-]*8w  g   K  ]P_                  ]-4      ]]*&   ]]*,          R-,          ]]*,          R-&   ]Pg                  ]-4       ]PC                  ]*4       EK  	  ]P7                  4        F=  w  tt4]5! R7 ]! ^^4       4       4      ]4R8&   ]! ^^4       F  t ] ]49  g   K  R.]4] &   K  	  K?  	  . t6]P7                  4        FX  w  tt4]6PW                  R]R-]4R-,          R9]4^,          R:]4^,          R;]4^,          R<]4^,          R=]4^,          R8]4R8,          /4       KZ  	  ]6Po                  R> R?R@7       / t8]6 FP  t9]9R-,          t:]:]89  d   R9^ R:^ R;^ R<^ R=^ R8^ /]8]:&   R F&  t ]8]:,          ] ;;,          ]9] ,          ,          uu&   K(  	  KR  	  R9^ R:^ R;^ R<^ R=^ /t;]6 F(  t9R F  t ];] ;;,          ]9] ,          ,          uu&   K!  	  K*  	  ]5! ];Py                  4       4      t=]! RA4       ]! RB]=RC 24       ]! RD]! ]64       24       R F  w  t>t?]! RE]? RF];]>,          RC 24       K  	  ]8 F1  t@]! RE]@ RF]8]@,          R8,          RC RG]5! RH ]6 4       4       RI24       K3  	  ]! RJ4       ]6 Fc  t9]! RE]9R-,          RK RL]9R,          RM RN]9R8,          RO RP]9R9,          RQ RR]9R:,          RQ RS]9R;,          RQ RT]9R<,          RQ RU]9R=,          RQ 24       Ke  	  RVtA]! RW]A 24       ]P                  ! 4       tC]! RXR?^RYRZ7      tD]! R[R[R\R]7      tE]! RX^
R^7      tF]! RXR?^R[RZ7      tGR_tH]! ]! R`Ra7      ]! R`Ra7      ]! R`Ra7      ]! R`Ra7      Rb7      tIR]! RcRcR\R]7      R]! RdRdR\R]7      R]! ReReR\R]7      /tJ]CP                  tLRf]LnM        ]LP                  Rg4       Rh]LRi&   ]G]LRi,          nO        ]! RjRk7      ]LRi,          nP        ]LP                  Rl4       RmP                  ]8 Uu. uF  q" RF]8V,          R8,          RC 2NK  	  up4      tRRB]=RC Rm]! ]64       Rn]R 2]LRo&   ]! RX^
RpRq7      ]LRo,          nO        ]! RjRk7      ]LRo,          nP        . ROtS]T! ]S^4       F=  w  tUtV]LP                  ^]U]VRr7      tW]D]WnO        ]E]WnX        ]! RjRk7      ]WnP        ]I]WnY        K?  	  ]T! ]64       F  w  tZt9^]Z,           t[]9R-,          t]JPe                  ]]! 4       4      tX]Z^,           ]9R,          ]]9R9,          ]9R:,          ]9R;,          ]9R<,          ]9R=,          ]9R8,          .	t<]T! ]<^4       Fd  w  tUt.]LP                  ][]U].Rr7      tW]F]WnO        ]I]WnY        ]X]WnX        ]U^8  d   ]H]Wn\        ]! RsRk7      ]WnP        KM  ]U^8X  g   KV  ]! RjRk7      ]WnP        Kf  	  K  	  . ROt]]T! ]]^4       F$  w  tZt^]^]LP                  ]	! ]Z4      ,          n`        K&  	  ]CP                  Rt4      tb]bP                  Ru4       Rv]bRi&   ]G]bRi,          nO        ]! RjRk7      ]bRi,          nP        . ROtc]T! ]c^4       F=  w  tUtV]bP                  ^]U]VRr7      tW]D]WnO        ]E]WnX        ]! RjRk7      ]WnP        ]I]WnY        K?  	  ^t[R F  t@]8]@,          td]='       d   ]dR8,          ]=,          ^d,          M^ te]JPe                  ]@]! 4       4      tX]@]dR9,          ]dR:,          ]dR;,          ]dR<,          ]dR=,          ]dR8,          ]eRw Rx2.t<]T! ]<^4       FY  w  tUt.]bP                  ][]U].Rr7      tW]F]WnO        ]I]WnY        ]X]WnX        ^]Uu;8:  d
   ^8:  g   K@  M KD  ]H]Wn\        ]! RsRk7      ]WnP        K[  	  ][^,          t[K  	  ]! RyRyR\R]7      tfR"];R9,          ];R:,          ];R;,          ];R<,          ];R=,          ]=Rz.t<]T! ]<^4       Fb  w  tUt.]bP                  ][]U].Rr7      tW]! RXR?^
R{7      ]WnO        ]I]WnY        ]f]WnX        ^]Uu;8:  d
   ^8:  g   KI  M KM  ]H]Wn\        ]! RsRk7      ]WnP        Kd  	  ]T! . RO^4       F$  w  tZt^]^]bP                  ]	! ]Z4      ,          n`        K&  	  ]CP                  R|4      tg]gP                  R}4       R~]gRi&   ]G]gRi,          nO        ]! RjRk7      ]gRi,          nP        ]T! ]S^4       F=  w  tUtV]gP                  ^]U]VRr7      tW]D]WnO        ]E]WnX        ]! RjRk7      ]WnP        ]I]WnY        K?  	  ]T! ]6R,          4       F  w  tZt9^]Z,           t[]Z^,           ]9R,          ]9R-,          ]9R9,          ]9R:,          ]9R;,          ]9R<,          ]9R=,          ]9R8,          .	t<]T! ]<^4       FF  w  tUt.]gP                  ][]U].Rr7      tW]F]WnO        ]I]WnY        ]U^8  g   K1  ]H]Wn\        ]! RsRk7      ]WnP        KH  	  K  	  ]T! ]]^4       F$  w  tZt^]^]gP                  ]	! ]Z4      ,          n`        K&  	  ]CP                  ]A4       ]! R]A 24       Rti]! R]i 24       ]
P                  ! ]6 Uu. uFM  pRVR,          R-VR-,          R9VR9,          R:VR:,          R;VR;,          R<VR<,          R=VR=,          R8VR8,          /NKO  	  upRR7      tk]
P                  ! ]8P7                  4        UUu/ uF>  w  r$VR9VR9,          R:VR:,          R;VR;,          R<VR<,          R=VR=,          R8VR8,          /bK@  	  uppRR7      tl]
P                  ! ];RR7      tmRP                  . RN]! ]64       NRN]=R NRN]! ]64       NRN]8R,          R8,          R NRN]8R,          R8,          ^,          R NRN]5! R ]6 4       4       NRN]8R,          R8,          R NRN]8R,          R8,          ^,          R NRN]5! R ]6 4       4       NRN]8R,          R8,          R NRN]8R,          R8,          ^,          R NRN]5! R ]6 4       4       NRN]k NRN]l NRN]m NRN]= NRN4      tn]o! ]iRRR7      ;_uu_ 4       tp]pP                  ]n4       RRR4       ]! R]i 24       ]! R4       R# u upp i u upp i u upi u upi u uppi   + '       g   i     LA; i)u   
宝锐生物2026年1-5月工作汇报分析 - 数据提取 v2
修复: 1月多表提取 + 垂直格式 + 过滤非人员条目
N)Font	AlignmentPatternFillBorderSide)get_column_letterignoreu;   /Users/liuxinyuan/work/secure/2026/6月工作汇报/2026年name   大客户部jan_fileui   /诊断原料大客户_生命科学销售部/1月份/2026月度数据-诊断原料大客户销售部.xlsx	jan_sheetu   1）年度指标达成情况monthsux   /诊断原料大客户_生命科学销售部/2月份/1、大客户部/1.大客户部销售数据统计表（2月）.xlsxux   /诊断原料大客户_生命科学销售部/3月份/1、大客户部/1.大客户部销售数据统计表（3月）.xlsxux   /诊断原料大客户_生命科学销售部/4月份/1、大客户部/1.大客户部销售数据统计表（4月）.xlsxug   /诊断原料大客户_生命科学销售部/5月份/1.大客户部销售数据统计表（5月）.xlsxdetail_sheet_patternu   4-月度明细表amount_col_2_5name_col_2_5   生命科学销售部u`   /诊断原料大客户_生命科学销售部/1月份/2026月度数据-生命科学销售部.xlsxu~   /诊断原料大客户_生命科学销售部/2月份/5、科研销售部/1.科研销售部销售数据统计表（2月）.xlsxu   /诊断原料大客户_生命科学销售部/3月份/5、生命科学销售部/1.生命科学销售部销售数据统计表（3月）.xlsxu   /诊断原料大客户_生命科学销售部/4月份/5、生命科学销售部/1.生命科学销售部销售数据统计表（4月）.xlsxup   /诊断原料大客户_生命科学销售部/5月份/1.生命科学销售部销售数据统计表（5月）.xlsxu   6-月度明细表   销售拓展部uV   /诊断原料销售拓展部/1月份/2026月度数据-诊断原料销售拓展部.xlsxun   /诊断原料销售拓展部/2月份/2、销售拓展部/1.销售拓展部销售数据统计表（2月）.xlsxuZ   /诊断原料销售拓展部/3月份/1.销售拓展部销售数据统计表（3月）.xlsxuZ   /诊断原料销售拓展部/4月份/1.销售拓展部销售数据统计表（4月）.xlsxuZ   /诊断原料销售拓展部/5月份/1.销售拓展部销售数据统计表（5月）.xlsx   合计c                 F   V f   R# \        V \        \        34      '       d    \        V 4      # \        V \        4      '       dM   V P	                  4       P                  RR4      P                  RR4      P                  RR4      p  \        V 4      # R#     R# ; i    R# ; i)N        ,    ¥u   元)
isinstanceintfloatstrstripreplace)vs   &C   /Users/liuxinyuan/Desktop/Hermes输出-工作类/build_report_v2.py
safe_floatr!   I   s    y!c5\""	8O !SGGIc2&..tR8@@K	8O 	
	s   
B 
B BB c                X   V '       g   R# \        V 4      P                  4       p V \        9   d   R# R F  pW9   g   K   R# 	   \        V 4       R#     M; iT P	                  4       '       d=   \
        ;QJ d    R T  4       F  '       g   K   RM	  RM! R T  4       4      '       g   R# R# )zOCheck if a name looks like a real person name (Chinese 2-4 chars, not a label).Fc              3   N   "   T F  pR Tu;8*  ;'       d    R8*  Mu x  K  	  R# 5i)u   一u   鿿N ).0cs   & r    	<genexpr>!is_person_name.<locals>.<genexpr>k   s      !JT(a";";8";";Ts   %%T)	u   目标u   完成u   客户u   销售   部门u   数据u   宝锐   月份u   说明)r   r   
NON_PERSONr   isasciiany)r	   kws   & r    is_person_namer/   Y   s    t9??Dzh: id||~~cc!JT!Jccc!JT!JJJs   A Ac                   \         P                  ! V RR7      pW!,          p/ p\        VP                  ^VP                  RR7      4      p\        V4       EF?  w  rg\        V4      ^8  d   K  V^,          P                  e+   \        V^,          P                  4      P                  4       MRpVR8w  d   Kb  Rp	\        V^,
          \        V^,
          R
4      R
4       Fj  p
WZ,          pV^ ,          P                  e+   \        V^ ,          P                  4      P                  4       MRp\        V4      '       g   K_  VR9  g   Kh  Tp	 M	  V	'       g   EK  \        V^,          P                  4      pW9  d   WV	&   EK+  \        WI,          V4      WI&   EKB  	  . pV F  p\        V4      ^8  d   K  V^,          P                  e+   \        V^,          P                  4      P                  4       MRpV^,          P                  e+   \        V^,          P                  4      P                  4       MRpVR8X  g   K  VR	8X  g   K  VP                  V^ ,          P                  4       K  	  V'       d   V^ ,          pV F  pV^ ,          P                  V8:  d   K  \        V4      ^8  d   K/  V^,          pV^,          pVP                  f   KQ  \        VP                  4      P                  4       p	V	R8X  d    MUV	R9   d    ML\        V	4      '       g   K  \        VP                  4      pW9  d   WV	&   K  \        WI,          V4      WI&   K  	  VP!                  4        V# )u"  
Extract per-person sales data from 1月 files.
Strategy:
1. Vertical format: name in col A, next row has "实际完成" in col C, amount in col D (1月)
2. Flattened table: "销售人员" header in col B, "1月" in col C; data rows: name in col B, amount in col C
Merge results from both.
T	data_onlyFmin_rowmax_rowvalues_onlyNr      实际完成   销售人员   1月)r7      月度目标	   达成率)   KA客户	   NGS客户   KA客户月度目标   NGS客户月度目标)openpyxlload_workbooklist	iter_rowsr5   	enumeratelenvaluer   r   rangemaxr/   r!   appendrowclose)filepath
sheet_namewbwsresult	rows_datairK   c_valr	   jprev_rowa_valamountheader_rowsb_val
header_row	name_cellamount_cells   &&                 r    extract_jan_datar^   o   s    
		D	9B	BF R\\!RZZU\STII&s8a<-0V\\-ECFLL!'')2N" q1uc!a%nb1A |H6>qk6G6G6SC))*002Y[Ee$$6c)c 2  CFLL)!4Lv|V4FL1 '8 Ks8a<-0V\\-ECFLL!'')2-0V\\-ECFLL!'')2N"us1vzz*   ^
C1vzzZ'3x!|AIa&K&y'--/D ~%aa!$'' 1 12F!%t"6<87 : HHJM    c                   \         P                  ! V RR7      pRpVP                   F  pW9   g   K  Tp M	  V'       g   VP                  4        / # WE,          p/ pRp	VP	                  ^\        ^
VP                  4      RR7       FR  p
V
 F?  pVP                  '       g   K  R\        VP                  4      9   g   K3  VP                  p	 M	  V	'       g   KR   M	  V	'       g   V'       d   TM^	p	\        RV	 24       VP	                  ^VP                  RR7       F  p
\        V
4      \        W)4      8  d   K  W^,
          ,          pW^,
          ,          pVe   VP                  f   KP  \        VP                  4      P                  4       p\        V4      '       g   K  \        V'       d   VP                  M^ 4      pW9  d   WV&   K  \        W,          V4      W&   K  	  VP                  4        V# )u   Extract per-person sales data from 2-5月 files (月度明细表).
Dynamically finds '订单金额' column. name_col is always used as-is (col B = 2).Tr1   NFr3   u   订单金额u9       WARNING: 未找到'订单金额'列，使用默认列)rA   rB   
sheetnamesrL   rD   minr5   rG   r   columnprintrF   rI   r   r/   r!   )rM   sheet_patternname_col
amount_colrO   target_sheetsrP   rQ   amount_col_dynamicrK   r&   r\   r]   r	   rX   s   &&&&            r    extract_month_detailrk      s    
		D	9BL]]L 
 

			BF ||As2rzz/BPU|VAwww>S\9%&XX"   W +5Z1IJ\I]^_||Arzzu|Ms8c(771%	q01	 79??#))+d##K--!D!4Lv|V4FL) N, HHJMr_   u   正在提取数据...
u	     提取 z...u
       1月: u    人 - deptr   z    u   月: u	   
总计: u    人u   （(:   NNu   离职c              #   N   "   T F  p\         P                  VR 4      x  K  	  R# 5i)r   N)dataget)r%   ms   & r    r'   r'   U  s     >+QC((+s   #%totalm1m2m3m4m5c                     V R ,          # )rt   r$   )xs   &r    <lambda>r|   d  s    !G*r_   T)keyreverseu   
=== 最终数据汇总 ===u   总销售额: ¥z,.2fu   总人数: z  u   : ¥z (c              #   L   "   T F  qR ,          \         8X  g   K  ^x  K  	  R# 5i)rm   N)	dept_namer%   ps   & r    r'   r'   |  s!     ItU[PQag_hjs_s!!U[s   $
$u   人)u   
全员名单及总额:12sz | 10su    | ¥z>12,.2fu    | 1月:z>10,.2fu    2月:u    3月:u    4月:u    5月:uV   /Users/liuxinyuan/Desktop/Hermes输出-工作类/工作汇报分析_2026年1-5月.xlsxu   
生成Excel: u   微软雅黑FFFFFF)r	   boldsizecolor2F5496solid)start_color	end_color	fill_type)r	   r   z#,##0.00thin)style)leftrighttopbottomD6E4F0E2EFDAFCE4D6u   全员销售明细zA1:J1u0   宝锐生物 2026年1-5月 全员销售明细表A1center)
horizontalzA2:J2z  |  u   位人员  |  A2666666)r	   r   r   )rK   rc   rG   r   u   部门月度汇总zA1:H1u3   宝锐生物 2026年1-5月 部门月度销售汇总z.1f%FFF2CCz100%)r	   r   r   Top10zA1:I1u,   宝锐生物 2026年1-5月 Top10销售排名:N
   Nu   Excel已保存: uY   /Users/liuxinyuan/Desktop/Hermes输出-工作类/工作汇报仪表盘_2026年1-5月.htmlu   
生成HTML仪表盘: F)ensure_asciir   u  <!DOCTYPE html>
<html lang="zh-CN">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>宝锐生物 2026年1-5月 工作汇报仪表盘</title>
<style>
* { margin: 0; padding: 0; box-sizing: border-box; }
body { font-family: 'Microsoft YaHei', 'PingFang SC', sans-serif; background: #f0f2f5; color: #333; }
.header { background: linear-gradient(135deg, #1a3a6b, #2F5496); color: white; padding: 20px 30px; }
.header h1 { font-size: 24px; margin-bottom: 5px; }
.header .sub { font-size: 13px; opacity: 0.85; }
.container { max-width: 1400px; margin: 0 auto; padding: 20px; }

/* KPI Cards */
.kpi-row { display: flex; gap: 16px; margin-bottom: 20px; flex-wrap: wrap; }
.kpi-card { flex: 1; min-width: 180px; background: white; border-radius: 10px; padding: 18px; box-shadow: 0 2px 8px rgba(0,0,0,0.08); text-align: center; }
.kpi-card .label { font-size: 12px; color: #888; margin-bottom: 6px; }
.kpi-card .value { font-size: 26px; font-weight: bold; color: #1a3a6b; }
.kpi-card .sub-value { font-size: 12px; color: #999; margin-top: 4px; }
.kpi-card.dept1 { border-top: 3px solid #2F5496; }
.kpi-card.dept2 { border-top: 3px solid #548235; }
.kpi-card.dept3 { border-top: 3px solid #ED7D31; }
.kpi-card.total { border-top: 3px solid #C00000; }

/* Sections */
.section { background: white; border-radius: 10px; padding: 20px; margin-bottom: 20px; box-shadow: 0 2px 8px rgba(0,0,0,0.08); }
.section h2 { font-size: 18px; color: #1a3a6b; margin-bottom: 15px; padding-bottom: 8px; border-bottom: 2px solid #e8e8e8; }

/* Table */
.table-wrap { overflow-x: auto; }
table { width: 100%; border-collapse: collapse; font-size: 13px; }
th { background: #2F5496; color: white; padding: 10px 8px; text-align: center; font-weight: 600; position: sticky; top: 0; z-index: 1; }
td { padding: 8px; border-bottom: 1px solid #eee; text-align: right; }
td:nth-child(1), td:nth-child(2), td:nth-child(3) { text-align: center; }
tr:hover { background: #f5f7fa !important; }
tr.dept-dakehu { background: #f0f4fa; }
tr.dept-shengming { background: #f4faf0; }
tr.dept-tuozhan { background: #faf4f0; }
.num { font-family: 'Consolas', 'Monaco', monospace; }

/* Rank badge */
.rank-badge { display: inline-block; width: 24px; height: 24px; line-height: 24px; border-radius: 50%; text-align: center; font-size: 12px; font-weight: bold; color: white; }
.rank-1 { background: #FFD700; color: #333; }
.rank-2 { background: #C0C0C0; color: #333; }
.rank-3 { background: #CD7F32; }
.rank-other { background: #ccc; color: #666; }

/* Chart area */
.chart-row { display: flex; gap: 20px; flex-wrap: wrap; }
.chart-box { flex: 1; min-width: 400px; }
.chart-box h3 { font-size: 14px; color: #555; margin-bottom: 10px; text-align: center; }

/* Bar chart CSS */
.bar-chart { display: flex; flex-direction: column; gap: 6px; }
.bar-row { display: flex; align-items: center; gap: 8px; }
.bar-label { width: 55px; text-align: right; font-size: 12px; flex-shrink: 0; }
.bar-track { flex: 1; height: 22px; background: #eee; border-radius: 4px; overflow: hidden; position: relative; }
.bar-fill { height: 100%; border-radius: 4px; transition: width 0.5s; display: flex; align-items: center; padding-left: 6px; font-size: 11px; color: white; font-weight: bold; min-width: fit-content; }
.bar-fill.dakehu { background: linear-gradient(90deg, #2F5496, #4a7bc7); }
.bar-fill.shengming { background: linear-gradient(90deg, #548235, #6ea843); }
.bar-fill.tuozhan { background: linear-gradient(90deg, #ED7D31, #f09a5c); }

/* Annotation */
#annotation-bar { display: flex; gap: 10px; margin-bottom: 15px; align-items: center; flex-wrap: wrap; }
#annotation-bar button { padding: 8px 16px; border: none; border-radius: 6px; cursor: pointer; font-size: 13px; font-weight: 600; transition: all 0.2s; }
#btn-annotate { background: #2F5496; color: white; }
#btn-annotate:hover { background: #1a3a6b; }
#btn-annotate.active { background: #C00000; }
#btn-export { background: #548235; color: white; }
#btn-export:hover { background: #3d5e26; }
#btn-clear { background: #999; color: white; }
#btn-clear:hover { background: #777; }
.annotation-col { display: none; }
.annotation-col.show { display: table-cell; }
.annotation-col input { width: 100%; padding: 4px 8px; border: 1px solid #ddd; border-radius: 4px; font-size: 12px; min-width: 120px; }
.annotation-col input:focus { border-color: #2F5496; outline: none; }

/* Top10 highlight */
.top10-highlight { font-weight: bold; }

/* Zero-value dimming for readability */
.zero-val { color: #ccc; }

@media (max-width: 768px) {
  .kpi-card { min-width: 140px; }
  .chart-box { min-width: 100%; }
}
</style>
</head>
<body>

<div class="header">
  <h1>宝锐生物 2026年1-5月 工作汇报分析</h1>
  <div class="sub">全量数据 · 全员可见 · 可批注 · u   位销售人员</div>
</div>

<div class="container">

<!-- KPI Cards -->
<div class="kpi-row">
  <div class="kpi-card total">
    <div class="label">总销售额（1-5月）</div>
    <div class="value">¥z,.0fz"</div>
    <div class="sub-value">u   位销售人员</div>
  </div>
  <div class="kpi-card dept1">
    <div class="label">大客户部</div>
    <div class="value">¥u+   </div>
    <div class="sub-value">月均 ¥u    · c              #   D   "   T F  qR ,          R8X  g   K  ^x  K  	  R# 5i)rm   r
   Nr$   r   s   & r    r'   r'     s/       ZJek`aqwox  {I  pIZ[Z[ek    
 u~   人</div>
  </div>
  <div class="kpi-card dept2">
    <div class="label">生命科学销售部</div>
    <div class="value">¥c              #   D   "   T F  qR ,          R8X  g   K  ^x  K  	  R# 5i)rm   r   Nr$   r   s   & r    r'   r'     s7       c\ntij  {A  yB  D[  y[cdcdntr   ux   人</div>
  </div>
  <div class="kpi-card dept3">
    <div class="label">销售拓展部</div>
    <div class="value">¥c              #   D   "   T F  qR ,          R8X  g   K  ^x  K  	  R# 5i)rm   r   Nr$   r   s   & r    r'   r'     s/       ]Phncdtzr{  ~O  sO]^]^hnr   u  人</div>
  </div>
</div>

<!-- Monthly Trend Chart -->
<div class="section">
  <h2>月度销售趋势</h2>
  <div class="chart-row">
    <div class="chart-box">
      <h3>总销售额月度趋势</h3>
      <div class="bar-chart" id="monthly-bar"></div>
    </div>
    <div class="chart-box">
      <h3>各部门月度对比</h3>
      <div class="bar-chart" id="dept-monthly-bar"></div>
    </div>
  </div>
</div>

<!-- Top 10 -->
<div class="section">
  <h2>Top 10 销售排名</h2>
  <div class="table-wrap">
    <table>
      <thead>
        <tr><th>排名</th><th>姓名</th><th>部门</th><th>1月</th><th>2月</th><th>3月</th><th>4月</th><th>5月</th><th>合计</th></tr>
      </thead>
      <tbody id="top10-body"></tbody>
    </table>
  </div>
</div>

<!-- Department Comparison -->
<div class="section">
  <h2>部门对比</h2>
  <div class="table-wrap">
    <table>
      <thead>
        <tr><th>部门</th><th>1月</th><th>2月</th><th>3月</th><th>4月</th><th>5月</th><th>合计</th><th>占比</th></tr>
      </thead>
      <tbody id="dept-comp-body"></tbody>
    </table>
  </div>
</div>

<!-- Full Ranking -->
<div class="section">
  <h2>全员排名（含零销售额人员）</h2>
  <div id="annotation-bar">
    <button id="btn-annotate" onclick="toggleAnnotations()">✏️ 添加批注</button>
    <button id="btn-export" onclick="exportAnnotations()">📥 导出批注(CSV)</button>
    <button id="btn-clear" onclick="clearAnnotations()">🗑️ 清除批注</button>
    <span id="annotation-status" style="font-size:12px;color:#888;margin-left:8px;"></span>
  </div>
  <div class="table-wrap">
    <table id="full-table">
      <thead>
        <tr>
          <th>排名</th><th>姓名</th><th>部门</th>
          <th>1月</th><th>2月</th><th>3月</th><th>4月</th><th>5月</th>
          <th>合计</th>
          <th class="annotation-col" id="annotation-header">📝 批注</th>
        </tr>
      </thead>
      <tbody id="full-body"></tbody>
    </table>
  </div>
</div>

</div>

<script>
const PEOPLE = z;
const DEPT_TOTALS = z;
const MONTHLY = z;
const GRAND_TOTAL = uN   ;

const DEPT_NAMES = ['大客户部', '生命科学销售部', '销售拓展部'];
const DEPT_COLORS = {'大客户部': '#2F5496', '生命科学销售部': '#548235', '销售拓展部': '#ED7D31'};
const DEPT_CLASSES = {'大客户部': 'dept-dakehu', '生命科学销售部': 'dept-shengming', '销售拓展部': 'dept-tuozhan'};
const DEPT_BAR_CLASS = {'大客户部': 'dakehu', '生命科学销售部': 'shengming', '销售拓展部': 'tuozhan'};

function fmt(v) {
  if (Math.abs(v) < 0.005) return '<span class="zero-val">¥0</span>';
  return '¥' + v.toLocaleString('zh-CN', {minimumFractionDigits: 0, maximumFractionDigits: 0});
}

function fmtDetail(v) {
  if (Math.abs(v) < 0.005) return '<span class="zero-val">¥0.00</span>';
  return '¥' + v.toLocaleString('zh-CN', {minimumFractionDigits: 2, maximumFractionDigits: 2});
}

function rankBadge(r) {
  if (r === 1) return '<span class="rank-badge rank-1">1</span>';
  if (r === 2) return '<span class="rank-badge rank-2">2</span>';
  if (r === 3) return '<span class="rank-badge rank-3">3</span>';
  return '<span class="rank-badge rank-other">' + r + '</span>';
}

function buildMonthlyBar() {
  const container = document.getElementById('monthly-bar');
  const months = [
    {label: '1月', val: MONTHLY.m1},
    {label: '2月', val: MONTHLY.m2},
    {label: '3月', val: MONTHLY.m3},
    {label: '4月', val: MONTHLY.m4},
    {label: '5月', val: MONTHLY.m5},
  ];
  const maxVal = Math.max(...months.map(m => m.val), 1);
  let html = '';
  months.forEach(m => {
    const pct = (m.val / maxVal * 100);
    html += '<div class="bar-row"><div class="bar-label">' + m.label + '</div><div class="bar-track"><div class="bar-fill dakehu" style="width:' + pct + '%">' + fmt(m.val) + '</div></div></div>';
  });
  container.innerHTML = html;
}

function buildDeptMonthlyBar() {
  const container = document.getElementById('dept-monthly-bar');
  const allVals = [];
  DEPT_NAMES.forEach(d => { for (let m = 1; m <= 5; m++) allVals.push(DEPT_TOTALS[d]['m' + m]); });
  const maxVal = Math.max(...allVals, 1);
  let html = '';
  DEPT_NAMES.forEach(d => {
    const dt = DEPT_TOTALS[d];
    html += '<div style="font-weight:bold;margin:8px 0 2px;color:' + DEPT_COLORS[d] + '">' + d + ' (合计: ' + fmt(dt.total) + ')</div>';
    for (let m = 1; m <= 5; m++) {
      const val = dt['m' + m];
      const pct = val / maxVal * 100;
      html += '<div class="bar-row"><div class="bar-label">' + m + '月</div><div class="bar-track"><div class="bar-fill ' + DEPT_BAR_CLASS[d] + '" style="width:' + pct + '%">' + fmt(val) + '</div></div></div>';
    }
  });
  container.innerHTML = html;
}

function buildTop10() {
  const tbody = document.getElementById('top10-body');
  let html = '';
  for (let i = 0; i < Math.min(10, PEOPLE.length); i++) {
    const p = PEOPLE[i];
    html += '<tr class="' + DEPT_CLASSES[p.dept] + ' top10-highlight"><td>' + rankBadge(i+1) + '</td><td>' + p.name + '</td><td>' + p.dept + '</td><td class="num">' + fmtDetail(p.m1) + '</td><td class="num">' + fmtDetail(p.m2) + '</td><td class="num">' + fmtDetail(p.m3) + '</td><td class="num">' + fmtDetail(p.m4) + '</td><td class="num">' + fmtDetail(p.m5) + '</td><td class="num" style="font-weight:bold">' + fmtDetail(p.total) + '</td></tr>';
  }
  tbody.innerHTML = html;
}

function buildDeptComp() {
  const tbody = document.getElementById('dept-comp-body');
  let html = '';
  DEPT_NAMES.forEach(d => {
    const dt = DEPT_TOTALS[d];
    const pct = GRAND_TOTAL > 0 ? (dt.total / GRAND_TOTAL * 100).toFixed(1) : 0;
    html += '<tr class="' + DEPT_CLASSES[d] + '"><td style="font-weight:bold">' + d + '</td><td class="num">' + fmtDetail(dt.m1) + '</td><td class="num">' + fmtDetail(dt.m2) + '</td><td class="num">' + fmtDetail(dt.m3) + '</td><td class="num">' + fmtDetail(dt.m4) + '</td><td class="num">' + fmtDetail(dt.m5) + '</td><td class="num" style="font-weight:bold">' + fmtDetail(dt.total) + '</td><td>' + pct + '%</td></tr>';
  });
  html += '<tr style="background:#FFF2CC;font-weight:bold"><td>合计</td><td class="num">' + fmtDetail(MONTHLY.m1) + '</td><td class="num">' + fmtDetail(MONTHLY.m2) + '</td><td class="num">' + fmtDetail(MONTHLY.m3) + '</td><td class="num">' + fmtDetail(MONTHLY.m4) + '</td><td class="num">' + fmtDetail(MONTHLY.m5) + '</td><td class="num">' + fmtDetail(GRAND_TOTAL) + '</td><td>100%</td></tr>';
  tbody.innerHTML = html;
}

function buildFullTable() {
  const tbody = document.getElementById('full-body');
  const saved = loadAnnotations();
  let html = '';
  for (let i = 0; i < PEOPLE.length; i++) {
    const p = PEOPLE[i];
    const annKey = p.dept + '|' + p.name;
    const ann = saved[annKey] || '';
    html += '<tr class="' + DEPT_CLASSES[p.dept] + '" data-key="' + annKey + '"><td>' + rankBadge(i+1) + '</td><td>' + p.name + '</td><td>' + p.dept + '</td><td class="num">' + fmtDetail(p.m1) + '</td><td class="num">' + fmtDetail(p.m2) + '</td><td class="num">' + fmtDetail(p.m3) + '</td><td class="num">' + fmtDetail(p.m4) + '</td><td class="num">' + fmtDetail(p.m5) + '</td><td class="num" style="font-weight:bold">' + fmtDetail(p.total) + '</td><td class="annotation-col"><input type="text" value="' + ann.replace(/"/g, '&quot;').replace(/</g, '&lt;').replace(/>/g, '&gt;') + '" data-key="' + annKey + '" placeholder="添加批注..." onchange="saveAnnotation(this)" /></td></tr>';
  }
  tbody.innerHTML = html;
  updateAnnotationStatus();
}

// ===== Annotation System =====
const STORAGE_KEY = 'baorui_annotations_2026_1_5';

function loadAnnotations() {
  try { return JSON.parse(localStorage.getItem(STORAGE_KEY) || '{}'); }
  catch(e) { return {}; }
}

function saveAnnotations(data) {
  localStorage.setItem(STORAGE_KEY, JSON.stringify(data));
  updateAnnotationStatus();
}

function saveAnnotation(input) {
  const key = input.dataset.key;
  const val = input.value.trim();
  const saved = loadAnnotations();
  if (val) { saved[key] = val; }
  else { delete saved[key]; }
  saveAnnotations(saved);
}

function updateAnnotationStatus() {
  const saved = loadAnnotations();
  const count = Object.keys(saved).length;
  const status = document.getElementById('annotation-status');
  if (count > 0) {
    status.textContent = '已有 ' + count + ' 条批注';
    status.style.color = '#548235';
  } else {
    status.textContent = '暂无批注';
    status.style.color = '#888';
  }
}

let annotationVisible = false;

function toggleAnnotations() {
  annotationVisible = !annotationVisible;
  const cols = document.querySelectorAll('.annotation-col');
  const btn = document.getElementById('btn-annotate');
  const header = document.getElementById('annotation-header');
  if (annotationVisible) {
    cols.forEach(c => c.classList.add('show'));
    btn.textContent = '✏️ 隐藏批注';
    btn.classList.add('active');
    if (header) header.classList.add('show');
  } else {
    cols.forEach(c => c.classList.remove('show'));
    btn.textContent = '✏️ 添加批注';
    btn.classList.remove('active');
    if (header) header.classList.remove('show');
  }
}

function exportAnnotations() {
  const saved = loadAnnotations();
  const keys = Object.keys(saved);
  if (keys.length === 0) {
    alert('没有批注可导出。请先添加批注。');
    return;
  }
  let csv = '\uFEFF部门,姓名,批注\n';
  keys.forEach(k => {
    const idx = k.indexOf('|');
    const dept = k.substring(0, idx);
    const name = k.substring(idx + 1);
    const ann = saved[k].replace(/"/g, '""');
    csv += '"' + dept + '","' + name + '","' + ann + '"\n';
  });
  const blob = new Blob([csv], {type: 'text/csv;charset=utf-8'});
  const url = URL.createObjectURL(blob);
  const a = document.createElement('a');
  a.href = url;
  a.download = '宝锐生物_批注导出_2026年1-5月.csv';
  a.click();
  URL.revokeObjectURL(url);
}

function clearAnnotations() {
  if (confirm('确认清除所有批注？此操作不可恢复。')) {
    localStorage.removeItem(STORAGE_KEY);
    buildFullTable();
    if (annotationVisible) {
      setTimeout(() => { document.querySelectorAll('.annotation-col').forEach(c => c.classList.add('show')); }, 50);
    }
  }
}

// Initialize
buildMonthlyBar();
buildDeptMonthlyBar();
buildTop10();
buildDeptComp();
buildFullTable();
</script>

</body>
</html>wzutf-8)encodingu   HTML已保存: u   
✅ 全部完成!>   r9      2月   3月r      姓名   小计   总计r*      类别r)   r=   r>   r<   r7   r;   r8      KA客户数量   NGS客户数量   KA客户实际完成r?      NGS客户实际完成r@   )Nz<============================================================)ru   rv   rw   rx   ry   rt   )ru   rv   rw   rx   ry   ))ru   r9   )rv   r   )rw   r   )rx      4月)ry      5月)	u   排名r   r)   r9   r   r   r   r   r   )	            r   r   r   r   r   )r)   r9   r   r   r   r   r   u   占比)r
   r   r   )r   r   r   r   r   r   r      )r__doc__rA   openpyxl.stylesr   r   r   r   r   openpyxl.utilsr   jsonwarningsfilterwarningsBASEDEPT_CONFIGSr+   r!   r/   r^   rk   rd   all_dataset	all_namesrm   jan_datarF   itemsroundr	   rX   rH   rs   addmonthrM   detailname_variantsrC   keyssplitr   baserJ   variants	canonicalr   popdata_vrI   rr   discardrq   sumpeoplesortdept_totalsr   dmonthly_totalsvaluesgrand_totalm_keym_labelr   	EXCEL_OUTWorkbookrO   header_fontheader_fill	data_font
title_fontnum_fmtthin_border
dept_fillsactivews1titlemerge_cellsfont	alignmentjoindept_strheadersrE   colhcellfillborderrS   rK   number_format
col_widthsr   column_dimensionswidthcreate_sheetws2dept_headersdtpct
total_fillws3saveHTML_OUTdumpspeople_jsondept_totals_jsonmonthly_jsonhtmlopenfwrite)kr   r   r   r   s   00000r    <module>r	     s  
  F F ,      ! E 	tf  F  G3$  P  Q$  P  Q$  P  Q$~	
 	 3! 	'tf|}3$  V  W$  b  c$  b  c$  H  I	
 	 3" 	!tfrs3$  F  G$qr$qr$qr	
 	 3";+\
 ,Wt8~  E	D	Bvh-	Id6l^3
'(  Z 0${2CDH	Js8}oWHX.YHXq%!*}HX.Y,[
\] (fx$d6l3HTN1a[$'q! !"q!%fvd ) q!>%(%d12 $'7"8
 	UG5VWPVP\P\P^6_P^q%!*}P^6_4abc"LLNLD&8#"($v,!7q!A(+HTN1% %$*HTN5!%)&\HTN6"MM$ + % F 
3y>"$' ( !D::eQ%%c*1-335D=  d$t$	 " $))+ND(
8}qQK	"Aq 	 
 AI~\\!T*Fvq!A-0)1DQ1GTUWXIY-ZHY'* %  %\\)4HTN%-d^F%;HTN6"i(MM$+ ,0 .."JD$>%1+>>DM1a[D=DG  # 
.."JD$
MMVd1gtT!WdDGT47D$q'g	  # $d 3 	A	&	A4D!T1dAwPQRA4AqQqT! 5	 
 4D!T1dA>	A+qQqT! , 
 .'')* $ & T*+ , CK=! "fNE7	BwitN51$7
89 gI	Bykk)4W=dC2cItU[ItFtEuuy
z{    !	A	BqyoS63uQwZ4HQRSWQXY`Paaghijnhopwgxx~  @A  BF  @G  HO  P  PV  WX  Y]  W^  _f  Vg  gm  no  pt  nu  v}  m~    @ 
 e	 	{# $T(Kh(gVn2.	~DrJ

	F	4f#564f#5
 KHT[\[X]de{x8W^_
 	ii 	  >D	D	84D	   <<;W;a3d;q>'#:4"@A;WX{40c&k].QYPZ[D	>(CD	84D	 
Z#FC88#Q8/DDIDI(3DNDK $ fDAq
a%CV9D>>$.D!eQvYagqw$4!D'STU\S]^FFA&QxxC1x5	!	!8!(D&':DNAX&(;DN '   1
j!$DAq89C+A./5 % 	oo*+  AD	D	84D	 Ua(FC88#Q8/DDIDI(3DNDK ) MI	Y	B-8"W+
#c
)aC>>)[]3DD2d8RXr$xD2g;[^_bZccdXefFFA&QxxC1x5	!	=q==!(D&':DN ' 1HC N XWU

N4(.*>t@T

t 4k6
K"FC88Cq81D.t"=DIDKDIC}1}}$"g6 # 5q9DAq89C+A./5 : 	oog  :D	D	84D	 #FC88#Q8/DDIDI(3DNDK $ fSk"DAq
a%C!eQvY&	1T7AdGQtWagqQUwXYZaXbcFFA&QxxC1x5	!!8!(D&':DN ' # j!$DAq89C+A./5 % 	  $ % g z* +jj 	 a AfIvqy!D'44$$qwagQwZ 	 "'	( ::""$$ bhbhbhbhbQUhX_acdkalmm$  
 zz.u=D D ^A D| BEV}D|	N}DN &d+ODN,ODP  K=QDP)QDX &n5g>tDYDXE%YDZ &1%@%I!%KD$Q[DZ RV[DZ WZ  ZJek  ZJ  WJ  VK[DZK[Db &&=>wGMcDbN%cDd &11H%I'%RST%TUY$ZeDd [_eDd `c  c\nt  c\  `\  _]eDd]eDl &&78A$GmDlH%mDn &11B%CG%LQ%Nt$ToDn UYoDn Z]  ]Phn  ]P  ZP  YQoDnHQoD~ }D~D@ &&AD@'ADB CDBCDD !MEDDB"EDL 
(C'**aGGDM + z" #   /Z$ 7`@ XlZ +*s2   (8 >A@AA@	AA@
	A@@A@$	