Skip to content

Instantly share code, notes, and snippets.

@litefeel litefeel/excel2xml.bat

Last active Jun 19, 2017
Embed
What would you like to do?
*.xml <==> *.xlsx
@echo off
set xmldir="xmldir"
set exceldir="exceldir"
set method=e2x
xmlexcel.py %method% %xmldir% %exceldir%
if %errorlevel% NEQ 0 (
pause
)
<?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>
@echo off
set xmldir="xmldir"
set exceldir="exceldir"
set method=x2e
xmlexcel.py %method% %xmldir% %exceldir%
if %errorlevel% NEQ 0 (
pause
)
# -*- 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)
# -*- 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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.