excelpy Overwriting Saved data

asked 2014-02-02 17:43:21 -0500

JarrodB
1 ●1 ●1 ●1

Hi all,

Am having issues with excelpy overwriting my prevoiusly saved data in a working spreadsheet, hope someone can help.

I've got a routine running PSSE that completes around 262k iterations to find the fault level at a certain bus and then records this fault level and the status of each of the 18 generators into two python lists. Every 10,000 iterations I want this data to be dumped to excel (as the 262k at once cause the routine to crash).

Problem is, every time I call the routine below, the script dumps the data in the correct location in the spreadsheet, but clears all the existing cells,eg, after 100 iterations, the new data is inserted in cell 106, but cells 5-105 are cleared.

Can anyone please give me some advice with this please?

The creation of the excel file:

SS_Name = 'SOUTH Fault Level Analysis.xlsx'
testwbook = excelpy.workbook(SS_Name,"Results",False)
testwbook.show_alerts(True)
# Next lines configure Excel workbook
# testwbook.show()          
testwbook.merge((1,1,1,8))
testwbook.merge((2,1,2,2))
testwbook.merge((2,4,2,5))
testwbook.merge((3,3,3,5))
testwbook.merge((3,6,3,8))
testwbook.set_cell((1,1),"Results",fontStyle='bold',fontName='arial',fontSize=14,fontColor='black')
testwbook.align((1,1,4,9),'h_centre')
testwbook.align((2,1,2,3),'h_right')
testwbook.align((2,3,2,8),'h_left')
testwbook.set_cell((3,3),'GT 220',fontStyle='bold',fontName='arial',fontSize=12,fontColor='black')
testwbook.set_cell((2,1),'PBase',fontStyle='bold',fontName='arial',fontSize=12)
# testwbook.set_cell((2,3),tot_base,fontStyle='bold',fontName='arial',fontSize=12)
testwbook.set_cell((2,4),'IBase',fontStyle='bold',fontName='arial',fontSize=12)
# testwbook.set_cell((2,6),i_base,fontStyle='bold',fontName='arial',fontSize=12)
testwbook.set_range(4,1,['Fault Level',"GO1","GO2","GO3","BG","TA1","TA2","TU1","TU2","TA3","TA4","TU3","LE","TA5","TA6","TU4","TU5","CA1","LI1","WY1","LI2","WY2","CA2","LI3","WY3","RE","CL","MB","TR1","TR2","TR3","TR4","PO1","PO2","PO3","PO4","PO5","PO6"],fontStyle='bold',fontName='arial',fontSize=10,fontColor='black')
testwbook.save(SS_Name)
testwbook.close()

I then call this after each set of iterations to dump the stored fault level data:

if (counter % 100 == 0): 
    rows = exporttoexcel(FL,STATUS,rows,SS_Name)
    testwbook.save(SS_Name)
    testwbook.close()
    print rows
    FL  = [ ]
    STATUS  = [ ]

The routine that completes the dump:

 def exporttoexcel(FL,STATUS,rows,name):
    testwbook = excelpy.workbook(name,"Results",overwritesheet=False)
    brow, rcln = testwbook.set_range(rows,1,FL,transpose=True,numberFormat='0.00')
    rcln += 1 
    brow, rcln = testwbook.set_range(rows,rcln,STATUS,transpose=False,numberFormat='0')

    fl_len = len(FL)
    rows = rows + fl_len

    testwbook.save(name)
    testwbook.close()

    popto = raw_input("Excel Dump Complete")

    return rows