What is the meaning of Allah Almighty being in front of the worshipper, and does that contradict Him Almighty being in the heavens?

There is no contradiction between Allah the Almighty being above His creation and Him being in front of the worshipper's face. The meaning of "Indeed, Allah is in front of his face when he prays" is true on its apparent meaning. The two are reconciled by understanding that this description applies to created beings; for example, the sun is in front of the face of one who faces the east, even though it is in the sky. If such a combination is permissible for a created being, then it is even more so for the Creator. Even if their combination were impossible for a created being, it would not be impossible for the Creator, for "there is nothing like unto Him."