SMath Studio Forum
»
SMath Studio
»
Extensions
»
EXCEL (2003+) I/O with recalculation and export to PNG
  Rank: Advanced Member Groups: Registered
Joined: 25/09/2013(UTC) Posts: 325  Location: IL Was thanked: 19 time(s) in 17 post(s)
|
I'm trying to understand the limits of the plugin before getting started...
In general this calculation allows information to be sent to excel to do some "work" then return the output to SMath. This is great!
This goes along with the whole idea of trying to use separate "apps" and use Smath to tie them all together. What I had originally asked for was some way to do this with other .sm files. Essentially this would be an extension of a function.
My concern with just using functions is Global variables overwriting local variables of the function. The other general issue is speed. Once you start getting a lot of function calls you start to slow down the program when there is a total recalculation (but this is a separate issue not pertaining to my current questions).
So I'm wondering with this plugin...
1. Could you use in a function? This could eliminate my first concern regarding global variables. I'm wondering if you had a beam calculator created in excel. You store one instance of the beam calculator.xls in your folder and then in your .sm page you create a function to send values to the .xls file. For each beam in your .sm page you call the function which I/O to the .xls file?
2. Is it possible to create a plugin which basically does the same thing but with .sm files? Maybe this already exists?
|
|
|
|
|
|
Rank: Advanced Member Groups: Registered
Joined: 03/03/2014(UTC) Posts: 418  Was thanked: 125 time(s) in 96 post(s)
|
RFreund,
To begin with I would like to say that I have developed this plugin because I believe that both EXCEL and SMath are wonderful, however different calculation tools. EXCEL is great for data sets and for calculations that are to be mass-edited in future, while SMath is very powerful at creating visual calculation books that are easy to follow and understand.
Hand calcs and spreadsheets coexisted long before arrival of computer, so why shouldn't they now?
Now to address your comments:
Speed - except for excel_PNG function all other functions exchange data with EXCEL process in memory (real-time)and are extremely fast after initial start up.
Using EXCEL as a function - this is the original purpose of the plugin - SMath feeds data to EXCEL and Exports the output.
Doing something similar with .sm files - my plugin uses EXCEL api and I would not know how to make it work with other SMath workbooks.
Hope this helps
|
|
|
|
|
|
Rank: Advanced Member Groups: Registered
Joined: 03/03/2014(UTC) Posts: 418 Was thanked: 125 time(s) in 96 post(s)
|
Gentlemen, I need some ideas here.
After real life use of the plugin I have realized that excel_PNG slows down the workbook if used extensively. Workbook with 12 "live" excel_PNG image regions take 9 seconds to recalculate, while same workbook with evaluation disabled for those regions take only 1.5-2 seconds.
After stripping down excel_PNG function to basics I have realized that I cannot speed it up significantly. Just to copy the range to clipboard without adding row/column headings takes 0.45 second, while "full" function call with headings and writing PNG to disk takes 0.7 second.
I would like to come up with an approach that will make excel_PNG run only if .xls file running in the background was changed. One of the ways to do it is to make excel_IN function generate a .txt file with input string (filename = input cell range, e.g. A1B7.txt), then next time excel_IN is run it will compare previous input to current input and return a True/False which excel_PNG can pick up.
This might work however far from elegant. Also this would not work if excel_IN constantly modifies same cell range.
Would you please give it a thought and throw in ideas on how to make excel_PNG run selectively (based on changed input)?
|
|
|
|
|
|
Rank: Advanced Member Groups: Registered
Joined: 15/04/2012(UTC) Posts: 1,984  Was thanked: 1124 time(s) in 721 post(s)
|
I don't know if that might be an issue here, but sometimes switching input operations to numeric optimization can make the day. At least, with inputdata, symbolic opt gives you quadratic scaling of computation time with file size, whereas numeric optimization leads to just linear scaling. |
|
|
|
|
|
|
Rank: Advanced Member Groups: Registered
Joined: 03/03/2014(UTC) Posts: 418 Was thanked: 125 time(s) in 96 post(s)
|
Originally Posted by: mkraska  I don't know if that might be an issue here, but sometimes switching input operations to numeric optimization can make the day. Martin, if you are reffering to choosing "numeric optimization" from context menu in SMath workbook it does not have much effect.. If I did not understand you correctly please explain 
|
|
|
|
|
|
Rank: Advanced Member Groups: Registered
Joined: 03/03/2014(UTC) Posts: 418 Was thanked: 125 time(s) in 96 post(s)
|
Plugin update, v 0.0.6:
- excel_IN function, if succesful, generates a hash code of input data
- excel_PNGv2 function uses input hash code to decide whether new png file needs to be exported (it creates a image.png.txt file where it stores hashcode to be compared to the new one later)
|
|
|
|
|
|
Rank: Advanced Member Groups: Registered
Joined: 03/03/2014(UTC) Posts: 418 Was thanked: 125 time(s) in 96 post(s)
|
April 25th, 2015 - Plugin update, v 0.0.10:
- added 3 functions to facilitate selective calculation of SMath Workbook Equations - txt_IN, txt_OUT, txt_HASH. While not elegant, functions allow to create an if() loop that can compare previous input (saved to .txt) to current one and either run the expression or read previously saved output from .txt
- txt_IN - exports input to .txt file
- txt_OUT - imports all text from .txt file
- txt_HASH - generates numeric hash value of input
|
 1 user thanked Alex M. for this useful post.
|
|
|
|
Rank: Advanced Member Groups: Registered
Joined: 03/03/2014(UTC) Posts: 418 Was thanked: 125 time(s) in 96 post(s)
|
Set of latest changes... And we are out of BETA!
October 30th, 2015 - Plugin update, v 0.1.2.2, plugin upload pending:
- fixed bug on import of char(34) symbol - quotation mark "
- fixed bug on export of single string that includes a comma
- known bug - export of matrix, one of which elements includes comma. My code splits the mat(,,,,) string by commas to convert it to array, so currently no easy fix.
November 19th, 2015 - Plugin update:
- Plugin works with units
- PNG export (excel_PNGv2) checks whether output cells changed automatically
- excel_PNGv0 addded, exported file does not contain row/column reference
- excel_QUIT now can selectively close EXCEL program or just a workbook
- SampleHowTo.zip is added at the 1st post - it is a good guide to possible uses of the plugin
Plugin is fully functional - in fact I use it at work quite extensively, and have fixed few bugs over time.
|
2 users thanked Alex M. for this useful post.
|
on 19/11/2015(UTC), on 19/11/2015(UTC)
|
|
|
Rank: Advanced Member Groups: Registered
Joined: 12/03/2011(UTC)
Posts: 101
Location: Chicago
Was thanked: 16 time(s) in 7 post(s)
|
oxe007-
thanks for this, i have been using it a bunch this week.
much appreciated
dennis |
|
|
|
|
|
|
Rank: Advanced Member Groups: Registered
Joined: 03/03/2014(UTC) Posts: 418 Was thanked: 125 time(s) in 96 post(s)
|
I'm glad you find the plugin useful! Plugin update: - I have attempted to make functions excel_VISIBLE & excel_QUIT redundant (I did not remove them yet). When SMath quits, my plugin is supposed to close (WITHOUT SAVING) all the EXCEL workbooks it opened OR connected to and than make excel visible again. Please test and report. If you have suggestions on implementing this feature please let me know. Right now I am contemplating whether it should close EXCEL workbooks with or without saving. Edited by user 23 November 2015 01:02:14(UTC)
| Reason: Not specified
|
1 user thanked Alex M. for this useful post.
|
|
|
|
Rank: Advanced Member Groups: Registered
Joined: 03/03/2014(UTC) Posts: 418 Was thanked: 125 time(s) in 96 post(s)
|
Plugin update (NOV 27 2015):
- Likely finalized way of handling EXCEL process in background:
- if EXCEL workbook passed to a plugin is already open, the plugin connects to it and saves it when SMath quits.
- if EXCEL workbook passed to a plugin is not already open, the plugin opens it and saves / closes it when SMath quits.
- When plugin works with excel it makes excel window not visible. Excel becomes visible when SMath quits
- Plugin does not close workbooks it does not open / connect to when SMath quits
I think this is final.
Report bugs - there might be some!
|
1 user thanked Alex M. for this useful post.
|
|
|
|
Rank: Advanced Member Groups: Registered
Joined: 03/03/2014(UTC) Posts: 418 Was thanked: 125 time(s) in 96 post(s)
|
Plugin is back to BETA  status; not because it is incomplete (it is), but because it may contain undiscovered bugs. In a way it is a reminder to ALWAYS back check the plugins functionality before bulk data processing. USE THIS PLUGIN AT YOUR OWN RISK. My programming experience is minimal (this is my first and potentially last coding project), and I cannot state with any degree of certainty that you will not encounter a new bug in the plugin. So please always back-check plugins functionality to confirm that it does what you would like it to do.
|
|
|
|
|
|
Rank: Advanced Member Groups: Registered
Joined: 03/03/2014(UTC) Posts: 418 Was thanked: 125 time(s) in 96 post(s)
|
A small pat on the back - this excel_IO plugin is MORE usable and functional than similar implementation in MathCAD Prime 3 (which does not seem to recalculate excel values in real-time). On the other hand MathCAD implementation is capable of outputting large data tables from excel almost instantaneously. So if someone helps me out with my question, I can improve on that as well: http://en.smath.info/for...-plugin-development.aspxEdited by user 08 December 2015 01:56:46(UTC)
| Reason: Not specified
|
|
|
|
|
|
Rank: Advanced Member Groups: Registered
Joined: 12/03/2011(UTC)
Posts: 101
Location: Chicago
Was thanked: 16 time(s) in 7 post(s)
|
THIS IS GENIUS! I want to check the assignments for the other shapes and properties. Originally Posted by: Alex.M November 19th, 2015 - Plugin update: - Plugin works with units - PNG export (excel_PNGv2) checks whether output cells changed automatically - excel_PNGv0 addded, exported file does not contain row/column reference - excel_QUIT now can selectively close EXCEL program or just workbook - SampleHowTo.zip is added - it is a good guide to possible uses of the plugin - Plugin is out of beta! Use at your own risk and please report bugs.   SampleHowTo.zip (1,331kb) downloaded 416 time(s). |
|
|
|
|
|
|
Rank: Advanced Member Groups: Registered
Joined: 03/03/2014(UTC) Posts: 418 Was thanked: 125 time(s) in 96 post(s)
|
Thank you for the kind words. I do believe that this and similar approach opens up a door to some interesting SMath <-> EXCEL interaction.
On a plugin side:
- Some serious performance tweaks - up to 50% increase in evaluation speed
- Bug noted - excel prompts (are you sure you want to.. blah blah) stay disabled after plugin finished executing - will look into it
|
|
|
|
|
|
Rank: Advanced Member Groups: Registered
Joined: 03/03/2014(UTC) Posts: 418 Was thanked: 125 time(s) in 96 post(s)
|
An example of the plugin use - calculating the force and centroid of non-linear earth pressure distribution acting on non-rectangular wall. Example demonstrates utilization of previously created spreadsheet. For a "live" demonstration (excel plot animation) position SMath and EXCEL windows side by side. Keep in mind that this example is for demonstration only and was not confirmed to be bug or error free. wall.xlsx (45kb) downloaded 59 time(s). EarthPressureOnPolygon.sm (135kb) downloaded 61 time(s).
|
|
|
|
|
|
 Rank: Advanced Member Groups: Registered, Advanced Member Joined: 13/01/2012(UTC) Posts: 2,647   Location: Italy Was thanked: 1329 time(s) in 875 post(s)
|
Originally Posted by: Alex.M It might be me and my lack of programming skills, but unless I use an eval() on the expression passed to my excel_IN function, it will not cancel out all the units before pasting the value into the EXCEL cell. End result - MESS. Instead of cell value 3138.0096 i get 2.82646777304901*10^19/9007199254741*'kg*'m^2/'s^2*1/10^3*'kg*'m/'s^2*'m (which is actually 3138.0096).
I figured our proposed symb() function can be used in similar way to cancel out units, but preserve symbolic notation.
P.S.: I'm sure I could do it somehow inside the plugin code, I just do not know how... And would not want to get off topic here. You're right, I'm moving here  The best way is to use the numerical engine (you can find example in FFTPACK [pure numerical], PieChartRegion [mixed Terms/numerical], ...). This handles numbers, matrices, systems and math strings (all except unknowns). This is from Floor() in CustomRegion, as you can see you can get the numbers and the units separately in an easy way Code:
public static bool ExpressionEvaluation(Term root, Term[][] args, ref Store store, ref Term[] result)
{
TNumber tNum = Decision.NumericCalculation(args[0], ref store);
result = GetFloor(tNum).obj.ToTerms();
return true;
}
public static TNumber GetFloor(TNumber tNumber)
{
TDouble tDouble = tNumber.obj as TDouble;
if (tDouble != null && !tDouble.isText)
{
TDouble tFloor = new TDouble(Math.Floor(tDouble.D));
tFloor.Units = tNumber.obj.Units;
return tFloor;
}
TFraction tFraction = tNumber.obj as TFraction;
if (tFraction != null)
{
TFraction tFloor = new TFraction(Math.Floor(tFraction.ToDouble()));
tFloor.Units = tNumber.obj.Units;
return tFloor;
}
TComplex tComplex = tNumber.obj as TComplex;
if (tComplex != null)
{
TNumber tFloor = new TComplex(Math.Floor(tComplex.Re.ToDouble()), Math.Floor(tComplex.Im.ToDouble()));
tFloor.obj.Units = tNumber.obj.Units;
return tFloor;
}
TMatrix tMatrix = tNumber.obj as TMatrix;
if (tMatrix != null)
{
int rows = tMatrix.unit.GetLength(0);
int cols = tMatrix.unit.GetLength(1);
for (int r = 0; r < rows; r++)
for (int c = 0; c < cols; c++)
tMatrix[r, c] = GetFloor(tMatrix[r, c]);
return tMatrix;
}
TSystem tSystem = tNumber.obj as TSystem;
if (tNumber.obj is TSystem)
{
for (int s = 0; s < tSystem.matrix.GetLength(0); s++)
tSystem.matrix[s] = GetFloor(tSystem.matrix[s]);
return tSystem;
}
// TInfinity or math strings (skipped in TDouble)
return tNumber;
}
Edited by user 10 March 2016 11:06:25(UTC)
| Reason: Not specified |
If you like my plugins consider to support SMath Studio buying a plan; to offer me a coffee: paypal.me/dcprojects |
|
|
|
|
|
Rank: Newbie
Groups: Registered
Joined: 01/04/2016(UTC) Posts: 6  Location: Maribor
|
I tried the plugin function excel_IN. I noticed that there is a problem if in regional settings of Windows comma (","  is defined as a decimal separator and number I want to transfer to excel has decimal part. If I switch (only for test purposes -- for my work I must use decimal comma) this setting to decimal dot, everything works correctly. Similar setting in Smath does not matter. I think that this is a bug in plugin whic causes that internally values are transferred according to Windows setting and not always with decimal dot. Until it is corrected, the only workaround I found is to use function round in Smath to strip any decimal part of values before sending to Excel.
|
|
|
|
|
|
 Rank: Guest
Groups: Registered
Joined: 04/07/2015(UTC) Posts: 6,866 Was thanked: 981 time(s) in 809 post(s)
|
Originally Posted by: ZarkoM
Until it is corrected, the only workaround I found is to use
function round in Smath to strip any decimal part of values
before sending to Excel.
Do it wiser: from the Smath function menu f(x), look for mwfix(,) plug your matrix name M:=mwfix(myMatrix). I use it a lot for making image from integer matrix [0..255] Can't you force your Windows settings to decimal dot ? Snippets from foreign countries show 123,456789 open their work sheet => 123.456789 [local US Canada]. In the attached WS, copy/paste Image from Image:=mwfix(Matrix) paste on right margin to see the matrix in integer values. Jean Forum mwfix [Matrix].sm (39kb) downloaded 43 time(s).
|
|
|
|
|
|
Rank: Advanced Member Groups: Registered
Joined: 03/03/2014(UTC) Posts: 418 Was thanked: 125 time(s) in 96 post(s)
|
Originally Posted by: ZarkoM I tried the plugin function excel_IN. I noticed that there is a problem if in regional settings of Windows comma ("," is defined as a decimal separator and number I want to transfer to excel has decimal part. If I switch (only for test purposes -- for my work I must use decimal comma) this setting to decimal dot, everything works correctly. Similar setting in Smath does not matter. I think that this is a bug in plugin whic causes that internally values are transferred according to Windows setting and not always with decimal dot. Until it is corrected, the only workaround I found is to use function round in Smath to strip any decimal part of values before sending to Excel. Please post offending Smath file, xls file, Decimal and argument separator settings from Smath, decimal and argument separator settings from Windows (with directions where to find them - never used comma as my system decimal separator before)
|
|
|
|
|
|
SMath Studio Forum
»
SMath Studio
»
Extensions
»
EXCEL (2003+) I/O with recalculation and export to PNG
Forum Jump
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.
