26 lines
1.4 KiB
Python
26 lines
1.4 KiB
Python
import xlrd, json, pathlib, decimal, hashlib, re
|
|
out=pathlib.Path(__file__).parent
|
|
rows=[]; summary=[]; seen={}
|
|
for p in sorted(pathlib.Path('/Users/lingniu/Documents').glob('里程统计报表*.xls')):
|
|
s=xlrd.open_workbook(str(p)).sheet_by_index(0)
|
|
meta={'file':p.name,'sha256':hashlib.sha256(p.read_bytes()).hexdigest(),'rows':s.nrows-1}
|
|
if s.cell_value(0,2)!='日期':
|
|
meta.update(excluded='No daily date column',nonzero=sum(decimal.Decimal(str(s.cell_value(i,3)))!=0 for i in range(1,s.nrows)))
|
|
summary.append(meta); continue
|
|
dates=[]
|
|
for i in range(1,s.nrows):
|
|
plate,phone,day,km=map(lambda x:str(x).strip(),s.row_values(i))
|
|
import datetime
|
|
datetime.date.fromisoformat(day)
|
|
assert re.fullmatch(r'(?:\d+|\d{1,3}(?:,\d{3})+)(?:\.\d+)?',km), km
|
|
amount=decimal.Decimal(km.replace(',','')); assert amount.is_finite() and amount>=0
|
|
key=(plate,day)
|
|
assert key not in seen, ('duplicate',key)
|
|
seen[key]=amount; dates.append(day)
|
|
rows.append(dict(plate=plate,phone=phone,date=day,km=str(amount),file=p.name,row=i+1))
|
|
meta.update(date_min=min(dates),date_max=max(dates),vehicles=len({r['plate'] for r in rows if r['file']==p.name}))
|
|
summary.append(meta)
|
|
(out/'rows.json').write_text(json.dumps(rows,ensure_ascii=False))
|
|
(out/'input-summary.json').write_text(json.dumps(summary,ensure_ascii=False,indent=2))
|
|
print(json.dumps(summary,ensure_ascii=False,indent=2))
|