https://bugs.documentfoundation.org/show_bug.cgi?id=94423
Bug ID: 94423
Summary: UNO/Java: cannot retrieve Named Ranges from .ods, but
can from .xls/.xlsx
Product: LibreOffice
Version: 5.0.1.2 release
Hardware: x86-64 (AMD64)
OS: Windows (All)
Status: UNCONFIRMED
Severity: normal
Priority: medium
Component: Calc
Assignee: [email protected]
Reporter: [email protected]
Created attachment 118913
--> https://bugs.documentfoundation.org/attachment.cgi?id=118913&action=edit
.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.
--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Libreoffice-bugs mailing list
[email protected]
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
