Mods from the Steam Workshop are found in the Steam Workshop content folder, the location of which depends on where you installed Steam. For the laptop I'm on right now, it's C:\Program Files (x86)\Steam\steamapps\workshop\content and then a numbered sub-folder that corresponds to Civ VI and then numbered sub-sub-folders (containing zip files) for each mod. Makes it a bit trickier to find particular mods (unless you've memorized that mod's number).