Python clean way to wrap individual statements in a try except block

Question

I'm currently doing some Python automation of Excel with com. It's fully functional, and does what I want, but I've discovered something surprising. Sometimes, some of the Excel commands I use will fail with an exception for no apparent reason. Other times, they will work.

In the VB equivalent code for what I'm doing, this problem is apparently considered normal, and is plastered over with a On Error Resume Next statement. Python does not have said statement, of course.

I can't wrap up the whole set in a try except loop, because it could "fail" halfway through and not complete properly. So, what would be a pythonic way to wrap several independent statements into a try except block? Specifically, something cleaner than:

try:
   statement
except:
   pass
try:
   statement
except:
   pass

The relevant code is the excel.Selection.Borders bit.

def addGridlines(self, infile, outfile):
    """convert csv to excel, and add gridlines"""
    # set constants for excel
    xlDiagonalDown = 5
    xlDiagonalUp = 6
    xlNone = -4142
    xlContinuous = 1
    xlThin = 2
    xlAutomatic = -4105
    xlEdgeLeft = 7
    xlEdgeTop = 8
    xlEdgeBottom = 9
    xlEdgeRight = 10
    xlInsideVertical = 11
    xlInsideHorizontal = 12
            # open file
    excel = win32com.client.Dispatch('Excel.Application')
    workbook = excel.Workbooks.Open(infile)
    worksheet = workbook.Worksheets(1)

    # select all cells
    worksheet.Range("A1").CurrentRegion.Select()
    # add gridlines, sometimes some of these fail, so we have to wrap each in a try catch block
    excel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    excel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    excel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    excel.Selection.Borders(xlEdgeLeft).LineStyle = xlContinuous
    excel.Selection.Borders(xlEdgeLeft).Weight = xlThin
    excel.Selection.Borders(xlEdgeLeft).ColorIndex = xlAutomatic
    excel.Selection.Borders(xlEdgeTop).LineStyle = xlContinuous
    excel.Selection.Borders(xlEdgeTop).Weight = xlThin
    excel.Selection.Borders(xlEdgeTop).ColorIndex = xlAutomatic
    excel.Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous
    excel.Selection.Borders(xlEdgeBottom).Weight = xlThin
    excel.Selection.Borders(xlEdgeBottom).ColorIndex = xlAutomatic
    excel.Selection.Borders(xlEdgeRight).LineStyle = xlContinuous
    excel.Selection.Borders(xlEdgeRight).Weight = xlThin
    excel.Selection.Borders(xlEdgeRight).ColorIndex = xlAutomatic
    excel.Selection.Borders(xlInsideVertical).LineStyle = xlContinuous
    excel.Selection.Borders(xlInsideVertical).Weight = xlThin
    excel.Selection.Borders(xlInsideVertical).ColorIndex = xlAutomatic
    excel.Selection.Borders(xlInsideHorizontal).LineStyle = xlContinuous
    excel.Selection.Borders(xlInsideHorizontal).Weight = xlThin
    excel.Selection.Borders(xlInsideHorizontal).ColorIndex = xlAutomatic
    # refit data into columns
    excel.Cells.Select()
    excel.Cells.EntireColumn.AutoFit()
    # save new file in excel format
    workbook.SaveAs(outfile, FileFormat=1)
    workbook.Close(False)
    excel.Quit()
    del excel

Update:

Perhaps a bit of explanation on the error bit is required. Two identical runs on my test machine, with identical code, on the same file, produce the same result. One run throws exceptions for every xlInsideVertical line. The other throws exceptions for every xlInsideHorizontal. Finally, a third run completes with no exceptions at all.

As far as I can tell Excel considers this normal behavior, because I'm cloning the VB code built by excel's macro generator, not VB code produced by a person. This might be an erroneous assumption, of course.

It will function with each line wrapped in a try except block I just wanted something shorter and more obvious, because 20 lines wrapped in their own try catch loops is just asking for trouble later.

Update2:

This is a scrubbed CSV file for testing: gist file

Conclusion:

The answer provided by Vsekhar is perfect. It abstracts away the exception suppression, so that later, if and when I have time, I can actually deal with the exceptions as they occur. It also allows for logging the exceptions so they don't disappear, not stopping other exceptions, and is small enough to be easily manageable six months from now.

How about making it not to fail in the first place? Python is not PHP or Visual Basic and unfortunately it enforces sane error handling :( — Mikko Ohtamaa, Sep 01 '11 at 13:50
Could you save all the calls in a list and then iterate over them with single try statement in the loop? Just don't know how to "save" this kind of calls. — rplnt, Sep 01 '11 at 13:53
@rpInt: statements are not first-class objects in Python - they can't be assigned to variables and passed into functions — Eli Bendersky, Sep 01 '11 at 14:24
Could you upload a dummy sheet that shows the problem? Your code seems to work consistently for me (after removing the xlDiagonalup -- note the lowercase u -- line, which makes me think that the above can't be the code you were actually running). — DSM, Sep 01 '11 at 14:57
@DSM the lowercase u is a typo. As you've noted this is part of the problem. Most the time excel has no problems, on other runs I get errors, *but the end result is still the same*, which just boggles my mind. — Spencer Rathbun, Sep 01 '11 at 16:04
@Spencer Rathbun: I can't get it to fail for me, unfortunately (excel 07). Others have reported intermittent success with InsideHorizontal and InsideVertical depending on things like the width (e.g. if the selected range has width 1), so I can believe your symptoms could happen even in more realistic cases, but without something that crashes it's hard to help. If you come up with a simple crashing file and put it on (e.g.) https://gist.github.com/ I'll have a look. [BTW: typo? Always cut and paste exactly the code that runs, too much time is wasted guessing the original otherwise.] — DSM, Sep 01 '11 at 16:25
@DSM Ah, that's probably it, I'm using excel 2003. I've never seen a crash with anything other than the xlInsideVertical and xlInsideHorizontal. I'd actually intended to leave out the try catch block as unnecessary until testing showed the occasional exception. The typo is from the copy and pasted code in the ipython log file. I had two lines, one with it and one without, and I thought I deleted the line with the problem before pasting it to SO. Guess not. Oh, I've uploaded a scrubbed file for you. — Spencer Rathbun, Sep 01 '11 at 16:35
@Spencer Rathbun: Thanks for posting the sample file. I've just tried it several hundred times without a single failure in '07, so I have no helpful things to suggest on the Excel side as I can't get it to crash.. Unhelpful things involve experimenting which region is selected, to see if (e.g.) it always works on small, 2x2 chunks, or making a manual Selection rather than accepting the region, etc., but those are all random guesses based on trying to imagine what the Excel problem could be. — DSM, Sep 01 '11 at 17:16
There is a typo: `xlDiagonalup` instead of `xlDiagonalUp` (up vs. Up). Names in Python are case sensitive. — Aaron Digulla, Sep 02 '11 at 13:33

vsekhar · Accepted Answer · 2011-09-01T14:51:14.990

Consider abstracting away the suppression. And to Aaron's point, do not swallow exceptions generally.

class Suppressor:
    def __init__(self, exception_type):
        self._exception_type = exception_type

    def __call__(self, expression):
        try:
            exec expression
        except self._exception_type as e:
            print 'Suppressor: suppressed exception %s with content \'%s\'' % (type(self._exception_type), e)
            # or log.msg('...')

Then, note in the traceback of your current code exactly what exception is raised, and create a Suppressor for just that exception:

s = Suppressor(excel.WhateverError) # TODO: put your exception type here
s('excel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone')

This way you get line-by-line execution (so your tracebacks will still be helpful), and you are suppressing only the exceptions you explicitly intended. Other exceptions propagate as usual.

This is very much in the spirit of what Aaron Digulla was suggesting, except that you *are* "swallowing" the *designated* exceptions silently. You are indeed correct to *avoid* suppressing exceptions by default, but IMO it would be better at least to log the exceptions that you *do* suppress, or store them in a data structure which can be dumped at the end of program execution so you can see what actually happened. — Peter, Sep 01 '11 at 14:44

Aaron Digulla · Answer 2 · 2011-09-02T13:52:26.257

10

Exceptions never happen "for no apparent reason". There is always a reason and that reason needs to be fixed. Otherwise, your program will start to produce "random" data where "random" is at the mercy of the bug that you're hiding.

But of course, you need a solution for your problem. Here is my suggestion:

Create a wrapper class that implements all the methods that you need and delegates them to the real Excel instance.
Add a decorator before each method which wraps the method in a try except block and log the exception. Never swallow exceptions

Now the code works for your customer which buys you some time to find out the cause of the problem. My guess is that a) Excel doesn't produce a useful error message or b) the wrapper code swallows the real exception leaving you in the dark or c) the Excel method returns an error code (like "false" for "failed") and you need to call another Excel method to determine what the cause of the problem is.

[EDIT] Based on the comment below which boil down to "My boss doesn't care and there is nothing I can do": You're missing a crucial point: It's your bosses duty to make the decision but it your duty to give her a list of options along with pros/cons so that she can make a sound decision. Just sitting there saying "I can't do anything" will get you into the trouble that you're trying to avoid.

Example:

Solution 1: Ignore the errors

Pro: Least amount of work Con: There is a chance that the resulting data is wrong or random. If important business decisions are based on it, there is a high risk that those decisions will be wrong.

Solution 2: Log the errors

Pro: Little amount of work, users can start to use the results quickly, buys time to figure out the source of the problem Con: "If you can't fix it today, what makes you think you will have time to fix it tomorrow?" Also, it might take you a long time to find the source of the problem because you're no expert

Solution 3: Ask an expert

Find an expert in the field and help him/her have a look/improve the solution.

Pro: Will get a solution much more quickly than learning the ins and outs of COM yourself Con: Expensive but high chance of success. Will also find problems that we don't even know about.

...

I think you see the pattern. Bosses make wrong decisions because we (willingly) let them. Any boss in the world is happy for hard facts and input when they have to make a decision (well, those who don't shouldn't be bosses, so this is a surefire way to know when to start looking for a new job).

If you select solution #2, go for the wrapper approach. See the docs how to write a decorator (example from IBM). It's just a few minutes of work to wrap all the methods and it will give you something to work with.

The next step is to create a smaller example which sometimes fails and then post specific questions about Python, Excel and the COM wrapper here to figure out the reason for the problems.

[EDIT2] Here is some code that wraps the "dangerous" parts in a helper class and makes updating the styles more simple:

class BorderHelper(object):
    def __init__(self, excel):
        self.excel = excel

    def set( type, LineStyle = None, Weight = None, Color = None ):
        border = self.excel.Selection.Borders( type )

        try:
            if LineStyle is not None:
                border.LineStyle = LineStyle
        except:
            pass # Ignore if a style can't be set

        try:
            if Weight is not None:
                border.Weight = Weight
        except:
            pass # Ignore if a style can't be set

        try:
            if Color is not None:
                border.Color = Color
        except:
            pass # Ignore if a style can't be set

Usage:

    borders = BorderHelper( excel )

    borders.set( xlDiagonalDown, LineStyle = xlNone )
    borders.set( xlDiagonalUp, LineStyle = xlNone )
    borders.set( xlEdgeLeft, LineStyle = xlContinuous, Weight = xlThin, Color = xlAutomatic )
    ...

edited Sep 02 '11 at 13:52

answered Sep 01 '11 at 13:52

Aaron Digulla

321,842
108
597
820

4

I think that word doesn't mean what you think it means. There is always a reason, it's quite often not "apparent." – agf Sep 01 '11 at 13:54
I couldn't find a better wording to express my contempt for ignorance :-) – Aaron Digulla Sep 01 '11 at 13:59
@Aaron Digulla I've updated my question with a little more background info. If you know enough about excel and com programming to shed light on this issue I'd be grateful. But in my case, 20 minutes of reading on com, and 15 minutes reading the python com examples simply doesn't cover the problem space enough to debug it. Mngt wants it in a day, not after I learn com and debug excel. – Spencer Rathbun Sep 01 '11 at 14:16
1

@Spencer Rathbun: "Mngt"? You mean "management"? Are you saying "management wants it in a day, not after I learn com and debug excel"? Are you saying management wants something buggy and unreliable that may never work properly? Is that what they're demanding? That doesn't sound like a rational request. – S.Lott Sep 01 '11 at 14:19
@S.Lott One of the horror stories I got told when I was hired was how one of my predecessors sat down with management and got told to write a program that would wait for user input, and do everything automatically with no user input. When he asked for clarification, he got told the same thing, but shortened to a single sentence. – Spencer Rathbun Sep 01 '11 at 14:25
You can write a generic wrapper without manually implementing all the methods, I think ... – Useless Sep 01 '11 at 14:26
@Spencer Rathbun: "One of the horror stories..." Doesn't really help at all. You've got a serious problem and you're attempting a bad workaround. Solving the serious problem is better than a bad workaround. "because 20 lines wrapped in their own try catch loops is just asking for trouble later" is false. What's trouble later is the unexplained exception. – S.Lott Sep 01 '11 at 14:30
2

@S.Lott - sometimes you're not in a position to say it can't be done in that time, and sometimes a buggy, unreliable solution is exactly what management demands --- in terms that are only clear to the programmer. That is to say, yes, this is the wrong thing to do, and sometimes you have to do the wrong thing to keep your job while you look for another. – JasonFruit Sep 01 '11 at 14:59
@JasonFruit: "sometimes you're not in a position to say..." Why? Phone got shut off? Email broken? Monastic Silence rules in effect? I'm unclear on why conversations are not permitted. – S.Lott Sep 01 '11 at 15:47
@S.Lott I can tell management, but they won't care. This isn't a programming shop, nor is my boss a programmer. She won't understand *what* I'm talking about, let alone *why* it's a bad idea. And as regards being in a position to say, I prefer being able to pay my bills. – Spencer Rathbun Sep 01 '11 at 16:00
@JasonFruit You can suggest using an HTML table instead, if your purpose is only to format things in a nice way. – Simon Bergot Sep 01 '11 at 16:46
@Simon --- I think you mean Spencer. It's not my job, happily enough. – JasonFruit Sep 01 '11 at 17:08
3

@Spencer: You're missing a crucial point: It's your bosses duty to make the decision but it **your** duty to give her a list of options along with pros/cons so that she can make a **sound** decision. Just sitting there saying "I can't do anything" will get you into the trouble that you're trying to avoid. – Aaron Digulla Sep 02 '11 at 08:49
2

@Aaron Digulla, I agree with your point, but I seem to have misstated my position. *The decision has already been made* which was get it working now, and we don't care how. This is a better solution than the previous one, which involved letting an excel macro magically start based on the filename. So my code would have been open file, save as excel, close. Finally, for heaven's sake! I'm adding grid lines, that's all! If excel isn't perfectly happy, and tells me some of the lines won't work, I don't really care. – Spencer Rathbun Sep 02 '11 at 12:45
*plink* Coin dropped. I'll suggest a different solution. – Aaron Digulla Sep 02 '11 at 13:44

score 4 · Answer 3 · answered Sep 01 '11 at 14:38

This just wraps functions calls, but you can extend it to handle attribute access as well, and to proxy the results of nested attribute accesses, finally just wrapping the __setattr__ in your try:except block.

It might be sensible to swallow only some specific exception types in your case (as @vsekhar says).

def onErrorResumeNext(wrapped):
    class Proxy(object):
        def __init__(self, fn):
            self.__fn = fn

        def __call__(self, *args, **kwargs):
            try:
                return self.__fn(*args, **kwargs)
            except:
                print "swallowed exception"

    class VBWrapper(object):
        def __init__(self, wrapped):
            self.wrapped = wrapped

        def __getattr__(self, name):
            return Proxy(eval('self.wrapped.'+name))

    return VBWrapper(wrapped)

Example:

exceptionProofBorders = onErrorResumeNext(excel.Selection.Borders)
exceptionProofBorders(xlDiagonalDown).LineStyle = xlNone
exceptionProofBorders(xlDiagonalup).LineStyle = xlNone

score 0 · Answer 4 · answered Sep 01 '11 at 16:52

You can zip arguments from three list, and do the following:

for border, attr, value in myArgs:
    while True:
        i = 0
        try:
            setattr(excel.Selection.Borders(border), attr, value) 
        except:
            if i>100:
                break
        else:
            break

If your exceptions are trully random, this will try until success (with a limit of 100 tries). I don't recommend this.

Python clean way to wrap individual statements in a try except block

4 Answers4

Solution 1: Ignore the errors

Solution 2: Log the errors

Solution 3: Ask an expert

Linked