python导入excel加入折线图_如何向excel插入新值同时更新对应的折线图

import win32com.client

import pymysql

import time

import xlrd

import xlsxwriter

class toExcel():

def __init__(self):

self.cursor=self.conlzg().cursor()

self.loczs=[]

self.locgp=[]

self.num=5

self.loc=[]

def conlzg(self):

con=pymysql.connect(host='localhost',

user='root',

passwd='lzg000',

db='lzg',

port=3306,

charset='utf8'

)

return con

def buildzs(self):

start='0'

left=1 #起始列

top=1 #起始行

picleft=1

pictop=15

numindex=0

filename='E:\\test\\test.xlsx'

#wbk =xlsxwriter.Workbook(filename)

#wbk.close()

xlApp = win32com.client.Dispatch('Excel.Application')

xlBook = xlApp.Workbooks.Open(filename)

xlApp.Visible=1

#xlApp.Sheets.Add()

sht1=xlBook.Worksheets(1)

#sht1.name='chart'

sht2=xlBook.Worksheets(2)

#xlApp.Sheets("Sheet2").Select

#sht2.Visible = False

#sht2.name='data'

cursor=self.cursor

cursor.execute('select count(*) from zs group by name')

s1=cursor.fetchall()

for i in xrange(5):

num=s1[numindex][0]

numindex+=1

count = cursor.execute('select * from zs order by name,time limit'+' '+start+','+str(num))

self.num=count

print 'has %s record' % count

cursor.scroll(0,mode='absolute')

results = cursor.fetchall()

fields = cursor.description

for ifs in range(top,len(fields)+top):

sht2.Cells(top,ifs+left-top).Value = fields[ifs-top][0]

for row in range(top+1,len(results)+top+1):

for col in range(left,len(fields)+left):

sht2.Cells(row,col).Value=results[row-1-top][col-left]

locxy=(row,left)

self.loc.append(locxy)

sht1.Shapes.AddChart2(271,4,picleft,pictop,593,200).Select()

cell1=sht2.cells(top,left+9)

cell2=sht2.cells(top+99,left+9)

xlApp.ActiveChart.SetSourceData(Source=sht2.Range(cell1,cell2))

cell3=sht2.cells(top,left+8)

cell4=sht2.cells(top,left+8)

xlApp.ActiveChart.FullSeriesCollection(1).XValues=sht2.Range(cell3,cell4)

xlApp.ActiveChart.ChartTitle.Text = sht2.cells(top+1,left+2).Value

start=int(start)+num

start=str(start)

pictop+=200

top+=100

#xlBook.Save()

def build(self):

start=0

left=14 #起始列

top=0 #起始行

pictop=2

numindex=0

#将字段写入到EXCEL新表的第一行

wbk =xlsxwriter.Workbook('E:/test/test2.xlsx')

#newwbk = copy(wbk)

sheet = wbk.add_worksheet('chart')

sheet1=wbk.add_worksheet('data')

red = wbk.add_format({'border':1,'align':'center','bg_color':'C0504D','font_size':12,'font_color':'white'})

sheet.merge_range(0,0,0,10,data,yellow)

cursor=self.cursor

cursor.execute('select count(*) from m group by name')

numlist=cursor.fetchall()

for i in xrange(5):

num=numlist[numindex][0]

numindex+=1

count = cursor.execute('select * from m order by name,time limit'+' '+ str(start)+','+ str(num))

print 'has %s record' % count

#重置游标位置

cursor.scroll(0,mode='absolute')

#搜取所有结果

results = cursor.fetchall()

#测试代码，print results

#获取MYSQL里的数据字段

fields = cursor.description

#从top行开始写标题

#code=results[0][0]

#loc[code]=(top,left) #得到每个表的左上角坐标

#向sheet中插入数据

for ifs in range( top,len(fields)+ top):

sheet1.write( top,ifs+ left- top,fields[ifs- top][0])

#写内容

for row in range( top+1,len(results)+ top+1):

#for col in range(left,len(fields)+left):

sheet1.write_row(row, left,results[row-1- top])

locxy=[row+1,left+1]

self.locgp.append(locxy)

#作图，类型为 line折现图

chart1 = wbk.add_chart({'type': 'line'})

chart1.set_style(4)

#向图表添加数据

chart1.add_series({

'name':['data', top+1, left+1],

'categories':['data', top+1, left+6, top+1, left+6],

'values':['data', top+1, left+5, top+200, left+5],

'line':{'color':'red'},

'fill': {'color':'#FF9900'}

})

#bold = wbk.add_format({'bold': 1})

chart1.set_title({'name':'1min line '})

chart1.set_x_axis({'name':'time'})

chart1.set_y_axis({'name':'close'})

chart1.set_size({'width':800,'height':300})

sheet.insert_chart( pictop, left,chart1)

#bg+=19

start+=num

top+=300

pictop+=15

start=0

left=0 #起始列

top=0 #起始行

pictop=2

cursor.execute('select count(*) from zs group by name')

numlist=cursor.fetchall()

self.num=numlist[0][0]

numindex=0

for i in xrange(5):

num=numlist[numindex][0]

numindex+=1

count = cursor.execute('select * from zs order by name,time limit'+' '+ str(start)+','+ str(num))

print 'has %s record' % count

#重置游标位置

cursor.scroll(0,mode='absolute')

#搜取所有结果

results = cursor.fetchall()

#测试代码，print results

#获取MYSQL里的数据字段

fields = cursor.description

#从top行开始写标题

#code=results[0][0]

#loc[code]=(top,left) #得到每个表的左上角坐标

#向sheet中插入数据

for ifs in range( top,len(fields)+ top):

sheet1.write( top,ifs+ left- top,fields[ifs- top][0])

#写内容

for row in range( top+1,len(results)+ top+1):

#for col in range(left,len(fields)+left):

sheet1.write_row(row, left,results[row-1- top])

locxy=(row+1,left+1)

self.loczs.append(locxy)

#作图，类型为 line折现图

chart1 = wbk.add_chart({'type': 'line'})

chart1.set_style(4)

#向图表添加数据

chart1.add_series({

'name':['data', top+1, left+2],

'categories':['data', top+1, left+6, top+1, left+6],

'values':['data', top+1, left+9, top+300, left+9],

'line':{'color':'red'},

'fill': {'color':'#FF9900'}

})

#bold = wbk.add_format({'bold': 1})

chart1.set_title({'name':'1min line '})

chart1.set_x_axis({'name':'time'})

chart1.set_y_axis({'name':'close'})

chart1.set_size({'width':750,'height':300})

sheet.insert_chart( pictop, left,chart1)

start+=num

top+=300

pictop+=15

cursor.close()

wbk.close()

def update(self):

filename='E:\\test\\test2.xlsx'

xlApp = win32com.client.Dispatch('Excel.Application')

xlBook = xlApp.Workbooks.Open(filename)

xlApp.Visible=1

sht2=xlBook.Worksheets(2)

start=0

numindex=0

locindex=0

while True:

S=0

con=self.conlzg()

cursor=con.cursor()

cursor.execute('select count(*) from zs group by name')

s1=cursor.fetchall()

for x,y in self.loczs:

num=s1[numindex][0]

numindex+=1

count = cursor.execute('select * from zs order by name,time desc limit'+' '+str(start)+','+'1')

if num==self.num:

print "未更新数据"

S=1

time.sleep(20)

break

print '指数更新 %s record' % count

results = cursor.fetchall()

for col in range(y,y+10):

sht2.Cells(x+1,col).Value=results[0][col-y]

start=start+num

if S==0:

for x,y in self.loczs:

self.loczs[locindex]=(x+1,y)

locindex+=1

self.num+=1

numindex=0

locindex=0

start=0

cursor.close()

con.close()

print '指数更新完毕'

def update2(self):

xlApp = win32com.client.Dispatch('Excel.Application')

xlBook = xlApp.Workbooks(1)

xlApp.Visible=1

sht2=xlBook.Worksheets(2)

start=0

numindex=0

locindex=0

while True:

S=0

con=self.conn

cursor=con.cursor()

cursor.execute('select count(*) from m group by name')

s1=cursor.fetchall()

for x,y in self.locgp:

num=s1[numindex][0]

numindex+=1

count = cursor.execute('select * from m order by name,time desc limit'+' '+str(start)+','+'1')

if num==self.num:

print "未更新数据"

S=1

time.sleep(20)

break

print 'has %s record' % count

self.num+=1

#cursor.scroll(0,mode='absolute')

results = cursor.fetchall()

for col in range(y,y+8):

sht2.Cells(x+1,col).Value=results[0][col-y]

start+=num

if S==0:

for x,y in self.locgp:

self.locgp[locindex]=(x+1,y)

locindex+=1

numindex=0

locindex=0

start=0

def main():

conn=pymysql.connect(host='localhost',

user='root',

passwd='lzg000',

db='lzg',

port=3306,

charset='utf8'

)

e=toExcel(conn)

e.build()

e.update()

#e.update2()

if __name__=='__main__':

main()