Hm... as a Steam/Valve fan, and a long time Civ fan, and a DRM hater, I really don't know what to think.
On one hand, Steam brings in lots of great things. I may actually give the online more of a chance now, since its integrated and isn't going to use Gamespy or something crap like that. I remember I barely ever touched the multiplayer in Civ4, mainly because none of my friends had it and it just wasn't that fun for me. But with Steam I'll be able to play with Steam friends right from the get-go, and I won't have to re-ad everyone. This could also help set up quick, fun games with friends. Plus with Steam we won't have to deal with the same DRM shit Bioshock 1 and 2 dealt with.
And on the other hand, I was perfectly happy playing alone. The game has infinite replayability since every new game has a brand new map, civs are randomized, and anything can happen. I didn't need to be connected online to have fun, the AI provided enough entertainment. Plus I doubt friends and lots of people would be willing to sit through a many hour game, going from ancient times to the future like I like to do. It just seems like a pointless addition to me since Civ4 offered a nice and easy way to play. No online required, infinite fun on your own, and that was it.
And I agree about the whole Steam monopoly thing. I know that right now Steam and Valve actually give a damn about their fans/costumers. But what will happen when Valve's gamer-friendly leadership is replaced with a not-so-gamer-friendly leadership. People say this won't happen, but once upon a time Microsoft was seen as the underdog, as the cool guy. Everyone loved Microsoft, and said that it would never turn into a big ass-hole corporation. And look what happened.