1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
| import openpyxl,json,hashlib,collections
| from pathlib import Path
| from datetime import datetime
| from decimal import Decimal
| P=Path('scripts/quotation-import');P.mkdir(exist_ok=True)
| source=Path('D:/WechatRecord/xwechat_files/wxid_vmeut7wztkbp22_1546/temp/RWTemp/2026-10/c40afdcfa98e2b4561823e6babbefa33/销售报价单_2026.8.1-10.11.xlsx')
| w=openpyxl.load_workbook(source,data_only=True)
| quotes=[];products=collections.Counter();excluded=[]
| def dec(v):return str(Decimal(str(v))) if v is not None else None
| for s in w:
| if '汇总' in s.title or s.title=='编制说明':excluded.append(s.title);continue
| rows=list(s.values)
| assert rows[0][0]=='销 售 报 价 单',s.title
| h=dict(sheet=s.title,no=rows[1][1],owner=rows[1][4],quotation_time=rows[1][7].isoformat(sep=' '),valid_until=rows[1][9].isoformat(sep=' '),customer=rows[2][1],business=rows[2][5],contact=rows[2][9],tax=dec(rows[3][1]),remark=rows[3][4],items=[])
| assert rows[5][:10]==('序号','产品名称','规格型号','单位','辅单位1','辅单位2','原价(元)','报价(元)','数量','合计(元)'),s.title
| for n,r in enumerate(rows[6:],7):
| if isinstance(r[0],(int,float)):
| assert r[1] and r[2] and r[3],(s.title,n,r)
| x=dict(row=n,line=int(r[0]),name=r[1],specification=r[2],unit=r[3],unit2=r[4],unit3=r[5],original_price=dec(r[6]),price=dec(r[7]),count=dec(r[8]),total=dec(r[9]))
| h['items'].append(x);products[(x['name'],x['specification'],x['unit'])]+=1
| elif any(v is not None for v in r):
| h.setdefault('footers',[]).append(dict(row=n,values=list(r)))
| assert h['items'],s.title
| quotes.append(h)
| data=dict(source=str(source),sha256=hashlib.sha256(source.read_bytes()).hexdigest(),excluded_sheets=excluded,quotes=quotes)
| (P/'source.json').write_text(json.dumps(data,ensure_ascii=False,indent=2,default=str),encoding='utf-8')
| (P/'source-products.json').write_text(json.dumps([dict(name=k[0],specification=k[1],unit=k[2],occurrences=v) for k,v in products.items()],ensure_ascii=False,indent=2),encoding='utf-8')
| print('Quotes',len(quotes),'Lines',sum(len(x['items']) for x in quotes),'Unique products',len(products),'Excluded',excluded)
| print('Owners',collections.Counter(x['owner'] for x in quotes))
| print('Taxes',collections.Counter(x['tax'] for x in quotes))
| print('Totals',sum(Decimal(x['total']) for q in quotes for x in q['items']))
| print('Footers',quotes[0].get('footers'))
|
|
