EXCEL (2003+) I/O with recalculation and export to PNG - Uses EXCEL PIA (2003+) to import/export data to and from XLS/XLSX files - Messages
#1 Posted: 4/4/2015 4:01:54 PM
XLSXupdate plugin
I have completed my EXCEL I/O plugin with recalculation. Plugin was added to extension manager.
Functions:
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.
AISC Shape Lookup.xlsx
SampleHowTo.zip
Steel Properties Look-up.sm
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
exportXLSX_1.sm
user defined function to perform cell Goal Seek
GSTEST.xlsx
excelGoalSeek.sm
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
Inst_Colebrook MOODY_excelPlot.sm
moody_plot.xlsx
I have completed my EXCEL I/O plugin with recalculation. Plugin was added to extension manager.
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.Functions:
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.
AISC Shape Lookup.xlsx
SampleHowTo.zip
Steel Properties Look-up.sm
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
exportXLSX_1.sm
user defined function to perform cell Goal Seek
GSTEST.xlsx
excelGoalSeek.sm
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
Inst_Colebrook MOODY_excelPlot.sm
moody_plot.xlsx
2 users liked this post
#2 Posted: 4/7/2015 4:55:36 AM
To upload sources and plugins look at this thread (the attachment in the first post should contains all that is needed) 
If you like my plugins please consider to support the program buying a license; for personal contributions to me: paypal.me/dcprojects
#3 Posted: 4/15/2015 6:29:30 PM
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.
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.
#4 Posted: 4/16/2015 6:14:15 AM
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)
About the registration of the plugin, I've tried and I haven't noticed issues (used just the dll in an uncompresed archive)
If you like my plugins please consider to support the program buying a license; for personal contributions to me: paypal.me/dcprojects
#5 Posted: 4/16/2015 10:19:38 AM
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!
Will try uploading myself again. Did you include in archive only two plugin files .dll and .pdb?
Thanks!
#6 Posted: 4/16/2015 10:56:17 AM
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\
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 to support the program buying a license; for personal contributions to me: paypal.me/dcprojects
#7 Posted: 4/17/2015 1:42:45 AM
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!
Test ExcelOUT.zip
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!
Test ExcelOUT.zip
#8 Posted: 4/17/2015 5:01:04 AM
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
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 to support the program buying a license; for personal contributions to me: paypal.me/dcprojects
#9 Posted: 4/17/2015 1:01:50 PM
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.
Done! the error message now is "check input 3"
The offending piece of code is
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")(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..
XLSXupdate.zip
2 users liked this post
#10 Posted: 4/17/2015 1:42:12 PM
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.
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 to support the program buying a license; for personal contributions to me: paypal.me/dcprojects
#11 Posted: 4/17/2015 3:46:15 PM
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:
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
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.
#12 Posted: 4/17/2015 5:51:18 PM
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:
#13 Posted: 4/17/2015 6:08:07 PM
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
Plugin and folder was deleted immediatly after the upload, you have to ask Andrey to access manually the database
Please try again using
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
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
Andrey Ivashov 4/17/2015 8:52:00 PM
#14 Posted: 4/17/2015 8:51:55 PM
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.
1 users liked this post
Davide Carpi 4/18/2015 7:34:00 AM
#15 Posted: 4/18/2015 5:04:03 PM
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 usingresult_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
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!
XLSXupdate.zip
1 users liked this post
Davide Carpi 4/18/2015 7:00:00 PM
#16 Posted: 4/18/2015 6:12:11 PM
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.
Davide, Andrey - thank you for your help!
P.S.: Andrey, I believe you will need to review / approve it.
#17 Posted: 4/18/2015 7:04:36 PM
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!
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
C:\Windows\System32\rundll32.exe shell32.dll,Control_RunDLL intl.cpl,,0If you like my plugins please consider to support the program buying a license; for personal contributions to me: paypal.me/dcprojects
#18 Posted: 4/19/2015 2:10:37 AM
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!
#19 Posted: 4/19/2015 6:46:08 PM
It works!
Great to hear!!! Looks like we are done here :d
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.
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 :-)
1 users liked this post
Davide Carpi 4/22/2015 6:57:00 AM
#20 Posted: 4/22/2015 6:55:30 AM
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
If you like my plugins please consider to support the program buying a license; for personal contributions to me: paypal.me/dcprojects
-
New Posts
-
No New Posts