Lets start from the end, game consoles these days are basically optimized PCs. They are effectively nothing like the first, second, or even third generation consoles that had fairly unique hardware, desirable exclusives and so on.
Cell may have been difficult to program well for, but it speaks volumes about the current state of consoles that devs worry about being able to be lazy with ports. The idiot consumer will buy whatever anyway. The side effect of the first generations' relatively unique hardware profile is that games actually had to be optimized and tailored to the hardware. No lazy ports ( I still get pissy sometimes over ps3 dragon age port ). Exclusives had to be good or at least showcase what the console could do.
So is that what makes Cell infamous? The thing made news when could be run in a cluster[1].
I have a different take. Cell is the remnant of the consoles, when they were still worth one's time.