[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
RE: Spreadsheet translation
From: |
Windhorn, Allen E [ACIM/LSA/MKT] |
Subject: |
RE: Spreadsheet translation |
Date: |
Wed, 29 Jan 2020 20:34:06 +0000 |
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
- Spreadsheet translation, Windhorn, Allen E [ACIM/LSA/MKT], 2020/01/29
- Re: Spreadsheet translation, Doug Stewart, 2020/01/29
- RE: Spreadsheet translation,
Windhorn, Allen E [ACIM/LSA/MKT] <=
- RE: Spreadsheet translation, PhilipNienhuis, 2020/01/30
- RE: Spreadsheet translation, Windhorn, Allen E [ACIM/LSA/MKT], 2020/01/30
- Re: Spreadsheet translation, Philip Nienhuis, 2020/01/30
- RE: Spreadsheet translation, Windhorn, Allen E [ACIM/LSA/MKT], 2020/01/30
- Re: Spreadsheet translation, Philip Nienhuis, 2020/01/31
- Re: Spreadsheet translation, Markus Mützel, 2020/01/31
- Re: Spreadsheet translation, Philip Nienhuis, 2020/01/31
- RE: Spreadsheet translation, Windhorn, Allen E [ACIM/LSA/MKT], 2020/01/31
- RE: Spreadsheet translation, PhilipNienhuis, 2020/01/31
- RE: Spreadsheet translation, Windhorn, Allen E [ACIM/LSA/MKT], 2020/01/31