"""Copy LibreOffice-calculated caches into originals, preserving their format and comments."""
from pathlib import Path
from zipfile import ZipFile,ZIP_DEFLATED
from xml.etree import ElementTree as E
from openpyxl import load_workbook
P=Path(__file__).resolve().parent/'output';N={'s':'http://schemas.openxmlformats.org/spreadsheetml/2006/main'}
for f in (P/'build').glob('*.xlsx'):
 cal=P/'recalculated'/f.name;w=load_workbook(cal,data_only=True)
 assert not [(s.title,c.coordinate,c.value) for s in w for row in s for c in row if c.data_type=='e']
 with ZipFile(f) as z,ZipFile(cal) as q,ZipFile(P/'交付'/f.name,'w',ZIP_DEFLATED) as out:
  for item in z.infolist():
   b=z.read(item.filename)
   if item.filename.startswith('xl/worksheets/sheet') and item.filename.endswith('.xml'):
    root=E.fromstring(b);calc=E.fromstring(q.read(item.filename));cs={c.attrib['r']:c for c in calc.findall('.//s:c',N)}
    for c in root.findall('.//s:c',N):
     if c.find('s:f',N) is None:continue
     cc=cs[c.attrib['r']];v=cc.find('s:v',N)
     for old in list(c):
      if old.tag.endswith('}v') or old.tag.endswith('}is'):c.remove(old)
     typ=cc.attrib.get('t')
     if typ in ['s','inlineStr']:
      t=''.join(x.text or '' for x in cc.findall('.//s:t',N))
      if typ=='s':
       ss=E.fromstring(q.read('xl/sharedStrings.xml'));si=ss.findall('s:si',N)[int(v.text)];t=''.join(x.text or '' for x in si.findall('.//s:t',N))
      c.set('t','str');E.SubElement(c,'{'+N['s']+'}v').text=t
     else:
      if typ:c.set('t',typ)
      else:c.attrib.pop('t',None)
      if v is not None:c.append(v)
    b=E.tostring(root,encoding='utf-8',xml_declaration=True)
   out.writestr(item,b)
 print('Cached',f.name)