excel to xml 国税接口

资料http://effbot.org/zone/element-index.htm
https://docs.python.org/3/library/xml.etree.elementtree.html

xm解析https://www.guru99.com/manipulating-xml-with-python.html

源码

# -*- coding: utf-8 -*-

#参考http://www.lexicon.net/sjmachin/xlrd.html
import xlrd
from lxml import etree

wb = xlrd.open_workbook(r"C:\Users\DZL\Desktop\1.xlsx")
sh = wb.sheet_by_index(0)

# build a tree structure
root = etree.Element('Kp')
#.text字段赋值，.set属性赋值
etree.SubElement(root, 'Version').text = '2.0'
# 有子标签的话必须要定义一个变量名
fpxx = etree.SubElement(root, 'Fpxx')
# 去重len(list(set(sh.col_values(0))))
etree.SubElement(fpxx, 'Zsl').text=str(len(list(set(sh.col_values(0))))-1)
fpsj = etree.SubElement(fpxx, 'Fpsj')

for row in range(1, sh.nrows):
    val = sh.row_values(row)

#对0列的单据号字段内容进行相邻判断
    if sh.cell_value(row,0) !=sh.cell_value(row-1,0):
# 不一样则添加'主体+明细'

        fp = etree.SubElement(fpsj, 'Fp')
     
        etree.SubElement(fp, 'Djh').text = str(val[0])
        etree.SubElement(fp, 'Gfmc').text = str(val[1])
        etree.SubElement(fp, 'Gfsh').text = str(val[2])
        etree.SubElement(fp, 'Gfyhzh').text = str(val[3])
        etree.SubElement(fp, 'Gfdzdh').text = str(val[4])
        etree.SubElement(fp, 'Bz').text = str(val[5])
        etree.SubElement(fp, 'Fhr').text = str(val[6])
        etree.SubElement(fp, 'Skr').text = str(val[7])
        etree.SubElement(fp, 'Spbmbbh').text = str(val[8])
        etree.SubElement(fp, 'Hsbz').text = str(val[9])
     
        spxx = etree.SubElement(fp, 'Spxx')
        sph = etree.SubElement(spxx, 'Sph')
     
        etree.SubElement(sph, 'Xh').text = str(val[10])
        etree.SubElement(sph, 'Spmc').text = str(val[11])
        etree.SubElement(sph, 'Ggxh').text = str(val[12])
        etree.SubElement(sph, 'Jldw').text = str(val[13])
        etree.SubElement(sph, 'Spbm').text = str(val[14])
        etree.SubElement(sph, 'Qyspbm').text = str(val[15])
        etree.SubElement(sph, 'Syyhzcbz').text = str(val[16])
        etree.SubElement(sph, 'Lslbz').text = str(val[17])
        etree.SubElement(sph, 'Yhzcsm').text = str(val[18])
        etree.SubElement(sph, 'Dj').text = str(val[19])
        etree.SubElement(sph, 'Sl').text = str(val[20])
        etree.SubElement(sph, 'Je').text = str(val[21])
        etree.SubElement(sph, 'Slv').text = str(val[22])
        etree.SubElement(sph, 'Se').text = str(val[23])
        etree.SubElement(sph, 'Kce').text = str(val[24])
    else:     
# 一样只添加明细行
        sph = etree.SubElement(spxx, 'Sph')
     
        etree.SubElement(sph, 'Xh').text = str(val[10])
        etree.SubElement(sph, 'Spmc').text = str(val[11])
        etree.SubElement(sph, 'Ggxh').text = str(val[12])
        etree.SubElement(sph, 'Jldw').text = str(val[13])
        etree.SubElement(sph, 'Spbm').text = str(val[14])
        etree.SubElement(sph, 'Qyspbm').text = str(val[15])
        etree.SubElement(sph, 'Syyhzcbz').text = str(val[16])
        etree.SubElement(sph, 'Lslbz').text = str(val[17])
        etree.SubElement(sph, 'Yhzcsm').text = str(val[18])
        etree.SubElement(sph, 'Dj').text = str(val[19])
        etree.SubElement(sph, 'Sl').text = str(val[20])
        etree.SubElement(sph, 'Je').text = str(val[21])
        etree.SubElement(sph, 'Slv').text = str(val[22])
        etree.SubElement(sph, 'Se').text = str(val[23])
        etree.SubElement(sph, 'Kce').text = str(val[24])
         

# wrap it in an ElementTree instance, and save as XML
tree = etree.ElementTree(root)
#添加encoding才会自动在xml文件首行加<?xml version='1.0' encoding='GBK'?>
tree.write("filename.xml",encoding='gbk')

GUI

# -*- coding: utf-8 -*-
"""
Created on Mon May 27 14:44:07 2019
@author: James Dang

"""
from tkinter import *
from tkinter.ttk import *
from tkinter.filedialog import askopenfilename
from tkinter.filedialog import asksaveasfilename
from tkinter import messagebox
import os
import xlrd
from lxml import etree
from pathlib import Path
from time import sleep
from time import strftime
import sqlite3


my_window = Tk()

# 先定义窗口大小、位置，再放置名称、图标，不然会有小弹窗闪现
width_of_window = 550
height_of_window = 330

screen_width = my_window.winfo_screenwidth()
screen_height = my_window.winfo_screenheight()
x_coordinate = (screen_width/2) - (width_of_window/2)
y_coordinate = (screen_height/2) - (height_of_window/2)
my_window.geometry(f"{width_of_window}x{height_of_window}+{int(x_coordinate)}+{int(y_coordinate)}")
my_window.resizable(width=False, height=False)
my_window.title("国税XML接口软件V1.0")
my_window.iconbitmap(Path('img/dragon.ico').absolute())


# 选择文件
def open_file():
    global filename
    filename = askopenfilename(parent=my_window,
                               initialdir='C:/Tutorial',
                               title='选择表格文件',
                               filetypes=[('XLSX Document', '*.xlsx'),
                                          ('XLS Document', '*.xls'),
                                          ('All Files', '*.*')])
 
    var_1.set(Path(filename).absolute())


# 打开选择文件路径
def open_filedir():
    os.startfile(Path(filename).parent.absolute())


# 保存文件
def save_file():
    wb = xlrd.open_workbook(Path(filename).absolute())
    sh = wb.sheet_by_index(0)
    # build a tree structure
    root = etree.Element('Kp')
    #.text字段赋值，.set属性赋值
    etree.SubElement(root, 'Version').text = '2.0'
    # 有子标签的话必须要定义一个变量名
    fpxx = etree.SubElement(root, 'Fpxx')
    # 去重len(list(set(sh.col_values(0))))
    etree.SubElement(fpxx, 'Zsl').text = str(
        len(list(set(sh.col_values(0))))-1)
    fpsj = etree.SubElement(fpxx, 'Fpsj')
    for row in range(1, sh.nrows):   
        val = sh.row_values(row)
    #对0列的单据号字段内容进行相邻判断
        if sh.cell_value(row, 0) != sh.cell_value(row-1, 0):
            # 不一样则添加'主体+明细'
            fp = etree.SubElement(fpsj, 'Fp')
            etree.SubElement(fp, 'Djh').text = str(val[0])
            etree.SubElement(fp, 'Gfmc').text = str(val[1])
            etree.SubElement(fp, 'Gfsh').text = str(val[2])
            etree.SubElement(fp, 'Gfyhzh').text = str(val[3])
            etree.SubElement(fp, 'Gfdzdh').text = str(val[4])
            etree.SubElement(fp, 'Bz').text = str(val[5])
            etree.SubElement(fp, 'Fhr').text = str(val[6])
            etree.SubElement(fp, 'Skr').text = str(val[7])
            etree.SubElement(fp, 'Spbmbbh').text = str(val[8])
            etree.SubElement(fp, 'Hsbz').text = str(val[9])
            spxx = etree.SubElement(fp, 'Spxx')
            sph = etree.SubElement(spxx, 'Sph')
            etree.SubElement(sph, 'Xh').text = str(val[10])
            etree.SubElement(sph, 'Spmc').text = str(val[11])
            etree.SubElement(sph, 'Ggxh').text = str(val[12])
            etree.SubElement(sph, 'Jldw').text = str(val[13])
            # 调用fun_0函数补零         
            etree.SubElement(sph, 'Spbm').text = str("{:0<19d}".format(int(val[14])))       
            etree.SubElement(sph, 'Qyspbm').text = str(val[15])
            # round取整
            etree.SubElement(sph, 'Syyhzcbz').text = str(val[16])
            etree.SubElement(sph, 'Lslbz').text = str(val[17])
            etree.SubElement(sph, 'Yhzcsm').text = str(val[18])
            etree.SubElement(sph, 'Dj').text = str(val[19])
            etree.SubElement(sph, 'Sl').text = str(val[20])
            etree.SubElement(sph, 'Je').text = str(val[21])
            etree.SubElement(sph, 'Slv').text = str(val[22])
            etree.SubElement(sph, 'Se').text = str(val[23])
            etree.SubElement(sph, 'Kce').text = str(val[24])
        else:
            # 一样只添加明细行
            sph = etree.SubElement(spxx, 'Sph')
            etree.SubElement(sph, 'Xh').text = str(val[10])
            etree.SubElement(sph, 'Spmc').text = str(val[11])
            etree.SubElement(sph, 'Ggxh').text = str(val[12])
            etree.SubElement(sph, 'Jldw').text = str(val[13])
            etree.SubElement(sph, 'Spbm').text = str("{:0<19d}".format(int(val[14])))
            etree.SubElement(sph, 'Qyspbm').text = str(val[15])
            etree.SubElement(sph, 'Syyhzcbz').text = str(val[16])
            etree.SubElement(sph, 'Lslbz').text = str(val[17])
            etree.SubElement(sph, 'Yhzcsm').text = str(val[18])
            etree.SubElement(sph, 'Dj').text = str(val[19])
            etree.SubElement(sph, 'Sl').text = str(val[20])
            etree.SubElement(sph, 'Je').text = str(val[21])
            etree.SubElement(sph, 'Slv').text = str(val[22])
            etree.SubElement(sph, 'Se').text = str(val[23])
            etree.SubElement(sph, 'Kce').text = str(val[24])
    # wrap it in an ElementTree instance, and save as XML
    tree = etree.ElementTree(root)
    global savefile
    savefile = asksaveasfilename(filetypes=(('XML Files', '*.xml'),
                                            ("All Files", "*.*")),
                                 defaultextension=".xml",
                                 title='保存XML文件',
                                 initialfile=strftime('%Y%m%d%H%M%S'+'_vat.xml'))

# pretty_print=True 多行显示xml
    tree.write(savefile, encoding='gbk',pretty_print=True)
    messagebox.showinfo("恭喜", "成功转换XML文件并保存完成!")
 
    var_2.set(Path(savefile).absolute())
 
    total_num = int(len(list(set(sh.col_values(0))))-1)

    total_value = 0
    for row in range(1, sh.nrows):
        val = sh.row_values(row)
        total_value += float(val[21])

    var_3.set(f'本次共转换 {total_num} 条单据，合计金额(不含税)为 {total_value:.2f}')


# 打开保存文件路径
def save_filedir():
    os.startfile(Path(savefile).parent.absolute())


# 打开微信图片窗口
def wechat_pay():
    weixin_window = Toplevel(my_window)
    weixin_window.title("微信")
    width_of_window = 290
    height_of_window = 410
    screen_width = my_window.winfo_screenwidth()
    screen_height = my_window.winfo_screenheight()
    x_coordinate = (screen_width/2) - (width_of_window/2)
    y_coordinate = (screen_height/2) - (height_of_window/2)
    weixin_window.geometry("%dx%d+%d+%d" % (width_of_window, height_of_window, x_coordinate, y_coordinate))
    weixin_window.iconbitmap(Path('img/wechat.ico').absolute())
    img_wechat = PhotoImage(file=Path('img/wechat_pay.png').absolute())
    lable_1 = Label(weixin_window, image=img_wechat)
    lable_1.image = img_wechat
    lable_1.pack()
    btn1 = Button(weixin_window,text='关闭',command=weixin_window.destroy)
    btn1.pack(pady=10)


# 打开支付宝图片窗口
def alipay():
    zhifubao_window = Toplevel(my_window)
    zhifubao_window.title("支付宝")
    width_of_window = 290
    height_of_window = 410
    screen_width = my_window.winfo_screenwidth()
    screen_height = my_window.winfo_screenheight()
    x_coordinate = (screen_width/2) - (width_of_window/2)
    y_coordinate = (screen_height/2) - (height_of_window/2)
    zhifubao_window.geometry("{}x{}+{}+{}".format(width_of_window, height_of_window, int(x_coordinate), int(y_coordinate)))
    zhifubao_window.iconbitmap(Path('img/zhifubao.ico').absolute())
    img_alipay = PhotoImage(file=Path('img/alipay.png').absolute())
    lable_1 = Label(zhifubao_window, image=img_alipay)
    lable_1.image = img_alipay
    lable_1.pack()
    btn1 = Button(zhifubao_window,text='关闭',command=zhifubao_window.destroy)
    btn1.pack(pady=10)

 
#暂时不用
def about_zhixie():
    new_window = Toplevel(my_window)
    scrollbar = Scrollbar(new_window)
    scrollbar.pack(side = RIGHT, fill = Y )
    mylist = Listbox(new_window, yscrollcommand = scrollbar.set )
    for line in range(100):
       mylist.insert(END, "This is line number " + str(line))
    mylist.pack( side = LEFT, fill = BOTH )
    scrollbar.config(command = mylist.yview)
    for i in range(1000):
        mylist.yview(i)
        new_window.update()
        sleep(0.3)


# 龟叔介绍
def about_guido():
    new_window = Toplevel(my_window)
    text = Text(new_window,height=3)
    text.insert(INSERT, "Hello.....\n")
    text.insert(END, "Bye Bye.....")
    text.pack(side=TOP) 
    text.tag_add("here", "1.0", "1.4")
    text.tag_add("start", "1.8", "1.13")
    text.tag_config("here", background="yellow", foreground="blue")
    text.tag_config("start", background="black", foreground="green")
    text1 = Text(new_window, height=25, width=30)
    img_guido = PhotoImage(file=Path("img/guido.gif").absolute())
    text1.insert(END, '\n')
    text1.image_create(END, image=img_guido)
    text1.pack(side=LEFT)
    text2 = Text(new_window, height=25, width=50)
    scroll = Scrollbar(new_window, command=text2.yview)
    text2.configure(yscrollcommand=scroll.set)
    text2.tag_configure('bold_italics', font=('Arial', 12, 'bold', 'italic'))
    text2.tag_configure('big', font=('Verdana', 20, 'bold'))
    text2.tag_configure('color',
                        foreground='#476042',
                        font=('Tempus Sans ITC', 12, 'bold'))
    text2.tag_bind('follow',
                   '<1>',
                   lambda e, t=text2: t.insert(END, "Not now, maybe later!"))
    text2.insert(END,'\nThe Zen of Python\n', 'big')
    quote = """
    Beautiful is better than ugly.
    Explicit is better than implicit.
    Simple is better than complex.
    Complex is better than complicated.
    Flat is better than nested.
    Sparse is better than dense.
    Readability counts.
    Special cases aren't special enough to break the rules.
    Although practicality beats purity.
    Errors should never pass silently.
    Unless explicitly silenced.
    In the face of ambiguity, refuse the temptation to guess.
    There should be one-- and preferably only one --obvious way to do it.
    Although that way may not be obvious at first unless you're Dutch.
    Now is better than never.
    Although never is often better than *right* now.
    If the implementation is hard to explain, it's a bad idea.
    If the implementation is easy to explain, it may be a good idea.
    Namespaces are one honking great idea -- let's do more of those!
    """
    text2.insert(END, quote, 'color')
    text2.insert(END, 'follow-up\n', 'follow')
    text2.pack(side=LEFT)
    scroll.pack(side=RIGHT, fill=Y)
    new_window.mainloop()


def about_fun():
    messagebox.showinfo("关于",r"""版本: V 1.0.1
作者: James Dang
反馈邮箱：744351600@qq.com

        感谢您使用本软件，希望该软件给您提供了良好的开票效率，如
果实力允许，请赞助支持下作者！它将有助于软件的后续开发。""")


# 打开模板
def open_demo():
    os.startfile(Path('demo/模板.xlsx').absolute())


# 打开税收分类编码总表
def open_spbmzb():
    os.startfile(Path('doc/税收编码记录表33.0.xls').absolute())


# 打开税收分类编码总表
def open_help():
    os.startfile(Path('doc/help.chm').absolute())

# 打开接口规范
def open_gsjkgf():
    os.startfile(Path('doc/税控发票开票软件发票信息数据接口规范V4.0.doc').absolute())

# 商品编码检索
def search_spbm():
    spbm_window = Toplevel(my_window)
    spbm_window.title("商品编码检索")
    spbm_window.geometry("1000x450")
    spbm_window.iconbitmap(Path('img/dragon.ico').absolute())
 
    def search_view():
        connection = sqlite3.connect("spbmzb.db")
        cursor = connection.cursor()
        var = var_k1.get()
        tree.delete(*tree.get_children())
        cursor.execute("SELECT SPBM,SPMC,SPBMJC,ZZSSL,SM FROM spfwssflbm where SPMC LIKE (?) or SM LIKE (?) or GJZ LIKE (?)",('%'+var+'%','%'+var+'%','%'+var+'%'))
        rows = cursor.fetchall()
        for row in rows:
            var_n = '*' + row[2] +'*'+var
            tree.insert("", END, values=(row[0],row[1],var_n,row[3],row[4]))
     
        def onDBClick(event):
            text_1.delete('1.0', END)
            item = tree.selection()
 
         
            for key in tree.item(item,"values"):
                i='\n'+key+'\n\n'
                text_1.insert(END, i)

     
        tree.bind("<Double-1>", onDBClick)

   
        cursor.close()
        connection.close()

    frame1 = Frame(spbm_window)
    frame1.pack(side = LEFT,fill = BOTH)
 
    scrollBar = Scrollbar(frame1)
    scrollBar.pack(side= RIGHT, fill= Y)
 
    tree= Treeview(frame1, column=("column1", "column2", "column3", "column4","column5"), show='headings',
                       yscrollcommand = scrollBar.set)
    tree.heading("#1", text="税收分类编码")
    tree.heading("#2", text="税收分类名称")
    tree.heading("#3", text="开票商品名称")
    tree.heading("#4", text="税率")
    tree.heading("#5", text="说明")
    tree.column("#1",width=150,anchor="center")
    tree.column("#2",width=150,anchor="center")
    tree.column("#3",width=150,anchor="center")
    tree.column("#4",width=50,anchor="center")
    tree.column("#5",width=100,anchor="center")
    tree.pack(side= LEFT ,fill = Y )
 
    scrollBar.config(command=tree.yview)
 
    frame2 = Frame(spbm_window)
    frame2.pack(side = LEFT,fill = BOTH)

    label_1 = Label(frame2,text='请输入货物名称\n      关键字：')
    label_1.grid(row=1,column=0,pady=10,sticky=W,padx=10)
 
    global var_k1
    var_k1 = StringVar()
    print(var_k1)
    entry_1 = Entry(frame2,textvariable=var_k1,width=30)
    entry_1.focus_force()
    entry_1.grid(row=1,column=1,sticky=W,padx=10)
     
    button = Button(frame2,text='检索',command=search_view)
    button.grid(row=2,column=1,pady=10)
 
    label_2 = Label(frame2,text='<<双击左边检索结果\n\n       右边显示详情>>')
    label_2.grid(row=3,column=0)
 
    scrollBar = Scrollbar(frame2)
    scrollBar.grid(row=3,column=2,sticky=NS)
 
    text_1 = Text(frame2,width=30,yscrollcommand = scrollBar.set)
    text_1.grid(row=3,column=1)
    scrollBar.config(command=text_1.yview)


# 主放置框架
frame = Frame(my_window,borderwidth=1,relief='groove')
#frame.pack(padx=5,pady=5)
frame.pack(fill=BOTH)

# 框架中第1行放置空白标签，控制上下空间
label_1 = Label(frame)
label_1.grid(row=1,columnspan=3,pady=15)

# 框架中第2行放置标签框、输入框、按钮
label_2 = Label(frame)
label_2.grid(row=2, column=0,sticky='E',padx=10)
label_2["text"] = '输入路径：'
var_1 = StringVar()
entry_1 = Entry(frame,textvariable=var_1,width=45)
entry_1.grid(row=2,column=1)
entry_1.focus_force()
btn1 = Button(frame, text='① 选择单据表格', command=lambda: open_file())
btn1.grid(row=2, column=2,padx=10)

# 框架中第3行放置按钮
btn2 = Button(frame, text='打开输入文件夹', command=lambda: open_filedir())
btn2.grid(row=3, column=1,sticky='W',pady=10)

# 框架中第4行放置空白标签，控制上下空间
label_3 = Label(frame)
label_3.grid(row=4,columnspan=3,pady=10)

# 框架中第5行放置标签框、输入框、按钮
label_4 = Label(frame)
label_4.grid(row=5, column=0,sticky='E',padx=10)
label_4["text"] = '输出路径：'
var_2 = StringVar()
entry_2 = Entry(frame,textvariable=var_2,width=45)
entry_2.grid(row=5,column=1)
btn3 = Button(frame, text='② 保存XML文件', command=lambda: save_file())
btn3.grid(row=5, column=2,padx=10)

# 框架中第6行放置空白标签，控制上下空间
btn4 = Button(frame, text='打开输出文件夹', command=lambda: save_filedir())
btn4.grid(row=6, column=1,sticky='W',pady=10)

# 框架中第7行放置标签，显示转换结果
var_3 = StringVar()
label_5 = Label(frame,textvariable=var_3,foreground='red',
                font="Times 15 bold")
label_5.grid(row=7,columnspan=3,pady=20)


# 创建菜单栏
#frame1 = Frame(my_window,borderwidth=5,relief='groove')
#frame1.pack(padx=5,pady=5)
#menubar = Menu(frame1)
menubar = Menu(my_window)

# create a pulldown filemenu, and add it to the menubar
# tearoff=1 允许您分离主窗口的菜单，创建浮动菜单
# 单击顶部菜单项时，顶部会显示虚线。如果单击虚线，菜单会变为浮动状态
filemenu = Menu(menubar, tearoff=0)
filemenu.add_command(label='打开表格模板',command=open_demo)
filemenu.add_command(label='选择单据表格', command=open_file)
filemenu.add_command(label='保存XML文件', command=save_file)
filemenu.add_separator()
filemenu.add_command(label='退出', command=my_window.destroy)
# cascade 级联
menubar.add_cascade(label='文件', menu=filemenu)


# 添加商品编码
spbmmenu = Menu(menubar,tearoff=0)
spbmmenu.add_command(label='商品编码检索',command=search_spbm)
spbmmenu.add_command(label='商品编码总表',command=open_spbmzb)
menubar.add_cascade(label='商品编码', menu=spbmmenu)

# 添加赞助菜单
donatemenu = Menu(menubar, tearoff=0)
donatemenu.add_command(label='微信', command=wechat_pay)
donatemenu.add_command(label='支付宝', command=alipay)
menubar.add_cascade(label='赞助', menu=donatemenu)

# 添加帮助菜单
helpmenu = Menu(menubar, tearoff=0)
# 二级菜单
#submenu = Menu(helpmenu, tearoff=0)
#
#submenu.add_command(label='用户手册',command=None)
#submenu.add_command(label='国税接口规范',command=open_gsjkgf)
#helpmenu.add_cascade(label='文档',menu=submenu)
helpmenu.add_command(label='帮助', command=open_help)
helpmenu.add_separator()
helpmenu.add_command(label='关于', command=about_fun)
menubar.add_cascade(label='帮助', menu=helpmenu)

# display Menu
my_window.config(menu=menubar)

my_window.mainloop()