99
edits
IO package: Difference between revisions
Jump to navigation
Jump to search
→Spreadsheet formula support
AndrewJanke (talk | contribs) m (Fix typo) |
|||
| (3 intermediate revisions by the same user not shown) | |||
| Line 99: | Line 99: | ||
==== 32 vs. 64-bit issues ==== | ==== 32 vs. 64-bit issues ==== | ||
Generally, if you use a Java-based interface for spreadsheet I/O, it doesn't matter much whether you use Octave 32-bit or Octave 64-bit. | Generally, if you use a Java-based interface for spreadsheet I/O, it doesn't matter much whether you use Octave 32-bit or Octave 64-bit, as long as Octave's bit width matches that of the Java JRE. If you want to use the UNO interface (for LibreOffice & OpenOffice.org), also LibreOffice's bit width needs to match that of Octave and the Java JRE. | ||
So for spreadsheet I/O with Java-based add-on software like e.g., Apache POI, 64-bit Octave requires a 64-bit Java JRE and -if so desired- a 64-bit LibreOffice. The add-on SW itself (Java .jar files) is bit width agnostic. | |||
On Windows, Octave with a loaded Octave-Forge windows package can invoke MS-Excel for spreadsheet I/O but only 32-bit MS-Office; 64-bit MS-Office does not support ActiveX. In this case it doesn't matter much whether Octave is 32-bit or 64-bit. | |||
==== Java example ==== | ==== Java example ==== | ||
| Line 489: | Line 490: | ||
==== Spreadsheet formula support ==== | ==== Spreadsheet formula support ==== | ||
When using the COM, POI, JXL, and UNO interfaces you can: | When using the OCT, COM, POI, JXL, and UNO interfaces you can: | ||
* (When reading, xls2oct) either read spreadsheet formula results, or the literal formula text strings (also works with OCT interface); | * (When reading, xls2oct) either read spreadsheet formula results, or the literal formula text strings (also works with OCT interface); | ||
* (When writing, oct2xls) either enter formulas in the worksheet as formulas, or enter them as literal text strings. | * (When writing, oct2xls) either enter formulas in the worksheet as formulas, or enter them as literal text strings. | ||
| Line 501: | Line 502: | ||
Be aware that there's no formula evaluator in JExcelAPI (JXL) nor OpenXLS (OXS). So if you create formulas in your spreadsheet using oct2xls or xlswrite with 'JXL' or 'OXS', do not expect meaningful results when reading those files later on ,unless you open them in Excel and write them back to disk. | Be aware that there's no formula evaluator in JExcelAPI (JXL) nor OpenXLS (OXS). So if you create formulas in your spreadsheet using oct2xls or xlswrite with 'JXL' or 'OXS', do not expect meaningful results when reading those files later on ,unless you open them in Excel and write them back to disk. | ||
While both Apache POI and JExcelAPI feature a formula validator, not all spreadsheet functions present in Excel have been implemented (yet). | While both Apache POI and JExcelAPI feature a formula validator, not all spreadsheet functions present in Excel have been implemented (yet). <br /> | ||
Worse, older Excel versions feature less functions than newer versions. So be wary as this may make for interesting confusion. | Worse, older Excel versions feature less functions than newer versions. So be wary as this may make for interesting confusion. <br /> | ||
Reading and writing "shared formulas" with the OCT interface isn't (yet) supported; see bug | |||
[https://savannah.gnu.org/bugs/?52875 bug #52875]. | |||
==== Matlab compatibility ==== | ==== Matlab compatibility ==== | ||
| Line 594: | Line 597: | ||
=== OCT interface === | === OCT interface === | ||
Since io package version 1.2.4, an interface called "OCT" was added. Except for unzip, it has no dependencies | Since io package version 1.2.4, an interface called "OCT" was added. Except for unzip, it has no dependencies and it is faster than the Java-based interfaces. | ||
If | If | ||
<pre>chk_spreadsheet_support == 0</pre> | <pre>chk_spreadsheet_support == 0</pre> | ||
| Line 603: | Line 605: | ||
<pre>m = xlsread ('file.xlsx', 1, [], 'OCT');</pre> | <pre>m = xlsread ('file.xlsx', 1, [], 'OCT');</pre> | ||
About development: <br /> | |||
The OCT interface makes use of regular expressions for parsing the XML contents of OOXML, ODS and gnumeric formats. While frowned upon by XML gurus (see for example [https://stackoverflow.com/questions/1732348/regex-match-open-tags-except-xhtml-self-contained-tags/1732454#1732454 here] for some amusing postings), using regexps is much faster than any current XML parser. But the trade-off is that regexps are fragile, esp. withregard to the order in which XML tags appear in XML nodes. <br /> | |||
Just for reassurance: to date we haven't seen any problems with the OCT interface for reading and writing regular data. | |||
[[Category:Octave Forge]] | [[Category:Octave Forge]] | ||