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