XLSXupdate plugin
I have completed my EXCEL I/O plugin with recalculation. Plugin was added to extension manager.
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.
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?
:
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 compatibilityexcel_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 (3,349kb) downloaded 402 time(s). SampleHowTo.zip (1,331kb) downloaded 437 time(s). Steel Properties Look-up.sm (26kb) downloaded 256 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 175 time(s). exportXLSX_1.sm (21kb) downloaded 195 time(s).user defined function to perform cell Goal Seek
GSTEST.xlsx (22kb) downloaded 175 time(s). excelGoalSeek.sm (11kb) downloaded 201 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 151 time(s). Inst_Colebrook MOODY_excelPlot.sm (21kb) downloaded 141 time(s). moody_plot.xlsx (32kb) downloaded 112 time(s).
Edited by moderator 20 July 2022 14:03:09(UTC)
| Reason: Not specified
