Yes, "son of a gun" can be used as an expression of excitement, but is more commonly a familiar and friendly form of address to someone you know well. My father used to call me that occasionally when I was a teenager. It is a bit old fashioned and is not much used today.
The origin of the phrase is as follows: It is folklore that in the American wild west, if a girl became pregnant, her father would take his gun and drag the father-to-be and his daughter to the local priest and force them to marry. This is know as a "shotgun wedding", and, if the child is a boy, he is known as a "son of a gun".