[Octave-bug-tracker] [bug #65624] [octave-forge] (io) xlswrite OCT interface deletes previously written formulas

[Philip Nienhuis]

Update of bug #65624 (group octave):

                  Status:                    None => In Progress            
             Assigned to:                    None => philipnienhuis         
                 Release:                   9.1.0 => other                  
        Operating System:       Microsoft Windows => Any                    
                 Summary: Xlswrite deletes previously written formulas =>
[octave-forge] (io) xlswrite OCT interface deletes previously written formulas

    _______________________________________________________

Follow-up Comment #1:

Thanks for the report.

Some discussion took place on Discourse,
https://octave.discourse.group/t/xlswrite-deletes-previously-written-formulas/5535/5

TL;DR:
I'll push a fix soon to the io package repo, for now just try with attached 
__OCT_oct2xlsx__.m 

1. Find & go to current of io package location, esp. private/ subdir, using
the command:

cd (strrep (which ("oct2xls"), "oct2xls.m", "private"))

2. In e.g., Windows' file explorer, go that directory, and swap the file 
__OCT_oct2xlsx__.m  with the one attached to this report.

3. Close Octave, try your script again, and please! report back.

Longer explanation:
In order to write to an existing sheet containing data, first the sheet
contents are read. Next these data and the new data are merged, the sheet is
wiped empty, and then the combined data are written back to the sheet. For the
'oct' interface this is just the fastest way to do it.
However, formula entries need to be explicitly retained then and there's a
flag for it in a user options struct, <options>.formulas_as_text, that has to
be set to 'true'. If not set, the cached numeric formula results rather than
the formulas themselves will be read and merged. However, Octave's io package
(oct interface) has no spreadsheet formula evaluator so in your case,
rewriting the same spreadsheet again and again, there simply aren't any cached
results. Consequently there are no data to be read back, and the net effect is
that the cells containing your formulas were left empty in the updated sheet.

For the other two file type in the 'oct' interface (.ods and .gnumeric) the
flag was properly set, just not for .xlsx.
So all in all this was quite an easy fix.

(file #55957)

    _______________________________________________________

Additional Item Attachment:

File name: __OCT_oct2xlsx__.m             Size: 22KiB
    <https://file.savannah.gnu.org/file/__OCT_oct2xlsx__.m?file_id=55957>


    AGPL NOTICE

These attachments are served by Savane. You can download the corresponding
source code of Savane at
https://git.savannah.nongnu.org/cgit/administration/savane.git/snapshot/savane-758ecd5172e512b667882c3f5b73d7b5e9a30eb0.tar.gz


    _______________________________________________________

Reply to this item at:

  <https://savannah.gnu.org/bugs/?65624>

_______________________________________________
Message sent via Savannah
https://savannah.gnu.org/