apacsys-vaporrecoverymonitor/gunreport/TankHandler/TankHandler.py

#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

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

import configparser
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


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



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


#workbook = xlsxwriter.Workbook('c:\\tokheim\\'+'环境数据报表'+root.get('attrtime')+'.xlsx')
 

fn = 'C:\\Tokheim\\环境数据报表' + root.get('attrtime') + '.xlsx'

sectionname = 'TankReport'
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)

recordcount = 0


for index,oilgun in enumerate(root):
    sheetoil = workbook.add_worksheet('环境数据报表')
    sheetgrid = workbook.add_worksheet('环境数据图表')

    sheetoil.write(0,0,"油气回收在线监测环境数据表")

    sheetoil.write(4, 0, '计数')
    sheetoil.write(4, 1, '时间')
    sheetoil.write(4, 2, '油罐压力1')
    sheetoil.write(4, 3, '油罐压力2')
    sheetoil.write(4, 4, '油罐压力3')
    sheetoil.write(4, 5, '油罐压力4')
    sheetoil.write(4, 6, '油罐温度1')
    sheetoil.write(4, 7, '油罐温度2')
    sheetoil.write(4, 8, '油罐温度3')
    sheetoil.write(4, 9, '油罐温度4')
    sheetoil.write(4, 10, '管线压力')
    sheetoil.write(4, 11, '卸油区浓度')
    sheetoil.write(4, 12, '三次回收浓度')
    sheetoil.write(4, 13, '人孔井1浓度')
    sheetoil.write(4, 14, '人孔井2浓度')
    sheetoil.write(4, 15, '人孔井3浓度')
    sheetoil.write(4, 16, '人孔井4浓度')
    sheetoil.write(4, 17, '加油区1浓度')
    sheetoil.write(4, 18, '加油区2浓度')
    sheetoil.write(4, 19, '加油区3浓度')
    sheetoil.write(4, 20, '加油区4浓度')
    sheetoil.write(4, 21, '加油区5浓度')
    sheetoil.write(4, 22, '加油区6浓度')
    sheetoil.write(4, 23, '加油区7浓度')
    sheetoil.write(4, 24, '加油区8浓度')
    sheetoil.write(4, 25, '加油区9浓度')
    sheetoil.write(4, 26, '加油区10浓度')
    sheetoil.write(4, 27, '加油区11浓度')
    sheetoil.write(4, 28, '加油区12浓度')
    sheetoil.write(4, 50, '油罐温度*10')
    sheetoil.write(4, 51, '油气浓度*10')
    sheetoil.set_column('B:B', 20)
    sheetoil.set_column('C:BA', 13)
    count=0
    for guncol in oilgun:
        alcounts = 0
        avicounts = 0
        count=0
        for index,gunele in enumerate(guncol):
            recordcount = recordcount + 1
            timetxt = gunele.find('time').text
            sheetoil.write(index + 5, 0, int(gunele.find('count').text))
            date_time = datetime.datetime.strptime(timetxt, '%Y%m%d%H%M%S')
            sheetoil.write(index+5, 1, date_time.strftime('%Y-%m-%d %H:%M:%S'))
            sheetoil.write(index+5, 2, float(gunele.find('pressure1').text))
            sheetoil.write(index+5, 3, float(gunele.find('pressure2').text))
            sheetoil.write(index+5, 4, float(gunele.find('pressure3').text))
            sheetoil.write(index+5, 5, float(gunele.find('pressure4').text))
            sheetoil.write(index+5, 6, float(gunele.find('temp1').text))
            sheetoil.write(index+5, 7, float(gunele.find('temp2').text))
            sheetoil.write(index+5, 8, float(gunele.find('temp3').text))
            sheetoil.write(index+5, 9, float(gunele.find('temp4').text))
            sheetoil.write(index+5, 10, float(gunele.find('liquid').text))
            sheetoil.write(index+5, 11, float(gunele.find('gaspercentage').text))
            sheetoil.write(index+5, 12, float(gunele.find('postpercentage').text))
            sheetoil.write(index+5, 13, float(gunele.find('wellpercentage1').text))
            sheetoil.write(index+5, 14, float(gunele.find('wellpercentage2').text))
            sheetoil.write(index+5, 15, float(gunele.find('wellpercentage3').text))
            sheetoil.write(index+5, 16, float(gunele.find('wellpercentage4').text))
            sheetoil.write(index+5, 17, float(gunele.find('oilpercentage1').text))
            sheetoil.write(index+5, 18, float(gunele.find('oilpercentage2').text))
            sheetoil.write(index+5, 19, float(gunele.find('oilpercentage3').text))
            sheetoil.write(index+5, 20, float(gunele.find('oilpercentage4').text))
            sheetoil.write(index+5, 21, float(gunele.find('oilpercentage5').text))
            sheetoil.write(index+5, 22, float(gunele.find('oilpercentage6').text))
            sheetoil.write(index+5, 23, float(gunele.find('oilpercentage7').text))
            sheetoil.write(index+5, 24, float(gunele.find('oilpercentage8').text))
            sheetoil.write(index+5, 25, float(gunele.find('oilpercentage9').text))
            sheetoil.write(index+5, 26, float(gunele.find('oilpercentage10').text))
            sheetoil.write(index+5, 27, float(gunele.find('oilpercentage11').text))
            sheetoil.write(index+5, 28, float(gunele.find('oilpercentage12').text))

            sheetoil.write_number(index+5, 50, float(gunele.find('temp1').text) * 10)
            sheetoil.write(index + 5, 51, float(gunele.find('gaspercentage').text) * 10)

            count = count + 1

   
    chart1 = workbook.add_chart({'type': 'scatter'})
    #chart1.set_size({'width': 650, 'height': 480})
	
# Configure second series. Note use of alternative syntax to define ranges.
    chart1.add_series({
    'name':       ['环境数据报表', 4, 2],
    'categories': ['环境数据报表', 5, 0, count + 5 - 1, 0],
    'values':     ['环境数据报表', 5, 2, count + 5 - 1, 2],
    'line':   {'color': '#005500',
	    'width': 1},
	'marker': {
        'type': 'none',
        'size': 5,
    },
})
    chart1.add_series({
        'name': ['环境数据报表', 4, 3],
        'categories': ['环境数据报表', 5, 0, count + 5 - 1, 0],
        'values': ['环境数据报表', 5, 3, count + 5 - 1, 3],
        'line': {'color': '#0000ff',
                 'width': 1},
        'marker': {
            'type': 'none',
            'size': 5,
        },
    })
    chart1.add_series({
        'name': ['环境数据报表', 4, 4],
        'categories': ['环境数据报表', 5, 0, count + 5 - 1, 0],
        'values': ['环境数据报表', 5, 4, count + 5 - 1, 4],
        'line': {'color': '#00ff00',
                 'width': 1},
        'marker': {
            'type': 'none',
            'size': 5,
        },
    })
    chart1.add_series({
        'name': ['环境数据报表', 4, 5],
        'categories': ['环境数据报表', 5, 0, count + 5 - 1, 0],
        'values': ['环境数据报表', 5, 5, count + 5 - 1, 5],
        'line': {'color': '#ff0000',
                 'width': 1},
        'marker': {
            'type': 'none',
            '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': ['环境数据报表', 4, 6],
        'categories': ['环境数据报表', 5, 0, count + 5 - 1, 0],
        'values': ['环境数据报表', 5, 6, count + 5 - 1, 6],
        'line': {'color': '#aa5555',
                 'width': 1},
        'marker': {
            'type': 'none',
            'size': 5,
        },
    })
    chart2.add_series({
        'name': ['环境数据报表', 4, 7],
        'categories': ['环境数据报表', 5, 0, count + 5 - 1, 0],
        'values': ['环境数据报表', 5, 7, count + 5 - 1, 7],
        'line': {'color': '#00ff00',
                 'width': 1},
        'marker': {
            'type': 'none',
            'size': 5,
        },
    })
    chart2.add_series({
        'name': ['环境数据报表', 4, 8],
        'categories': ['环境数据报表', 5, 0, count + 5 - 1, 0],
        'values': ['环境数据报表', 5, 8, count + 5 - 1, 8],
        'line': {'color': '#ffff00',
                 'width': 1},
        'marker': {
            'type': 'none',
            'size': 5,
        },
    })
    chart2.add_series({
        'name': ['环境数据报表', 4, 9],
        'categories': ['环境数据报表', 5, 0, count + 5 - 1, 0],
        'values': ['环境数据报表', 5, 9, count + 5 - 1, 9],
        'line': {'color': '#ff0000',
                 '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': ['环境数据报表', 4, 10],
        'categories': ['环境数据报表', 5, 0, count + 5 - 1, 0],
        'values': ['环境数据报表', 5, 10, count + 5 - 1, 10],
        'line': {'color': 'green',
                 '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': 11})


    chart4 = workbook.add_chart({'type': 'scatter'})
    chart4.add_series({
        'name': ['环境数据报表', 4, 11],
        'categories': ['环境数据报表', 5, 0, count + 5 - 1, 0],
        'values': ['环境数据报表', 5, 11, count + 5 - 1, 11],
        'line': {'color': 'yellow',
                 'width': 1},
        'marker': {
            'type': 'none',
            'size': 5,
        },
    })

    chart4.set_title({'name': '卸油区浓度'})
    chart4.set_x_axis({'name': '计数'})
    chart4.set_y_axis({'name': '浓度'})
    chart4.set_style(11)
    sheetgrid.insert_chart('I16', chart4, {'x_offset': 5, 'y_offset': 5})


    chart5 = workbook.add_chart({'type': 'scatter'})
    chart5.add_series({
        'name': ['环境数据报表', 4, 12],
        'categories': ['环境数据报表', 5, 0, count + 5 - 1, 0],
        'values': ['环境数据报表', 5, 12, count + 5 - 1, 12],
        'line': {'color': 'green',
                 '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('A31', chart5, {'x_offset': 5, 'y_offset': 11})




    chart6 = workbook.add_chart({'type': 'scatter'})
    chart6.add_series({
        'name': ['环境数据报表', 4, 13],
        'categories': ['环境数据报表', 5, 0, count + 5 - 1, 0],
        'values': ['环境数据报表', 5, 13, count + 5 - 1, 13],
        'line': {'color': 'green',
                 'width': 1},
        'marker': {
            'type': 'none',
            'size': 5,
        },
    })
    chart6.add_series({
        'name': ['环境数据报表', 4, 14],
        'categories': ['环境数据报表', 5, 0, count + 5 - 1, 0],
        'values': ['环境数据报表', 5, 14, count + 5 - 1, 14],
        'line': {'color': '#00ff00',
                 'width': 1},
        'marker': {
            'type': 'none',
            'size': 5,
        },
    })
    chart6.add_series({
        'name': ['环境数据报表', 4, 15],
        'categories': ['环境数据报表', 5, 0, count + 5 - 1, 0],
        'values': ['环境数据报表', 5, 15, count + 5 - 1, 15],
        'line': {'color': '#ffff00',
                 'width': 1},
        'marker': {
            'type': 'none',
            'size': 5,
        },
    })
    chart6.add_series({
        'name': ['环境数据报表', 4, 16],
        'categories': ['环境数据报表', 5, 0, count + 5 - 1, 0],
        'values': ['环境数据报表', 5, 16, count + 5 - 1, 16],
        'line': {'color': '#ff0000',
                 'width': 1},
        'marker': {
            'type': 'none',
            'size': 5,
        },
    })
    chart6.set_title({'name': '人孔井浓度'})
    chart6.set_x_axis({'name': '计数'})
    chart6.set_y_axis({'name': '浓度'})
    chart6.set_style(11)
    sheetgrid.insert_chart('I31', chart6, {'x_offset': 5, 'y_offset': 11})




    chart7 = workbook.add_chart({'type': 'scatter'})
    chart7.set_size({'width': 650, 'height': 480})
    chart7.add_series({
        'name': ['环境数据报表', 4, 17],
        'categories': ['环境数据报表', 5, 0, count + 5 - 1, 0],
        'values': ['环境数据报表', 5, 17, count + 5 - 1, 17],
        'line': {'color': 'green',
                 'width': 1},
        'marker': {
            'type': 'none',
            'size': 5,
        },
    })
    chart7.add_series({
        'name': ['环境数据报表', 4, 18],
        'categories': ['环境数据报表', 5, 0, count + 5 - 1, 0],
        'values': ['环境数据报表', 5, 18, count + 5 - 1, 18],
        'line': {'color': '#000000',
                 'width': 1},
        'marker': {
            'type': 'none',
            'size': 5,
        },
    })
    chart7.add_series({
        'name': ['环境数据报表', 4, 19],
        'categories': ['环境数据报表', 5, 0, count + 5 - 1, 0],
        'values': ['环境数据报表', 5, 19, count + 5 - 1, 19],
        'line': {'color': '#aaaaff',
                 'width': 1},
        'marker': {
            'type': 'none',
            'size': 5,
        },
    })
    chart7.add_series({
        'name': ['环境数据报表', 4, 20],
        'categories': ['环境数据报表', 5, 0, count + 5 - 1, 0],
        'values': ['环境数据报表', 5, 20, count + 5 - 1, 20],
        'line': {'color': '#8888ff',
                 'width': 1},
        'marker': {
            'type': 'none',
            'size': 5,
        },
    })

    chart7.add_series({
        'name': ['环境数据报表', 4, 21],
        'categories': ['环境数据报表', 5, 0, count + 5 - 1, 0],
        'values': ['环境数据报表', 5, 21, count + 5 - 1, 21],
        'line': {'color': '#ffaaaa',
                 'width': 1},
        'marker': {
            'type': 'none',
            'size': 5,
        },
    })
    chart7.add_series({
        'name': ['环境数据报表', 4, 22],
        'categories': ['环境数据报表', 5, 0, count + 5 - 1, 0],
        'values': ['环境数据报表', 5, 22, count + 5 - 1, 22],
        'line': {'color': '#ffaa11',
                 'width': 1},
        'marker': {
            'type': 'none',
            'size': 5,
        },
    })
    chart7.add_series({
        'name': ['环境数据报表', 4, 23],
        'categories': ['环境数据报表', 5, 0, count + 5 - 1, 0],
        'values': ['环境数据报表', 5, 23, count + 5 - 1, 23],
        'line': {'color': '#ff11aa',
                 'width': 1},
        'marker': {
            'type': 'none',
            'size': 5,
        },
    })
    chart7.add_series({
        'name': ['环境数据报表', 4, 24],
        'categories': ['环境数据报表', 5, 0, count + 5 - 1, 0],
        'values': ['环境数据报表', 5, 24, count + 5 - 1, 24],
        'line': {'color': '#ff8888',
                 'width': 1},
        'marker': {
            'type': 'none',
            'size': 5,
        },
    })

    chart7.add_series({
        'name': ['环境数据报表', 4, 25],
        'categories': ['环境数据报表', 5, 0, count + 5 - 1, 0],
        'values': ['环境数据报表', 5, 25, count + 5 - 1, 25],
        'line': {'color': '#33ffaa',
                 'width': 1},
        'marker': {
            'type': 'none',
            'size': 5,
        },
    })
    chart7.add_series({
        'name': ['环境数据报表', 4, 26],
        'categories': ['环境数据报表', 5, 0, count + 5 - 1, 0],
        'values': ['环境数据报表', 5, 26, count + 5 - 1, 26],
        'line': {'color': '#553311',
                 'width': 1},
        'marker': {
            'type': 'none',
            'size': 5,
        },
    })
    chart7.add_series({
        'name': ['环境数据报表', 4, 27],
        'categories': ['环境数据报表', 5, 0, count + 5 - 1, 0],
        'values': ['环境数据报表', 5, 27, count + 5 - 1, 27],
        'line': {'color': '#5577aa',
                 'width': 1},
        'marker': {
            'type': 'none',
            'size': 5,
        },
    })
    chart7.add_series({
        'name': ['环境数据报表', 4, 28],
        'categories': ['环境数据报表', 5, 0, count + 5 - 1, 0],
        'values': ['环境数据报表', 5, 28, count + 5 - 1, 28],
        'line': {'color': '#44ff44',
                 'width': 1},
        'marker': {
            'type': 'none',
            'size': 5,
        },
    })


    chart7.set_title({'name': '加油区浓度'})
    chart7.set_x_axis({'name': '计数'})
    chart7.set_y_axis({'name': '浓度'})
    chart7.set_style(11)
    sheetgrid.insert_chart('A46', chart7, {'x_offset': 5, 'y_offset': 11})

'''
    chart5 = workbook.add_chart({'type': 'scatter'})
    chart5.set_size({'width': 650, 'height': 480})
    chart5.add_series({
        'name': ['环境数据报表', 4, 2],
        'categories': ['环境数据报表', 5, 0, count + 5 - 1, 0],
        'values': ['环境数据报表', 5, 2, count + 5 - 1, 2],
        'line': {'color': 'blue',
                 'width': 1},
        'marker': {
            'type': 'none',
            'size': 5,
        },
    })
    chart5.add_series({
        'name': ['环境数据报表', 4, 50],
        'categories': ['环境数据报表', 5, 0, count + 5 - 1, 0],
        'values': ['环境数据报表', 5, 50, count + 5 - 1, 50],
        'line': {'color': 'red',
                 'width': 1},
        'marker': {
            'type': 'none',
            'size': 5,
        },
    })
    chart5.add_series({
        'name': ['环境数据报表', 4, 4],
        'categories': ['环境数据报表', 5, 0, count + 5 - 1, 0],
        'values': ['环境数据报表', 5, 4, count + 5 - 1, 4],
        'line': {'color': 'green',
                 'width': 1},
        'marker': {
            'type': 'none',
            'size': 5,
        },
    })
    chart5.add_series({
        'name': ['环境数据报表', 4, 51],
        'categories': ['环境数据报表', 5, 0, count + 5 - 1, 0],
        'values': ['环境数据报表', 5, 51, count + 5 - 1, 51],
        'line': {'color': 'yellow',
                 '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('A31', 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"))