Python|解析excel自动创建业务系统凭证

财务同事近期提了一个需求：公司有一类产品，定期需要从网上银行下载交易流水。筛选出符合条件的到账流水后，要逐笔在业务系统中录入凭证。这个凭证逻辑比较简单，每月需要集中做几次。项目自成立以来已经累计做了八百多张凭证了。同事希望可以通过系统实现excel的解析和凭证的自动录入。
我这边所负责的项目中，恰巧有需要在业务系统录入凭证，之前都是采用的RPA模拟鼠标键盘操作的，这种效率很低。这次想借此机会让业务系统开放个接口，以后类似需求就可以通过接口实现了。😁😁
在拿到业务系统提供的接口后，我这边的实现方式是：

  • 创建共享目录，让财务同事将下载好的excel放到指定的【待处理excel】目录中。
  • 通过python脚本遍历处理excel，包括解析内容、生成报文、调用接口等。
  • 处理过程中将报文、接口返回内容保存到数据库中，在处理结束后将处理结果邮件通知给财务人员。

源码如下

main.py

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
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
'''
    XX项目凭证自动录入：
    1、遍历读取待处理excel，解析具体内容
    2、解析银行账号，获取具体的信托项目
    3、封装xml报文，生成业务系统凭证
    4、修改excel名字，移到已处理excel文件夹
'''
import os
import pandas as pd
from mssql import connectMSsql,executeSQL,closeConn
import requests  
import html
import datetime
from loguru import logger
import shutil
import sqlite3
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.header import Header

# 创建日志对象
FileLog = 'D:/workspace/Enfo_Voucher/log/log{}.log'.format(datetime.date.today())
logger.add(FileLog,rotation='500MB',encoding='utf-8',enqueue=True)

# 文件移动并重命名
def excel_move(path_befor,path_end,excel_name):
    shutil.move(os.path.join(path_befor,excel_name),os.path.join(path_end,'已完成_'+excel_name))

# 读取excel
# 交易时间  对方户名    对方账户    收入金额    支出金额    账户余额    摘要
def read_excel(filepath):
    excel_list = []
    df = pd.read_excel(filepath)
    for index,row in df.iterrows():
        if row[1] == 'XXXX服务股份有限公司':
            excel_list.append(list(row))
    return excel_list
# 获取产品id
def get_productid(acct_bank):
    # 连接sqlserver
    sqlConnectParams = {"database_name": "XXXX", "username": "XXXX", "password": "XXXX", "host": "XXXX", "port": "XXXX", "encoding": "cp936"}
    conn = connectMSsql(sqlConnectParams)
    productidSql = r"select b.product_id from NCMIDDLEDB..JQR_ALL_TBANK a left join intrust..tproduct b on b.product_code=a.product_code where a.BANK_ACCT='"+acct_bank+"'"
    lista = executeSQL(conn,productidSql)
    accasoaSql = r"select  sub_code   from    intrust..TSUBBANKINFO1021  where ACCT_TYPE  = '120801'  AND  PRODUCT_ID  =" + str(lista[0][0])
    listb = executeSQL(conn,accasoaSql)
    closeConn(conn)
    return lista,listb
# 创建凭证
def create_voucher(row_excel,product_id,accasoa):
    now = datetime.datetime.now()
    timestamp = int(now.timestamp())
    PzDate = str(row_excel[0]).replace('-','')[0:8]
    xmlDetail = '''
    <![CDATA[ 
    <root>
        <row Guid="{}">  <!--guid：【*】业务流水编号（不能重复）  -->
        <Flow>              <!--第一笔凭证  -->
        <Id>1</Id>       <!--【*】编号    -->
        <Productid>{}</Productid>      <!--【*】产品ID    -->
        <PzDate>{}</PzDate>       <!--【*】记帐日期    -->
        <BusiDate>{}</BusiDate><!--【*】业务日期    -->
        <PutMan>cwjqr</PutMan><!--【*】制单人    -->
        <Description>{}</Description><!--凭证摘要   -->
        <ListXml>   <!--【*】分录1-->
        <ListId>1</ListId>                <!--【*】分录1-->
        <SubCode>{}</SubCode> <!--【*】明细科目-->
        <Balance1>{}</Balance1>    <!--【*】借方金额-->
        <Balance2>0</Balance2>           <!--【*】贷方金额-->
        <Description>{}</Description> <!--【*】分录摘要-->
        </ListXml>
        <ListXml>   <!--【*】分录2-->
        <ListId>2</ListId>           <!--【*】分录2-->
        <SubCode>224199</SubCode>  <!--【*】明细科目-->
        <Balance1>0</Balance1>       <!--【*】借方金额-->
        <Balance2>{}</Balance2>   <!--【*】贷方金额-->
        <Description>{}</Description> <!--【*】分录摘要-->
        </ListXml>
        </Flow>                          <!--第一笔凭证 end  -->
        </row>
    </root>]]>
    '''.format(str(timestamp) , str(product_id) , PzDate , PzDate , row_excel[6] , str(accasoa) , row_excel[3] , row_excel[6] , row_excel[3] , row_excel[6])
    
    # 定义XML文件内容  
    xml_data = """  
    <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:intf="http://intf.intrust.enfo.com/">
    <soapenv:Header/>
    <soapenv:Body>
        <intf:doBizEx>
            <!--Optional:-->
            <arg0>ApplyAddPz</arg0>
            <!--Zero or more repetitions:-->
            <arg1>Xmlinfo</arg1>
            <!--Zero or more repetitions:-->
            <arg2>{}</arg2>
        </intf:doBizEx>
    </soapenv:Body>
    </soapenv:Envelope>
    """.format(xmlDetail)
    logger.info(xml_data)
    url = "http://XXXX:XXXX/CRMServer/services/BaseService?wsdl" 
    headers = {  
        "Content-Type": "text/xml;charset=utf-8",
    }  
    response = requests.post(url, data=xml_data.encode('utf-8'), headers=headers)  
    if response.status_code == 200:  
        logger.info(html.unescape(response.text))
    else:  
        logger.info(f"请求失败，状态码：{response.status_code}")
    return xml_data,html.unescape(response.text),str(datetime.datetime.today())

# 更新至sqlite中
# [[报文1,回执1],[报文2,回执2]]
def insert_sqlite(contentList):
    connSqlite = sqlite3.connect(r'D:\sqlite\XXXXDatabase')
    cursor = connSqlite.cursor()
    for i in contentList:
        insertSql = "insert into gl_voucher_detail (sendxml,contentxml,ts) values (?,?,?)"
        cursor.execute(insertSql,(i[0],i[1],i[2],))
    connSqlite.commit()
    connSqlite.close()
# 发送邮件
def toManager():
    conn = sqlite3.connect(r'D:\sqlite\XXXXDatabase')
    c = conn.cursor()
    logger.info('查询是否需要发送邮件')
    querySql = "select *  from gl_voucher_detail where ts like '{}%'".format(datetime.date.today().strftime(r'%Y-%m-%d'))
    queryResult = c.execute(querySql)
    if queryResult.fetchone():
        logger.info('今日有新增凭证，需发送邮件')
        subject = '{}-XX系列银行收款凭证处理通知'.format(datetime.date.today().strftime(r'%Y%m%d'))
        to = 'XXXX@XXXX.cn'
        contents = '<p><b>今日XX系列银行收款凭证已处理完毕，请登陆业务系统核对。</b></p><span>该邮件为系统自动发送，请勿回复。</span>'
        smtp = smtplib.SMTP()
        smtp.connect("mail.XXXX.cn", port=25)
        smtp.login(user="ai@XXXX.cn", password=r"XXXX")
        message = MIMEMultipart()
        message['From'] = Header("ai@XXXX.cn", 'utf-8') 
        message['Subject'] = Header(subject, 'utf-8')
        message['To'] = Header(to, 'utf-8')
        message.attach(MIMEText(contents, 'html', 'utf-8'))
        smtp.sendmail(from_addr="ai@XXXX.cn", to_addrs=to, msg=message.as_string())
        smtp.quit()
        logger.info('邮件发送完成')
    else:
        logger.info('今日无需发送邮件')
    conn.close()

if __name__ == '__main__':
    logger.info('****准备处理XX项目凭证录入****')
    path = r'D:/workspace/Enfo_Voucher/excel/待处理excel'
    path2 = r'D:/workspace/Enfo_Voucher/excel/已处理excel'
    logger.info('待处理excel路径为：{}'.format(path))
    # 遍历读取excel文件
    for folder in os.listdir(path):
        logger.info('处理文件：{}'.format(folder))
        # 查询productId
        acct_bank = folder.split('.')[0][0:21]
        logger.info('银行账号：{}'.format(acct_bank))
        product = get_productid(acct_bank)
        product_id = product[0][0][0]
        accasoa_id = product[1][0][0]
        logger.info('product_id:{} ,accasoa_id:{}'.format(product_id,accasoa_id))
        # 解析excel内容
        print(os.path.join(path,folder))
        content = read_excel(path+'//'+folder)
        request_list = []
        for i in content:
            logger.info('处理行：{}'.format(i))
            request_detail = create_voucher(i,product_id,accasoa_id)
            request_list.append(request_detail)
            logger.info('该行处理完毕')
        logger.info('准备插入sqlite数据库')
        insert_sqlite(request_list)
        logger.info('插入sqlite数据库完成')
        logger.info('文件处理完成，移动至已处理目录')
        excel_move(path,path2,folder)    
        logger.info('文件：{} ，处理完成'.format(folder))
        # break
    toManager()
    logger.info('****XX项目凭证处理完毕****')

mssql.py

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
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
import pymssql

def connectMSsql(params):
    """
    连接数据库
    """
    try:
        if 'host' not in params or 'port' not in params or 'username' not in params or 'password' not in params or 'database_name' not in params:
            raise Exception('缺少参数')
        host = params.get('host')
        port = params.get('port')  # 端口这里默认是1433
        try:
            port = int(port)
        except Exception:
            return "端口信息错误"
        username = params.get('username')
        password = params.get('password')
        database_name = params.get('database_name')
        encoding = params.get('encoding')
        conn = pymssql.connect(host=host,port=port, user=username, password=password, database=database_name,charset=encoding)
        return conn
    except pymssql._pymssql.OperationalError:
        return "数据库连接超时"
    except Exception as e:
        raise e

def executeSQL(conn,sql):
    """
    执行SQL语句,select ,update,insert
    """
    try:
        cursor = conn.cursor()
        if sql.startswith("select") or sql.startswith("SELECT"):
            cursor.execute(sql)
            result = cursor.fetchall()
            return result
        elif sql.startswith("update") or sql.startswith("UPDATE"):
            cursor.execute(sql)
            conn.commit()
            return {}
        elif sql.startswith("insert") or sql.startswith("INSERT"):
            cursor.execute(sql)
            conn.commit()
            return {}
        else:
            return {}
    except Exception as e:
        raise e
    
def closeConn(conn):
    conn.close()

if __name__ == '__main__':
    pass

商业转载请联系作者获得授权，非商业转载请注明出处。

Python > pandas,requests
#Python #pandas #requests
赏 支付宝打赏 微信打赏

如果文章对你有帮助，欢迎点击上方按钮打赏作者

Python|解析excel自动创建业务系统凭证
http://hncd1024.github.io/2023/12/18/Python_createEnfoVoucher/
作者
CHEN DI
发布于
2023-12-18
许可协议