I see in Civil War movies and such that when a canon ball lands, it's almost like an explosion, but I don't think ordinary balls really do that. How much damage would a canon actually do to troops on the ground? Obviously getting hit by a canon would probably kill you, but I mean in the grander scheme of the battle. It seems to me that launching a single canon ball at a time at the advancing forces would be kind of inefficient. Did they use other types of special antipersonnel ammunition? How many people could you expect to be effected by a single shot? How did they maximize effectiveness against the enemy.
Movies love exploding cannonballs because roundshot would be harder to represent and perhaps too gory for many movies.
Basically, the ideal use of roundshot would be to enfilade the enemy formation so that a single ball could kill and maim a dozen or more.
The antipersonnel ammunition would be the various forms of canister and grapeshot (not common in movies but not such a rarity as roundshot) or the invention of Henry Shrapnel first employed in the Napoleonic wars (movies love those little white clouds in the sky).
Canister could be devastating at close range: read the accounts of Pickett's Charge where Union artillery was firing double or even triple canister from close range. The effect is sometimes described as groups of men disappearing in a cloud of red mist.
Like I said, movies usually avoid the unimaginable carnage that roundshot and canister can inflict on human body.
Exploding shells existed but were mostly used from mortars during sieges (but not exclusively). A movie (and literary) example would be the mortal wounding of Prince Andrei by a grenade fired from a cannon in War and Peace.