No, mods cant be whatever the hell they want. They can only use exiting game parameters.
The best you could do is making a cannon-like ballista (one siege weapon modding is in), but you couldnt make explosive shells, multishot firing cannons, explosive traps, etc.
If the game doesnt have a similar thing, you cant mod it in, which it isnt likely to have without any explosives.
The main reason I would want gunpowder weapons involved is also to make siegers a bit mroe dangerous. Unlike now, they could simply pummel your fort full of holes from a distance untill you either come out, or just die. It makes them a bit more chalengeing then the current idiots who just charge in, find out you closed the gates, and just stand there waiting for magma and botls.
This kind of thing cant be modded in either.
Early cannons just seem to DF fine to me. Not the steampunk things, but the weapons they actualy used in the time that DF takes place. Crude, inaccurate, expensive and noisy, but deadly enough to be worth it if you have enough resources to spare.
Also, making cannons like the ones the turks used to make (8 meter long, 18 tons in weight, firing 700kg stones, suspended in a triangular frame by chains. Fires only about 7 times a day) seems more then dwarfy enough to me. Good reason to make bronze too.
For those who still dont think gunpowder is 'proper' (whatever they mean by that) there could simply be an init file setting to turn gunpowder on and off.
Lastelly, just for the record, something about the difference between ballistas and cannons that was discussed. Because (real) ballistas realy arnt half as powerfull as cannons.
First, ballista projectiles carry far less kenetic energy. They can only pierce a few opponents at once, under favourable conditions. Cannonballs, even stone ones, carry far more energy. They could shoot trhough far more enemies then a ballista arrow, due to larger mass and speed.
More importantely is though, that ballista arrows dont fly horizontally. Like any object within earths gravity, they fall and hit the ground. Cannonballs only have part of this problem, as they have a higher speed, thus having a more shallow arch. More important is that they can bounce of the ground. Yes, sounds silly, but when cannonball hits the ground at a shallow angle, it skids off the ground like a stone on the water. Thus, its at the correct height to kill things for a far greater distance then a ballista is.