RE: Spreadsheet translation

[PhilipNienhuis]

Windhorn, Allen E [ACIM/LSA/MKT] wrote
> Doug,
> 
> From: Doug Stewart <

> doug.dastew@

> > 
> 
> On Wed, Jan 29, 2020 at 9:18 AM 

> Allen.Windhorn@.nidec

>  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'

Did you read the spreadsheet with option 'struct ("formulas_as_text", 1)' ?

If yes, please try another spreadsheet I/O "interface". It looks to me like
you have no additional SW installed so you fall back to the OCT interface.
That hasn't received much testing with formula output, so I'm not very
surprised that large parts of the spreadsheet cell XML are left in place.

Another thing I see is that there's a reference to a shared formula (i.e.,
the formula is not contained in an individual cell but rather in the
SharedStrings XML file in the .xlsx archive and the spreadsheet cell merely
holds a reference to that plus basic cell addresses).
That's a known bug for the OCT interface
(https://savannah.gnu.org/bugs/?52875) and a challenging one at that; its
priority has been lowered for a reason, and you're only the second person to
date to report a problem with it. Looking at the cell contents excerpt you
copied over I suppose it contains the contents of several spreadsheet cells
(each cell is what is between <c> .... </c> tags).

Chances are that with additional spreadsheet I/O SW like Apache POI, or, if
you are on Windows, just the OF windows package and MS-Excel, you'd able to
extract the formulae in a format more suitable for what you want to achieve.

Philip




--
Sent from: https://octave.1599824.n4.nabble.com/Octave-General-f1599825.html