It's because the game - like other TES games - holds the promise of being something far greater than it actually is.
Essentially, the game presents a world that promises constant adventure, all kinds of unique things to find, varied enemies to battle, and amazing dungeons to delve into. There's also a crafting system, an economy, an alchemy and enchanting system, equipment and spells, and so on.
In reality, very little of this promise materialises in the actual game, and the systems are quickly shown to be shallow. But the thing is that the outline of a great game is there, the framework for something great, and for over a decade now, modders have been obsessed with adding to that framework in the hopes of actually creating the game that Skyrim promises to be.
I've come to the conclusion that it's never really going to happen and it's not possible, but a heavily-modded Skyrim can still offer a fun action-adventure game for anyone who wants one. Modding in itself is also fun when you really start to get into it, arguably more fun than the actual game itself for some people. To answer your questions:
Did modders found solutions to poor writing
Try Interesting NPCs. The quality varies, but there's some interesting use of TES lore in there and some decent characters to meet (quite a lot of shit ones too).
uninteresting exploration
This depends what you think was wrong with Skyrim's exploration. Stuff like Morrowloot (a very old mod that I'm sure has been superceded) places unique loot around in a Morrowind-esque way that rewards exploration. Options exist to remove level scaling too if you want to get your ass kicked early in the game and occasionally discover late-game equipment long before you "should" get it.
Requiem for a large-scale attempt to remake the entire combat system (and various other systems too), SkyRe for something more lightweight. Two very different approaches that both overhaul the game in their own ways, and both have pros and cons to weigh up.
Never looked into this much because Bethesda's animations really make me laugh, but yes, there's animation packs out there. You could probably get most of the vanilla animations replaced.
Nope, it's fucked. Special Edition is more stable than the original though, and the script extender can allow modders to take the engine to its absolute limits.