Simulation of electronic structure is one of the most promising applications on noisy intermediate-scale quantum (NISQ) era devices. However, NISQ devices suffer from a number of challenges like limited qubit connectivity, short coherence times, and sizable gate error rates. Thus, desired quantum algorithms should require shallow circuit depths and low qubit counts to take advantage of these devices. Here, we attempt to reduce quantum resource requirements for molecular simulations on a quantum computer while maintaining the desired accuracy with the help of classical quantum chemical theories of canonical transformation and explicit correlation. In this work, compact ab initio Hamiltonians are generated classically, in the second quantized form, through an approximate similarity transformation of the Hamiltonian with (a) an explicitly correlated two-body unitary operator with generalized pair excitations that remove the Coulombic electron-electron singularities from the Hamiltonian and (b) a unitary one-body operator to efficiently capture the orbital relaxation effects required for accurate description of the excited states. The resulting transcorrelated Hamiltonians are able to describe both the ground and the excited states of molecular systems in a balanced manner. Using the variational quantum eigensolver (VQE) method based on the unitary coupled cluster with singles and doubles (UCCSD) ansatz and only a minimal basis set (ANO-RCC-MB), we demonstrate that the transcorrelated Hamiltonians can produce ground state energies comparable to the reference CCSD energies with the much larger cc-pVTZ basis set. This leads to a reduction in the number of required CNOT gates by more than 3 orders of magnitude for the chemical species studied in this work. Furthermore, using the quantum equation of motion (qEOM) formalism in conjunction with the transcorrelated Hamiltonian, we are able to reduce the deviations in the excitation energies from the reference EOM-CCSD/cc-pVTZ values by an order of magnitude. The transcorrelated Hamiltonians developed here are Hermitian and contain only one- and two-body interaction terms and thus can be easily combined with any quantum algorithm for accurate electronic structure simulations.