Spreadtab and newcommand

Question

I'm trying to achieve this kind of thing :

\newcommand{\test}[2]{#1 & :={#2} € & :={[-2,0] * [-1,0]} € \\}

\begin{spreadtab}{{tabularx}{\textwidth}{|X|r|r|}}
@ Col 1 & @ Col 2 & @ Col 3 \\
\hline
\test{2}{50}
\test{3}{100}
\hline
\multicolumn{2}{|r|}{@ Total} & :={sum(c2:[0,-1])} € \\
\end{spreadtab}

This doesn't work and shows me formulas instead of result.

I understand formulas aren't processed because they are in the command, but I didn't found how to resolve it.

----- edited 5 month later -----

The accepted answer is a pretty good answer to the question I was asking, but after some time unable to really getting what I wanted, I let it go and came back later when I had time, with another technique, not using spreadtab at all (it's a good module, but not adapted to my case).

For those reading this question, there is my solution :

\newcounter{total}
\setcounter{total}{0}
\newcommand{\test}[2]{
    \addtocounter{total}{\fpeval{#1 * #2}}
    #1 & #2 € & {\fpeval{#1 * #2} €} \\
}

\begin{tabularx}{\textwidth}{|X|r|r|}
Col 1 & Col 2 & Col 3 \\
\hline
\test{2}{50}
\test{3}{100}
\hline
\multicolumn{2}{|r|}{Total} & \the\numexpr\value{total} € \\
\end{tabularx}

This is a longer solution than what I wanted, with "manual" formulas, but this works well.

Answer 1

Better? Answer

A variant that doesn't need you to put \noexpand everywhere, but expands only a defined set of macros (in the order you added those). The macros need to be expandable and expand in one step (so nothing that gets optional arguments and nothing too fancy, well if they need more they are still only expanded once). The macros which should be expanded can't be in any braced group as then they will not be found and not expanded.

Use \AddXspreadtabMacro on a macro that should be expanded in the body of Xspreadtab, you can also use \RemXspreadtabMacro to remove a macro from the list which should be expanded. Both \Add... and \RemXspreadtabMacro work on a local scope.

\documentclass[]{article}

\usepackage{tabularx}
\usepackage{spreadtab}
\usepackage{xparse}

\ExplSyntaxOn
% will store the body of the spreadtab on which we're working
\tl_new:N \l_guildem_spreadtab_tl
% will store the macro names which should be expanded
\seq_new:N \l_guildem_expansion_seq
% used to signal that we should do another expansion loop for a macro
\bool_new:N \l__guildem_loop_bool
% add the macro to the list of macros which should be expanded
\NewDocumentCommand \AddXspreadtabMacro { m }
  {
    \seq_if_in:NnF \l_guildem_expansion_seq { #1 }
      {
        \seq_put_right:Nn \l_guildem_expansion_seq { #1 }
      }
  }
% remove a macro from the list
\NewDocumentCommand \RemXspreadtabMacro { m }
  {
    \seq_remove_all:Nn \l_guildem_expansion_seq { #1 }
  }
\cs_new_protected:Npn \__guildem_sanitize_spreadtab_aux:w #1 \q_nil \q_stop
  {
    \tl_set:No \l_guildem_spreadtab_tl { \use_none:n #1 }
  }
\cs_new_protected:Npn \__guildem_sanitize_spreadtab:
  {
    \exp_after:wN
    \__guildem_sanitize_spreadtab_aux:w \l_guildem_spreadtab_tl \q_stop
  }
\cs_new_protected:Npn \__guildem_expand_macro:n #1
  {
    % check whether the macro is in the body of the spreadtab
    \tl_if_in:NnT \l_guildem_spreadtab_tl { #1 }
      {
        \bool_set_true:N \l__guildem_loop_bool
        % define a helper function that expands the macro
        \cs_set:Npn \__guildem_expand_macro_aux:w ##1 #1 ##2 \q_stop
          {
            \tl_set:Nn \l_guildem_spreadtab_tl { ##1 }
            \tl_put_right:No \l_guildem_spreadtab_tl { #1 ##2 }
          }
      }
    % \l__guildem_loop_bool is true as long as the macro is still contained in
    % the body and not yet expanded
    \bool_while_do:Nn \l__guildem_loop_bool
      {
        % call the helper macro that expands the macro
        \exp_after:wN
        \__guildem_expand_macro_aux:w \l_guildem_spreadtab_tl \q_stop
        % check whether there is another occurrence of macro in the body
        \tl_if_in:NnF \l_guildem_spreadtab_tl { #1 }
          { \bool_set_false:N \l__guildem_loop_bool }
      }
  }
\cs_new_protected:Npn \__guildem_spreadtab:nn #1 #2
  {
    \begin{spreadtab}#1#2\end{spreadtab}
  }
\cs_generate_variant:Nn \__guildem_spreadtab:nn { nV }
\NewDocumentEnvironment{Xspreadtab}{O{} m b}
  {
    % adding \q_nil on both sides to not accidentally remove a group while later
    % parsing.
    \tl_set:Nn \l_guildem_spreadtab_tl { \q_nil #3 \q_nil }
    % step over the macros which should be expanded and expand those
    \seq_map_function:NN \l_guildem_expansion_seq \__guildem_expand_macro:n
    % remove the \q_nil from both ends
    \__guildem_sanitize_spreadtab:
    % output the spreadtab
    \__guildem_spreadtab:nV { [{#1}]{#2} } \l_guildem_spreadtab_tl
  }
  {}
\ExplSyntaxOff

\newcommand{\test}[2]{#1 & :={#2} € & :={[-2,0] * [-1,0]} €}
\AddXspreadtabMacro\test

\begin{document}
\begin{Xspreadtab}{{tabularx}{\textwidth}{|X|r|r|}}
@ Col 1 & @ Col 2 & @ Col 3 \\
\hline
\test{2}{50} \\
\test{3}{100} \\
\hline
\multicolumn{2}{|r|}{@ Total} & :={sum(c2:[0,-1])} € \\
\end{Xspreadtab}
\end{document}

Original Answer

The problem is, that spreadtab doesn't expand its contents before searching for formulas. You could (in theory) create an environment that works like spreadtab but expands its contents before forwarding it to spreadtab, though this would make it necessary to put \noexpand before every macro that shouldn't be expanded, so the input doesn't really get easier.

\documentclass[]{article}

\usepackage{tabularx}
\usepackage{spreadtab}

\usepackage{xparse}
\ExplSyntaxOn
\cs_new:Npn \Xspreadtab_expansion_helper:nn #1 #2 { #1 #2 }
\cs_generate_variant:Nn \Xspreadtab_expansion_helper:nn { nx }
\NewDocumentEnvironment{Xspreadtab}{O{} m b}
  {
    \Xspreadtab_expansion_helper:nx { \begin{spreadtab}[{#1}]{#2} } { #3 }
    \end{spreadtab}
  }
  {}
\ExplSyntaxOff

\newcommand{\test}[2]{#1 & :={#2} € & :={[-2,0] * [-1,0]} €}

\begin{document}
\begin{Xspreadtab}{{tabularx}{\textwidth}{|X|r|r|}}
@ Col 1 & @ Col 2 & @ Col 3 \noexpand\\
\noexpand\hline
\test{2}{50} \noexpand\\
\test{3}{100} \noexpand\\
\noexpand\hline
\noexpand\multicolumn{2}{|r|}{@ Total} & :={sum(c2:[0,-1])} € \noexpand\\
\end{Xspreadtab}
\end{document}