# -*- coding: utf-8 -*- import openpyxl, copy, io from openpyxl.styles import Font BASE="https://www.kofpi.or.kr" FN="한국임업진흥원.xlsx" ORG="한국임업진흥원" log=io.open("_rebuild_log.txt","w",encoding="utf-8") def P(*a): log.write(" ".join(str(x) for x in a)+"\n") G={ 7:("child",[("감사·윤리","/public/publicInfo_03.do","페이지",1,"어문"),("계약·회계","/public/publicInfo_03_02.do","페이지",1,"어문"),("기획·재정·홍보","/public/publicInfo_03_03.do","페이지",1,"어문"),("인사·총무·노무·법인","/public/publicInfo_03_04.do","페이지",1,"어문"),("임업관련","/public/publicInfo_03_05.do","페이지",1,"어문")]), 10:("child",[("단기임산물 재배적지도","/public/dataOpen_04.do","페이지",1,"어문,이미지"),("임산물DB백과 API","/public/dataOpen_02.do","페이지",1,"어문"),("목재제품 DB백과 API","/public/dataOpen_03.do","페이지",1,"어문")]), 15:("child",[("산림 관련 직종","/info/reForestInfo/job_01.do","페이지",1,"어문"),("산림분야 자격과정","/info/reForestInfo/job_02.do","페이지",1,"어문"),("전문임업인","/info/reForestInfo/job_03.do","페이지",1,"어문")]), 16:("child",[("산촌 곳곳의 즐길거리","/info/reForestInfo/exper_01.do","페이지",1,"어문"),("산촌 인사이트","/info/reForestInfo/exper_02.do","페이지",1,"어문")]), 17:("child",[("귀산촌인 창업 및 주택구입 자금","/info/rFarm_02_01.do","페이지",1,"어문"),("전문임업인 기반조성","/info/rFarm_02_02.do","페이지",1,"어문"),("산림소득분야 보조사업","/info/rFarm_02_03.do","페이지",1,"어문"),("임산물 소득원의 지원 대상 품목","/info/rFarm_02.do","페이지",1,"어문")]), 18:("sibling",[("산나물","/info/imupStory/forestStory_04_01.do","페이지",1,"어문"),("산약초","/info/imupStory/forestStory_05_01.do","페이지",1,"어문"),("임산물레시피","/info/imupStory/forestStory_02.do","게시판",30,"어문,이미지")]), 20:("child",[("우리목재","/info/imupStory/wood_02.do","게시판",48,"어문,이미지"),("세계목재 300종","/info/imupStory/wood_03.do","게시판",300,"어문,이미지")]), 21:("child",[("제재목","/info/tech_02.do?pc_cd=002&bc_cd=B200&mc_cd=1","페이지",1,"어문,이미지"),("방부목재","/info/tech_02.do?pc_cd=002&bc_cd=B200&mc_cd=2","페이지",1,"어문,이미지"),("난연목재","/info/tech_02.do?pc_cd=002&bc_cd=B200&mc_cd=3","페이지",1,"어문,이미지"),("목재 플라스틱 복합재","/info/tech_02.do?pc_cd=002&bc_cd=B200&mc_cd=4","페이지",1,"어문,이미지"),("집성재","/info/tech_02.do?pc_cd=002&bc_cd=B200&mc_cd=5","페이지",1,"어문,이미지"),("합판","/info/tech_02.do?pc_cd=002&bc_cd=B200&mc_cd=6","페이지",1,"어문,이미지"),("파티클보드","/info/tech_02.do?pc_cd=002&bc_cd=B200&mc_cd=7","페이지",1,"어문,이미지"),("섬유판","/info/tech_02.do?pc_cd=002&bc_cd=B200&mc_cd=8","페이지",1,"어문,이미지"),("배향성 스트랜드 보드","/info/tech_02.do?pc_cd=002&bc_cd=B200&mc_cd=9","페이지",1,"어문,이미지"),("목질바닥재","/info/tech_02.do?pc_cd=002&bc_cd=B200&mc_cd=10","페이지",1,"어문,이미지"),("목재펠릿","/info/tech_02.do?pc_cd=002&bc_cd=B200&mc_cd=11","페이지",1,"어문,이미지"),("목재칩","/info/tech_02.do?pc_cd=002&bc_cd=B200&mc_cd=12","페이지",1,"어문,이미지"),("목재브리켓","/info/tech_02.do?pc_cd=002&bc_cd=B200&mc_cd=13","페이지",1,"어문,이미지"),("성형숯","/info/tech_02.do?pc_cd=002&bc_cd=B200&mc_cd=14","페이지",1,"어문,이미지"),("숯,목초액","/info/tech_02.do?pc_cd=002&bc_cd=B200&mc_cd=15","페이지",1,"어문,이미지")]), 25:("child",[("임가경제조사","/info/imupStory/statistics_01_01.do","페이지",1,"어문"),("임산물생산비조사","/info/imupStory/statistics_01_01_02.do","페이지",1,"어문"),("임산물소득조사","/info/imupStory/statistics_01_01_03.do","페이지",1,"어문"),("임산물생산조사","/info/imupStory/statistics_01_01_04.do","페이지",1,"어문"),("임업경영실태조사","/info/imupStory/statistics_01_01_05.do","페이지",1,"어문"),("산림산업조사","/info/imupStory/statistics_01_01_06.do","페이지",1,"어문")]), 26:("child",[("목재이용실태","/info/imupStory/statistics_03.do","페이지",1,"어문"),("원목시장가격","/info/imupStory/statistics_04.do","페이지",1,"어문")]), 27:("child",[("수출/수입절차","/info/exportInfo/exportGuide_01.do","페이지",1,"어문"),("무역서식 및 증명서 발급","/info/exportInfo/exportGuide_02.do","페이지",1,"어문"),("카드뉴스","/info/exportInfo/exportGuide_05.do","게시판",10,"어문,이미지")]), 28:("child",[("공모중인 사업","/info/exportInfo/govSupport_02.do","게시판",161,"어문"),("임산물 수출지원 체계도","/info/exportInfo/govSupport_01.do","페이지",1,"어문"),("기관별 지원사업","/info/exportInfo/govSupport_03_01.do","페이지",1,"어문"),("내게 맞는 지원사업 찾기","/info/exportInfo/govSupport_04_01_01.do","페이지",1,"어문")]), 29:("child",[("통합 자료실","/info/exportInfo/exportData_01.do","게시판",84,"어문,이미지"),("해외시장 보고서","/info/exportInfo/exportData_02.do","게시판",48,"어문,이미지"),("연구 보고서","/info/exportInfo/exportData_03.do","게시판",48,"어문,이미지"),("통계자료","/info/exportInfo/exportData_04.do","게시판",20,"어문,이미지"),("임산물수출유망 발굴업체홍보","/info/exportInfo/exportData_05.do","게시판",20,"어문,이미지"),("임업기계장비","/info/exportInfo/exportData_06.do","게시판",20,"어문,이미지")]), 30:("child",[("유관기관","/info/exportInfo/exportSite_01.do","페이지",1,"기타"),("수출업체","/info/exportInfo/exportSite_02_01.do","페이지",1,"기타"),("관련사이트","/info/exportInfo/exportSite_02_02_01.do","페이지",1,"기타"),("국제박람회","/info/exportInfo/exportSite_03_01.do","페이지",1,"기타")]), 31:("child",[("기술이전이란?","/info/tech/techComm_01.do","페이지",1,"어문"),("업무처리절차","/info/tech/techComm_02_01.do","페이지",1,"어문"),("기술이전현황","/info/tech/techComm_01_02.do","게시판",100,"어문"),("사업화 성공 사례","/info/tech/techComm_01_03.do","게시판",21,"어문"),("보유특허정보","/info/tech/techComm_01_01.do","게시판",100,"어문"),("기술자료집","/info/tech_06.do","게시판",30,"어문,이미지"),("특허·논문검색","/info/tech_05_01.do","페이지",1,"어문"),("관련양식","/info/tech/techComm_03_01.do","게시판",8,"어문")]), 53:("child",[("시험·분석·조사·감정","/service/introduceInfo_01.do","페이지",1,"어문"),("목재제품규격ㆍ품질검사","/service/introduceInfo_04.do","페이지",1,"어문"),("KS인증","/service/introduceInfo_11.do","페이지",1,"어문"),("NET(신기술 지정)","/service/introduceInfo_15.do","페이지",1,"어문"),("임업기계 장비인증","/service/introduceInfo_34.do","페이지",1,"어문"),("목재제품 탄소저장량 표시제도","/service/introduceInfo_41.do","페이지",1,"어문"),("조달물자 전문기관검사","/service/introduceInfo_48.do","페이지",1,"어문")]), 55:("child",[("특별관리임산물(산양삼) 분석","/service/introduceInfo_20.do","페이지",1,"어문"),("토양/식물체분석","/service/introduceInfo_23.do","페이지",1,"어문"),("산림토양조사","/service/introduceInfo_26.do","페이지",1,"어문")]), 70:("child",[("숲푸드 소개","/service/ogFood_01.do","페이지",1,"어문"),("숲푸드 신청 절차/BI","/service/ogFood_02.do","페이지",1,"어문,이미지")]), 71:("child",[("숲푸드 더자연산 소개","/service/kfFood_01.do","페이지",1,"어문"),("숲푸드 더자연산 지정절차","/service/kfFood_02.do","페이지",1,"어문")]), 89:("child",[("윤리서약","/intro/intro_behavior_01_2.do","페이지",1,"어문"),("윤리헌장","/intro/intro_behavior_01.do","페이지",1,"어문"),("윤리강령","/intro/intro_behavior_01_1.do","페이지",1,"어문")]), 90:("sibling",[("청렴 서약서","/intro/intro_19.do","페이지",1,"어문")]), 93:("child",[("이사장(원장)","/intro/intro_behavior_02.do","페이지",1,"어문"),("상임이사","/intro/intro_behavior_02_1.do","페이지",1,"어문"),("상임이사","/intro/intro_behavior_02_2.do","페이지",1,"어문")]), 96:("child",[("공정거래 자율준수 프로그램 소개","/intro/cp_01.do","페이지",1,"어문"),("기관장 의지 선언문","/intro/cp_02.do","페이지",1,"어문")]), } wb=openpyxl.load_workbook(FN) ws=wb.active MAXR=ws.max_row filled={} for m in list(ws.merged_cells.ranges): top=ws.cell(m.min_row,m.min_col).value for r in range(m.min_row,m.max_row+1): for c in range(m.min_col,m.max_col+1): filled[(r,c)]=top def eff(r,c): return filled.get((r,c), ws.cell(r,c).value) def cap_style(r): d={} for c in range(2,28): cell=ws.cell(r,c) d[c]=(copy.copy(cell.font),copy.copy(cell.fill),copy.copy(cell.border),copy.copy(cell.alignment),cell.number_format) return d recs=[] for r in range(3,MAXR+1): e=[eff(r,c) for c in range(4,11)] leaf=-1 for i,v in enumerate(e): if v is not None and str(v).strip()!="": leaf=i if leaf<0: continue recs.append(dict(orig=r,e=e,leaf=leaf,K=ws.cell(r,11).value,L=ws.cell(r,12).value, M=ws.cell(r,13).value,N=ws.cell(r,14).value,O=ws.cell(r,15).value, Pc=ws.cell(r,16).value,Qc=ws.cell(r,17).value, extra=[ws.cell(r,c).value for c in range(18,28)],style=cap_style(r))) P("orig data recs:",len(recs)) def newrec(e,leaf,K,L,M,N,style): return dict(orig=None,e=e[:],leaf=leaf,K=K,L=L,M=M,N=N,O="미부착",Pc=None,Qc=None,extra=[None]*10,style=style) result=[] for rec in recs: o=rec['orig'] if o in G: model,tabs=G[o] anchorK=(rec['K'] or "").strip() if model=="sibling": result.append(rec) base=rec['e']; leaf=rec['leaf'] for (label,path,L,M,N) in tabs: e=base[:leaf]+[label]+[None]*(6-leaf) result.append(newrec(e,leaf,BASE+path,L,M,N,rec['style'])) P("r%d SIBLING +%d under col%d"%(o,len(tabs),leaf+4)) else: base=rec['e']; pleaf=rec['leaf']; cidx=pleaf+1 assert cidx<=6, "child col overflow r%d"%o for (label,path,L,M,N) in tabs: e=base[:pleaf+1]+[label]+[None]*(6-(pleaf+1)) result.append(newrec(e,cidx,BASE+path,L,M,N,rec['style'])) P("r%d CHILD parent='%s' -> %d children at col%d"%(o,base[pleaf],len(tabs),cidx+4)) else: result.append(rec) P("after transform recs:",len(result)) HEADER={"B1:R1","S1:W1","Y1:AA1"} for m in list(ws.merged_cells.ranges): if str(m) not in HEADER: ws.unmerge_cells(str(m)) ws.delete_rows(3, MAXR-2) NR=len(result) def val(e,c): v=e[c]; return v if (v is not None and str(v).strip()!="") else None covered=set(); mergeranges=[] for c in range(0,7): r=0 while rr: er1=3+r; er2=3+r2; col=4+c mergeranges.append((er1,col,er2,col)) for rr in range(er1+1,er2+1): covered.add((rr,col)) r=r2+1 blue=Font(name="맑은 고딕", size=10, color="FF0000FF", underline="single") for i,rec in enumerate(result): R=3+i ws.cell(R,2,i+1); ws.cell(R,3,ORG) for c in range(4,11): v=rec['e'][c-4] if (R,c) in covered: v=None ws.cell(R,c, v if (v is not None and str(v).strip()!="") else None) ws.cell(R,11,rec['K']); ws.cell(R,12,rec['L']); ws.cell(R,13,rec['M']); ws.cell(R,14,rec['N']) ws.cell(R,15,rec['O']); ws.cell(R,16,rec['Pc']); ws.cell(R,17,rec['Qc']) for j,v in enumerate(rec['extra']): ws.cell(R,18+j,v) for c in range(2,28): fnt,fil,bor,aln,nf=rec['style'][c] cell=ws.cell(R,c) cell.font=copy.copy(fnt); cell.fill=copy.copy(fil); cell.border=copy.copy(bor); cell.alignment=copy.copy(aln); cell.number_format=nf k=rec['K'] if k and str(k).strip().startswith("http"): cell=ws.cell(R,11); cell.hyperlink=str(k).strip(); cell.font=copy.copy(blue) ws.row_dimensions[R].height=17 for (r1,c1,r2,c2) in mergeranges: ws.merge_cells(start_row=r1,start_column=c1,end_row=r2,end_column=c2) wb.save(FN) P("SAVED rows:",NR,"merges:",len(mergeranges)) log.close() print("done",NR)