Wentworth & Flexner, Dictionary of American Slang (1960) actually gives two definition of party pooper:
party pooper[;] party-pooper n. 1 The first person or couple to leave a party; fig., one who causes the end of a party or good time. Wide student use since before c1945. --> 2 One who so lacks vitality, interest, or personality that his presence is a detriment to the enjoyment of others; a killjoy; a wet blanket. Not common. [Example from 1956 omitted.]
Wentworth & Flexner defines the related terms pooped and pooped out as follows:
pooped adj. Exhausted, fatigued, etc. [Example from 1942 omitted.] Since c1930. Not now as common as "beat" or "bushed" among students and young adults.
pooped out = pooped; bushed. [Example from 1939 omitted.] Archaic and child use.
These entries suggest that the first "party poopers" weren't people who were unpleasant to be around; they were people who, by leaving a party, caused (or were blamed for causing) the party to start breaking up, as other attendees began thinking about whether they should go home, too. The first people to depart figuratively take the wind out of the party: By being tired ("pooped") themselves, they effectively tire or deflate the rest of the party, too.
As for the etymological source of poop in the sense of "tire out," Merriam-Webster's Eleventh Collegiate Dictionary (2003) doesn't hazard a suggestion, but Robert Hendrickson, The Facts on File Dictionary of Word and Phrase Origins (1997) has this:
pooped out. Anyone pooped out or all pooped feels something like the 19th-century seamen who used the expression pooped to indicate what happened when they were caught on the poop or aft deck of a ship when a wave crashed down and washed over them.