bugGNU Octave - Bugs: bug #43321, Inserting formulas with xlswrite...

 
 

bug #43321: Inserting formulas with xlswrite odswrite (octave 3.8.0 with io 2.2.4 on osx 10.9.5)

Submitter:  None
Submitted:  Mon 29 Sep 2014 10:44:50 AM UTC
   
 
Category:  Octave Package Severity:  3 - Normal
Priority:  5 - Normal Item Group:  Incorrect Result
Status:  Fixed Assigned to:  philipnienhuis
Originator Name:  Malte Originator Email:  -email is unavailable-
Open/Closed:  * Closed Release:  * other
Operating System:  * Any Fixed Release:  None
Planned Release:  None
* Mandatory Fields

Add a New Comment Rich Markup
   

Jump to the original submission

Sat 04 Oct 2014 09:15:16 AM UTC, comment #6: 

Thank you for being guinea pig :-)  the OCT interface could use a bit more testing, indeed.

Closing report

Philip Nienhuis <philipnienhuis>
Group Member
Fri 03 Oct 2014 11:37:44 PM UTC, comment #5: 

Thanks a lot for the quick fix. It solves the problems. Meanwhile I have played around more with the xls-interface and I have found more problems and even a fix. I will file seperate reports for each one as you requested.

Thanks again for your support and the work you put into the project!



Malte Hinrichs <maldje>
Fri 03 Oct 2014 08:24:10 AM UTC, comment #4: 

Currently it looks like both bugs have been fixed.
Please test. Do as follows:

1. Locate the share/.../private subdir of your io package installation (in Octave do:
cd (strrep (which ("oct2xls"), "oct2xls.m", "private"))
pwd

and note the location.

2. replace the two files
_OCT_2ods_.m
and
_OCT_oct2xlsx_.m

with the new versions here (the io repo):
http://sourceforge.net/p/octave/io/ci/default/tree/inst/private/

and try again & report back, please.

Philip Nienhuis <philipnienhuis>
Group Member
Tue 30 Sep 2014 07:41:45 PM UTC, comment #3: 

Ah sorry now I see that it is OCT xlsx stuff that doesn't enter proper formulas. (I know that OTK isn't really good in it so I assumed too fast it was all about OTK).

As I wrote in comment #1, formula interpretation is out of scope for OCT interface.
I'm looking into OOXML formula writing now

Philip Nienhuis <philipnienhuis>
Group Member
Tue 30 Sep 2014 04:19:25 PM UTC, comment #2: 

BTW this is two bug reports into one.

Please file a separate bug report for the second issue so I can post separate updates for each individual issue.
The 2nd one is already largely fixed.

As to issue #1
The formula stuff in odfdom (OTK interface) is a bit lacking; when reading it returns mangled formulas, perhaps that mangling is also required when writing.
The odfdom project may not be dead but had quite a low heart beat the last years. To my surprise I just saw that there's a new odfdom version, I'll try if that gives better results.

FYI: JOpenDocument is now on version 1.4-rc and at last has implemented formula I/O. As jopendocument (JOD) is quite a bit faster and more actively maintained than odfdom (OTK) I think JOD may be a better option for you.

Philip Nienhuis <philipnienhuis>
Group Member
Mon 29 Sep 2014 02:06:21 PM UTC, comment #1: 

Formula input sensu stricto should work OK, but for what you expect, formula evaluation (= calculating formula results), the spreadsheet I/O interface needs a formula validator and formula evaluator. The only interfaces that feature it are:
- COM (on Windows only, invoking Excel)
- UNO (invoking LibreOffice/Openoffice.org behind the scenes)
- Apache POI (and I'm not sure if that does work properly)

BTW, in your examplex you may want to write {1,2,'=(A1+B1)'} rather than {1,2,'=(A1+A2)'} ?

As to your example, where's the attachment?

What interface did you use (or did Octave use) to (try to) write the .ods file?

Philip Nienhuis <philipnienhuis>
Group Member
Mon 29 Sep 2014 10:44:50 AM UTC, original submission:  

Hi,

I find some incorrect behavior of xlswrite and odswrite using the OCT-interface.

1: Inserting formulas (e.g.  '=(A1+A2)') :

xlswrite('xlsfile.xlsx',{1,2,'=(A1+A2)'})

this results in an xlsx-file, where A3 contains a string-entry, not an interpreted formula.


odswrite("odsfile.ods",{1,2,'=(A1+A2)'})

does not create a file at all, despite the fact that octave claims it does. To actually create a file WSH must be specified:

odswrite("odsfile.ods",{1,2,'=(A1+A2)'},"one")

creates the file and A3 is correctly interpreted as a formula.

I attached an example-file, which demonstrates this behavior.


Anonymous

 

(Note: upload size limit is set to 16384 kB, after insertion of the required escape characters.)

Attach Files:
   
   
Comment:
   

No files currently attached

 

Depends on the following items: None found

Items that depend on this one: None found

 

Carbon-Copy List
  • -email is unavailable- added by maldje (Posted a comment)
  • -email is unavailable- added by None (Submitted the item)
  •  

    There are 0 votes so far. Votes easily highlight which items people would like to see resolved in priority, independently of the priority of the item set by tracker managers.

    Only group members can vote.

     

    Follow 8 latest changes.

    Date Changed by Updated Field Previous Value => Replaced by
    2014-10-04 philipnienhuis StatusPatch Submitted Fixed
        Open/ClosedOpen Closed
    2014-10-03 philipnienhuis StatusIn Progress Patch Submitted
        Release3.8.0 other
    2014-09-30 philipnienhuis StatusConfirmed In Progress
        Assigned toNone philipnienhuis
    2014-09-30 philipnienhuis StatusNone Confirmed
        Operating SystemMac OS Any

    Back to the top

    Powered by Savane 3.13-4448.
    Corresponding source code