Bug 94423 - UNO/Java: cannot retrieve Named Ranges from .ods, but can from .xls/.xlsx
Summary: UNO/Java: cannot retrieve Named Ranges from .ods, but can from .xls/.xlsx
Status: RESOLVED NOTOURBUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.0.1.2 release
Hardware: x86-64 (AMD64) Windows (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Cell-Name
  Show dependency treegraph
 
Reported: 2015-09-21 20:00 UTC by pr.nienhuis
Modified: 2020-04-28 18:50 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments
.ods file with Named Ranges (8.21 KB, application/vnd.oasis.opendocument.spreadsheet)
2015-09-21 20:00 UTC, pr.nienhuis
Details
simple .ods file with 2 sheets and 3 named ranges (8.32 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-03-25 13:06 UTC, pr.nienhuis
Details
Excel showing Named range (46.81 KB, image/png)
2018-11-27 19:00 UTC, pr.nienhuis
Details

Note You need to log in before you can comment on or make changes to this bug.
Description pr.nienhuis 2015-09-21 20:00:09 UTC
Created attachment 118913 [details]
.ods file with Named Ranges

Using Java/UNO from Octave (an OSS Matlab alternative, www.octave.org) it is very well possible to retrieve Named Ranges from Excel spreadsheets (.xls/.xlsx). However, it isn't possible to retrieve them from .ods spreadsheets.

Making an .ods spreadsheet and defining some named ranges, then converting them (writing to disk) as Excel files and reading them in Excel 2013, the named ranges are gone.

Making an .xlsx spreadsheet and defining some named ranges, then converting them (writing to disk) as Calc (.ods) files, the named ranges cannot be retrieved.

In content.xml there *is* a stanza:
<table:named-expressions>
  <table:named-range table:name="textrange" table:base-cell-address="$First.$A$1" table:cell-range-address="$Second.$A$3:.$B$5"/>
</table:named-expressions>

My Octave code looks like this:

  nmr = cell (0, 3);
  ## Entire workbook
  unotmp = javaObject ("com.sun.star.uno.Type", "com.sun.star.beans.XPropertySet");
  docProps = xls.workbook.queryInterface (unotmp);
  urng = docProps.getPropertyValue ("NamedRanges");
  rnms = urng.getObject.getElementNames ();
  ## Get ranges
  for ii = 1:numel (rnms)
    rng = urng.getObject.getByName (rnms(ii));
    nm = strrep (rng.getObject ().getContent (), "$", ""); ## ->  $3rdSheet.$D$2:$I$4
    nmr{end+1, 1} = rnms(ii);
    nmr{end, 2} = nm(1:index(nm, ".")-1);
    nmr{end, 3} = nm(index(nm, ".")+1:end);
  endfor

  ## Per sheet. First some preparations
  sheets = xls.workbook.getSheets ();
  sh_names = sheets.getElementNames ();
  if (! iscell (sh_names))
    ## Java array (LibreOffice 3.4.+), convert to cellstr
    sh_names = char (sh_names);
  else
    sh_names = {sh_names};
  endif
  ## For each sheet get named ranges
  for jj = 1:numel (sh_names)
    unotmp = javaObject ("com.sun.star.uno.Type", "com.sun.star.sheet.XSpreadsheet");
    sh = sheets.getByName(sh_names{jj}).getObject.queryInterface (unotmp);
    unotmp = javaObject ("com.sun.star.uno.Type", "com.sun.star.beans.XPropertySet");
    shProps = sh.queryInterface (unotmp);
    urng = shProps.getPropertyValue ("NamedRanges");
    rnms = urng.getObject.getElementNames ();
    for ii=1:numel (rnms)
      rng = urng.getObject.getByName (rnms(ii));
      nm = strrep (rng.getObject ().getContent (), "$", ""); ## ->  $3rdSheet.$D$2:$I$4
      nmr{end+1, 1} = rnms(ii);
      nmr{end, 2} = nm(1:index(nm, ".")-1);
      nmr{end, 3} = nm(index(nm, ".")+1:end);
    endfor
  endfor

As said before, this code works fine on Excel files but not on .ods files

A typical .ods file (Ranges.ods) is attached.
Comment 1 Xisco Faulí 2017-03-08 11:46:31 UTC
Could you please try to reproduce it with the latest version of LibreOffice from https://www.libreoffice.org/download/libreoffice-fresh/ ?
I have set the bug's status to 'NEEDINFO'. Please change it back to 'UNCONFIRMED' if the bug is still present in the latest version.
Comment 2 pr.nienhuis 2017-03-25 13:04:13 UTC
Unfortunately, in LO 5.3.1.2.(x64) I can see no different behavior.

I now defined named ranges in LO itself, but using the UNO Java bridge in Octave (see code in OP) I still don't see any Named Ranges. See attached range_tst.ods, that contains 3 named ranges.

Writing the file from LO 5.3.1.2 as .xlsx and reading it into Excel 2013, Excel complains and offers to repair the file. So there's another, related bug now.
XML indicating what was repaired below.

<?xml version="1.0" encoding="UTF-8" standalone="true"?>
-<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<logFileName>error032680_01.xml</logFileName>
<summary>Errors were detected in file 'C:\tmp\range_tst.xlsx'</summary>
-<removedFeatures summary="Following is a list of removed features:">
<removedFeature>Removed Feature: Table from /xl/tables/table3.xml part (Table)</removedFeature>
-/removedFeatures>
-<repairedRecords summary="Following is a list of repairs:">
<repairedRecord>Repaired Records: Table from /xl/tables/table1.xml part (Table)</repairedRecord>
</repairedRecords>
</recoveryLog>
Comment 3 pr.nienhuis 2017-03-25 13:06:00 UTC
Created attachment 132140 [details]
simple .ods file with 2 sheets and 3 named ranges
Comment 4 Xisco Faulí 2018-11-27 12:55:43 UTC
Hello,
Could you please try to reproduce it with the latest version of LibreOffice
from https://www.libreoffice.org/download/libreoffice-fresh/ ?
I have set the bug's status to 'NEEDINFO'. Please change it back to
'UNCONFIRMED' if the bug is still present in the latest version.
Comment 5 pr.nienhuis 2018-11-27 19:00:10 UTC
Created attachment 147075 [details]
Excel showing Named range
Comment 6 pr.nienhuis 2018-11-27 19:07:57 UTC
A bit of a mixed bag.

The original uploaded files still show no Named range from Octaveusing the Java UNO bridge (LO 6.3.1).

I made a new .xlsx file in Excel 2013 with 3 named ranges, here the named ranges show up in Octave.
Then I converted that to .ods => Named ranges show up in Octave.

I made a new .ods file with 2 named ranges => Octave cannot show them.
Converted it to .xlsx uing LO 6.1.3 ==> Octave can't find the named ranges.
Converted it to .xlsx using Excel 2013 => shows just 1 named range in Excel (see attached pic) but no named ranges at all in octave.

I'd say it is beginning to get better :-)

Then I made another .ods file with named ranges, and Excel doesn't like the .ods file. After allowing Exel to "repair" the file excel mentions it removed illegal content in /xl/tables/table2.xml part (Table 2)". Ominous.
Comment 7 Myk Dowling 2019-01-17 04:04:21 UTC
Can confirm this bug is still active. Named ranges are not retrieved when opening either an xlsx file or an ods file saved with Libre Office
Comment 8 Xisco Faulí 2019-03-21 13:20:06 UTC
Hello,
LibreOffice 6.2.2.2 is going to be released today, could you please try again
with this version to see if the problem has been resolved meanwhile? Thanks in
advance
Comment 9 pr.nienhuis 2019-03-23 09:24:06 UTC
(In reply to Xisco Faulí from comment #8)
> Hello,
> LibreOffice 6.2.2.2 is going to be released today, could you please try again
> with this version to see if the problem has been resolved meanwhile? Thanks
> in
> advance

Hi Xisco,

Thanks for the reminder.
Just curious - is this just a random try request only because LO 6.2.2.2 is out, or has there really been some activity w.r.t. the code in question?

Note that this may be 2 bugs in one or 2 manifestations of the same bug:
- The Java UNO bridge doesn't convey Named Ranges properly to outside programs (my original complaint)
- Excel doesn't like Named Range info in .xlsx spreadsheets written by LO.

Sure I'll try but I have busy time ahead.
Might be interesting to compare with OOo - but I'd guess they usually lag behind on LO :-)

P.
Comment 10 Xisco Faulí 2019-11-08 12:48:29 UTC
(In reply to pr.nienhuis from comment #9)
> (In reply to Xisco Faulí from comment #8)
> > Hello,
> > LibreOffice 6.2.2.2 is going to be released today, could you please try again
> > with this version to see if the problem has been resolved meanwhile? Thanks
> > in
> > advance
> 
> Hi Xisco,
> 
> Thanks for the reminder.
> Just curious - is this just a random try request only because LO 6.2.2.2 is
> out, or has there really been some activity w.r.t. the code in question?

kind of... LibreOffice is under constant developers and bugs might get fixed as a side effect of other fixes, that's why I asked.
The problem with this issue is that no one has reproduced since it was reported in 2015. If you want you can give a try to LibreOffice 6.3.3.2
Comment 11 Buovjaga 2020-04-26 14:17:14 UTC
Please give me steps how I can reproduce this with your example file and Octave. I installed Octave, opened the example file and ran your program, but I get fundamental errors like "error: [java] java.lang.ClassNotFoundException: com.sun.star.uno.Type" from the start. I have no idea how to set this up, so please guide me with exact steps.
Comment 12 Myk Dowling 2020-04-26 22:04:57 UTC
Create a spreadsheet in libre office.
Define a named range.
Save as a .xlsx file.
Open file in Excel or Google Docs
Named range does not appear.
Comment 13 pr.nienhuis 2020-04-28 10:17:11 UTC
@Myk Dowling:
That just isn't enough by itself. Reading with Excel shows all Named ranges. The bug is in the UNO-Java bridge, where they don't show up. Does Google Docs invoke the UNO-Java bridge?

@Buovjaga:
Octave needs the LO6 Java jars + program dir in the javaclasspath to avoid the ClassNotFoundException.
Use Octave's
javaaddpath ('/full/path/to/<name_of>.jar') and/or
javaaddpath ('/full/path/to/subdir')
to add them one by one to the javaclasspath. FYI, mine looks as follows w.r.t. the LO6 Java .jars: 

>> javaclasspath
   STATIC JAVA PATH

      - empty -

   DYNAMIC JAVA PATH

      C:\Program Files\LibreOffice\program\classes\ridl.jar
      C:\Program Files\LibreOffice\program\classes\unoloader.jar
      C:\Program Files\LibreOffice\program\classes\juh.jar
      C:\Program Files\LibreOffice\program\classes\jurt.jar
      C:\Program Files\LibreOffice\program\classes\unoil.jar
      C:\Program Files\LibreOffice\program
:
:
>>

Furthermore Octave need the io package installed and loaded.
pkg install -forge io  ## to install the io package
pkg load io            ## to load the io package

Then dos/th along the lines of:
[a, b, c, d] = xlsfinfo ("<ods file>.ods")
and if all is well you'll get s/th like:

>> [a, b, c, d] = xlsfinfo ("TrialRange.xlsx")
a = Microsoft Excel Spreadsheet
b =
{
  [1,1] = Sheet1
  [1,2] = A1:B4
}

c = xlOpenXMLWorkbook
d =
{
  [1,1] = TrialRange
  [1,2] = Sheet1
  [1,3] = A1:B3
}

BUT (and there's the bug)

>> [a, b, c, d] = xlsfinfo ("TrialRange_ods.ods")
fformat = ODSWorkbook
a = OpenOffice.org Calc spreadsheet
b =
{
  [1,1] = Sheet1
  [1,2] = A1:B4
}

c = ODSWorkbook
d = {}(0x3)
>>

where struct variable d (containing Named range info) is empty.
Comment 14 pr.nienhuis 2020-04-28 10:17:55 UTC
(Updated LO release)
Comment 15 Buovjaga 2020-04-28 14:34:23 UTC
Thanks, I confirm with attachment 132140 [details]:
[a, b, c, d] = xlsfinfo ("/home/tonttu/user/range_tst.ods")
fformat = ODSWorkbook
a = OpenOffice.org Calc spreadsheet
b =
{
  [1,1] = Blad1
  [2,1] = blad2
  [1,2] = B3:C5
  [2,2] = B7:D9
}

c = ODSWorkbook
d = {}(0x3)

For my notes, adding the paths on Linux:
javaaddpath ('/usr/lib/libreoffice/program/classes/ridl.jar')
javaaddpath ('/usr/lib/libreoffice/program/classes/unoloader.jar')
javaaddpath ('/usr/lib/libreoffice/program/classes/juh.jar')
javaaddpath ('/usr/lib/libreoffice/program/classes/jurt.jar')
javaaddpath ('/usr/lib/libreoffice/program/classes/unoil.jar')
javaaddpath ('/usr/lib/libreoffice/program')

Arch Linux 64-bit
Version: 6.4.3.2
Build ID: 6.4.3-1
CPU threads: 8; OS: Linux 5.6; UI render: default; VCL: kf5; 
Locale: fi-FI (fi_FI.UTF-8); UI-Language: en-US
Calc: threaded
Comment 16 pr.nienhuis 2020-04-28 18:46:54 UTC
@Buovjaga:
Hang on, I gave you a wrong Octave command for reading the file, sorry.
The spreadsheet I/O interface was missing (Octave has several "interfaces" (dependencies) to choose from for reading spreadsheets).

It should read:
[a, b, c, d] = xlsfinfo ("/home/tonttu/user/range_tst.ods", "uno")

Using that the Octave code seems to read the Named ranges well, but only the second try.  The first time Java emits a warning about "An illegal reflective access operation" and no name ranges are read then.

It could well be that 5 years ago this also happened but w/o the Java warning so it looked like an upstream bug (from Octave's perspective).  I do remember starting to see that Java warning intermittently after I upgraded Java on my Windows boxes from 8 to 10 some time ago; so this may be a concealed bug in Octave's Java subsystem that got somewhat more clearly exposed by a newer Java release.

I'll enter a bug report for Octave.

All in all I think this bug report can be closed as "invalid" or the like.
Thanks.
Comment 17 Buovjaga 2020-04-28 18:50:22 UTC
Hey, thanks for the good news (for us :)). Closing