apacsys-vaporrecoverymonitor/gunreport/GunHandler/GunHandler.py

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

import sys
sys.path.append("C:\Python\Python36\Lib")
import socket

# python3
import configparser

import win32api,win32con

start = time.time()


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





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

fn = 'C:\\Tokheim\\枪报表' + root.get('attrtime') + '.xlsx'

sectionname = 'GunReport'
cfgpath = "C:\\Tokheim\\plugs\\" + sectionname + "cfg.ini"

workbook = xlsxwriter.Workbook(fn)

try:
    workbook.close()
except:
    state = "2"
    conf = configparser.ConfigParser()
    conf.add_section(sectionname)
    conf.set(sectionname, "State", state)
    conf.write(open(cfgpath, "w"))
    exit(1)


workbook = xlsxwriter.Workbook(fn)

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


format1 = workbook.add_format()

format2 = workbook.add_format()
format2.set_fg_color('#d9d9d9')

recordcount = 0



for index, oilgun in enumerate(root):
    sheetoil = workbook.add_worksheet(oilgun.get('oilnum') + '号枪报表')
    sheetgrid = 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(0, 0, "油气回收在线监测枪数据表")
    sheetoil.write(2, 0, '枪号')
    sheetoil.write(2, 1, int(oilgun.get('oilnum')))

    sheetoil.set_column('A:A', len('2019-00-00 00:00:00') + 1)
    sheetoil.set_column('AO:AO', len('2019-00-00 00:00:00') + 1)

    sheetoil.set_column(11, 11, None, None, {'hidden': 1})
    sheetoil.set_column(12, 12, None, None, {'hidden': 1})
    sheetoil.set_column(13, 13, None, None, {'hidden': 1})
    sheetoil.set_column(14, 14, None, None, {'hidden': 1})
	
    sheetoil.set_column(50, 50, None, None, {'hidden': 1})
    sheetoil.set_column(51, 51, None, None, {'hidden': 1})


    sheetoil.write(4, 0, '时间')
    sheetoil.write(4, 1, '计数')
    sheetoil.write(4, 2, '有效计数')
    sheetoil.write(4, 3, 'A/L超标计数')
    sheetoil.write(4, 4, 'A/L')
    sheetoil.write(4, 5, '油体积')
    sheetoil.write(4, 6, '气体积')
    sheetoil.write(4, 7, '油流速')
    sheetoil.write(4, 8, '气流速')
    sheetoil.write(4, 9, '管线压力')
    sheetoil.write(4, 10, '油罐压力')
    sheetoil.write(4, 11, '气压力')
    sheetoil.write(4, 12, 'PWM')
    sheetoil.write(4, 13, 'VCC错误信息')
    sheetoil.write(4, 14, '流量计错误')


    sheetoil.write(4, 40, '时间')
    sheetoil.write(4, 41, '有效计数')
    sheetoil.write(4, 42, 'A/L超标计数')
    sheetoil.write(4, 43, 'A/L')
    sheetoil.write(4, 44, '油体积')
    sheetoil.write(4, 45, '气体积')
    sheetoil.write(4, 46, '油流速')
    sheetoil.write(4, 47, '气流速')
    sheetoil.write(4, 48, '管线压力')
    sheetoil.write(4, 49, '油罐压力')
    sheetoil.write(4, 50, '气压力')
    sheetoil.write(4, 51, 'PWM')

    sheetoil.write(4, 60, 'A/L')
    sheetoil.write(4, 61, '油流速')
    sheetoil.write(4, 62, '气流速')


    count = 0
    for guncol in oilgun:
        validcounts = 0
        validflag = 0
        avicounts = 0
        count = 0
        for index, gunele in enumerate(guncol):
            recordcount = recordcount + 1
            timetxt = gunele.find('time').text
            isfalse = gunele.find('isfalse').text
            al = float(gunele.find('al').text)
            liquidvl = float(gunele.find('liquidvl').text)
            vaporvl = float(gunele.find('vaporvl').text)
            liquidfr = float(gunele.find('liquidfr').text)
            vaporfr = float(gunele.find('vaporfr').text)
            yz = float(gunele.find('yz').text)
            tankpress = float(gunele.find('tankpress').text)
            gaspress = float(gunele.find('gaspress').text)
            pwm = float(gunele.find('pwm').text)
            vccerror = gunele.find('vccerror').text
            errornum =  gunele.find('errornum').text

            if liquidvl >= 15:
                validflag = 1
            else:
                validflag = 0;

            sheetoil.write(index + 5, 1, index + 1)
            if validflag == 1:
                sheetoil.write(index + 5, 2, validcounts+1)

            tmpformat = format1
            if validflag == 0:
                tmpformat = format2

            sheetoil.write_number(index + 5, 4, al,tmpformat)
            sheetoil.write(index + 5, 5, liquidvl,tmpformat)
            sheetoil.write(index + 5, 6, vaporvl,tmpformat)
            sheetoil.write(index + 5, 7, liquidfr,tmpformat)
            sheetoil.write(index + 5, 8, vaporfr,tmpformat)
            sheetoil.write(index + 5, 9, yz,tmpformat)
            sheetoil.write(index + 5, 10, tankpress,tmpformat)
            sheetoil.write(index + 5, 11, gaspress,tmpformat)
            sheetoil.write(index + 5, 12, pwm,tmpformat)
            sheetoil.write(index + 5, 13, vccerror,tmpformat)
            sheetoil.write(index + 5, 14, errornum,tmpformat)


            if validflag == 1:
                sheetoil.write(validcounts + 5, 41, validcounts+1)
                sheetoil.write_number(validcounts + 5, 43, al)
                sheetoil.write(validcounts + 5, 44, liquidvl)
                sheetoil.write(validcounts + 5, 45, vaporvl)
                sheetoil.write(validcounts + 5, 46, liquidfr)
                sheetoil.write(validcounts + 5, 47, vaporfr)
                sheetoil.write(validcounts + 5, 48, yz)
                sheetoil.write(validcounts + 5, 49, tankpress)
                sheetoil.write(validcounts + 5, 50, gaspress)
                sheetoil.write(validcounts + 5, 51, pwm)

                sheetoil.write_number(validcounts + 5, 60, al * 1000)
                sheetoil.write(validcounts + 5, 61, liquidfr * 100)
                sheetoil.write(validcounts + 5, 62, vaporfr * 100)



            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 validflag == 1:
                sheetoil.write(validcounts + 5, 40, date_time.strftime('%Y-%m-%d %H:%M:%S'))

            count = count + 1

            if validflag == 1:
                if isfalse == 'Y':
                    avicounts = avicounts + 1
                    sheetoil.write(index + 5, 3, avicounts)
                    sheetoil.write(validcounts + 5, 42, avicounts)
                else:
                    sheetoil.write(index + 5, 3, 0)
                    sheetoil.write(validcounts + 5, 42, 0)

            if validflag == 1:
                validcounts = validcounts + 1

    namerow = 4
    frow = 5
    lrow = validcounts + 5 -1
    countcol = 1


    chartcol = 3
    chart1 = workbook.add_chart({'type': 'scatter'})


    # Configure second series. Note use of alternative syntax to define ranges.
    chart1.add_series({
        'name': [oilgun.get('oilnum') + '号枪报表', namerow, 43],
        'categories': [oilgun.get('oilnum') + '号枪报表', frow, countcol, lrow, countcol],
        'values': [oilgun.get('oilnum') + '号枪报表', frow, 43, lrow, 43],
        'marker': {
            'type': 'plus',
            'size': 5,
        },
    })

    # Add a chart title and some axis labels.
    chart1.set_title({'name': '气液比'})
    chart1.set_x_axis({'name': '计数'})
    chart1.set_y_axis({'name': '数据'})

    # Set an Excel chart style.
    chart1.set_style(11)

    # Insert the chart into the worksheet (with an offset).
    sheetgrid.insert_chart('A1', chart1, {'x_offset': 5, 'y_offset': 5})


    chart2 = workbook.add_chart({'type': 'scatter'})
    chart2.add_series({
        'name': [oilgun.get('oilnum') + '号枪报表', namerow, 46],
        'categories': [oilgun.get('oilnum') + '号枪报表', frow, countcol, lrow, countcol],
        'values': [oilgun.get('oilnum') + '号枪报表', frow, 46, lrow, 46],
        'line': {'color': 'blue',
                 'width': 1},
        'marker': {
            'type': 'none',
            'size': 5,
        },
    })
    chart2.set_title({'name': '油流速'})
    chart2.set_x_axis({'name': '计数'})
    chart2.set_y_axis({'name': '数据'})
    chart2.set_style(11)
    sheetgrid.insert_chart('I1', chart2, {'x_offset': 5, 'y_offset': 5})

    chart3 = workbook.add_chart({'type': 'scatter'})
    chart3.add_series({
        'name': [oilgun.get('oilnum') + '号枪报表', namerow, 47],
        'categories': [oilgun.get('oilnum') + '号枪报表', frow, countcol, lrow, countcol],
        'values': [oilgun.get('oilnum') + '号枪报表', frow, 47, lrow, 47],
        'line': {'color': '#037b7e',
                 'width': 1},
        'marker': {
            'type': 'none',
            'size': 5,
        },
    })
    chart3.set_title({'name': '气流速'})
    chart3.set_x_axis({'name': '计数'})
    chart3.set_y_axis({'name': '数据'})
    chart3.set_style(11)
    sheetgrid.insert_chart('A16', chart3, {'x_offset': 5, 'y_offset': 5})

    chart4 = workbook.add_chart({'type': 'scatter'})
    # chart4.set_size({'width': 650, 'height': 480})
    chart4.add_series({
        'name': [oilgun.get('oilnum') + '号枪报表', namerow, 60],
        'categories': [oilgun.get('oilnum') + '号枪报表', frow, countcol, lrow, countcol],
        'values': [oilgun.get('oilnum') + '号枪报表', frow, 60, lrow, 60],
        'marker': {
            'type': 'plus',
            'size': 5,
        },
    })

    chart4.add_series({
        'name': [oilgun.get('oilnum') + '号枪报表', namerow, 61],
        'categories': [oilgun.get('oilnum') + '号枪报表', frow, countcol, lrow, countcol],
        'values': [oilgun.get('oilnum') + '号枪报表', frow, 61, lrow, 61],
        'line': {'color': 'blue',
                 'width': 1},
        'marker': {
            'type': 'none',
            'size': 5,
        },
    })

    chart4.add_series({
        'name': [oilgun.get('oilnum') + '号枪报表', namerow, 62],
        'categories': [oilgun.get('oilnum') + '号枪报表', frow, countcol, lrow, countcol],
        'values': [oilgun.get('oilnum') + '号枪报表', frow, 62, lrow, 62],
        'line': {'color': '#037b7e',
                 'width': 1},
        'marker': {
            'type': 'none',
            'size': 5,
        },
    })

    chart4.set_title({'name': '气液比、油流速、气流速'})
    chart4.set_x_axis({'name': '计数'})
    chart4.set_y_axis({'name': '数据'})
    chart4.set_style(12)
    sheetgrid.insert_chart('I16', chart4, {'x_offset': 5, 'y_offset': 5})


    chart5 = workbook.add_chart({'type': 'scatter'})
    chart5.add_series({
        'name': [oilgun.get('oilnum') + '号枪报表', namerow, 48],
        'categories': [oilgun.get('oilnum') + '号枪报表', frow, countcol, lrow, countcol],
        'values': [oilgun.get('oilnum') + '号枪报表', frow, 48, lrow, 48],
        'line': {'color': '#037b7e',
                 'width': 1},
        'marker': {
            'type': 'none',
            'size': 5,
        },
    })
    chart5.set_title({'name': '管线压力'})
    chart5.set_x_axis({'name': '计数'})
    chart5.set_y_axis({'name': '数据'})
    chart5.set_style(11)
    sheetgrid.insert_chart('A32', chart5, {'x_offset': 5, 'y_offset': 5})


state = "0"

try:
    workbook.close()
    state = "1"
except:
    state = "2"


end = time.time()
stime = end -start
print(stime)
print(recordcount)
print(state)


conf = configparser.ConfigParser()
conf.add_section(sectionname)
conf.set(sectionname, "State", state)
conf.set(sectionname, "time", str(stime))
conf.set(sectionname, "count", str(recordcount))
conf.write(open(cfgpath, "w"))