python win32com excel border formatting

Question

I have a piece of code here that actually work to format the borders in excel using python win32com. My concern is the time it take to format the borders. I tried to record a macro in excel to find out the required information to transpose it in my script but it didn't work.

So the best that I can do is to run in a for range loop where I always start at row 3 up to a row counter called shn[1] by increment of 1 and from column 1 to 10 by increment of 1. From there I use "BorderAround()" which work fine but too slow. Here my piece of code:

for shn in [("Beam-Beam", bb_row, bb_col), ("Beam-Col", bc_row, bc_col)]:
sheet = book.Worksheets(shn[0])
sheet.Range( "J3:DW3" ).Copy()
if shn[0] == "Beam-Col":
    sheet.Range( "J3:AA3" ).Copy()
sheet.Range( sheet.Cells( 4, 10 ), sheet.Cells( shn[1]-1, 10 )  ).PasteSpecial()
for mrow in xrange(3,shn[1],1):
    for mcol in xrange(1,10,1):
        sheet.Cells(mrow, mcol).BorderAround()#.Border(1)

Is there something I can do to format the borders with a range like ==> sheet.Range( sheet.Cells(3,1), sheet.Cells(shn[1],10) )? I tried ".Borders(11)" and ".Borders(12)" plus ".BorderAround()", but only ".BorderAround()" have worked.

Thanks in advance.

Ryan G · Accepted Answer · 2013-09-30 16:03:47Z

Hmm, what excel are you using?

This should work:

for shn in [("Beam-Beam", bb_row, bb_col), ("Beam-Col", bc_row, bc_col)]:
sheet = book.Worksheets(shn[0])
sheet.Range( "J3:DW3" ).Copy()
if shn[0] == "Beam-Col":
    sheet.Range( "J3:AA3" ).Copy()
## Set a variable named rng to the range
rng = sheet.Range( sheet.Cells( 4, 10 ), sheet.Cells( shn[1]-1, 10 )  )
rng.PasteSpecial()
## Using this range, we can now set its borders linestyle and weight
##  -> where 7 through 13 correspond to borders for xlEdgeTop,xlEdgeBottom,
##     xlEdgeRight, xlEdgeLeft, xlInsideHorizontal, and xlInsideVertical
##  -> LineStyle of 1 = xlContinous
##  -> Weight of 2 = xlThin
for border_id in xrange(7,13):
    rng.Borders(border_id).LineStyle=1
    rng.Borders(border_id).Weight=2
## And to finish just call
book.Close(True) # To close book and save
excel_app.Quit() # or some variable name established for the com instance

Let me know how this works for you.

Also it may be faster if you set the excel applications Visible to False or turned off screenupdating:

excel_app.Visible = False # This will not physically open the book
excel_app.ScreenUpdating = False # This will not update the screen on an open book
##
# Do Stuff...
##
# Just make sure when using the ScreenUpdating feature that you reenable it before closing
excel_app.ScreenUpdating = True

This way excel isn't updating the screen for every call.

Collectives™ on Stack Overflow

python win32com excel border formatting

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related