[Solved] Help writing non-linear solver extension for Calc

[CLaub]

I am appealing to readers here for help in the form of direct assistance, or pointing me to people or resources who might assist me. I am a scientist who has developed an optimization code based on simulated annealing and have worked with the Nelder Mead downhill simplex method. There does not seem to be a good general non-linear solver extension available for Calc, and I would like to create one from my work. I don't have the experience developing things on the OpenOffice end. I have read through some docs available online, but would really like to partner up with someone who has more experience in this area. My code is written in C++ and compiled with gcc. I would like everything to be under a very open license in the spirit of Open Office.

Please let me know if there is a person or group who can help with the development of the Calc interface for my code. Thanks

-CL

[Villeroy]

http://extensions.openoffice.org/de/search/node/solver

[CLaub]

Villeroy, Thanks for your reply.

I have already tried all of these except the EuroSolver Pro (which is not free).

None of them will solve my non-linear minimization problem.

Please note that I am not looking for a non-linear programming (systems of non-linear equations with constraints) solver. I need and want to create and extension that is optimizing a single "merit function" (for example a sum of squares of error) for a non-linear function in several variables. An example of the application would be curve fitting, or fitting a function to a set of data.

[CLaub]

Well, it's not an extension, but I did find some OO Basic code that runs a Nelder-Mead Simplex non-linear minimization routine:
http://www.oooforum.org/forum/viewtopic.phtml?t=92089

It's actually pretty easy to implement: You open the OO Basic macro editor and paste in the code. Click Save.

To run the solver, use Tools>Macros>Run Macro and navigate to the macro name: MVOstart. This opens the dialog box and then you can start optimizing!

I tried this out and it works as advertised. But I would still like to write something like this to do simulated annealing using the code I developed previously...

-CL

[Villeroy]

You have the algorithms written in some programming language.
You have the open source office suite with similar open source extensions and tons of documentation.
You are knowledgable about spreadsheets (data types, referencing, number formats under different locales).
Now it is your turn to combine all this and write your extension.
No, it is not trivial.
Yes, you can ask concrete questions here.

[MrProgrammer]

I am appealing to readers here for help in … pointing me to … resources [that] might assist me.

OpenOffice.org (OOo) Calc Add-in How-to
Developer's Guide: Extensions
Programmer’s Tutorial
Application Programming Interface Reference

[CLaub]

Thanks for the suggestions and the links. After reading through this, it still seems to be a daunting task.

I am now considering using a similar approach to the author of the Nelder Mead code that I linked to above. This would require a re-write of my application in OO Basic, meaning it must be used as a macro function... but it has already shown me how to create an input parameter dialog box, etc. and I think I would be comfortable working in this programming environment.

Are macros incorporated into and distributed as part of the Calc spreadsheet file?

-CL

[MrProgrammer]

I'm really not the right person to answer this question, since I've only written a few simple macros and no extensions, but here's my answer based on what I've read in the forum.

Are macros incorporated into and distributed as part of the Calc spreadsheet file?

They could be. See Developers Guide > OpenOffice.org Basic > Advanced Library Organization > General Structure. The dialog is Tools > Macros > Organize Macros > OpenOffice Basic.

This would require a re-write of my application in OO Basic …

While you might need a front end written in Basic, I believe you can call your C++ routine from Basic and do most of the work there. I'm pretty sure this technique is discussed in the Macros and UNO API forum. Basic is a rather primitive language, best suited for simple tasks. For anything complex, use another language. Look first for posts on C++, but you'll have to do a web search for C++ site:openoffice.org since the search in this forum doesn't index the term "C++". If that doesn't help, look for posts on Python, not because you want to use that language, but because I believe you'll learn how to call another language from Basic. Others no doubt can help more than I. If it turns out that you can't call C++ from Basic, convert your code from C++ to Python, not C++ to Basic.

If no one else responds, I'd suggest posting a new question like "Can I call C++ from Basic?"

[hanya]

If no one else responds, I'd suggest posting a new question like "Can I call C++ from Basic?"

Windows environment supports declare statement but it depends on the library to access. The program written in C++ as standalone executable, it can be called through command line but the shell function of the Basic does not support standard input, so it should be connected with local file. If the library is packed as dynamic or static library, it can be wrapped as UNO component that can be called by any other language binding between UNO and target language if it is there. This way can be adapted to other language such as Java.
About a month ago, the following issue is opened with the patch to support declare command on other UNIX like environment: https://issues.apache.org/ooo/show_bug.cgi?id=112064

[robweir]

I am appealing to readers here for help in the form of direct assistance, or pointing me to people or resources who might assist me. I am a scientist who has developed an optimization code based on simulated annealing and have worked with the Nelder Mead downhill simplex method. There does not seem to be a good general non-linear solver extension available for Calc, and I would like to create one from my work. I don't have the experience developing things on the OpenOffice end. I have read through some docs available online, but would really like to partner up with someone who has more experience in this area. My code is written in C++ and compiled with gcc. I would like everything to be under a very open license in the spirit of Open Office.

Please let me know if there is a person or group who can help with the development of the Calc interface for my code. Thanks

-CL

Hi CL,

Maybe I can help with your question.

The linear solver we have built in to Calc is based on code from the COIN-OR project: http://www.coin-or.org/index.html

They have some other solvers, as well, but we have not looked into integrated them yet. Having a similar interface across possible solvers would make the UI as well as the coding more consistent.

If you want to discuss more, I invite you to join our API mailing list. This is the list we use to discuss coding OpenOffice extensions. To join the list send an email to api-subscribe@openoffice.apache.org. And once you are subscribed you can post by sending an email to api@openoffice.apache.org.

Regards,

-Rob