problem with xlswrite

classic Classic list List threaded Threaded
6 messages Options
grg
Reply | Threaded
Open this post in threaded view
|

problem with xlswrite

grg
Hi there,

I need to report the results of an experiment in an excel worksheet that
also contains additional information.
I just need to fill in some of the cells in the worksheet and leave this
additional information untouched.

I'm using the following code:

status = xlswrite (fnout, OUT_olci, input_parameters.sheet, "B5:H5");

where:
fnout is a string containing the name of the output file;
OUT_olci is a 1x7 double array
input_parameters.sheet is a string containing the name of worksheet

The problem is that, after running my octave code when I open fnout with
libreoffice, I find that OUT_olci has been properly written in the correct
cells, but all the additional information has been deleted.

Can anybody help?

Many thanks in advance,
grg

below are my current settings:

octave:1> ver
----------------------------------------------------------------------
GNU Octave Version: 4.2.1
GNU Octave License: GNU General Public License
Operating System: Linux 4.11.12-100.fc24.x86_64 #1 SMP Fri Jul 21 17:35:20
UTC 2017 x86_64
----------------------------------------------------------------------
Package Name   | Version | Installation directory
---------------+---------+-----------------------
           io *|  2.4.10 | ..../octave/io-2.4.10






--
Sent from: http://octave.1599824.n4.nabble.com/Octave-General-f1599825.html


-----------------------------------------
Join us March 12-15 at CERN near Geneva
Switzerland for OctConf 2018.  More info:
https://wiki.octave.org/OctConf_2018
-----------------------------------------
Reply | Threaded
Open this post in threaded view
|

Re: problem with xlswrite

PhilipNienhuis
grg wrote

> Hi there,
>
> I need to report the results of an experiment in an excel worksheet that
> also contains additional information.
> I just need to fill in some of the cells in the worksheet and leave this
> additional information untouched.
>
> I'm using the following code:
>
> status = xlswrite (fnout, OUT_olci, input_parameters.sheet, "B5:H5");
>
> where:
> fnout is a string containing the name of the output file;
> OUT_olci is a 1x7 double array
> input_parameters.sheet is a string containing the name of worksheet
>
> The problem is that, after running my octave code when I open fnout with
> libreoffice, I find that OUT_olci has been properly written in the correct
> cells, but all the additional information has been deleted.

You mean that data is not just added but the worksheet in question has been
wiped first?
Are you sure that the sheet name matches? - it is case-sensitive. Just
asking ...

Some counter-questions:
- what operating system & version?
- which Octave version?
- which spreadsheet interface?

The io package contains some test scripts that contain checks on exactly
this sort of functionality. Just run:

more off
test_spsh

in some temporary directory / map / folder.
At the end of the process, test-spsh.m echoes info tho the screen that looks
like this:

...
Interface:          COM  POI  POI  JXL  OXS  UNO  OCT  OTK  JOD  UNO  OCT
OCT
File type           xls  xls xlsx  xls  xls  xls xlsx  ods  ods  ods  ods
gnumeric
Numeric array p.1:   +    +    +    +    +    +    +    +    +    +    +  
+
Numeric array p.2:   +    +    +    +    +    +    +    +    +    +    +  
+
Numeric array p.3:   +    +    +    +    +    +    +    +    +    +    +  
+
Numeric array p.4:   +    +    +    +    +    +    +    +    +    +    +  
+
Cellstr array p.1:   +    +    +    +    +    +    +    +    +    +    +  
+
Cellstr array p.2:   +    +    +    +    +    +    +    +    +    +    +  
+
 ...special chars:   +    +    +    +    +    +    +    +    +    +    +  
+
Boolean value    :   +    +    +    +    +    o    +    +    +    o    +  
+
Formula read back:   +    +    +    +    +    +    +    +    +    +    +  
+
  +  = correct result returned
  o  = partly correct (e.g., double rather than logical)
  -  = erroneous or no result.

- End of test_spsh -

Please report back this info too.

Philip




--
Sent from: http://octave.1599824.n4.nabble.com/Octave-General-f1599825.html


-----------------------------------------
Join us March 12-15 at CERN near Geneva
Switzerland for OctConf 2018.  More info:
https://wiki.octave.org/OctConf_2018
-----------------------------------------
grg
Reply | Threaded
Open this post in threaded view
|

Re: problem with xlswrite

grg
PhilipNienhuis wrote

> grg wrote
>> Hi there,
>>
>> I need to report the results of an experiment in an excel worksheet that
>> also contains additional information.
>> I just need to fill in some of the cells in the worksheet and leave this
>> additional information untouched.
>>
>> I'm using the following code:
>>
>> status = xlswrite (fnout, OUT_olci, input_parameters.sheet, "B5:H5");
>>
>> where:
>> fnout is a string containing the name of the output file;
>> OUT_olci is a 1x7 double array
>> input_parameters.sheet is a string containing the name of worksheet
>>
>> The problem is that, after running my octave code when I open fnout with
>> libreoffice, I find that OUT_olci has been properly written in the
>> correct
>> cells, but all the additional information has been deleted.
>
> You mean that data is not just added but the worksheet in question has
> been
> wiped first?

Yes the data are written, but the rest of the content of the worksheet is
wiped out. I can tell it's just the content, because this worksheet has some
merged cells and, after my xlswrite command, they remain merged, but empty.



PhilipNienhuis wrote
> Are you sure that the sheet name matches? - it is case-sensitive. Just
> asking ...

I am quite sure, because the file contains multiple worksheets and only the
worksheet I want wo work with is affected (and also because of the merged
cells mentioned above).


PhilipNienhuis wrote
> Some counter-questions:
> - what operating system & version?
> - which Octave version?
> - which spreadsheet interface?

octave:14> ver
----------------------------------------------------------------------
GNU Octave Version: 4.2.1
GNU Octave License: GNU General Public License
Operating System: Linux 4.11.12-100.fc24.x86_64 #1 SMP Fri Jul 21 17:35:20
UTC 2017 x86_64
----------------------------------------------------------------------
Package Name   | Version | Installation directory
---------------+---------+-----------------------
           io *|  2.4.10 | ...../octave/io-2.4.10


I am not sure how to check which spreadsheet interface.



PhilipNienhuis wrote
> The io package contains some test scripts that contain checks on exactly
> this sort of functionality. Just run:
>
> more off
> test_spsh
>
>
> Please report back this info too.

octave:15> test_spsh

Testing .xls interface OCT using file io-test.xlsx...

 1. Initialize arrays.
 2. Insert first empty sheet.
 3. Add data to test sheet.
 4. Add another sheet with just one number in A1.
 5. Explore sheet info.
 6. Read data back.
 7. Tests part 1 (basic I/O):
    ...Numeric array... matches...
    ...Cellstr array... matches...
    ...special characters... matches...
    ...Boolean... recovered...
 8. Repeat reading, now return formulas as text
 9. Tests part 2 (read back formula):
    ...OK, formula recovered ('=c2+d2').
10. Cleaning up..... OK

Testing .ods interface OCT using file io-test.ods...
 1. Initialize arrays.
 2. Insert first empty sheet.
 3. Add data to test sheet.
 4. Add another sheet with just one number in A1.
 5. Explore sheet info.
 6. Read data back.
 7. Tests part 1 (basic I/O):
    ...Numeric array... matches.
    ...Cellstr array... matches...
    ...special characters... matches.
    ...Boolean... recovered.
 8. Repeat reading, now return formulas as text
 9. Tests part 2 (read back as formula):
    (Note: just a check for a string rather than a numerical value)
    ...OK, formula recovered ('=c2+sin(d3)').
10. Cleaning up..... OK

Testing .ods interface OCT using file io-test.gnumeric...
 1. Initialize arrays.
 2. Insert first empty sheet.
 3. Add data to test sheet.
 4. Add another sheet with just one number in A1.
 5. Explore sheet info.
 6. Read data back.
 7. Tests part 1 (basic I/O):
    ...Numeric array... matches.
    ...Cellstr array... matches...
    ...special characters... matches.
    ...Boolean... recovered.
 8. Repeat reading, now return formulas as text
 9. Tests part 2 (read back as formula):
    (Note: just a check for a string rather than a numerical value)
    ...OK, formula recovered ('=c2+sin(d3)').
10. Cleaning up..... OK

Interface:          OCT  OCT  OCT
File type          xlsx  ods gnumeric
Numeric array p.1:   +    +    +  
Numeric array p.2:   +    +    +  
Numeric array p.3:   +    +    +  
Numeric array p.4:   +    +    +  
Cellstr array p.1:   +    +    +  
Cellstr array p.2:   +    +    +  
 ...special chars:   +    +    +  
Boolean value    :   +    +    +  
Formula read back:   +    +    +  
  +  = correct result returned
  o  = partly correct (e.g., double rather than logical)
  -  = erroneous or no result.

- End of test_spsh -

Thanks for your fast response
grg





--
Sent from: http://octave.1599824.n4.nabble.com/Octave-General-f1599825.html


-----------------------------------------
Join us March 12-15 at CERN near Geneva
Switzerland for OctConf 2018.  More info:
https://wiki.octave.org/OctConf_2018
-----------------------------------------
Reply | Threaded
Open this post in threaded view
|

Re: problem with xlswrite

PhilipNienhuis
grg wrote

> PhilipNienhuis wrote
>> grg wrote
>>> Hi there,
>>>
>>> I need to report the results of an experiment in an excel worksheet that
>>> also contains additional information.
>>> I just need to fill in some of the cells in the worksheet and leave this
>>> additional information untouched.
>>>
>>> I'm using the following code:
>>>
>>> status = xlswrite (fnout, OUT_olci, input_parameters.sheet, "B5:H5");
>>>
>>> where:
>>> fnout is a string containing the name of the output file;
>>> OUT_olci is a 1x7 double array
>>> input_parameters.sheet is a string containing the name of worksheet
>>>
>>> The problem is that, after running my octave code when I open fnout with
>>> libreoffice, I find that OUT_olci has been properly written in the
>>> correct
>>> cells, but all the additional information has been deleted.
>>
>> You mean that data is not just added but the worksheet in question has
>> been
>> wiped first?
>
> Yes the data are written, but the rest of the content of the worksheet is
> wiped out. I can tell it's just the content, because this worksheet has
> some
> merged cells and, after my xlswrite command, they remain merged, but
> empty.
>
>
>
> PhilipNienhuis wrote
>> Are you sure that the sheet name matches? - it is case-sensitive. Just
>> asking ...
>
> I am quite sure, because the file contains multiple worksheets and only
> the
> worksheet I want wo work with is affected (and also because of the merged
> cells mentioned above).
>
>
> PhilipNienhuis wrote
>> Some counter-questions:
>> - what operating system & version?
>> - which Octave version?
>> - which spreadsheet interface?
>
> octave:14> ver
> ----------------------------------------------------------------------
> GNU Octave Version: 4.2.1
> GNU Octave License: GNU General Public License
> Operating System: Linux 4.11.12-100.fc24.x86_64 #1 SMP Fri Jul 21 17:35:20
> UTC 2017 x86_64
> ----------------------------------------------------------------------
> Package Name   | Version | Installation directory
> ---------------+---------+-----------------------
>            io *|  2.4.10 | ...../octave/io-2.4.10
>
>
> I am not sure how to check which spreadsheet interface.
>
>
>
> PhilipNienhuis wrote
>> The io package contains some test scripts that contain checks on exactly
>> this sort of functionality. Just run:
>>
>> more off
>> test_spsh
>>
>>
>> Please report back this info too.
>
> octave:15> test_spsh
>
> Testing .xls interface OCT using file io-test.xlsx...
>
>  1. Initialize arrays.
>  2. Insert first empty sheet.
>  3. Add data to test sheet.
>  4. Add another sheet with just one number in A1.
>  5. Explore sheet info.
>  6. Read data back.
>  7. Tests part 1 (basic I/O):
>     ...Numeric array... matches...
>     ...Cellstr array... matches...
>     ...special characters... matches...
>     ...Boolean... recovered...
>  8. Repeat reading, now return formulas as text
>  9. Tests part 2 (read back formula):
>     ...OK, formula recovered ('=c2+d2').
> 10. Cleaning up..... OK
>
> Testing .ods interface OCT using file io-test.ods...
>  1. Initialize arrays.
>  2. Insert first empty sheet.
>  3. Add data to test sheet.
>  4. Add another sheet with just one number in A1.
>  5. Explore sheet info.
>  6. Read data back.
>  7. Tests part 1 (basic I/O):
>     ...Numeric array... matches.
>     ...Cellstr array... matches...
>     ...special characters... matches.
>     ...Boolean... recovered.
>  8. Repeat reading, now return formulas as text
>  9. Tests part 2 (read back as formula):
>     (Note: just a check for a string rather than a numerical value)
>     ...OK, formula recovered ('=c2+sin(d3)').
> 10. Cleaning up..... OK
>
> Testing .ods interface OCT using file io-test.gnumeric...
>  1. Initialize arrays.
>  2. Insert first empty sheet.
>  3. Add data to test sheet.
>  4. Add another sheet with just one number in A1.
>  5. Explore sheet info.
>  6. Read data back.
>  7. Tests part 1 (basic I/O):
>     ...Numeric array... matches.
>     ...Cellstr array... matches...
>     ...special characters... matches.
>     ...Boolean... recovered.
>  8. Repeat reading, now return formulas as text
>  9. Tests part 2 (read back as formula):
>     (Note: just a check for a string rather than a numerical value)
>     ...OK, formula recovered ('=c2+sin(d3)').
> 10. Cleaning up..... OK
>
> Interface:          OCT  OCT  OCT
> File type          xlsx  ods gnumeric
> Numeric array p.1:   +    +    +  
> Numeric array p.2:   +    +    +  
> Numeric array p.3:   +    +    +  
> Numeric array p.4:   +    +    +  
> Cellstr array p.1:   +    +    +  
> Cellstr array p.2:   +    +    +  
>  ...special chars:   +    +    +  
> Boolean value    :   +    +    +  
> Formula read back:   +    +    +  
>   +  = correct result returned
>   o  = partly correct (e.g., double rather than logical)
>   -  = erroneous or no result.

That looks good.

Admittedly adding data to worksheets containing merged cells might be an
issue with the OCT interface, never tried but as long as merged cell ranges
do not occupy cells written to I think that should be safe. I am not sure if
merged cell ranges do survive however, and if not it might be hard to fix
that with the OCT interface.

Could you please file a bug report (see:
https://www.gnu.org/software/octave/bugs.html) and attach to that report:
- the xlsx file in question before you added the OUT_olci array. If it
contains sensitive data, could you remove them and then check that the issue
still persists?

Thanks,
Philip



--
Sent from: http://octave.1599824.n4.nabble.com/Octave-General-f1599825.html


-----------------------------------------
Join us March 12-15 at CERN near Geneva
Switzerland for OctConf 2018.  More info:
https://wiki.octave.org/OctConf_2018
-----------------------------------------
Reply | Threaded
Open this post in threaded view
|

Re: problem with xlswrite

PhilipNienhuis
PhilipNienhuis wrote

> grg wrote
>> PhilipNienhuis wrote
>>> grg wrote
>>>> Hi there,
>>>>
>>>> I need to report the results of an experiment in an excel worksheet
>>>> that
>>>> also contains additional information.
>>>> I just need to fill in some of the cells in the worksheet and leave
>>>> this
>>>> additional information untouched.
>>>>
>>>> I'm using the following code:
>>>>
>>>> status = xlswrite (fnout, OUT_olci, input_parameters.sheet, "B5:H5");
> <snip>
>  
>> Interface:          OCT  OCT  OCT
>> File type          xlsx  ods gnumeric
>> Numeric array p.1:   +    +    +  
>> Numeric array p.2:   +    +    +  
>> Numeric array p.3:   +    +    +  
>> Numeric array p.4:   +    +    +  
>> Cellstr array p.1:   +    +    +  
>> Cellstr array p.2:   +    +    +  
>>  ...special chars:   +    +    +  
>> Boolean value    :   +    +    +  
>> Formula read back:   +    +    +  
>>   +  = correct result returned
>>   o  = partly correct (e.g., double rather than logical)
>>   -  = erroneous or no result.
>
> That looks good.
>
> Admittedly adding data to worksheets containing merged cells might be an
> issue with the OCT interface, never tried but as long as merged cell
> ranges
> do not occupy cells written to I think that should be safe. I am not sure
> if
> merged cell ranges do survive however, and if not it might be hard to fix
> that with the OCT interface.
>
> Could you please file a bug report (see:
> https://www.gnu.org/software/octave/bugs.html) and attach to that report:
> - the xlsx file in question before you added the OUT_olci array. If it
> contains sensitive data, could you remove them and then check that the
> issue
> still persists?

I just had a little time to experiment with the OCT interface.
It turns out that adding data to a worksheet containing merged cells works
fine as long as you don't add data into merged cell ranges.
If you do add them into a merged range, the data in the merged range simply
don't show up in LibreOffice or Excel.

But that doesn't explain plain data loss.
So I'm curious to your bug report and spreadsheet file.

Philip




--
Sent from: http://octave.1599824.n4.nabble.com/Octave-General-f1599825.html


-----------------------------------------
Join us March 12-15 at CERN near Geneva
Switzerland for OctConf 2018.  More info:
https://wiki.octave.org/OctConf_2018
-----------------------------------------
grg
Reply | Threaded
Open this post in threaded view
|

Re: problem with xlswrite

grg
PhilipNienhuis wrote
> I just had a little time to experiment with the OCT interface.
> It turns out that adding data to a worksheet containing merged cells works
> fine as long as you don't add data into merged cell ranges.
> If you do add them into a merged range, the data in the merged range
> simply
> don't show up in LibreOffice or Excel.
>
> But that doesn't explain plain data loss.
> So I'm curious to your bug report and spreadsheet file.

Well, in my case, I am not writing inside merged cells.
I've now also noticed that xlswrite not only wipes out all the additional
info from the worksheet I am writing on, but also from an other worksheet in
which I am not trying to write anything.
I've filed a bug (https://savannah.gnu.org/bugs/index.php?53298) and
uploaded expected and obtained results, as well as the original xlsx file.

Thanks again for your help,
grg




--
Sent from: http://octave.1599824.n4.nabble.com/Octave-General-f1599825.html


-----------------------------------------
Join us March 12-15 at CERN near Geneva
Switzerland for OctConf 2018.  More info:
https://wiki.octave.org/OctConf_2018
-----------------------------------------