147 lines
12 KiB
Python
147 lines
12 KiB
Python
# -*- 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 r<NR:
|
|
v=val(result[r]['e'],c)
|
|
if v is None or result[r]['leaf']<=c:
|
|
r+=1; continue
|
|
r2=r
|
|
while r2+1<NR:
|
|
nr=result[r2+1]
|
|
if nr['leaf']<=c: break
|
|
if any(val(nr['e'],k)!=val(result[r]['e'],k) for k in range(0,c+1)): break
|
|
r2+=1
|
|
if r2>r:
|
|
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)
|