RE: Spreadsheet translation

[Windhorn, Allen E [ACIM/LSA/MKT]]

Doug,

From: Doug Stewart <address@hidden> 

On Wed, Jan 29, 2020 at 9:18 AM address@hidden wrote:
>> I have a very large, messy, and poorly commented spreadsheet, and I want
>> to translate it to Octave.  Any chance there's an automated way to do that?
>> Or even a way to copy all the formulae into a list?

> Does this help?
> https://wiki.octave.org/IO_package#Spreadsheet_formula_support

Yes, sort of (it means I can extract the formulae, but I still need to work).
Now I just need to (a) make sure all the formulae are correct Octave, and
(b) sort them so that all dependencies are satisfied.  Any hints on (b)?  I'm
not even sure that has to be possible.

I wrote the following code and appended it to the code from the wiki,
where txtarr1 has all the text cells in it:

clear txtarr2;
count = 1;
% Now extract all the formulae
stxt = size(txtarr1)
alph = ['A':'Z'];  % Index of the alphabet
for ii = 1:stxt(1)     % Loop over rows
  for jj = 1:stxt(2)   % Loop over columns (maybe a way to do without loops?)
    if length(txtarr1{ii,jj})>0    % If there is text, and it starts with "=",
      if substr(txtarr1{ii,jj}, 1, 1)=='=' % ... it's a formula
        cola = '';     % Column letter(s)
        ltr2 = alph(mod(jj,26)+1);   % Second letter is...
        if (jj>26) % Is there a first letter?
          cola = alph(fix(jj/26));     % If so, prepend it
        endif
        cola = [cola ltr2];    % Build one- or two-letter combo for column
        txtarr2{count++} = sprintf('%s%d %s', cola, ii, txtarr1{ii,jj});
        % append row #, then prepend to equation
      endif
    endif
  endfor
endfor

Any suggestions for improvement are welcome.  This works pretty well, but
many of the resultant formula cells have stuff like the following:

'Y24 =<v>0.23319999999999999</v></c><c r="Z24" s="6"><v>3.1E-2</v></c><c 
r="AA24" s="7"><v>0.13100000000000001</v>
</c><c r="AB24" s="6"><v>0</v></c><c r="AC24" s="6"><f t="shared" 
si="1"/><v>0.16200000000000001</v></c><c r="AM24" s="
6"><f>AM22'

where what shows up in the spreadsheet formula is '=W24+X24' and the
result is shown as 0.233.  What's up with that?  The original txtarr1 cell has
the same thing, so that's how Octave received it from the parsecell
function.

BTW, there are 3800 formulae in the spreadsheet, so converting them by
hand is not a pleasant prospect.

Regards,
Allen