# 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\Python35-32\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' workbook = xlsxwriter.Workbook(fn) try: workbook.close() except: state = "2" cfgpath = "C:\\Tokheim\\plugs\\cfg.ini" conf = configparser.ConfigParser() conf.add_section("GunReport") conf.set("GunReport", "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, 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(13, 13, 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, 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 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) 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) cfgpath = "C:\\Tokheim\\plugs\\cfg.ini" conf = configparser.ConfigParser() conf.add_section("GunReport") conf.set("GunReport", "State", state) conf.set("GunReport", "time", str(stime)) conf.set("GunReport", "count", str(recordcount)) conf.write(open(cfgpath, "w"))