Don Giovanni is one of Mozart's most popular operas. The title character is clearly the villain; he attempts rape and commits a murder in the first scene, and he has a long history of seducing women and walking away from them. In the final scene, though, something strange happens. It's easy to feel sympathy for him. He hasn't actually earned any, so what's happening?
Here's a quick summary for anyone unfamiliar with the opera. Don Giovanni (the Italian name for Don Juan) is driven off by Donna Anna, whom he had just attacked ("date rape" would be the modern term). Her father, the Commendatore, challenges him, and Don Giovanni easily kills the older man. Later on, Giovanni finds a statue of the Commendatore, which speaks to him. He invites the statue to dinner. The Commendatore's statue walks into Giovanni's home and demands that he repent. Giovanni refuses, and demons drag him to Hell.
What happens between these scenes establishes that Don Giovanni is a scoundrel. Why, then, object to his damnation? (Aside from objections to the idea of eternal punishment, which aren't the point here.) Because Mozart was brilliant and used his musical skills to flip the moral landscape. (He did that in The Magic Flute as well, though the situation there is quite different.)
Leporello's panic on seeing the Commendatore arriving sets the mood. The music when he enters is some of the most terrifying Mozart wrote. Giovanni responds politely and courageously, even when the statue grasps his hand with an unbreakable, freezing grip.
While the statue supposedly is a messenger from heaven, offering a last chance for salvation, he feels like an emissary from Hell. Don Giovanni is a human being, even if he's a very bad one, standing up to a statue animated by supernatural forces. If we're supposed to understand that these forces are doing God's dirty work for him, that's even worse. We side with human courage and defiance.
After his scream dies away, the other characters enter, and Mozart tries to reset the moral balance. The concluding scene is weak by comparison. Maybe that was his intention.
If you find yourself echoing Giovanni's cries of "No!" it's all right. It doesn't mean you approve of people who do what he did. It just means that you understand and respond to courage, even when it's misapplied, and especially when Mozart is underscoring the conflict with his music.
On another part of the opera: In Taking Funny Music Seriously (an excellent book, by the way), author Lily E. Hirsch takes issue with Leporello's "Madamina" aria, where he details his master's activities. Hirsch says the aria is "more disgusting and disturbing than funny, though [Mozart's] librettist must of course share some of the blame." She refers to "the assumption that the titular character is somehow a hero."
Elsewhere in the book, she has a whole chapter on funny songs about death, including a couple by Tom Lehrer on a nuclear holocaust, and she doesn't call them "disgusting and disturbing." The opera shows Giovanni being repeatedly cruel and getting his comeuppance, so it would take a warped mind to consider him a hero. I've just discussed the final scene, which is the only place where he draws some sympathy but still isn't a hero.
The song contributes to the picture of the protagonist as indifferent to the people he harms. The humor is in the gross exaggeration, combined with Leporello's meticulous record-keeping. In Spain alone, he's recorded 1,003 conquests, all written down in the book. These numbers are part of the setup for a well-deserved downfall.