# -*- coding: utf-8 -*-
"""
Spyder Editor
import sys
import os
import pandas as pd
from openpyxl import load_workbook
spath=os.path.join(os.path.dirname(sys.argv[0]),'制作分表.xlsx')
df=pd.read_excel(spath,dtype={'Document Number':str,'Account':str,'Document Date':object,
'Posting Date':object,"Material":str,"Amount in local currency":float,"User Name":str,
"G/L Account":str,"Plant":str})
df=df.loc[:len(df)-2]
wb=load_workbook(spath)
keywd=set(df['Account'])
newsheet=[name for name in keywd if name not in wb.sheetnames]
print(newsheet)
if len(newsheet):
for k in newsheet:
wb.create_sheet(title=k,index=len(wb.sheetnames))
wb.save(spath)
writer=pd.ExcelWriter(spath,engine='openpyxl')
writer.book=wb
writer.sheets=dict((ws.title,ws) for ws in wb.worksheets)
for key in keywd:
data=df[(df['Account']==key)]
data.to_excel(writer,sheet_name=key,index=False)
writer.save()
"""
Spyder Editor
This is a temporary script file.
修改了一下防止连续运行出现的问题。避免覆盖原来的表。
"""import sys
import os
import pandas as pd
from openpyxl import load_workbook
spath=os.path.join(os.path.dirname(sys.argv[0]),'制作分表.xlsx')
df=pd.read_excel(spath,dtype={'Document Number':str,'Account':str,'Document Date':object,
'Posting Date':object,"Material":str,"Amount in local currency":float,"User Name":str,
"G/L Account":str,"Plant":str})
df=df.loc[:len(df)-2]
wb=load_workbook(spath)
keywd=set(df['Account'])
newsheet=[name for name in keywd if name not in wb.sheetnames]
print(newsheet)
if len(newsheet):
for k in newsheet:
wb.create_sheet(title=k,index=len(wb.sheetnames))
wb.save(spath)
writer=pd.ExcelWriter(spath,engine='openpyxl')
writer.book=wb
writer.sheets=dict((ws.title,ws) for ws in wb.worksheets)
for key in keywd:
data=df[(df['Account']==key)]
data.to_excel(writer,sheet_name=key,index=False)
writer.save()