Data Exchange plugin

#121 Posted: 2/2/2014 12:10:31 PM

1417 likes in 2873 posts.

Group: Moderator

UPDATED

- SMath Studio 0.96.5146 or later required

- [EDIT: February 10, 2014] dll direct download removed (SMath without Extensions Manager is no longer supported)

best regards,

Davide

If you like my plugins please consider to support the program buying a license; for personal contributions to me: paypal.me/dcprojects

1 users liked this post

ioan92 2/3/2014 3:30:00 PM

#122 Posted: 12/15/2014 7:17:37 AM

owndante

1 likes in 15 posts.

Group: User

I runned in to an issue with the exportData.ODS and exportData.XLSX function. If the *.sm file is compiled to *.exe (with save as.. name.exe), the newly created *.exe file will not create ODS or XLSX files.

Thanks.

Export file Test.rar (752 KiB) downloaded 108 time(s).

#123 Posted: 12/15/2014 7:10:41 PM

Davide Carpi

1417 likes in 2873 posts.

Group: Moderator

This is because Viewer's Store.FileName property contains the file name of the parent SMath worksheet (I suppose is by design).

I've tried to implement a workaround (uploaded in the EM). Should works also on unix systems but I'm not sure. Test it deeply and report any issue. If it works I'll do the same for the XLSX plugin.

Best regards,

Davide

If you like my plugins please consider to support the program buying a license; for personal contributions to me: paypal.me/dcprojects

#124 Posted: 12/16/2014 6:39:02 AM

owndante

1 likes in 15 posts.

Group: User

Hello!

I've tested the update and now the ODS file is created.
If the exportODS function is not hidden in an area then it is working perfectly.
If you hide the exportODS function in an area then the exported (the outputfile)
ODSfile won't be refreshed if you make changes in the *.exe file (ex. ComboBox).

Thanks!
Szabi

ComboBoxTest.rar (1 MiB) downloaded 104 time(s).

#125 Posted: 12/16/2014 12:03:12 PM

Davide Carpi

1417 likes in 2873 posts.

Group: Moderator

Using collapsed area a file is created when you open the executable, this it means that the function is evaluated; the content of the collapsed region is no more evaluated when you change the combobox item. I guess the issue is in the RequestEvaluation triggered from the combobox in the SMathViewer vs the collapsed areas, that is not in my hands (API stuff).

BUG opened: SV-1254

If you like my plugins please consider to support the program buying a license; for personal contributions to me: paypal.me/dcprojects

#126 Posted: 12/17/2014 8:04:46 AM

owndante

1 likes in 15 posts.

Group: User

Thanks!

#127 Posted: 3/6/2015 4:32:44 PM

Alexander O. Melnik

127 likes in 494 posts.

Group: Moderator

Hello!

First of all - thank you for the plugin - I was playing with it for a while and it seems to import data from ODS very well.

Now, for the export function, is there a way to "modify" a single cell in existing ODS file? What I am trying to achieve is to use spreadsheet as a large data processor (it is quite good at it), while I would like to use Smath to feed variables into the spreadsheet and to get output from it.

Many thanks

#128 Posted: 3/6/2015 7:47:22 PM

Davide Carpi

1417 likes in 2873 posts.

Group: Moderator

Hello oxel007!

I guess this is not possible, at least without a third-party library to handle correctly the existing data.

If you need this, the simplest way is to reference the output spreadsheet of SMath in you spreadsheet; you have to open both the files the first time to link the cells, then your data processor will load automatically the data from the first even without opening it again (attached an example both for ODS and XLSX spreadsheets - links are shown as absolute but are relative, so if you keep the structure of the folders you can move them where do you want)

Referencing Workbooks.zip (40 KiB) downloaded 102 time(s).

If you like my plugins please consider to support the program buying a license; for personal contributions to me: paypal.me/dcprojects

#129 Posted: 3/6/2015 10:16:25 PM

Alexander O. Melnik

127 likes in 494 posts.

Group: Moderator

Davide,

I have spent some time trying to develop a complete solution that would export data from smath to xlsx or ods, recalculate the spreadsheet using new values, and import the updated output back into smath.

Unfortunately I am stuck.

1. Excel plugin does not seem to import value that is obtained by a formula (multiply one cell by another)
2. Neither XLSX nor ODS plugins update the spreadsheet using external links at the time of import.

Please see attached. If we successfully tackle this I believe it will be used by a few people - Smath and Excel are powerful tools and each has strengths and weaknesses.

Best Regards

SpreadSheet IO.zip (75 KiB) downloaded 100 time(s).

#130 Posted: 3/7/2015 9:58:16 AM

Davide Carpi

1417 likes in 2873 posts.

Group: Moderator

Thank you for sharing an example.

For the 1st point I think I've found a solution, an updated XLSX Import/Export plugin is available in the EM;

For the 2nd point, both the plugins handles just data from/to the files. Data preprocessed by SMath is written in the file or data preprocessed and saved by some kind of excel program is read from the file.

This means that to get updated results from the spreadsheet you have to open it, save the updated file, and then refresh the SMath worksheet (or split the SMath worksheet in 2 worksheets: open the first worksheet, open & update & save the spreadsheet, open the second worksheet)

To accomplish your task in just one step, another plugin with functions to trigger the third-party application in background is needed (something like a MS Excel COM plugin / LibreOffice Calc COM plugin / or a plugin that handles in background any other program you have on your system)

If you like my plugins please consider to support the program buying a license; for personal contributions to me: paypal.me/dcprojects

#131 Posted: 3/9/2015 2:24:49 PM

Alexander O. Melnik

127 likes in 494 posts.

Group: Moderator

That makes sense; possibly pyUNO can be used to update ODS file in background. However given my non-existing programming skills it is highly unlikely I will be able to incorporate it myself - I will just stick to reopening files manually.

Thank you for your help

#132 Posted: 3/9/2015 7:59:36 PM

Davide Carpi

1417 likes in 2873 posts.

Group: Moderator

You're welcome... When it will be possible I'll explore this thing (I've seen a couple of interesting posts for both the APIs of excel and libreoffice)

If you like my plugins please consider to support the program buying a license; for personal contributions to me: paypal.me/dcprojects

#133 Posted: 3/10/2015 8:00:52 PM

Alexander O. Melnik

127 likes in 494 posts.

Group: Moderator

Is there any particular way that I may assist you with this? If you point me in the right direction I could research how this feature may be implemented (xlsx files) - I am not a programmer but can attempt my best to piece pieces together.

A little bit background on why I think that is important:

Quite often we, number crunchers, have previously created spreadsheets (in Excel) to do specific tasks. Those spreadsheets have been triple checked by ourselves and others to contain no bugs. At times it is enforced that we use specific spreadsheets for specific calculations.

It would be immensely useful to use SMath Studio to input data in a given spreadsheet and to record corresponding output. In fact it will effectively make use of a spreadsheet as a monstrous data crunching formula that was previously verified to work by others. You feed in data, record output for different variable sets and present them in a readable format of SMath Workbook.

Best Regards

#134 Posted: 3/11/2015 9:22:48 AM

Davide Carpi

1417 likes in 2873 posts.

Group: Moderator

COM plugins are a unexplored field for me. My first idea is to have something like a Calculate.MSExcel("file") / Calculate.LOCalc("file") functions; I don't know if this is possible for the whole document, anyway should be possible for a range Calculate.XYZ("file","cells").

Base documentation:
- for excel: How to: Run Excel Calculations Programmatically
- for LO: SDK 4.3: Common Language Infrastructure (CLI) examples

If you like my plugins please consider to support the program buying a license; for personal contributions to me: paypal.me/dcprojects

#135 Posted: 10/5/2015 7:24:04 AM

b3nd3r

0 likes in 3 posts.

Group: User

@1st thank you w3b5urf3r_reloaded for this greeat plugin it supports my workflow very much

Currently the plugin also displays the unit conversion in a stored .odt files as documented below. Is it possible to add a feature to deactivate this displayed unit convserion?

Thank you for the plugin

b3nd3r

#136 Posted: 10/5/2015 7:53:35 AM

b3nd3r

0 likes in 3 posts.

Group: User

@1st thank you w3b5urf3r_reloaded for this greeat plugin it supports my workflow very much

Currently the plugin also displays the unit conversion in a stored .odt files as documented below. Is it possible to add a feature to deactivate this displayed unit convserion?

Thank you for the plugin

b3nd3r

#137 Posted: 1/20/2016 1:31:49 PM

Davide Carpi

1417 likes in 2873 posts.

Group: Moderator

PLUGIN UPDATED SMath Studio 0.97.5737

importData.ODS(): can access files opened by other processes (only saved data);
importData.ODS(): sheet name no more case-sensitive;
importData.ODS(3): range of cells accepted as 3rd argument;

If you like my plugins please consider to support the program buying a license; for personal contributions to me: paypal.me/dcprojects

2 users liked this post

ioan92 1/20/2016 1:36:00 PM, Radovan Omorjan 1/21/2016 5:40:00 AM

#138 Posted: 1/21/2016 12:40:29 AM

Jean Giraud

983 likes in 6866 posts.

Group: User

Wrote
It would be immensely useful to use SMath Studio to input data in a given spreadsheet and to record corresponding output. In fact it will effectively make use of a spreadsheet as a monstrous data crunching formula that was previously verified to work by others. You feed in data, record output for different variable sets and present them in a readable format of SMath Workbook.

_______________________

As soon as I was given Mathcad 11 by Mathsoft, Mathcad 11 came with Excel read/write
spread sheet. In the remainng of the > 120000 Collaboratory exchange [which I read all]
not a single *.XLS [now *.ODS] were exchanged. But lot of data were referenced in
technical/scientific web sites. The universal data exchange is *.dat. If you can't
paste directly in Mathcad because you want to get rid of comments ... just transit
via NotePad and clean, then copy/paste back in Mathcad data table. That would be useful
in Smath, at least to read *.dat.
Smath is a CAS, Excel is just a spread sheet.
A very minimal amount of Smath work could be exchanged, especially wrt the miserable
way many results are obtained from Smath. In 15 years, I have not even done 2+3 in Excel.
I guess it mothballed itself.
If you like so much "Spread Sheet", OriginLab is quite a tool and superb plotter as well
as doing lot of curve fitting.

Jean

#139 Posted: 1/22/2016 2:48:30 PM

Alexander O. Melnik

127 likes in 494 posts.

Group: Moderator

Wrote
In 15 years, I have not even done 2+3 in Excel.

Jean, it might not be true for you in particular, but for many in my field it is a requirement to re-use existing spreadsheets. Additionally it is hard to beat excel when you need to mass edit your formulas to correct an error or add a feature (in fact find/replace in not even an option in SMath). I would hate to make a choice between CAS and spreadsheets. Both are great; in their own ways.

Anyway, it has been quite a while since the post you are looking at - I have addressed my own request by creating plugin excel_IO, which I find very useful.

Cheers,

Alex M.

#140 Posted: 1/22/2016 8:54:50 PM

Jean Giraud

983 likes in 6866 posts.

Group: User

My friend: you have good arguments.
I developped my gorgeous "Thiele Continued Fraction" in Excel Millenium.
In 2005, switched to XP Home, result: zap all my previous Excel.
Lot more than Thiele. Nothing else was scrapped, only Excel.

FindReplace is more delicate than it looks: exact value, range ...

This work sheet works for me, why not for you ?

Jean

Utilities Matrix Find_Replace.sm (19 KiB) downloaded 121 time(s).

2 users liked this post

Davide Carpi 1/23/2016 8:50:00 AM, ioan92 1/23/2016 9:01:00 AM

SMath Forum

Data Exchange plugin - share data between SMath Studio and CSV/ODF/ODS/ODT/XeLaTeX files - Messages