bugGNU Octave - Bugs: bug #60830, [octave-forge] (io) xls2oct can't...

 
 

bug #60830: [octave-forge] (io) xls2oct can't find worksheets in .xlsx file created by Google Docs

Submitter:  Greg Holmberg <gholmberg>
Submitted:  Sat 26 Jun 2021 09:58:36 PM UTC
   
 
Category:  Octave Package Severity:  3 - Normal
Priority:  5 - Normal Item Group:  Incorrect Result
Status:  Fixed Assigned to:  None
Originator Name:  Greg Holmberg 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

Wed 28 Jul 2021 06:51:06 PM UTC, comment #8: 

Thanks very much for verifying.

Closing report.

Philip Nienhuis <philipnienhuis>
Group Member
Wed 28 Jul 2021 12:30:54 AM UTC, comment #7: 

I tested and the fix works.

Environment: Octave 6.1.1, io 2.6.3, linux 5.11 (Ubuntu 21.04), LibreOffice 7.1.4.2.

My steps were:

Download a new .xlsx file from Google Sheets.

Check that I can open it in LibreOffice.

Make sure I can recreate the problem.  At first, I couldn't, then I realized I was using the UNO interface.  So I changed the call to:

    xlsopen("sample.xlsx", true, "OCT");

And now xls2oct failed.

Copied _OCT_spsh_open_.m to /usr/share/octave/packages/io-2.6.3/private

And xls2oct succeeded.

I made sure that LibreOffice could still open the file.  It did. No apparent losses to the file.

Conclusion: the fix looks good to me.

Greg Holmberg <gholmberg>
Sat 24 Jul 2021 06:15:51 PM UTC, comment #6: 

Ping!
@OP: did you try if the (patched) file in comment #2 fixes your issue?

Philip Nienhuis <philipnienhuis>
Group Member
Tue 13 Jul 2021 11:29:51 AM UTC, comment #5: 

No response on my request in comment #2. In the mean time I've pushed that fix here:
http://hg.code.sf.net/p/octave/io/rev/dc2030ea13a4

Status => Ready for test, see file attached in comment #2.

Philip Nienhuis <philipnienhuis>
Group Member
Sun 27 Jun 2021 08:05:10 PM UTC, comment #4: 

No the problem here looks to be a different XML tag order (not alphabetic) - a known weakness of the OCT interface. But in this case one that can be worked around.

docProps.xml and also e.g., calcChain.xml aren't vital parts of an OOXML archive and -as you say- will be re-generated automatically.

Philip Nienhuis <philipnienhuis>
Group Member
Sun 27 Jun 2021 05:45:12 PM UTC, comment #3: 

Part of the problem may be that the originally posted sample.xlsx, when unzipped, lacks a docProps directory. If you open it in excel, and save it excel will add a docProps directory.

I've seen the same happening in excel sheets created by writetable with matlab on unix.

A.R. Burgers <arb>
Sun 27 Jun 2021 09:35:42 AM UTC, comment #2: 

(Luckily I had some time in between packing)

Please try with attached __OCT_spsh_open__ and report back.
To do that just swap it with the one in <io_package_install_dir>/private, you can find it like this:

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




(file #51603)

Philip Nienhuis <philipnienhuis>
Group Member
Sun 27 Jun 2021 07:36:40 AM UTC, comment #1: 

Confirmed on Windows:

>> [a, b, c] = xlsfinfo ("sample.xlsx", "oct")
a = Microsoft Excel Spreadsheet
b = [](0x1)
c =

>> [a, b, c] = xlsfinfo ("sample.xlsx", "poi")
a = Microsoft Excel Spreadsheet
b =
{
  [1,1] = Modeling
  [2,1] = Constants
  [1,2] = A1:AG52
  [2,2] = A1:Z10
}

c = xlOpenXMLWorkbook
>>


Given bug #60825 and this one, I wonder what Google does to provide these spreadheets. Apparently they set them up differently than any other spreadsheet SW we've seen til now.
I'm wondering if locale settings and text encoding play a role here.

If no one beats me to it I'll look at it after my vacation i.e., after mid-July. Until then I can't do much.

In the mean time you can work around it by invoking the Apache POI interface ("poi") as shown above. You need a Java JRE and add the relevant files from Apache POI to the javaclasspath; see the html doc in the io package installation dir for details, of see the wiki. Many distros have Apache POI in their repos, chances are that your distro has it as well.
As in bug #60825 you mention LibreOffice, another alternative is using the Java UNO bridge supplied by LibreOffice. It could be that it needs additional installation of LibreOffice parts from your distro's repo.

In the io package there's a function chk_spreadsheet_support that can set up the javaclasspath for you, given paths to where Apache POI files and/or the LibreOffice program are installed.

Philip Nienhuis <philipnienhuis>
Group Member
Sat 26 Jun 2021 09:58:36 PM UTC, original submission:  

I have an .xlsx file created by Google Sheets (File > Download > Microsoft Excel (.xlsx)).

After download, I can open the file in LibreOffice 7.0.6.2 on Ubuntu 20.10 (Linux 5.8.0).

However, if I try to read it with xls2oct, I get:


error: xls2oct: worksheet number 1 > number of worksheets in file (0)


If I give a worksheet name, I get:


error: xls2oct: cannot find sheet 'Modeling' in file sample.xlsx


The following script reproduces the problem with the attached .xlsx file.


xls = xlsopen("sample.xlsx", true);
[cells, xls, status] = xls2oct(xls);
xls = xlsclose(xls);


My environment:


ver
----------------------------------------------------------------------
GNU Octave Version: 6.2.0 (hg id: 5cfdf97c5350)
GNU Octave License: GNU General Public License
Operating System: Linux 5.8.0-59-generic #66-Ubuntu SMP Thu Jun 17 00:46:01 UTC 2021 x86_64
----------------------------------------------------------------------
Package Name  | Version | Installation directory
--------------+---------+-----------------------
     control  |   3.2.0 | /home/greg/snap/octave/78/octave/control-3.2.0
          io *|   2.6.3 | /home/greg/snap/octave/78/octave/io-2.6.3
      signal  |   1.4.1 | /home/greg/snap/octave/78/octave/signal-1.4.1

version -java
ans = no usable Java Runtime Environment (x86_64) found:
javaMethod: support for Java was unavailable or disabled when Octave was built

chk_spreadsheet_support([], 2)

(OCT interface... OK, included in io package)

1. Checking Octave's Java support... none.
This Octave has no built-in Java support. Skipping Java checks
Only ODS 1.2 (.ods) & OOXML (.xlsx) & .gnumeric r/w support present
ans = 0


Greg Holmberg <gholmberg>

 

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

Attach Files:
   
   
Comment:
   

Attached Files
file #51601:  sample.xlsx added by gholmberg (121KiB - application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)

 

Depends on the following items: None found

Items that depend on this one: None found

 

Carbon-Copy List
  • -email is unavailable- added by arb (Posted a comment)
  • -email is unavailable- added by siko1056 (Updated the item)
  • -email is unavailable- added by gholmberg (Submitted the item)
  • -email is unavailable- added by gholmberg
  •  

    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 13 latest changes.

    Date Changed by Updated Field Previous Value => Replaced by
    2021-07-28 philipnienhuis StatusReady For Test Fixed
        Open/ClosedOpen Closed
        Release6.2.0 other
    2021-07-13 philipnienhuis StatusNeed Info Ready For Test
        Summary[octave-forge] (io) xls2oct can't find worksheets in .xlsx file [octave-forge] (io) xls2oct can't find worksheets in .xlsx file created by Google Docs
    2021-06-27 philipnienhuis Attached File- Added _OCT_spsh_open_.m, #51603
        StatusConfirmed Need Info
    2021-06-27 philipnienhuis StatusNone Confirmed
        Operating SystemGNU/Linux Any
        Summary[octave forge] (io) xls2oct can't find worksheets in .xlsx file [octave-forge] (io) xls2oct can't find worksheets in .xlsx file
    2021-06-27 siko1056 Summaryxls2oct can't find worksheets in .xlsx file [octave forge] (io) xls2oct can't find worksheets in .xlsx file
    2021-06-26 gholmberg Attached File- Added sample.xlsx, #51601
        Carbon-Copy- Added gholmberg

    Back to the top

    Powered by Savane 3.13-4448.
    Corresponding source code