The status of the following answer is a bit speculative, unfortunately. To be precise, I believe that all of what I say below is true; I also believe that there is no proof in the literature of some of the things I state below, and I am not enough of an expert to supply those proofs. Nevertheless I'm putting it out there in the hope that it can be helpful.
First of all, when dealing with unbounded complexes of sheaves one runs into the issue of hypercompleteness, and I have to say something about this. Let's first do the purely psychological change that instead of complexes of sheaves, we think of sheaves of complexes. Chain complexes are most naturally thought of as an $\infty$-category, once we localize at quasi-isomorphisms, so one is then led to thinking about sheaves valued in an $\infty$-category. Now a sheaf on a space $X$ in a complete 1-category $C$ is is a functor $F \colon \mathrm{Op}(X)^{op} \to C$ such that if $\{U_i \to U\}$ is an open cover of a subset $U$, then $F(U)$ is the equalizer (limit) of the two arrows $\prod_i F(U_i) \to \prod_{i,j} F(U_i \cap U_j)$. If $C$ is an $\infty$-category then the limit must be a homotopy limit, taking higher coherences into account, and then the proper definition of a $C$-valued sheaf turns out to be that the natural map from $F(U)$ to the (homotopy) limit of the cosimplicial diagram which in level $n$ is given by $\prod_{i_1,\ldots,i_n} F(U_{i_1} \cap \ldots \cap U_{i_n})$, is an isomorphism. This specializes to the usual $1$-categorical sheaf axiom when $C$ is a 1-category.
When we take $C$ to be the $\infty$-category of bounded below cochain complexes modulo quasi-isomorphism, then the $\infty$-category of $C$-valued sheaves on $X$ is an $\infty$-categorical enhancement of the derived category $D^+(X)$. But if we consider instead unbounded complexes, the analogous statement is false. Namely, the $\infty$-categorical sheaf axiom from the previous paragraph describes what's known as descent for Cech covers. To recover the classical unbounded derived category one must instead impose descent for hypercovers. In a sense this has been known since the 60's, and traditionally this has been interpreted as meaning that Cech descent produces the "wrong" answer, and hypercovers "correct" this deficiency. After Lurie, a more modern perspective is that Cech descent is for many purposes more natural. In any case, the upshot is that for a space $X$ there are two typically inequivalent notions one can consider: $\infty$-sheaves on $X$ valued in unbounded complexes of sheaves, and hypersheaves on $X$ valued in unbounded complexes. The latter category can be recovered from the former via the process of hypercompletion, and the latter produces an $\infty$-categorical enhancement of the classical unbounded derived category.
Now you mention the condition $(\ast)$ in Spaltenstein's paper, which looks like it is just an annoying technicality, and whether it can be removed using more modern homotopical machinery. I believe instead that the result is just plain false without some condition like $(\ast)$. More specifically I think that if a space $X$ satisfies condition $(\ast)$ then this forces Cech descent and hyperdescent to coincide for abelian sheaves, and that this is fundamentally the reason that $(\ast)$ appears in Spaltenstein's paper. Namely, Spaltenstein's Theorem B concerns the classical unbounded derived category, and I believe that all of these results fail in general when one works with hypersheaves. But a very general version of Spaltenstein's Theorem B should hold if one works with $\infty$-sheaves throughout, with no condition like $(\ast)$ or finiteness.
Here's one reason to believe this. Part of Spaltenstein's Theorem B is proper base change. In Higher Topos Theory, Lurie proves a very general nonabelian version of proper base change, which implies the classical one. Crucially, Lurie's version of proper base change is a theorem for $\infty$-sheaves, not hypersheaves (and he gives an example where proper base change fails for hypersheaves). In particular it implies a form of proper base change for $\infty$-sheaves of unbounded complexes on a space $X$, and not for hypersheaves. Now I should add that in Higher Topos Theory Lurie only considers proper morphisms, so he works in the setting where $f_! = f_\ast$. So he does not introduce the functors $f_!$ or $f^!$ to state his result.
Another indication that $(\ast)$ is actually about hypercompleteness is that Spaltenstein remarks that locally finite dimensional spaces satisfy $(\ast)$. But locally finite dimensional things should also be hypercomplete. More precisely, Lurie proves this statement in HTT, if "dimension" is interpreted as "homotopy dimension", a notion that he introduces. For paracompact topological spaces, "homotopy dimension" coincides with "covering dimension". Spaltenstein doesn't elaborate on what notion of dimension he's thinking of but I assume cohomological dimension.
In any case, it is true that higher category theory can be used to give constructions of $f_!$ and $f^!$, under milder hypotheses than what Spaltenstein uses. Namely, $f_!$ and $f^!$ should exist for any continuous map between locally compact Hausdorff spaces, for $\infty$-sheaves valued in any complete and cocomplete stable $\infty$-category. Unlike Lurie's proper base theorem which is fully nonabelian (ie works for sheaves of spaces), this part of the story uses stability in a crucial way. In Higher Topos Theory, Lurie proves that on a locally compact Hausdorff space $X$, $\infty$-sheaves on $X$ can be described equivalently in terms of functors taking values on open subsets of $X$, or taking values on compact subsets of $X$. If the target category is moreover stable then this can be used to construct an equivalence of $\infty$-categories between sheaves and cosheaves on $X$. There is a natural pushforward operation on cosheaves, much like the pushforward of sheaves. Translating the pushforward functor via the sheaf-cosheaf equivalence to an operation on sheaves, one recovers precisely the functor $f_!$. By the adjoint functor theorem one directly gets $f^!$, too. This is sketched in https://www.math.ias.edu/~lurie/282ynotes/LectureXXI-Verdier.pdf
In your second question it sort of sounds like you are asking about whether versions of the functors $f_!$ and $f^!$ can be useful in coherent cohomology (and you are not just asking about Grothendieck duality). Something like this is true in the setting of condensed mathematics. One version of six-functors formalism is in the final chapter of Scholze's "Condensed mathematics", but even closer to what you're looking for is I think the material from Dustin Clausen's final lecture in the Copenhagen Masterclass (you can find it on Youtube), which I will not attempt to summarize.
Best Answer
I think here is how one should understand the last paragraph of the wiki.
Consider $f:X\to pt$. We have (all functors are derived and my $Hom$ are sheaf $Hom$) $$ Hom_{pt}(f_!F,F)=f_*Hom_X(F,f^!F) $$
$f_!F=\Gamma_c(X,F)$, so the l.h.s. computes the dual of the cohomology with compact support of the constant sheaf $F$, i.e. the dual of the cohomology with compact support of $X$.
$Hom_X(F,f^!F)= \Gamma(f^!F)$ and thus the r.h.s. is $f_*(\Gamma(f^!F))=\Gamma(X,f^!F)$, the homology of $X$.
EDIT : to answer precisely the question I include a summary of the comments.
there is a typo in the wiki: $[F,X[k]]$ should be understood as $[F,F[k]]$.
for any sheaf of $F$-modules $S$ (concentrated in degree 0), $[F[−k],S]=H^k(X,S)$.
Contrary to what is claimed in the wiki, there is no duality between $H^k(X,F)=[F[−k],F]$ and $H_k(X,F)=[F[−k],D_X]$ (where $D_X:=f^!F$ for $f:X\to pt$). The duality is, either between $H^k_c(X,F)$ and $H_k(X,F)$, or between $H^k(X,F)$ and $H_k^{BM}(X,F)$. And as far as I understand $H^*(X,S)$ is dual to $H_c^*(X,S^\vee)$.