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

Question

目前只做到了将抓到的数据插入excel并作图

我现在想要向没有数据的那一行插入新值，更新图表并刷新显示

Answer 1

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