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.
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.
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>
Created attachment 132140 [details] simple .ods file with 2 sheets and 3 named ranges
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.
Created attachment 147075 [details] Excel showing Named range
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.
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
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
(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.
(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
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.
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.
@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.
(Updated LO release)
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
@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.
Hey, thanks for the good news (for us :)). Closing