Hi, Cassio,
first of all it sounds very interesting for me because you are coming
for the financial market and you knowing what's missing and what can OOo
bring forward in this area.
We need exactly the help of experts or ISV's who doing exactly the same
for Excel and who want now enter a new market with OOo.
Some of the points you have addressed are probably only bugs and have to
be fixed. Other are probably missing features but could be addressed as
well depending on the demand and priorities.
Anyway i am sure that the developers of Calc can tell us more details here.
Related to your last point i would like to point you to NetBeans and our
OpenOffice.org API plugin for NetBeans. The plugin provides a Calc
Add-in wizard that helps you to create new add-in functions on a very
hight level and creates a complete working project with code skeletons
and everything else necessary for a complete oxt package. It allows you
to create a new add-in function in less than 5 minutes.
If you want to try it out, download the latest stable version of
NetBeans 6.7.1 or even the 6.8 Beta version. The OOo API plugin is
available directly in NetBeans via the plugin update center.
Additionally to NE and h lugin you need an office installation and the
OOo SDK. If you should have problems please let me know.
It's probably a good and useful tools for you to do some prototyping.
The generated code is Java but the IDL's and the xcu's can be used for a
C++ Add-In later on a well if necessary. I would prefer Java because of
the platform independence, means one Add-in developed on windows or any
other platform and working on all OOo supported systems. Our code
skeleton maker can generate a C++ skeleton as well and in both cases
(Java or C++) you have to focus on the real implementation only.
I am really interested to help you where i can and if i have time. And i
am sure that the Calc experts are also interested to help and support you.
Juergen
Cassio Neri wrote:
Hello all.
I wasn't sure if I should send this message to discuss or dev. It
addresses "strategic" points (in my opinion) for OOoCalc but,
sometimes, they get quite technical.
I work for the investment bank arm of a major German bank. Among my
dutties, I participate in the development of Excel add-ins.
Additionally, in my spare time I'm developing an open-source project
to help library developers to make their libraries easly accessible
through different front-ends including Excel and OOo Calc.
I want to share with you my experience hoping to help make OOo Calc
more appealing to financial institutions. I believe this would be a
good strategy since those companies are among the biggest users of
spreadsheet applications.
Although complex spreadsheets are a minority they play major roles.
For them built-in functions are not enough and institutions implement
in-house features through VBA or C++ add-ins.
Many other teams inside my bank also develop Excel add-ins in C++. The
overall framework followed by all teams is very similar. From
discussions with former colleagues, I know that the same happens in
other big institutions. My onw library also follows the same. In this
framework some add-in functions create objects which are stored in a
pool. Each object has an ID. Other functions take object IDs, process
the corresponding objects and return results to Excel.
Let's see a silly but illustrative example.
A function called CreateSquare creates a square and stores it in the
internal pool of objects. CreateSquare takes two parameters: The name
(ID) of the square and it's edge size. The result, returned to the
spreadsheet, is the object's name if it is successfully build or an
error message, otherwise.
Another function called GetArea takes the name of a square and returns its area.
Then, one can enter the formula =CreateSquare("Foo"; 2) in cell A1 and
=GetArea("Foo") in cell B1. The values shown in A1 and B1 are, resp.,
"Foo" and 4.
Typically, we turn off automatic calculation and do it manually, sheet
by sheet, pressing Shift+F9. There are many reasons for that
including:
1. In real world examples, object constructions or computations may be
very time consuming. Then we want to avoid calling those functions
before setting up the whole spreadsheet.
2. We need to control the order of evaluation. In the example above,
suppose we change A1 to =CreateSquare("Foo"; 3), that is, we have
changed the size of "Foo". Obviously, B1 need to be updated. However,
as far as the spreadsheet application is concerned, B1 is up to date
since it doesn't depend on A1. To avoid this issue we put the the two
formulas in different sheets and recalculate the sheets in the correct
order. Recall that in Excel Shift+F9 recalculates all changed formulas
in the current sheet. Hence, normally Excel wouldn't recalculate
=GetArea("Foo") except if GetArea is a volatile function. For this
reason, we typically make all add-in functions volatile.
One can argue that, since A1's value is "Foo", then putting
=GetArea(A1) in B1 would be a better solution. I don't disagree in
this case. However this is a simple example. In more complex
situations we really have to manually control the order of evaluation.
What are the issues with OOo Calc and what could be done? Please,
forgive me if I'm wrong, but I think:
1. Currently, there is no way to recalculate all changed formulas in
current sheet. Although the documentation says that F9 does so, in
fact, it doesn't. I've recently reported this bug
http://qa.openoffice.org/issues/show_bug.cgi?id=105743
I'm working on this issue and I going to submit a patch. With this
patch F9 recalculates all changed formulas in current sheet. This is
in accordance with OOo documentation. However it's in opposition with
my own suggestion in the bug report (that is, to use Shift+F9 as in
Excel). I've changed my mind because I found many places (in the web
and in OOo help) stating this behavior for F9.
I'll come back to this issue below.
2. As far as I know, there are no volatile functions in OpenOffice
add-ins. Of course, the OOo API allows add-in functions to return
volatile results. This is much more powerful than what we need and
consequently more complex to implement. All we need is to tell OOo
that some formulas must always be considered out of date even if they
don't seem to be.
Recall that in Excel, Shift+F9 recalculates all changed formulas in
the current sheet. Since for the add-ins that I'm considering all
functions are volatile, it implies that Shift+F9 recalculates all
(add-in) formulas in current sheet. Therefore, we can avoid any change
in the OOo API while keeping the same user's felling provided that
Shift+F9 recalculates all cells in the current sheet.
I hope I can make a patch for that.
Having considered issues 1 and 2 above, in a more general way, we have
issue number 3:
3. The majority of users work with automatic calculation turned on.
For this reason, I guess, OOo is not very well tested when automatic
calculation is turned off. I already found and reported another bug in
this set up.
http://qa.openoffice.org/issues/show_bug.cgi?id=106135
4. Sometimes, we want to recalculate just one cell which calls an
add-in function. In Excel it's enough to double click on the cell to
start it's edition and then press Enter without modifying anything. I
don't really know if Excel recalculates anyway or only because the
add-in function is volatile. OOo realizes that we haven't change
anything it doesn't recalculate. That's not a big issue. A simple
workaround is to make a fake change (e.g. one can press LeftArrow
before pressing Enter).
Unfortunately, it doesn't work when the result is an array. That's a
big issue. I would like to report a bug but I can't give a simple way
to reproduce it. The problem is that I have to provide the complete
code for an add-in. The is not a simple task: The simplest add-in I
can think of requires 4 files (one C++, one IDL and two XML) and
complicated instructions on how to build it.
Sorry for this long message.
Best regards,
Cassio.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscr...@openoffice.org
For additional commands, e-mail: dev-h...@openoffice.org
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscr...@openoffice.org
For additional commands, e-mail: dev-h...@openoffice.org
