You will run into some issues with differential equations with singularities.
Consider the differential operator $x\frac{d}{dx} -t $ from the trivial rank $1$ vector bundle on $\mathbb R$ to itself, for some constant $t$. The adjoint map is $-xd/dx -1 - t$, which is another operator in the same class.
If $t$ is not a nonnegative integer, then this complex is locally exact. We have to check that the differential equation $xdy/dx - ty =f(x)$ has solutions for smooth $f$. We will check this for $t<0$, but I think it is also true when $t$ is not a nonnegative integer. A solution is:
$$y = \frac{ f(0) }{t} + x^t \int_{0}^x \left(f(z)-f(0)\right) z^{-1-t} dz $$
This gives a global solution also, so it is globally exact. Moreover, since any solution to the differential equation is a multiple of $x^{t}$, if $t$ is not a nonnegative integer, there are no nonzero solutions, so the regular cohomology is trivial.
If $t$ is a negative integer, then the dual complex will have nontrivial $H^0$ due to the nonzero solutions, otherwise, say for $-1<t<0$, we can easily show that the dual complex, with $t$ also in that range, will have nontrivial compactly supported $H^1$. Indeed, since there are no solutions to the homogeneous version of the differential equation, our solution of the inhomogeneous version is unique, and we can easily find a compactly supported $f(x)$ where the unique solution $y$ is not compactly supported.
On the other hand, suppose we have a smoothness condition - specifically, that the kernel of the first map is a locally constant sheaf. In other words, a local solution to the differential equations that define the first map can be extended uniquely along any path, with possible monodromy.
Given a differential equation, a common trick is to add enough extra functions to make the equation first order. We can just as easily do this with a complex of vector bundles with differential equation operators - add variables to each map in the reverse order. This process is a homotopy equivalence of complexes, as is its dual.
Take the locally constant kernel sheaf, view it as a vector bundle iwth flat connection, and tensor it with the de Rham complex. We will build a map from this complex to the original one. This is plausible because they are both injective resolutions of the same thing, but we need to check it can be done with vector bundle maps. This is trivial in degree $0$. If we have built maps for the first $n$ degrees, we compose the $n$th map with $d$ and get a first-order function on $\Omega^{n-1}$ that vanishes, locally, on the image of anything from $\Omega^{n-2}$. Such a function, by the linear algebra of differential forms at a single point, depends only on $d$ of the form on $\Omega_{n}$.
This bundle map is a quasi-isomorphism of sheaf complexes. If we can check that its dual is also a quasi-isomorphism, we win - duality in an arbitrary locally free complex can be reduced to duality for the de Rham complex. By using mapping cones, it is sufficient to check that if a first-order complex is locally exact, its dual is also locally exact.
Let $V_0 \to V_1 \to \dots V_n$ be a locally exact first-order complex. We will actually be able to find a homotopy to $0$. $d: V_0 \to V_1$ is a first-order differential equation with no local solutions. If it has no solutions,it must have a formal reason. Specifically, if $f_1,..f_k$ are local coordinates for $V_0$, then by taking linear combinations of the differential equations, their derivatives, and the commutation relations,we must be able to obtain $f_1,\dots,f_k$. Otherwise we could solve it along curves and extend consistently to the whole space.
But this linear combination just gives an operator $k: V_1 \to V_0$ such that $kd$ is the identity. Now we have a differential equation $k\oplus d$ on $V_1$, still linear, that has no solutions. Repeating the process, we eventually get a homotopy between the bundle and $0$. Applying this homotopy to the dual, it will be locally exact as well.
Best Answer
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.