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.
I added formula support into the .xls and .ods scripts in svn. I made a
few amendments and changes to your suggested patch, and added a new
argument (options struct) to be able to read back formulas as text
strings (works only with POI, JXL and -somewhat with- OTK).
I also added try-catch structures in the java functions because user
input spreadsheet formulas may contain errors (and not all spreadsheet
functions are recognized by the Java interfaces and/or older Excel
versions) that make the writing scripts choke.
> I gave the POI interface a try attached a patch which enables this
> feature also there.
>
> This may not be a really nice implementation, but it boils down to
Please define "nice"..... :-) I think the scripts haven't been written
in a "nice" style anyway. but well, They Work.
> *) a string starting with "=" is interpreted as formula (like in the COM
> interface)
I added a check on ")" as last character.
> *) the "=" character is stripped off
Is only needed for POI. In COM, JXL and OTK the leading "=" is required.
> *) the corresponding formula text written using SetCellFormula
>
> Caveats:
> the POI interface appears to be english only, whereas the COM interface
> seems to use the installed excel's language only.
This still has to be sorted out. I'll try some Dutch Excel versions at
the office later this week. It would be nice if you could check this
with German (?) Excel versions.
> 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
Yeah, this means the file was already screwed up earlier on. JXL is very
picky; it needs a leading "=" and the function names must be in upper
case (...). And you need to create and add a formula cell.
Anyway, formula support now also works through the JXL interface.
Some remarks:
- Only POI and of course COM have a formula evaluator, i.e. only with
POI & COM the cached values in a spreadsheet are updated when writing
formulas to spreadsheets. This means that with JXL and OTK one needs to
read & write back the spreadsheet file with Excel /OOo Calc to be sure
the cached values are updated (or even created).
- Once again I found that JXL writing is really risky. If things go
wrong the spreadsheet can be completely borked, whether xlsclose is
invoked or not. This is noted in the io html doc but mentioning it again
and again doesn't hurt. N.B. the JXL author himself states that JXL is
geared towards reading.
I also found that adding two or more worksheets using an xlsopen -
oct2xls(1) - oct2xls(2) - [oct2xls(...) -] xlsclose sequence doesn't
work with JXL either.
In fact, JXL writing is actually not needed as POI is much better at
that. JXL is only useful for reading spreadsheets made with older Excel
versions ('95, BIFF5) as POI doesn't support that.
- JOD (.ods) has no formula support at all. Really a pity. I'm afraid it
may take one or two more years before jOpenDocuments' functionality is
on par with current OTK (except that JOD is much faster) - several
indispensable methods are still lacking and intermediate improvements
are kept in an svn that is only accessible for paying customers (note
that JOD = GPL'd).
- OTK yields formulas read back as literal text strings in the form of
"of=<reworked formula>". I left it at that but when I have more time
I'll check out the ODS specification for this.
- I cleaned up the code some more and added two support function files
in svn. These comprise code snippets that used to be doubled (or even
quadrupled) in the older script file versions.
- I'll update the docs later on.
Please try the new file versions and let me know if they work OK.
If they do I'll plan to upgrade the io package version within a month or so.
Best wishes,
Philip
------------------------------------------------------------------------------
The Palm PDK Hot Apps Program offers developers who use the
Plug-In Development Kit to bring their C/C++ apps to Palm for a share
of $1 Million in cash or HP Products. Visit us here for more details:
http://p.sf.net/sfu/dev2dev-palm
_______________________________________________
Octave-dev mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/octave-dev
