I have heard excellent examples in application of just about every type of tweeter. The key is putting a given tweeter in the right application with the right electrical integration to its mating speaker and enclosure.
I have also heard some terrible examples of applications of different tweeter types.
My favorite sounding tweeter of all time is the Scan Speak aluminum dome tweeter in the Harbeth C7ES3 speaker. What a gorgeous sounding tweeter.