#coding=utf-8
import os
import xml.etree.ElementTree as ET
import xlwt
from xlwt import Workbook, XFStyle, Borders, Pattern, Font
import  datetime
import time
import xlsxwriter


def getHeaderStyle():
    return xlwt.easyxf('font:height 720;')  # 36pt,类型小初的字号


def getHeaderStyleFont():
    fnt = Font()
    fnt.height = 400
    fnt.bold = True
    style = XFStyle()
    style.font = fnt
    return style

def gettitlestyle():
    fnt = Font()
    fnt.bold = True
    alignment = xlwt.Alignment()  # Create Alignment
    alignment.horz = xlwt.Alignment.HORZ_CENTER  # May be: HORZ_GENERAL, HORZ_LEFT, HORZ_CENTER, HORZ_RIGHT, HORZ_FILLED, HORZ_JUSTIFIED, HORZ_CENTER_ACROSS_SEL, HORZ_DISTRIBUTED
    alignment.vert = xlwt.Alignment.VERT_CENTER  # May be: VERT_TOP, VERT_CENTER, VERT_BOTTOM, VERT_JUSTIFIED, VERT_DISTRIBUTED
    style = XFStyle()
    style.alignment = alignment  # Add Alignment to Style
    style.font = fnt
    return style

def getcontentstyle():
    fnt = Font()
    alignment = xlwt.Alignment()  # Create Alignment
    alignment.horz = xlwt.Alignment.HORZ_CENTER  # May be: HORZ_GENERAL, HORZ_LEFT, HORZ_CENTER, HORZ_RIGHT, HORZ_FILLED, HORZ_JUSTIFIED, HORZ_CENTER_ACROSS_SEL, HORZ_DISTRIBUTED
    alignment.vert = xlwt.Alignment.VERT_CENTER  # May be: VERT_TOP, VERT_CENTER, VERT_BOTTOM, VERT_JUSTIFIED, VERT_DISTRIBUTED
    style = XFStyle()
    style.alignment = alignment  # Add Alignment to Style
    style.font = fnt
    return style


workbook = xlsxwriter.Workbook('demo.xlsx')



tree = ET.parse('C:\\Tokheim\\xml\\GunReport.xml')
root = tree.getroot()
tag = root.tag



borders = Borders()
borders.left = Borders.THICK
borders.right = Borders.THICK
borders.top = Borders.THICK
borders.bottom = Borders.THICK
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 0x0A
style = XFStyle()
style.borders = borders
headerstyle = getHeaderStyle()
headerStyleFont = getHeaderStyleFont()
titlestyle = gettitlestyle()
contentstyle = getcontentstyle()

for index,oilgun in enumerate(root):
    sheetoil = workbook.add_worksheet(oilgun.get('oilnum')+'号枪报表')

    first_row = sheetoil.row(0)
    first_row.set_style(headerstyle)

    sheetoil.write_merge(0,0,0,25,"油气回收在线监测枪数据表",headerStyleFont)
    col1 = sheetoil.col(1)
    col1.width = 256 * 20

    first_col = sheetoil.col(0)
    first_col.width = 256 * 20

    col2 = sheetoil.col(2)
    col2.width = 256 * 15

    sheetoil.write(2, 0, '加油机编号',titlestyle)
    sheetoil.write(2, 1, oilgun.get('oilnum'))

    sheetoil.write(4, 0, '时间',titlestyle)
    sheetoil.write(4, 1, '有效计数',titlestyle)
    sheetoil.write(4, 2, 'A/L超标计数',titlestyle)
    sheetoil.write(4, 3, 'A/L',titlestyle)
    sheetoil.write(4, 4, '油体积', titlestyle)
    sheetoil.write(4, 5, '气体积', titlestyle)
    for guncol in oilgun:
        alcounts = 0
        avicounts = 0
        for index,gunele in enumerate(guncol):
            timetxt = gunele.find('time').text
            isfalse = gunele.find('isfalse').text
            al = gunele.find('al').text
            liquidvl  = gunele.find('liquidvl').text
            vaporvl = gunele.find('vaporvl').text
            sheetoil.write(index + 5, 1, index+1, contentstyle)
            sheetoil.write(index+5, 3, al,contentstyle)
            sheetoil.write(index + 5, 4, liquidvl,contentstyle )
            sheetoil.write(index + 5, 5, vaporvl, contentstyle)
            date_time = datetime.datetime.strptime(timetxt, '%Y%m%d%H%M%S')
            sheetoil.write(index+5, 0, date_time.strftime('%Y-%m-%d %H:%M:%S'))
            if isfalse == 'Y':
                avicounts = avicounts+1
                sheetoil.write(index+5, 2,avicounts ,contentstyle)
            else:
                sheetoil.write(index + 5, 2,0, contentstyle)

    workbook.save('c:\\tokheim\\'+'枪报表'+root.get('attrtime')+'.xls')