*.xml <==> *.xlsx

excel2xml.bat

@echo off
set xmldir="xmldir"
set exceldir="exceldir"
set method=e2x
xmlexcel.py %method% %xmldir% %exceldir%
if %errorlevel% NEQ 0 (
   pause
)

test.xml

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<static_martial_shop xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
	<table>
		<id>1</id>
		<need>[{"Id":120022,"Num":800,"Type":1,"IsCost":1}]</need>
		<award>[{"Id":412007,"Num":1,"Type":1}]</award>
	</table>
	<table>
		<id>2</id>
		<need>[{"Id":120023,"Num":1000,"Type":1,"IsCost":1}]</need>
		<award>[{"Id":412007,"Num":1,"Type":1}]</award>
	</table>
</static_martial_shop>

xml2excel.bat

@echo off
set xmldir="xmldir"
set exceldir="exceldir"
set method=x2e
xmlexcel.py %method% %xmldir% %exceldir%
if %errorlevel% NEQ 0 (
   pause
)

xmlexcel.py

# -*- coding: utf-8 -*-
# requed:
# https://pypi.python.org/pypi/pywin32
# excel
# excel com api
# https://msdn.microsoft.com/en-us/library/office/ff838613.aspx
# http://pythonexcels.com/python-excel-mini-cookbook/
# 
import argparse
import os, os.path
import shutil
import win32com.client
import xml.etree.ElementTree as ET

def makedirs(path):
    if not os.path.isdir(path):
        os.makedirs(path)

def writefile(filename, data, mode = 'w'):
    dir = os.path.dirname(filename)
    if len(dir) > 0 and not os.path.exists(dir):
        os.makedirs(dir)
    with open(filename, mode) as f:
        f.write(data)
        f.close()

def removefile(filename):
    if os.path.isfile(filename):
        os.remove(filename)

def xml2excel(xmlpath, excelpath, xlApp):
    print(xmlpath)
    removefile(excelpath)

    xml = ET.parse(xmlpath)
    xml = xml.getroot()
    row = len(xml)
    if row == 0:
        print("can not export empty xml to excel " + xmlpath)
        return
    # if row != 1:
    #     xlBook = xlApp.Workbooks.OpenXML(os.path.abspath(xmlpath), LoadOption = 2)
    # else:
    xlBook = xlApp.Workbooks.Add()
    xlws = xlBook.Worksheets(1)
    oneRows = xml[0]
    col = len(oneRows)
    # print(len(xmls), xmlpath)
    # l = len(xmls)
    # xmls[0]
    # row = len(cols)
    for j in xrange(1, col+1):
        # print(oneRows[j - 1].tag)
        # print(xlws.Cells(1, j))
        xlws.Cells(1, j).Value = oneRows[j - 1].tag
    for i in xrange(2, row + 2):
        oneRows = xml[i - 2]
        for j in xrange(1, col + 1):
            xlws.Cells(i, j).Value = oneRows[j - 1].text

    xlBook.SaveAs(os.path.abspath(excelpath))
    xlBook.Close()

def indent(elem, level=0, isLast = False):
    i = "\n" + level*"\t"
    j = "\n" + (level-1)*"\t"
    l = len(elem)
    if l:
        if not elem.text or not elem.text.strip():
            elem.text = i + "\t"
        if isLast:
            elem.tail = j
        elif not elem.tail or not elem.tail.strip():
            elem.tail = i
        ii = 0
        for subelem in elem:
            ii = ii + 1
            indent(subelem, level + 1, ii == l)
        # if not elem.tail or not elem.tail.strip():
        #     elem.tail = j
    else:
        if isLast:
            elem.tail = j
        elif level and (not elem.tail or not elem.tail.strip()):
            elem.tail = i
    return elem 

xlDown = -4121
xlLeft = -4159
xlRight = -4161
xlUp = -4162
xmldeclaration = """<?xml version="1.0" encoding="UTF-8" standalone="yes"?>\n"""

def tryint(v):
    if isinstance(v, float) and int(v) == v:
        return int(v)
    return v

def excel2xml(excelpath, xmlpath, xlApp):
    print(excelpath)
    xlBook = xlApp.Workbooks.OpenXML(os.path.abspath(excelpath), LoadOption = 2)
    xlws = xlBook.Worksheets(1)
    xlRange = xlws.UsedRange
    col = xlRange.End(xlRight).Column
    row = xlRange.End(xlDown).Row
    if row <= 1:
        print('!!!!!empty excel!!!!!')
        xlBook.Close()
        return
    # print(col, row)
    xlws.Columns.AutoFit()
    tiles = []
    for i in xrange(1, col + 1):
        tiles.append(xlws.Cells(1, i).Text)

    xml = ET.Element(os.path.basename(xmlpath)[0:-4])
    xml.set('xmlns:xsi', 'http://www.w3.org/2001/XMLSchema-instance')
    for i in xrange(2, row + 1):
        table = ET.SubElement(xml, 'table')
        for j in range(1, col + 1):
            one = ET.SubElement(table, tiles[j - 1])
            one.text = xlws.Cells(i, j).Text
    xlBook.Close(SaveChanges = False)
    indent(xml)
    data = ET.tostring(xml, 'utf-8')
    writefile(xmlpath, xmldeclaration + data)

def xml2excels(xmldir, exceldir):
    makedirs(exceldir)
    xlApp = win32com.client.Dispatch('Excel.Application')
    oldVisible = xlApp.Visible
    xlApp.Visible = True
    for root, dirs, files in os.walk(xmldir):
        for f in files:
            if f.endswith('.xml'):
                xmlpath = os.path.join(root, f)
                relpath = os.path.relpath(xmlpath, xmldir)
                excelpath = os.path.join(exceldir, relpath[:-3] + 'xlsx')
                xml2excel(xmlpath, excelpath, xlApp)
    xlApp.Visible = oldVisible

def excel2xmls(exceldir, xmldir):
    makedirs(xmldir)
    xlApp = win32com.client.Dispatch('Excel.Application')
    oldVisible = xlApp.Visible
    xlApp.Visible = True
    for root, dirs, files in os.walk(exceldir):
        for f in files:
            if f.endswith('.xlsx'):
                excelpath = os.path.join(root, f)
                relpath = os.path.relpath(excelpath, exceldir)
                xmlpath = os.path.join(xmldir, relpath[:-4] + 'xml')
                excel2xml(excelpath, xmlpath, xlApp)
    xlApp.Visible = oldVisible

# -------------- main ----------------
if __name__ == '__main__':
    parser = argparse.ArgumentParser(usage='%(prog)s <method> <xmldir> <exceldir>',
        description='.xml <==> .xlsx\nexport xml  files to xlsx\nexport xlsx files to xml',
        formatter_class=argparse.RawTextHelpFormatter)
    parser.add_argument('method', choices=['x2e', 'e2x'],
        help='x2e: xml to excel\ne2x: excel to xml')
    parser.add_argument('xmldir',
        help='xml files directory')
    parser.add_argument('exceldir',
        help='excel files directory')

    args = parser.parse_args()

    if args.method == 'x2e':
        xml2excels(args.xmldir, args.exceldir)
    elif args.method == 'e2x':
        excel2xmls(args.exceldir, args.xmldir)
    

xmlexcel2.py

# -*- coding: utf-8 -*-
# requed:
# https://openpyxl.readthedocs.io/en/default/
# 
import argparse
import os, os.path
import xml.etree.ElementTree as ET
from openpyxl import Workbook, load_workbook

def makedirs(path):
    if not os.path.isdir(path):
        os.makedirs(path)

def writefile(filename, data, mode = 'w'):
    dir = os.path.dirname(filename)
    if len(dir) > 0 and not os.path.exists(dir):
        os.makedirs(dir)
    with open(filename, mode) as f:
        f.write(data)
        f.close()

def removefile(filename):
    if os.path.isfile(filename):
        os.remove(filename)

def xml2excel(xmlpath, excelpath):
    print(xmlpath)
    removefile(excelpath)

    xml = ET.parse(xmlpath)
    xml = xml.getroot()
    row = len(xml)
    if row == 0:
        print("can not export empty xml to excel " + xmlpath)
        return

    wb = Workbook()
    ws = wb.active
    oneRows = xml[0]
    col = len(oneRows)
    # print(len(xmls), xmlpath)
    # l = len(xmls)
    # xmls[0]
    # row = len(cols)
    for j in xrange(1, col+1):
        # print(oneRows[j - 1].tag)
        # print(ws.cell(1, j))
        ws.cell(row = 1, column = j, value = oneRows[j - 1].tag)
    for i in xrange(2, row + 2):
        oneRows = xml[i - 2]
        for j in xrange(1, col + 1):
            ws.cell(row = i, column = j, value = oneRows[j - 1].text)

    wb.save(os.path.abspath(excelpath))

def indent(elem, level=0, isLast = False):
    i = "\n" + level*"\t"
    j = "\n" + (level-1)*"\t"
    l = len(elem)
    if l:
        if not elem.text or not elem.text.strip():
            elem.text = i + "\t"
        if isLast:
            elem.tail = j
        elif not elem.tail or not elem.tail.strip():
            elem.tail = i
        ii = 0
        for subelem in elem:
            ii = ii + 1
            indent(subelem, level + 1, ii == l)
        # if not elem.tail or not elem.tail.strip():
        #     elem.tail = j
    else:
        if isLast:
            elem.tail = j
        elif level and (not elem.tail or not elem.tail.strip()):
            elem.tail = i
    return elem 

xmldeclaration = """<?xml version="1.0" encoding="UTF-8" standalone="yes"?>\n"""

def tryint(v):
    if isinstance(v, float) and int(v) == v:
        return int(v)
    return v

def excel2xml(excelpath, xmlpath):
    print(excelpath)
    wb = load_workbook(os.path.abspath(excelpath))
    ws = wb.active
    col = ws.max_column
    row = ws.max_row
    if row <= 1:
        print('!!!!!empty excel!!!!!')
        return
    # print(col, row)
    # ws.Columns.AutoFit()
    tiles = []
    for i in xrange(1, col + 1):
        tiles.append(ws.cell(row = 1, column = i).value)

    xml = ET.Element(os.path.basename(xmlpath)[0:-4])
    xml.set('xmlns:xsi', 'http://www.w3.org/2001/XMLSchema-instance')
    for i in xrange(2, row + 1):
        table = ET.SubElement(xml, 'table')
        for j in range(1, col + 1):
            one = ET.SubElement(table, tiles[j - 1])
            one.text = ws.cell(row = i, column = j).value
    indent(xml)
    data = ET.tostring(xml, 'utf-8')
    writefile(xmlpath, xmldeclaration + data)

def xml2excels(xmldir, exceldir):
    makedirs(exceldir)
    for root, dirs, files in os.walk(xmldir):
        for f in files:
            if f.endswith('.xml'):
                xmlpath = os.path.join(root, f)
                relpath = os.path.relpath(xmlpath, xmldir)
                excelpath = os.path.join(exceldir, relpath[:-3] + 'xlsx')
                xml2excel(xmlpath, excelpath)

def excel2xmls(exceldir, xmldir):
    makedirs(xmldir)
    for root, dirs, files in os.walk(exceldir):
        for f in files:
            if f.endswith('.xlsx'):
                excelpath = os.path.join(root, f)
                relpath = os.path.relpath(excelpath, exceldir)
                xmlpath = os.path.join(xmldir, relpath[:-4] + 'xml')
                excel2xml(excelpath, xmlpath)

# -------------- main ----------------
if __name__ == '__main__':
    parser = argparse.ArgumentParser(usage='%(prog)s <method> <xmldir> <exceldir>',
        description='.xml <==> .xlsx\nexport xml  files to xlsx\nexport xlsx files to xml',
        formatter_class=argparse.RawTextHelpFormatter)
    parser.add_argument('method', choices=['x2e', 'e2x'],
        help='x2e: xml to excel\ne2x: excel to xml')
    parser.add_argument('xmldir',
        help='xml files directory')
    parser.add_argument('exceldir',
        help='excel files directory')

    args = parser.parse_args()

    if args.method == 'x2e':
        xml2excels(args.xmldir, args.exceldir)
    elif args.method == 'e2x':
        excel2xmls(args.exceldir, args.xmldir)