---

Here there is a plugin to share data between SMath and XLSX Spreadsheets (*.xlsx, *.xlsm).

Plugin UI languages:

PLEASE REPORT IN THIS TOPIC ANY ISSUE.


Best regards

• requirements: SMath Studio 0.96.4909 or later, .Net Framework 3.5 (Windows) / Latest Mono distributive (Linux);
  
• installation: download the plugin from the gallery;
  
• documentation: XlsxImportExport_docs.7z (38 KiB) downloaded 1399 time(s).;
  
• languages: if you want to have a new UI language look into the documentation's Language folder.

---

Attached below OLD documentation for SMath Studio prior to 0.97.5346 (0.96.4909 <= SS < 0.97.5346)

---

XlsxImportExport_docs.7z (37 KiB) downloaded 1118 time(s).

thank you for the feedback


best regards,

Davide

How to use this plugin - did not quite understand. Thank you.

Wrote

How to use this plugin - did not quite understand. Thank you.

Examples added to the first post (XlsxImportExport_docs.7z - nothing changes in the usage from those contained in the previous Data Exchange plugin)

Attached here a pdf to show the expected output.


regards,

w3b5urf3r

---

Xlsx Import_Export examples.pdf (62 KiB) downloaded 1812 time(s).

Wrote

How to use this plugin - did not quite understand. Thank you.

All plugin functions are explained in the function reference part of the handbook (see signature). It is in german, but the examples should be self documenting (most of them in fact taken from Davide's examples)

I tried this plugin out, since importing and exporting to excel would be extremely useful.

However when I try to import a cell with a function referencing another cell, the cell reference is imported (eg: A5*G4), instead of the value of the cell. Is there any way around this?

Also, is it possible to export to an existing excel file, without overwriting it? I tried exporting to an existing file, and everything in the file gets erased, except for the newly exported data.


Thanks,

Ernesto.

I need to see the ClosedXML API references...

About cell's references if I remember correctly there are some limitations (not all the functions are supported and not all the references, f.e. between different sheets) but I can check if something can be made...

For both the things, I'll see what I can do

In the example file it is stated

Quote

import a single cell
(file in the same folder of this sheet; use a relative/absolute path otherwise)

Does this mean if the file is in the same folder as SMath?

It appears that the program looks where the Smath.exe file is stored is that correct?
Thanks

EDIT:

I wanted to use the folder where the calulcation page and the excel sheet was stored. Therefore I should first set CurrentDirectory(DocumentDirectory(""))

Wrote

In the example file it is stated

Quote

import a single cell
(file in the same folder of this sheet; use a relative/absolute path otherwise)

Does this mean if the file is in the same folder as SMath?

It appears that the program looks where the Smath.exe file is stored is that correct?

The "base" path is the path of the worksheet, if available (existing worksheet) otherwise is the user "Documents" folder.
The archive in the first post contains some import/export examples.


BTW the path of the executable has no much interest (in most of the cases the user don't know where it is or needs administration privileges to write inside it)


Best regards,

Davide

Is the Excel import meant to be Viewer-safe? I get the attached error message if I try to run a viewer application with excel read. With ods-format the situation is similar.

---

Wrote

Is the Excel import meant to be Viewer-safe? I get the attached error message if I try to run a viewer application with excel read. With ods-format the situation is similar.

To be honest, I've never tried these plugins in SMath Viewer; seems that the external assemblies are not embedded in the Viewer.

As secondary thing reading the ClosedXML/Ionic.Zip licenses I suppose that any Viewer executable shall contains the relative copyright notice, and this actually is not possible (if this is not made by you manually)...

I try to setup a data browser for use by my colleagues, therefore any hints on how to do viewer-safe data import of spreadsheets are welcome. Perhaps this could be done within excel, but I am not comfortable with it and just use the opportunity to do some SMath testing.

Nice idea...

Added in the BTS as SV-146 and SV-147


Best regards,

Davide

Davide,

The issue with "DocumentFormat.OpenXml.dll" not found when you create and run an .exe can be resolved by removing the reference to the .dll from the "References" section in your extention solution. You do not call this .dll anywhere in the extension so it is not needed. When I do this, I no longer get the missing .dll error.

The .exe still does not pull in the Excel spreadsheets I want with the missing .dll error fixed. I did do further investigation and the .exe program can not handle even a simple mat() function by itself with no other references. This lead me to believe that the .exe program is at issue and not your extension.

Mike

PLUGIN UPDATED

- ImportData.XLSX now get results where cells references other cells;
- fixed export on SMath Viewer;

I figured this conversation is best to be continued here

Wrote

COM plugins are a unexplored field for me. My first idea is to have something like a Calculate.MSExcel("file") / Calculate.LOCalc("file") functions; I don't know if this is possible for the whole document, anyway should be possible for a range Calculate.XYZ("file","cells").

Base documentation:
- for excel: How to: Run Excel Calculations Programmatically
- for LO: SDK 4.3: Common Language Infrastructure (CLI) examples

Is it possible to run visual basic script from your plugin? I have come up with (pieced together from numerous online sources) a .vbs file that will update (in background) all the XLSX files in the directory it is run from (it will actually run a background EXCEL process that can be tasked to do anything excel can). If this or similar could be incorporated in your plugin I can see following happening:

1. Modify "input" xlsx workbook using your data exchange plugin
2. Run .vbs script that will recalculate/save all workbooks linked to input work book (I wonder if it is possible to somehow start the sript from within Smath)
3. Export cell range from desired workbook

I am sure with .vbs there is a way to perform all input - calculations - output strictly in memory, so no files need to be saved unless desired. I am not sure, however, if it is something that can be used by a Smath Studio plugin.

Best regards

---

SpreadSheet IOxlsx.7z (11 KiB) downloaded 132 time(s).

PLUGIN UPDATED SMath Studio 0.97.5581

- import data: fixed bug when quotation mark (") is used in a cell, f.e. for inches measurements. Now this character is converted in the "Double Prime" character (″) [many thanks to Don Bollard for pointing out this bug];
- import data: importData.XLSX(3) now accepts a range of cells as third argument (f.e. "A3:B12");

PLUGIN UPDATED SMath Studio 0.97.5737

• importData.XLSX() can access files opened by other processes (only saved data);

PLUGIN UPDATED SMath Studio 0.97.5346 and later

- added exportData.XLSX(4)* [extends exportData.XLSX(2)];
- added exportData.XLSX(5)* [extends exportData.XLSX(3)];
- importData.XLSX(...): fixes in path processing;


* How they works:
- If the file doesn't exists, a new file is created;
- If the sheet in the workbook doesn't exists, a new sheet is created;
- If the sheet exists in the workbook, data in the target cells will be overwritten; use these functions with caution if your output may have a different size at each run;

Does this or any other "excel" plugin work with "ods" extensions? I am on Libre office on both Windows and Linux at home. I could not get to work.