SMath Studio Forum
»
SMath Studio
»
Extensions
»
EXCEL (2003+) I/O with recalculation and export to PNG
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. This should be fixed. Test it and report with information I asked for in the previous post if the issue persists.
|
|
|
|
Rank: Newbie
Groups: Registered
Joined: 01/04/2016(UTC) Posts: 6 Location: Maribor
|
Originally Posted by: Alex.M
This should be fixed. Test it and report with information I asked for in the previous post if the issue persists.
I just tried with Smath version 0.98 (build 5935) and plugin version 0.2.0.11351. Problem still exists. It is not related to decimal symbol/Arguments separator defined in Smath (I tried with both combinations ","/";" and "."/";" ) but on the other hand it is present onyl if Windows system setting for decimal symbol is set to "," and digit grouping symbol to "." (quite common in most European countries). Under vice versa setting (normal in most of other world) plugin is working correctly. This setting (decimal symbol "," and digit grouping symbol "." ) can be changed in control panel -- see this link (as I have Slovenian version of windows that link would be more helpful than screen shots from my computer). In attachment you will find offending Smath and Excel file. TestExcel.zip (8kb) downloaded 59 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. Bug confirmed and fixed. It was a peculiar one - apparently .NET framework and EXCEL have a different decimal point settings, in a way that .NET does not follow regional settings of windows when processing numbers. Makes sense? Maybe. Edited by user 09 April 2016 18:52:56(UTC)
| Reason: Not specified
|
1 user thanked Alex M. for this useful post.
|
|
|
Rank: Newbie
Groups: Registered
Joined: 01/04/2016(UTC) Posts: 6 Location: Maribor
|
Originally Posted by: Alex.M Bug confirmed and fixed. It was a peculiar one - apparently .NET framework and EXCEL have a different decimal point settings, in a way that .NET does not follow regional settings of windows when processing numbers. Makes sense? Maybe.
Thank you very much, but I am sorry to report that the test example I sent in previous message produces COM exception when testing with plugin version 0.2.0.14035. The only difference is plugin version. TestExcel.sm file with plugin version 0.2.0.11351 works correctly (but, as expected, with bug), when I change to plugin version 0.2.0.14035, I got a message as you can see below. If it helps: When trying with newer version -- Excel file is opened correctly, after that I got an error in Smath and excel file is not updated. At my computer is quite repeatable -- no other change than plugin version is neccessary to reproduce. Smath version: 0.98 build: 5935 Excel version: Microsoft Excel 2013 (15.0.4805.1001) 32 bit Edited by user 10 April 2016 18:14:57(UTC)
| Reason: Not specified
|
|
|
|
Rank: Advanced Member Groups: Registered
Joined: 03/03/2014(UTC) Posts: 418 Was thanked: 125 time(s) in 96 post(s)
|
As you can see I cannot reproduce your bug. Get back to me which of the inputs from the file attached causes the error. Also make sure you are using latest stable SMath Studio 0.98 SMath community, can anybody else reproduce the bug with stable 0.98 SMath Studio? Thanks TestExcel.sm (12kb) downloaded 46 time(s). TestExcel.xlsx (11kb) downloaded 44 time(s).
|
|
|
|
Rank: Newbie
Groups: Registered
Joined: 01/04/2016(UTC) Posts: 6 Location: Maribor
|
Originally Posted by: Alex.M As you can see I cannot reproduce your bug. Get back to me which of the inputs from the file attached causes the error. Also make sure you are using latest stable SMath Studio 0.98
SMath community, can anybody else reproduce the bug with stable 0.98 SMath Studio?
Thanks
Here are results from me. I tried to give you similar information as you. I don't see any pattern. :-(
|
|
|
|
Rank: Advanced Member Groups: Registered
Joined: 03/03/2014(UTC) Posts: 418 Was thanked: 125 time(s) in 96 post(s)
|
There is a pattern and solution to your issue.. As you can see 0.007 transferred into excel because it was represented as a "=7/1000".
This is not the approach I would like to go though, so I will try few things first.
I have already updated a plugin to v .14879, see if it provides a fix (and report)
If not I will get back to it sometime midweek
|
|
|
|
Rank: Newbie
Groups: Registered
Joined: 01/04/2016(UTC) Posts: 6 Location: Maribor
|
Originally Posted by: Alex.M There is a pattern and solution to your issue.. As you can see 0.007 transferred into excel because it was represented as a "=7/1000".
This is not the approach I would like to go though, so I will try few things first.
I have already updated a plugin to v .14879, see if it provides a fix (and report)
If not I will get back to it sometime midweek Yes. After I sent a message, I saw this detail. It seems that problem exists when it is neccessary to transfer "real floating" (not fraction) number. Unfortunately plugin version .14879 does not help:
|
|
|
|
Rank: Advanced Member Groups: Registered
Joined: 03/03/2014(UTC) Posts: 418 Was thanked: 125 time(s) in 96 post(s)
|
Try this and report with screenshots like previous post. To install plugin manually: - Delete from extension manager - Close smath - copy .dlls into plugin folder of smath in program files Release.zip (381kb) downloaded 141 time(s).
|
|
|
|
Rank: Newbie
Groups: Registered
Joined: 01/04/2016(UTC) Posts: 6 Location: Maribor
|
Originally Posted by: Alex.M Try this and report with screenshots like previous post.
To install plugin manually:
- Delete from extension manager - Close smath - copy .dlls into plugin folder of smath in program files
Here is the result. In order to be much clear what is transferred and what is not I deleted target cells in TestExcel.xslx before opening TestExcel.sm. As you can see, 2.45 is reported as transferred, but value in Excel is incorrect. Last value (eval(b/'m)*10 still didn't go through.
|
|
|
|
Rank: Advanced Member Groups: Registered, Advanced Member Joined: 13/01/2012(UTC) Posts: 2,690 Location: Italy Was thanked: 1368 time(s) in 895 post(s)
|
Originally Posted by: Alex.M I need to get clipboard content (image) in my EXCEL plugin (screenshot of range), and I am encountering "current thread must be set to sta before ole calls can be made". I understand it has something to do with <STAThread()> _ Could you suggest me a direction for the fix? Some direction is provided here, but it does not seem to quite work for me http://stackoverflow.com...-single-thread-apartmentThe code I am using is Public Shared Sub SaveClipboardImageToFile(filePath As String) Dim image As BitmapSource = Clipboard.GetImage() Using fileStream = New FileStream(filePath, FileMode.Create) Dim encoder As BitmapEncoder = New PngBitmapEncoder() encoder.Frames.Add(BitmapFrame.Create(image)) encoder.Save(fileStream) End Using End Sub I did some poking around and tried workaround like getting idataobject from clipboard (returns null), or clipboard.getimage (object reference not set to an instance... which means that data in clipboard is null). All those errors are related to current thread not being STA (realized it from browsing online). The solution is to apply an STA flag to the MAIN() function, which I have no clue what it is. How can I do it from my low-level evaluation plugin? http://computer-programm...arp/48d5e12bcaad228a.htm Main() function is simply... the main function Main() is the entry point of the executable; Since the executable is SMathstudio_desktop.exe, obviously you can't apply the STAthread attribute to that function. I guess you can use the following approach (is C#, from DataExchange save functions) Code:using System.Threading;
// ...
Thread staThread = new Thread( () => {
Thread thisThread = Thread.CurrentThread;
// place here the code you want to execute in a Single-Threaded Apartment
});
staThread.SetApartmentState(ApartmentState.STA);
staThread.Start();
staThread.Join();
// ...
To check if the clipboard contains an image there should be the method Code:bool Clipboard.ContainsImage()
Edited by user 12 May 2016 01:41:27(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 |
1 user thanked Davide Carpi 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)
|
This worked great!
Speeding up my excel_PNG (excel range screenshot) function by a factor of 4!!! Thank you very much...
|
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.
With the help of Davide I have managed to increase the speed of EXCEL range screenshot capture by a factor of X4 to X8. Captured screenshots can be displayed using the Image Region.
Why is this important? User can display visual feedback of EXCEL I/O with virtually no loss in performance. This is useful for visual check of I/O values not only by user but also by anyone who can visually see the printout of SMath workbook.
Export of pixel perfect EXCEL charts is now an option as well.
Function excel_PNG() was modified to take 5th argument – the font size of excel range table headers (set to 0 if none). Functions excel_PNGv0 and excel_PNGv2 are obsolete, however kept for the sake of backward compatibility.
|
|
|
|
Rank: Advanced Member Groups: Registered
Joined: 12/03/2011(UTC) Posts: 101 Location: Chicago
Was thanked: 16 time(s) in 7 post(s)
|
I was getting the EXACT same errors after upgrading to 0.98 build 5981. I tried the steps outlined in Davide's reply. Still no go....
I ended up having to EXPLICITLY define the file and path for the location of the excel file to not get any error.
I tried defining EXCELpath: "C:\Users\djp\Google Drive\SMATH\"
but no luck.
The fix was doing it within the function like this-
excel_IN("no","C:\Users\djp\Google Drive\SMATH\test.xlsx","Sheet1",concat("B",num2str(row.out)),eval(data.out))
somehow filenames and paths were not critical before. i had them defined as ./filename before sinneD
|
|
|
|
|
Rank: Advanced Member Groups: Registered
Joined: 03/03/2014(UTC) Posts: 418 Was thanked: 125 time(s) in 96 post(s)
|
Originally Posted by: sinneD I was getting the EXACT same errors after upgrading to 0.98 build 5981. I tried the steps outlined in Davide's reply. Still no go....
I ended up having to EXPLICITLY define the file and path for the location of the excel file to not get any error.
I tried defining EXCELpath: "C:\Users\djp\Google Drive\SMATH\"
but no luck.
The fix was doing it within the function like this-
excel_IN("no","C:\Users\djp\Google Drive\SMATH\test.xlsx","Sheet1",concat("B",num2str(row.out)),eval(data.out))
somehow filenames and paths were not critical before. i had them defined as ./filename before sinneD
Please update to lates SMath http://smath.info/file/XbN7z , update the plugin (delete / install), make sure that ALL your plugins are up to date (sometimes this causes funny errors), try again and if issue persists report back with: - .sm and .xlsx file - What is the error (exactly same as davide does not tell me too much) - How to reproduce it I am sure I will fix it over a weekend or so. Edited by user 17 May 2016 23:10:19(UTC)
| Reason: Not specified
|
|
|
|
Rank: Newbie
Groups: Registered
Joined: 18/05/2016(UTC) Posts: 1 Location: Stockholm
|
Thank you Alex and Davide plus others contributing to this fantastic and very promising plugin!
I'm not an SMath expert but I was using MathCad about 15 years ago (before PTC ruined it) with the "Excel components". A very good combination for certain applications. And yesterday I had a look at SMath (again) and found this plugin.
Installed the standard (stable) SMath Studio and this plugin. Downloaded and tried the "cow" Excel in/out example found above in this thread and the other plugin needed for this example. I'm on Windows 10, MS Excel 2016 (version 16.0.6868.2062)/Office 365 latest "click to run".
I'm not sure the SMath worksheet processing run as expected BUT I noticed a strange thing with the SMath window in that it was resized to about half the previous window size and barely was visible and manageable after the operation stopped. The Excel window was unaffected.
|
|
|
|
Rank: Advanced Member Groups: Registered
Joined: 03/03/2014(UTC) Posts: 418 Was thanked: 125 time(s) in 96 post(s)
|
Originally Posted by: cml Thank you Alex and Davide plus others contributing to this fantastic and very promising plugin!
I'm not an SMath expert but I was using MathCad about 15 years ago (before PTC ruined it) with the "Excel components". A very good combination for certain applications. And yesterday I had a look at SMath (again) and found this plugin.
Installed the standard (stable) SMath Studio and this plugin. Downloaded and tried the "cow" Excel in/out example found above in this thread and the other plugin needed for this example. I'm on Windows 10, MS Excel 2016 (version 16.0.6868.2062)/Office 365 latest "click to run".
I'm not sure the SMath worksheet processing run as expected BUT I noticed a strange thing with the SMath window in that it was resized to about half the previous window size and barely was visible and manageable after the operation stopped. The Excel window was unaffected. Hello there. I did some digging around and there is a good chance that it is related to SMath not playing nicely with custom DPI settings. I am attaching an archive of my testing sreenshots. DPItest.zip (1,777kb) downloaded 51 time(s).Good news that on my PC (win 10 & office 2016), all I had to do was click on window maximize, than on minimize than on maximize again and after that it was working as expected. SMath is built on .NET 2.0... There might be a couple features that do not look right in latest Windows 10. I will debug the plugin on weekend and see what I can do (may be there are some DPI flags I can set from C#, or maybe it is related to a separate proces that captures screenshots...). Davide, do you have any opinion on this?
|
|
|
|
Rank: Advanced Member Groups: Registered
Joined: 03/03/2014(UTC) Posts: 418 Was thanked: 125 time(s) in 96 post(s)
|
Plugin update: excel_PNG(file path, sheet name, range, png path, rendering option) produces high quality screenshots of excel range, while excel_PNGv0 and excel_PNGv2 are left as is (read screenshots at regular resolution). rendering option can be set to: 0 - no row/column identifiers, 3xsysDPI 1 - no row/column identifiers, 8xsysDPI scaled back to 3xsysDPI, recaptures screenshot only if cell content changed 2 - row/column identifiers present, 3xsysDPI 3 - row/column identifiers present, 8xsysDPI scaled back to 3xsysDPI, recaptures screenshot only if cell content changed 4+ - option 3 + font size of row/column identifiers HD screenshots are slow (pure CPU overhead for clipboard -> EMF -> Bitmap -> File) I am using this to display EXCEL charts and other content in .sm workbooks. pareto-analysis-chart-excel-template.xlsx (18kb) downloaded 67 time(s). ExcelIO_HDPNG.sm (620kb) downloaded 73 time(s).typical not HD screenshot Edited by user 15 June 2016 21:14:40(UTC)
| Reason: Not specified
|
2 users thanked Alex M. for this useful post.
|
on 16/06/2016(UTC), on 16/06/2016(UTC)
|
|
Rank: Guest
Groups: Registered
Joined: 04/07/2015(UTC) Posts: 6,866 Was thanked: 983 time(s) in 811 post(s)
|
Thanks Alex: lovely Excel companion to Smath. I proceeded differently because I don't have Excel Plugin. All explained in the *.sm attached. The procedure has preserved the orange trace. I like your dedication to Smath hi-pro contribution. Jean Forum Alex ExcelIO_HDPNG.sm (680kb) downloaded 65 time(s).
|
|
|
|
Rank: Guest
Groups: Registered
Joined: 04/07/2015(UTC) Posts: 6,866 Was thanked: 983 time(s) in 811 post(s)
|
... this one has been sharpened twice.
|
|
|
|
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.