Hi Benjamin:
Benjamin Lindner wrote:
> Hi Philip,
>
> Your xls interface functions work quite well.
> I just today realized that using the COM interface one can not only
> write text and numbers, but also formulas and these are recognized
> as such by excel.
Yes, I got that working from the very start but didn't pay much
attention to it as I was primarily after straightforward data exchange.
> I gave the POI interface a try attached a patch which enables this
> feature also there.
In one of the early oct2xls versions (e.g., rev 6682, around line 460)
you can find a similar construct. I needed it to write boolean values
using the java interfaces; that didn't work straight away initially - my
kludge was to enter a boolean formula and then evaluate it in the
spreadsheet. This kludge could be dropped since Michael Goffioul finally
suggested a patch for __java__.cc in the java pkg. __java__.cc is still
not patched in svn, so my kludge is probably still present (I'm not
sure, I have to check again) in oct2xls.m and/or oct2ods.m.
> This may not be a really nice implementation, but it boils down to
> *) a string starting with "=" is interpreted as formula (like in the COM
> interface)
> *) the "=" character is stripped off
> *) the corresponding formula text written using SetCellFormula
>
> Works for me using this simple test code and excel 2003:
>
> xls = xlsopen("foo.xls", 1, "POI");
> xls = oct2xls({1;2;3}, xls, 1, "A1");
> xls = oct2xls({"=SUM(A1:A3)"}, xls, 1, "B3");
> xlsclose(xls);
>
> Caveats:
Let's call it "issues".
I'm a bit hesitant to implement this "feature". I'd rather await some
more thinking over and discussion.
> the POI interface appears to be english only, whereas the COM interface
> seems to use the installed excel's language only.
Language specific stuff is one of the reasons I didn't pursue this
(yet). As for Excel, I regularly read/write Excel files using a mix of
Dutch and English Excel versions. AFAIK the functions are referenced
internally by some enumeration so I think this is no big deal; it's the
macros that are language dependent.
Anyway, something for further investigation.
Other issues are:
(1) Function / formula input doesn't work very reliably (=more testing
is needed)
(2) Testing for "=" as first character is tricky, a check for a ")" as
last character is indispensable IMO.
But... I can think up perfectly valid strings starting with "=" and
ending in ")" to be input in a spreadsheet. There are always corner
cases. In the end a formula validator (evaluator) cannot be avoided.
In the Excel GUI, input starting with = means a formula is to be
expected; input starting with '= means a text string. I haven't looked
deeply yet how to mimick this in octave's spreadsheet I/O, but it is not
straightforward.
(3) There's no check at all for formula validity; it is the users
responsibility to ensure this. In the end I can imagine that really
fubarred formula input can render a spreadsheet completely unreadable.
For proper validation you actually need a formula evaluator.
ActiveX/COM checks "on the fly" (as it is a hidden Excel itself which
does the checking). Apache POI does have a formula evaluator but it
still lacks an Excel function or two. Neither JExcelAPI nor the ODS
libraries feature formula evaluators (yet?)
(4) If no formula evaluator is present, you cannot write a spreadsheet
and expect the formulas to have been evaluated (let alone validated)
when reading it back at a later time - only Excel (or for that matter,
OOo Calc) can do that for you and then still depending on recalculation
settings.
This is obvious when you think about it, but perhaps too unexpected for
unwary users.
(5) ML compatibility. High on the list, not least because there are
plans to port the functions to ML too (me and my colleagues need better
Excel I/O plus ODS support for ML on non-windows platforms like Mac and
Linux, a.o. to more easily mix ML& octave usage)
> I also gave the JXL interface a try but I am getting the error
>> xls = oct2xls({"=SUM(A1:A3)"}, xls, 1, "B3");
> error: [java] java.lang.NoSuchMethodException: createWorkbook
> error: called from:
> error: C:\Octave\3.2.4_gcc-4.4.0\share\octave\packages\io-1.0.11\oct2xls.m
> at line 556, column 6
> error: C:\Octave\3.2.4_gcc-4.4.0\share\octave\packages\io-1.0.11\oct2xls.m
> at line 94, column 17
I'll check this out but not very soon as I'm actually on holiday with my
family (the current camp site has wifi so I could catch your email more
or less "by accident").
Thank you anyway for your patches.
Actually I was more or less ready for a next io-pkg update but still
awaiting a new jOpenDocument version (1.2b4 that -hopefully- will
finally enable much faster & reliable OO.o Calc I/O than ODF Toolkit. So
in a way your patches come in timely :-)
I've been thinking about formula input already a bit longer, especially
on how to distinguish formulas from plain text. I thought about global
settings (some flag for 'first "=" means formula', flag not set =
literal text input) or extra arguments to xlswrite in the form of a cell
array (containing options), etc.
None of these would be ML-compatible.
I'd suggest to wait and think some more. Somewhere in August I think I
may find an opportunity to look better at this.
Thanks,
Philip
------------------------------------------------------------------------------
This SF.net email is sponsored by Sprint
What will you do first with EVO, the first 4G phone?
Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
_______________________________________________
Octave-dev mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/octave-dev
