Welcome Guest! To enable all features please Login or Register.

Notification

Icon
Error

4 Pages123>»
Options
Go to last post Go to first unread
Offline Alex M.  
#1 Posted : 04 April 2015 22:01:54(UTC)
Alex M.


Rank: Advanced Member

Groups: Registered
Joined: 03/03/2014(UTC)
Posts: 402
Canada

Was thanked: 119 time(s) in 92 post(s)
XLSXupdate plugin

SMath Studio compatibility SMath Viewer compatibility mono compatibility Extension page Sources


I have completed my EXCEL I/O plugin with recalculation. Plugin was added to extension manager.

Plugin is back to BETA 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.

A simple "how-to" on using the plugin is attached to this post.

Davide, thank you for your help and the code to output excel range back to SMath! Also many thanks to Andrey for the SMath software, plugin tutorial, and ExcelIOCom plugin, which was a great learning source.

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 (and for many other purposes that I am still trying to wrap my head around).

Hand calculations and paper spreadsheets coexisted long before arrival of computer, so why shouldn't they now?

Functions added:

excel_IN - export to excel (xlsx or xls) (with or without saving the file)
excel_OUT - import from excel (xlsx or xls)
excel_IO - in memory real-time data exchange with EXCEL - can be replaced by separate excel_IN and excel_OUT functions
excel_PNG - exports excel range to PNG file (together with row/column headers, formatting, charts, etc.) - can be used together with Image region.
Function excel_PNG() was modified to take 5th argument – the font size of excel range table headers (set to 0 if none). The PNG export speed was increased to about 0.08sec per screenshot. Functions excel_PNGv0 and excel_PNGv2 are obsolete, however kept for the sake of backward compatibility

excel_PNGv0 - exports excel range to PNG file (together with formatting, charts, etc.) - can be used together with Image region. Function is executed on each SMath recalculation (slow, but twice as fast as excel_PNG).
excel_PNGv2 - exports excel range to PNG file (together with row/column headers, formatting, charts, etc.) - can be used together with Image region. Function is executed only if content of excel range have changed (fast).
excel_VISIBLE - makes background excel process visible in case you would like to take a look at / modify the file
excel_QUIT - will shut down currently running excel process (use with care - does not save data, will close ALL open excel files)

Plugin does not continuously open/close workbooks (unless excel_QUIT is used), once workbook is open it remains in memory and plugin "connects" to it for data exchange or to save it if desired.

Presently plugin works with EXCEL 2003+. If someone is willing to test plugin for earlier EXCEL versions I can provide a different build.

One of the ways to use plugin:

1. Insert image region into SMath workbook for visual confirmation of SMath <-> EXCEL interaction. Use excel_PNGv2 as an argument of the image region, point it to the range you would like to modify in your xls file. NOW YOUR EXCEL WORKBOOK IS OPEN IN BACKGROUND (this step is optional, however handy to visualize your input)
2. Once you visually see the screenshot of your excel file (together with row/column headers) you can use excel_IN to modify particular cells of the file.
3. Insert image region into SMath workbook that refers to your desired output range in xls file (similar to 1, this step is optional, however handy to visualize your output)
4. Use excel_OUT to point to particular cells/ranges in xls file (once you visually identify them from image region).

If you come up with an idea of what my plugin can potentially do (and it hypothetically should be able to do anything EXCEL does), post it below!

Changelog:

April 25th, 2015 - 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)

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 workbook
- SampleHowTo.zip is added - it is a good guide to possible uses of the plugin

November 27th, 2015 - Plugin update:
- 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

Use at your own risk and please report bugs.

AISCsample.png


AISC Shape Lookup.xlsx (3,349kb) downloaded 231 time(s).
SampleHowTo.zip (1,331kb) downloaded 209 time(s).
Steel Properties Look-up.sm (26kb) downloaded 116 time(s).

Example that writes rows of data (in my case just one cell) recursively. The concept is that you have an elaborate formula that takes input and provides output. Each line of output is written to a new row in Excel worksheet.
test.xlsx (8kb) downloaded 73 time(s). exportXLSX_1.sm (21kb) downloaded 80 time(s).

user defined function to perform cell Goal Seek

GSTEST.xlsx (22kb) downloaded 87 time(s). excelGoalSeek.sm (11kb) downloaded 93 time(s).

A sample of a Plot produced by combination of excel_EMF function and Image Region inside the SMath workbook.
The example was borrowed from one of the calculations by Jean Giraud.

Note the difference of as-printed quality between X-Y Plot output and excel_EMF output.

moody.pdf (93kb) downloaded 58 time(s). Inst_Colebrook MOODY_excelPlot.sm (21kb) downloaded 64 time(s). moody_plot.xlsx (32kb) downloaded 54 time(s).
moody_comp.png

Edited by moderator 09 May 2020 23:29:33(UTC)  | Reason: Not specified

thanks 2 users thanked Alex M. for this useful post.
on 07/04/2015(UTC),  on 03/05/2015(UTC)
Offline Davide Carpi  
#2 Posted : 07 April 2015 10:55:36(UTC)
Davide Carpi


Rank: Advanced Member

Groups: Registered, Advanced Member
Joined: 13/01/2012(UTC)
Posts: 2,254
Man
Italy
Location: Italy

Was thanked: 1142 time(s) in 747 post(s)
To upload sources and plugins look at this thread (the attachment in the first post should contains all that is needed) Good

Edited by user 07 April 2015 10:57:23(UTC)  | Reason: Not specified

If you like my plugins please consider a donation to SMath Studio; for personal contributions to me: paypal.me/dcprojects
Offline Alex M.  
#3 Posted : 16 April 2015 00:29:30(UTC)
Alex M.


Rank: Advanced Member

Groups: Registered
Joined: 03/03/2014(UTC)
Posts: 402
Canada

Was thanked: 119 time(s) in 92 post(s)
Plugin is updated. Import/export speed increased anywhere from x3 to x6 (checked by running attached .sm workbook). Plugin starts an excel process that needs to be killed manually after the workbook is closed. Confirmed to work on two different machines (one with Excel 2010, another with Excel 2013).

Pointers:

- when specifying relative paths "../" is equivalent to one folder up
- Do not attempt to open workbooks with same name but different location - causes issues
- if you would like to use Export to PNG function use XLS files only

As far as i am concerned plugin is complete and I will (attempt) to submit it to the extension manager.

And... There is an error. Screenshot is attached.

Edited by user 16 April 2015 03:13:43(UTC)  | Reason: Not specified

File Attachment(s):
SmathExcelPluginHowTo.zip (3,335kb) downloaded 84 time(s).
Alex M. attached the following image(s):
New Bitmap Image (1).png
Offline Davide Carpi  
#4 Posted : 16 April 2015 12:14:15(UTC)
Davide Carpi


Rank: Advanced Member

Groups: Registered, Advanced Member
Joined: 13/01/2012(UTC)
Posts: 2,254
Man
Italy
Location: Italy

Was thanked: 1142 time(s) in 747 post(s)
Tried today with Excel 2010, i get an "index out of range" exception with excel_OUT and excel_IO (no issues with excel_PNG and excel_IN)

About the registration of the plugin, I've tried and I haven't noticed issues (used just the dll in an uncompresed archive)
Davide Carpi attached the following image(s):
2015-04-16 11_00_57-SMath Studio extensions.png
If you like my plugins please consider a donation to SMath Studio; for personal contributions to me: paypal.me/dcprojects
Offline Alex M.  
#5 Posted : 16 April 2015 16:19:38(UTC)
Alex M.


Rank: Advanced Member

Groups: Registered
Joined: 03/03/2014(UTC)
Posts: 402
Canada

Was thanked: 119 time(s) in 92 post(s)
Could you please confirm that "index out of range" error is due to file not being specified correctly? Circled is generic path that needs to be changed. I will update the plugin so correct exception is shown.

Will try uploading myself again. Did you include in archive only two plugin files .dll and .pdb?

Thanks!

Edited by user 16 April 2015 16:21:38(UTC)  | Reason: Not specified

Alex M. attached the following image(s):
Untitled.png
Offline Davide Carpi  
#6 Posted : 16 April 2015 16:56:17(UTC)
Davide Carpi


Rank: Advanced Member

Groups: Registered, Advanced Member
Joined: 13/01/2012(UTC)
Posts: 2,254
Man
Italy
Location: Italy

Was thanked: 1142 time(s) in 747 post(s)
Originally Posted by: oxel007 Go to Quoted Post
Could you please confirm that "index out of range" error is due to file not being specified correctly? Circled is generic path that needs to be changed. I will update the plugin so correct exception is shown.

Error is shown with the correct path and file name "C:\Users\<username>\Desktop\HowTo\HowTo\Book3.xls" and also with relative path "Book3.xls" or "folder\Book2.xls"

Originally Posted by: oxel007 Go to Quoted Post
Will try uploading myself again. Did you include in archive only two plugin files .dll and .pdb?

Thanks!

Just the dll. Including the PDB however should not change anything.
If you like my plugins please consider a donation to SMath Studio; for personal contributions to me: paypal.me/dcprojects
Offline Alex M.  
#7 Posted : 17 April 2015 07:42:45(UTC)
Alex M.


Rank: Advanced Member

Groups: Registered
Joined: 03/03/2014(UTC)
Posts: 402
Canada

Was thanked: 119 time(s) in 92 post(s)
Could you help me to debug the error (given it is non reproducible on my end).

I have compiled a plugin .dll that will help to pin point what section of code throws the exception - could you please run it using Smath workbook, xls file and plugin attached and let me know the results?

Thanks!
File Attachment(s):
Test ExcelOUT.zip (913kb) downloaded 52 time(s).
Offline Davide Carpi  
#8 Posted : 17 April 2015 11:01:04(UTC)
Davide Carpi


Rank: Advanced Member

Groups: Registered, Advanced Member
Joined: 13/01/2012(UTC)
Posts: 2,254
Man
Italy
Location: Italy

Was thanked: 1142 time(s) in 747 post(s)
Originally Posted by: oxel007 Go to Quoted Post
Could you help me to debug the error (given it is non reproducible on my end).

I have compiled a plugin .dll that will help to pin point what section of code throws the exception - could you please run it using Smath workbook, xls file and plugin attached and let me know the results?

Thanks!

Done! the error message now is "check input 3 <directory>"

Originally Posted by: ioan92 Go to Quoted Post
If you put the plug-in files in the SMatht plugins directory, you receive this message:

You have to use the plugin in the latest stable version (I use a portable version to test it)
If you like my plugins please consider a donation to SMath Studio; for personal contributions to me: paypal.me/dcprojects
Offline Alex M.  
#9 Posted : 17 April 2015 19:01:50(UTC)
Alex M.


Rank: Advanced Member

Groups: Registered
Joined: 03/03/2014(UTC)
Posts: 402
Canada

Was thanked: 119 time(s) in 92 post(s)
Originally Posted by: ioan92 Go to Quoted Post
If you put the plug-in files in the SMatht plugins directory, you receive this message:


ioan92, I have built the plugin with SMath dlls v.0.97.5346.24640. If you see benefit in building for earlier SMath I can do that as well.

Originally Posted by: w3b5urf3r_reloaded Go to Quoted Post

Done! the error message now is "check input 3 <directory>"


The offending piece of code is

Quote:

Try
Dim myResult As List(Of Term) = New List(Of Term)
For Each matValue As String In result_temp3
myResult.AddRange(SMath.Manager.Converter.ToTerms(matValue))
Next
myResult.AddRange(SMath.Manager.Converter.ToTerms(range_rows.ToString))
myResult.AddRange(SMath.Manager.Converter.ToTerms(range_cols.ToString))
myResult.Add(New Term(Functions.Mat, TermType.Function, (2 + (range_cols * range_rows))))
If save = "yes" Then
book.Save()
End If
excelApp.DisplayAlerts = True
Return myResult
Catch ex As Exception
Throw New Exception("check input 3 " & fileName)
'Dim noResult As List(Of Term) = New List(Of Term)
'noResult(0) = SMath.Manager.Converter.ToTerms("check input"Wink(0)
'Return noResult
End Try


Unfortunately this is the part of code that I do not fully grasp (I had troubles outputting Object Array back to SMath myself and I have adopted the code from your plugin)

I have further broken it down line-by-line with "Try". If you run the workbook again with attached plugin we should be able to pinpoint it to particular line of code.

Thank you for helping!!


P.S.: Davide and ioan92, would you happen to know why I cannot "thank" you guys on this forum? When I click the button it gives an error..

Edited by user 17 April 2015 19:27:05(UTC)  | Reason: Not specified

File Attachment(s):
XLSXupdate.zip (18kb) downloaded 50 time(s).
thanks 2 users thanked Alex M. for this useful post.
on 17/04/2015(UTC),  on 17/04/2015(UTC)
Offline Davide Carpi  
#10 Posted : 17 April 2015 19:42:12(UTC)
Davide Carpi


Rank: Advanced Member

Groups: Registered, Advanced Member
Joined: 13/01/2012(UTC)
Posts: 2,254
Man
Italy
Location: Italy

Was thanked: 1142 time(s) in 747 post(s)
Now the error is "check input 8 ..."

More: I've tried changing system settings, the issue seems in localization stuff because if I use '.' as decimal separator all it works fine.

For the thank button you have to ask Andrey, probably there's something to prevent abuses.
If you like my plugins please consider a donation to SMath Studio; for personal contributions to me: paypal.me/dcprojects
Offline Alex M.  
#11 Posted : 17 April 2015 21:46:15(UTC)
Alex M.


Rank: Advanced Member

Groups: Registered
Joined: 03/03/2014(UTC)
Posts: 402
Canada

Was thanked: 119 time(s) in 92 post(s)
Originally Posted by: w3b5urf3r_reloaded Go to Quoted Post
Now the error is "check input 8 ..."

More: I've tried changing system settings, the issue seems in localization stuff because if I use '.' as decimal separator all it works fine.


This is offending code:

Quote:
Try
For Each matValue As String In result_temp3
myResult.AddRange(SMath.Manager.Converter.ToTerms(matValue))
Next
Catch ex As Exception
Throw New Exception("check input 8 " & fileName)
End Try


I have attempted properly substituting decimal separator using
Quote:
result_temp1 = SMath.Manager.Converter.CorrectExpressionString(result_temp1, GlobalParams.DecimalSymbolStandard, GlobalParams.DecimalSymbol, GlobalParams.ArgumentsSeparatorStandard, GlobalParams.ArgumentsSeparator)

hopefully it works (worked for me with comma as a decimal separator, check screenshot).

Attached is recompiled plugin plus "short" version of smath workbook to test all 4 functions.

Edited by user 17 April 2015 21:56:58(UTC)  | Reason: Not specified

File Attachment(s):
XLSXupdate (3).zip (1,006kb) downloaded 57 time(s).
Alex M. attached the following image(s):
Untitled.png
Offline Alex M.  
#12 Posted : 17 April 2015 23:51:18(UTC)
Alex M.


Rank: Advanced Member

Groups: Registered
Joined: 03/03/2014(UTC)
Posts: 402
Canada

Was thanked: 119 time(s) in 92 post(s)
Originally Posted by: w3b5urf3r_reloaded Go to Quoted Post
Tried today with Excel 2010, i get an "index out of range" exception with excel_OUT and excel_IO (no issues with excel_PNG and excel_IN)

About the registration of the plugin, I've tried and I haven't noticed issues (used just the dll in an uncompresed archive)


Davide, could you please remove your version of my plugin, i get following error:



Alex M. attached the following image(s):
Untitled.png
Offline Davide Carpi  
#13 Posted : 18 April 2015 00:08:07(UTC)
Davide Carpi


Rank: Advanced Member

Groups: Registered, Advanced Member
Joined: 13/01/2012(UTC)
Posts: 2,254
Man
Italy
Location: Italy

Was thanked: 1142 time(s) in 747 post(s)
Doesn't work, the issue is related to the system decimal separator (I suppose the number returned by excel uses the system settings)

Please try again using

Code:
result_temp1 = SMath.Manager.Converter.CorrectExpressionString(result_temp1, GlobalParams.DecimalSymbolSystem, GlobalParams.DecimalSymbol, GlobalParams.ArgumentsSeparatorSystem, GlobalParams.ArgumentsSeparator)


Plugin and folder was deleted immediatly after the upload, you have to ask Andrey to access manually the database Blush

Edited by user 18 April 2015 00:21:05(UTC)  | Reason: Not specified

If you like my plugins please consider a donation to SMath Studio; for personal contributions to me: paypal.me/dcprojects
thanks 1 user thanked Davide Carpi for this useful post.
on 18/04/2015(UTC)
Offline Andrey Ivashov  
#14 Posted : 18 April 2015 02:51:55(UTC)
Andrey Ivashov


Rank: Administration

Groups: Developers, Registered, Knovel Developers, Administrators, Advanced Member
Joined: 11/07/2008(UTC)
Posts: 1,388
Man
Russian Federation

Was thanked: 1624 time(s) in 545 post(s)
Originally Posted by: oxel007 Go to Quoted Post
Davide, could you please remove your version of my plugin, i get following error

All references to uploaded by Davide extension removed. Please try to register extension again by yourself.
thanks 1 user thanked Andrey Ivashov for this useful post.
on 18/04/2015(UTC)
Offline Alex M.  
#15 Posted : 18 April 2015 23:04:03(UTC)
Alex M.


Rank: Advanced Member

Groups: Registered
Joined: 03/03/2014(UTC)
Posts: 402
Canada

Was thanked: 119 time(s) in 92 post(s)
Originally Posted by: w3b5urf3r_reloaded Go to Quoted Post
Doesn't work, the issue is related to the system decimal separator (I suppose the number returned by excel uses the system settings)

Please try again using

Code:
result_temp1 = SMath.Manager.Converter.CorrectExpressionString(result_temp1, GlobalParams.DecimalSymbolSystem, GlobalParams.DecimalSymbol, GlobalParams.ArgumentsSeparatorSystem, GlobalParams.ArgumentsSeparator)


Plugin and folder was deleted immediatly after the upload, you have to ask Andrey to access manually the database Blush


Plugin is updated as you suggested. Do you have ideas about a way to replicate the error on my side? I would really like this resolved.

Thank you for testing!

File Attachment(s):
XLSXupdate.zip (19kb) downloaded 40 time(s).
thanks 1 user thanked Alex M. for this useful post.
on 19/04/2015(UTC)
Offline Alex M.  
#16 Posted : 19 April 2015 00:12:11(UTC)
Alex M.


Rank: Advanced Member

Groups: Registered
Joined: 03/03/2014(UTC)
Posts: 402
Canada

Was thanked: 119 time(s) in 92 post(s)
I have successfully uploaded my plugin to extension manager.

Davide, Andrey - thank you for your help!

P.S.: Andrey, I believe you will need to review / approve it.
Alex M. attached the following image(s):
Untitled.png
Offline Davide Carpi  
#17 Posted : 19 April 2015 01:04:36(UTC)
Davide Carpi


Rank: Advanced Member

Groups: Registered, Advanced Member
Joined: 13/01/2012(UTC)
Posts: 2,254
Man
Italy
Location: Italy

Was thanked: 1142 time(s) in 747 post(s)
Originally Posted by: oxel007 Go to Quoted Post
Plugin is updated as you suggested. Do you have ideas about a way to replicate the error on my side? I would really like this resolved.

Thank you for testing!



It works! Clap

Tested mixing various SMath and system settings

To test it yourself go to the "control panel" > "region and language" and then change the system decimal separator (no needs to restart the system)

You can use also this shortcut from the search box of the start button
Code:
C:\Windows\System32\rundll32.exe shell32.dll,Control_RunDLL intl.cpl,,0
If you like my plugins please consider a donation to SMath Studio; for personal contributions to me: paypal.me/dcprojects
Offline Andrey Ivashov  
#18 Posted : 19 April 2015 08:10:37(UTC)
Andrey Ivashov


Rank: Administration

Groups: Developers, Registered, Knovel Developers, Administrators, Advanced Member
Joined: 11/07/2008(UTC)
Posts: 1,388
Man
Russian Federation

Was thanked: 1624 time(s) in 545 post(s)
Originally Posted by: oxel007 Go to Quoted Post
P.S.: Andrey, I believe you will need to review / approve it.

Approved OK with one comment: please don't include auto generated *.pdb and *.xml files into plug-in package next time.
Thank you!
Offline Alex M.  
#19 Posted : 20 April 2015 00:46:08(UTC)
Alex M.


Rank: Advanced Member

Groups: Registered
Joined: 03/03/2014(UTC)
Posts: 402
Canada

Was thanked: 119 time(s) in 92 post(s)
Originally Posted by: w3b5urf3r_reloaded Go to Quoted Post

It works! Clap


Great to hear!!! Looks like we are done here Biggrin

Update to a plugin - exported PNG includes row and column headings. I find it particularly convenient because one can visually refer to a desired excel cell/range after taking a glance at the spreadsheet screenshot.

Davide, is there a way to use Table Region to edit/create matrices? It would visualize input into a spreadsheet nicely (i.e you input the value into a table cell with row name "1" and column name "A", this value is transferred to EXCEL, and you can output desired cell from EXCEL after looking at the PNG screenshot.

Originally Posted by: smath Go to Quoted Post
Originally Posted by: oxel007 Go to Quoted Post
P.S.: Andrey, I believe you will need to review / approve it.

Approved OK with one comment: please don't include auto generated *.pdb and *.xml files into plug-in package next time.
Thank you!


Andrey, will do next time! Also at what point do you think it should come out of BETA? It seems to work but who knows..

Thank you guys for helping to test it :-)

Edited by user 20 April 2015 03:25:35(UTC)  | Reason: Not specified

Alex M. attached the following image(s):
Untitled.png
thanks 1 user thanked Alex M. for this useful post.
on 22/04/2015(UTC)
Offline Davide Carpi  
#20 Posted : 22 April 2015 12:55:30(UTC)
Davide Carpi


Rank: Advanced Member

Groups: Registered, Advanced Member
Joined: 13/01/2012(UTC)
Posts: 2,254
Man
Italy
Location: Italy

Was thanked: 1142 time(s) in 747 post(s)
Originally Posted by: oxel007 Go to Quoted Post
Davide, is there a way to use Table Region to edit/create matrices?


As for now there's no way to use the TableRegion as matrix source. I plan to do this but not in the near future.


P.S. I suggest you to use the first post of this thread as reference for the most updated informations of this plugin Good

Edited by user 22 April 2015 17:31:14(UTC)  | Reason: Not specified

If you like my plugins please consider a donation to SMath Studio; for personal contributions to me: paypal.me/dcprojects
Users browsing this topic
4 Pages123>»
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.