案例概述
有一供应商给我们安装了一个数据采集工具，按他们规定的Excel格式填入相应数据（进货销售库存退货）等情况，每周上传一次，也就需要从SAP中查询导出为表格将整理格式，每周做一次，领导将此任务交我，希望能从数据库中查询相应字段将自动填入xlsx中。我略懂Python，决定尝试。
解决思路
1，首先分析原操作流程，从SAP B1界面中查询报表，使用SQL Server2014自带的数据库跟踪软件，找到对应的原始 SQL话句。
2. 使用SQL Server Profiler跟踪对应SQL语句
3， 将优化SQL语句，前面提到按他们规定的Excel格式填入相应数据（进货销售库存退货）,将一些变量或无效的查询进行优化提炼，如客户编号做Declare变量设置，按业务需求格式Select 指定字段。只保留有效字段，无效的left join通通去除，提高SQL查询效率。
3，
# -*- coding: GBK -*-
import pymssql
from openpyxl import Workbook,load_workbook
import datetime
from time import time
t = time()
today=datetime.date.today()
year = today.year
month = today.month
#连接数据库
server = "192.168.1.161"
user = "XXXXXXXXX这里输入您的数据库用户名"
password = "XXXXXXXXXXXXX这里输入您的数据库密码"
database = "XXXXXX这里输入您的数据库名称"
conn = pymssql.connect(server, user, password, database)
cursor = conn.cursor()
if cursor:
print("连接数据库成功! ")
#打开模板：金佰利Excel上传模板.xlsx
wb = load_workbook(r'd:\Python\金佰利\excel上传模板-金佰利.xlsx')
#选择Excel表
ws = wb['库存']
sql = '''
declare
@FirmCode int = 16,
@OnHand int =0
Select CONVERT(varchar(10), getdate(), 120) 库存日期,T0.ItemCode 产品编码,T0.CodeBars 产品条码,T0.ItemName 产品名称,T4.OnHand 产品数量,T0.FrgnName 产品规格,T0.InvntryUom 产品单位,T5.WhsName 仓库类型,'' 产品批号,'' 备注
From OITM T0 Left Join OCRD T1 ON IsNull(T0.CardCode,'')=IsNull(T1.CardCode,'')
Inner Join OITB T2 ON T0.ItmsGrpCod=T2.ItmsGrpCod Inner Join OMRC T3 ON T0.FirmCode=T3.FirmCode
Inner Join OITW T4 ON T0.ItemCode=T4.ItemCode Inner Join OWHS T5 ON T4.WhsCode=T5.WhsCode
Left Join (
Select T0.ItemCode,X1.WhsCode,Sum(X1.CommitQty) As CommitQty From OBTN X0
Inner Join OBTQ X1 On X0.ItemCode=X1.ItemCode And X0.SysNumber=X1.SysNumber
Inner Join OITM T0 ON X0.ItemCode=T0.ItemCode
Where T0.ManBtchNum='Y'
Group By T0.ItemCode,X1.WhsCode) B On IsNull(T4.ItemCode,'')=IsNull(B.ItemCode,'')
And IsNull(T4.WhsCode,'')=IsNull(B.WhsCode,'')
Left Join
(Select X1.ItemCode,X0.U_WhsCode,Sum(U_Quantity) As AssignQty
From [@ORDRASSIGN] X0 Inner Join OITM X1 ON X0.U_ItemCode=X1.ItemCode
Group By X1.ItemCode,X0.U_WhsCode) T6 ON IsNull(T4.ItemCode,'')=IsNull(T6.ItemCode,'')
And IsNull(T4.WhsCode,'')=IsNull(T6.U_WhsCode,'')
Where T0.FirmCode=@FirmCode And T4.OnHand>@OnHand
'''
try:
cursor.execute(sql)
row = cursor.fetchone()
print("正在读取库存数据库...")
while row:
ws.append(row)
row = cursor.fetchone()
except:
print('数据整理出错')
print('时间消耗：%.2f秒' % (time() - t))
ws = wb['销售']
t = time()
sql = '''
declare
@FirmCode int = 16,
@sd date = DATEADD(mm, -1, GETDATE()),
@ed date = getdate()
Select T0.DocEntry 单据编号,T0.DocDate 日期,case Convert(Nvarchar(20),T0.ObjType) when '15' then '销售交货'+T0.ObjType when '16' then '销售退货'+T0.ObjType else 'else' end 单据类型,
T0.CardName 门店名称,T0.CardCode 门店编码,T4.ItemCode 产品编码,T1.CodeBars 产品条码,T1.Dscription As 产品名称,T4.FrgnName 产品规格,T1.unitMsr 产品单位,T1.Quantity 产品数量,T1.Price 产品价格, T1.LineTotal 销售金额,T3.WhsName 仓库类型,'' 产品批号,'' 下单日期,'' 发货日期,'' 实际发货数,'' 发货地址,'' 单据状态,'' 备注
From ODLN T0 Inner Join DLN1 T1 ON T0.DocEntry=T1.DocEntry
Inner Join OCRD T2 With (NoLock) ON T0.CardCode=T2.CardCode
INNER JOIN OWHS T3 With (NoLock) ON T1.WhsCode=T3.WhsCode
Inner Join OITM T4 With (NoLock) ON T1.ItemCode=T4.ItemCode
Where T0.CANCELED='N' And T0.DocDate Between @sd And @ed And T4.FirmCode=@FirmCode
Union All
Select T0.DocEntry 单据编号,T0.DocDate 日期,case Convert(Nvarchar(20),T0.ObjType) when '15' then '销售交货'+T0.ObjType when '16' then '销售退货'+T0.ObjType else 'else' end 单据类型,
T0.CardName 门店名称,T0.CardCode 门店编码,T4.ItemCode 产品编码,T1.CodeBars 产品条码,T1.Dscription As 产品名称,T4.FrgnName 产品规格,T1.unitMsr 产品单位,-T1.Quantity 产品数量,T1.Price 产品价格,-T1.LineTotal 销售金额,T3.WhsName 仓库类型,'' 产品批号,'' 下单日期,'' 发货日期,'' 实际发货数,'' 发货地址,'' 单据状态,'' 备注
From ORDN T0 Inner Join RDN1 T1 ON T0.DocEntry=T1.DocEntry
Inner Join OCRD T2 With (NoLock) ON T0.CardCode=T2.CardCode
INNER JOIN OWHS T3 With (NoLock) ON T1.WhsCode=T3.WhsCode
Inner Join OITM T4 With (NoLock) ON T1.ItemCode=T4.ItemCode
Where T0.CANCELED='N' And T0.DocDate Between @sd And @ed And T4.FirmCode= @FirmCode
'''
try:
cursor.execute(sql)
row = cursor.fetchone()
print("正在读取销售数据库...")
while row:
ws.append(row)
row = cursor.fetchone()
except:
print('数据整理出错')
print('时间消耗：%.2f秒' % (time() - t))
ws = wb['进货']
t = time()
sql = '''
declare
@FirmCode int = 16,
@sd date = DATEADD(mm, -1, GETDATE()),
@ed date = getdate()
Select T0.DocEntry 单据编号,T0.DocDate 单据日期,case Convert(Nvarchar(20),T0.ObjType) when '20' then '采购收货'+T0.ObjType when '21' then '采购退货'+T0.ObjType else 'else' end 单据类型,
T0.CardCode 供应商编码,T0.CardName 供应商名称,T4.ItemCode 产品编码,T1.CodeBars 产品条码,T1.Dscription As 产品名称,t4.FrgnName 产品规格,t4.InvntryUom 产品单位,T1.Quantity 产品数量,T1.Price 产品价格,T1.LineTotal 合计金额,T3.WhsName 仓库类型
From OPDN T0
Inner Join PDN1 T1 ON T0.DocEntry=T1.DocEntry
Inner Join OCRD T2 With (NoLock) ON T0.CardCode=T2.CardCode
INNER JOIN OWHS T3 With (NoLock) ON T1.WhsCode=T3.WhsCode
Inner Join OITM T4 With (NoLock) ON T1.ItemCode=T4.ItemCode
Where T0.CANCELED='N' And T0.DocDate Between @sd And @ed
And T4.FirmCode = @FirmCode
Union All
Select T0.DocEntry 单据编号,T0.DocDate 单据日期,case Convert(Nvarchar(20),T0.ObjType) when '20' then '采购收货'+T0.ObjType when '21' then '采购退货'+T0.ObjType else 'else' end 单据类型,
T0.CardCode 供应商编码,T0.CardName 供应商名称,T4.ItemCode 产品编码,T1.CodeBars 产品条码,T1.Dscription As 产品名称,t4.FrgnName 产品规格,t4.InvntryUom 产品单位,-T1.Quantity 产品数量,T1.Price 产品价格,-T1.LineTotal 合计金额,T3.WhsName 仓库类型
From ORPD T0
Inner Join RPD1 T1 ON T0.DocEntry=T1.DocEntry
Inner Join OCRD T2 With (NoLock) ON T0.CardCode=T2.CardCode
INNER JOIN OWHS T3 With (NoLock) ON T1.WhsCode=T3.WhsCode
Inner Join OITM T4 With (NoLock) ON T1.ItemCode=T4.ItemCode
Where T0.CANCELED='N' And T0.DocDate Between @sd And @ed
And T4.FirmCode= @FirmCode
'''
try:
cursor.execute(sql)
row = cursor.fetchone()
print("正在读取进货数据库...")
while row:
ws.append(row)
row = cursor.fetchone()
except:
print('数据整理出错')
print('时间消耗：%.2f秒' % (time() - t))
ws = wb['其他出入库']
t = time()
sql = '''
declare
@ZT varchar(30)='东莞',
@SD DATE = DATEADD(mm,-1,GETDATE()),
@ED DATE = getdate(),
@GroupName varchar(30) ='',
@FirmName varchar(30)='金佰利',
@itemcode varchar(30)=''
select a.DocEntry 单号,a.TaxDate 单据日期,'其他入库'+a.ObjType 单据类型,b.ItemCode 产品编码,b.CodeBars 产品条码,c.itemname 产品名称,c.InvntryUom 产品规格, b.Quantity 产品数量,b.price 产品价格,b.Quantity*b.StockPrice 总金额,e.whsname 仓库类型,'' 产品批号,'' 备注
from HJDG..oign a
left join HJDG..IGN1 b on a.docentry=b.DocEntry
left join HJDG..oitm c on c.itemcode=b.itemcode
left join HJDG..omrc d on d.FirmCode=c.FirmCode
left join HJDG..owhs e on e.whscode=b.whscode
left join HJDG..OCRD g on g.cardcode=a.cardcode
left join HJDG..OCRG h on h.groupcode=g.groupcode
left join HJDG..UFD1 f on c.U_ss=f.FldValue and FieldID='29' and TableID='oitm'
left join OMRC on OMRC.FirmCode = c.FirmCode
left join HJZZ..[@PINGPAI] pp on pp.U_DL = OMRC.FirmName
where a.TaxDate between @SD and @ED
and (d.FirmName like '%'+@FirmName+'%'or @FirmName='')
and (b.itemcode like '%'+@itemcode+'%'or @itemcode='')
and (@GroupName=h.GroupName or @GroupName='')
and b.BaseEntry is null
AND (@ZT='东莞' or @ZT='')
union all
select a.DocEntry 单号,a.TaxDate 单据日期,'其他出库'+a.ObjType 单据类型,b.ItemCode 产品编码,b.CodeBars 产品条码,c.itemname 产品名称,c.InvntryUom 产品规格,-b.Quantity 产品数量,b.price 产品价格,-b.Quantity*b.StockPrice 总金额,e.whsname 仓库类型,'' 产品批号,'' 备注
from HJDG..OIGE a
left join HJDG..IGE1 b on a.docentry=b.DocEntry
left join HJDG..oitm c on c.itemcode=b.itemcode
left join HJDG..omrc d on d.FirmCode=c.FirmCode
left join HJDG..owhs e on e.whscode=b.whscode
left join HJDG..OCRD g on g.cardcode=a.cardcode
left join HJDG..OCRG h on h.groupcode=g.groupcode
left join HJDG..UFD1 f on c.U_ss=f.FldValue and FieldID='29' and TableID='oitm'
left join OMRC on OMRC.FirmCode = c.FirmCode
left join HJZZ..[@PINGPAI] pp on pp.U_DL = OMRC.FirmName
where a.TaxDate between @SD and @ED
and (d.FirmName like '%'+@FirmName+'%'or @FirmName='')
and (b.itemcode like '%'+@itemcode+'%'or @itemcode='')
and (@GroupName=h.GroupName or @GroupName='')
and b.BaseEntry is null
AND (@ZT='东莞' or @ZT='')
union all
select a.Docnum 单号,a.taxdate 单据日期,'库存转储'+a.ObjType 单据类型,b.ItemCode 产品编码,b.CodeBars 产品条码,c.itemname 产品名称,b.unitMsr 产品规格,-b.Quantity 产品数量,b.StockPrice 产品价格,(-b.Quantity)*(b.StockPrice) 总金额,k.whsname 仓库类型,'' 产品批号,'出'+k.whsname+' 入'+e.whsname 备注
from HJDG..owtr a
left join HJDG..wtr1 b on a.docentry=b.DocEntry
left join HJDG..oitm c on c.itemcode=b.itemcode
left join HJDG..omrc d on d.FirmCode=c.FirmCode
left join HJDG..owhs e on e.whscode=b.whscode
left join HJDG..owhs f on f.whscode=b.whscode
left join HJDG..OCRD g on g.cardcode=a.cardcode
left join HJDG..OCRG h on h.groupcode=g.groupcode
left join HJDG..owhs k on k.whscode=b.FromWhsCod
left join HJZZ..[@PINGPAI] pp on pp.U_DL = d.FirmName
where a.TaxDate between @SD and @ED
and (@FirmName=d.FirmName or @FirmName='')
and (@itemcode=b.itemcode or @itemcode='')
AND (@ZT='东莞' or @ZT='')
and (@GroupName=h.GroupName or @GroupName='')
union all
select a.Docnum 单号,a.taxdate 单据日期,'库存转储'+a.ObjType 单据类型,b.ItemCode 产品编码,b.CodeBars 产品条码,c.itemname 产品名称,b.unitMsr 产品规格,-b.Quantity 产品数量,b.StockPrice 产品价格,(-b.Quantity)*(b.StockPrice) 总金额,k.whsname 仓库类型,'' 产品批号,'出'+k.whsname+' 入'+e.whsname 备注
from HJBND..owtr a
left join HJBND..wtr1 b on a.docentry=b.DocEntry
left join HJBND..oitm c on c.itemcode=b.itemcode
left join HJBND..omrc d on d.FirmCode=c.FirmCode
left join HJBND..owhs e on e.whscode=b.whscode
left join HJBND..owhs f on f.whscode=b.whscode
left join HJBND..OCRD g on g.cardcode=a.cardcode
left join HJBND..OCRG h on h.groupcode=g.groupcode
left join HJBND..owhs k on k.whscode=b.FromWhsCod
left join HJZZ..[@PINGPAI] pp on pp.U_DL = d.FirmName
where a.TaxDate between @SD and @ED
and (@FirmName=d.FirmName or @FirmName='')
and (@itemcode=b.itemcode or @itemcode='')
AND (@ZT='百纳德' or @ZT='')
and (@GroupName=h.GroupName or @GroupName='')
'''
try:
cursor.execute(sql)
row = cursor.fetchone()
print("正在读取其他出入库数据库...")
while row:
ws.append(row)
row = cursor.fetchone()
except:
print('数据整理出错')
conn.close()
print('时间消耗：%.2f秒' % (time() - t))
wb.save(r"D:\Python\金佰利\汇基_"+str(today)+"gbk.xlsx")
print("已保存在:D:\Python\金佰利\汇基_"+str(today)+"gbk.xlsx")
wb.close()如图所示
5， 读取Excel模板，写入数据。
生成后：
6， 给领导安装相关运行环境（python3.10和openpyxl库和pymssql库）
7， 写批处理文件，实现一键操作，自动生成指定时间周期的数据为Excel文件（xlsx）
8，希望对新手有相同需求的伙伴有所帮助。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
