# -*- 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)