I think it is in order, sometimes, and yes it has happened at our church. If you are in leadership in the church and you commit sin publicly, I think it makes sense that you repent publicly. That way you clear the air regarding your intentions.
If everyone in church knows I've been sleeping around (or whatever, that seems to be a big one), open repentance for something that everyone knows anyway, can only serve to help restore the person. Not that the congregation is trying to put you in heaven or hell, but you have a responsibility to the church that you serve to uphold the standard that is taught, which should be the standard of the Bible. When you fail to do that you don't just harm yourself, but you harm that body, and for some people, you harm the image of the body of Christ as a whole. The least you can do is repent to your local body and show remorse for the damage done. I think it has more to do with setting an example to those who follow you that it is right to repent when you have sinned. I would do it without being asked.