bugGNU Octave - Bugs: bug #45996, io package: add named...

 
 

bug #45996: io package: add named ranges/definedNames

Submitted by:  Andreas Weber <andy1978>
Submitted on:  Fri 18 Sep 2015 12:57:31 PM UTC  
 
Category: Octave Forge PackageSeverity: 1 - Wish
Priority: 3 - LowItem Group: Feature Request
Status: FixedAssigned to: Philip Nienhuis <philipnienhuis>
Originator Name: Open/Closed: Closed
Release: otherOperating System: Any

Add a New Comment(Rich Markup)
   

You are not logged in

Please log in, so followups can be emailed to you.

 

(Jump to the original submission Jump to the original submission)

Sat 10 Oct 2015 09:19:20 AM UTC, comment #8:

This has been fixed in the io repo.
io-2.2.11 will be out shortly

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Tue 29 Sep 2015 11:57:28 AM UTC, comment #7:

@Andy:
I see what you mean, just hit it at work.

I'll fix this myself very shortly.

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Mon 28 Sep 2015 05:54:55 PM UTC, comment #6:

Hmmm it did work for me. Anything not representing a regular range ("A1", "B25:VDF10234") could be a named range; if not matching one of the present named ranges it's an error.
I think it is not the job of xls2oct / oct2xls / ods2oct / oct2ods to validate or even gate range names. That's the user's responsibility, he/she can explore the present named ranges with xlsfinfo / odsfinfo.

AFAICS in Excel 2013 a named range applies to just one worksheet. A "local" named range can be invoked from that worksheet only, "global" named ranges from other worksheets as well (w/o specifying the sheet name).
For xlsread/write that implies that specifying a named range implicitly also specifies a specific worksheet.

For LibreOffice it works largely the same (for Excel files that is, .ods doesn't work, see upstream bug report:
https://bugs.documentfoundation.org/show_bug.cgi?id=94423 )

However, in gnumeric "global" ranges seem to apply to the currently selected worksheet.

IOW, the usual mess with competing programs.
I suppose that by io-2.2.12 it will all have been sorted out as far as possible.

As to a patch, I indicated that the named range stanza could be singled out into a separate private function. If you could do that it would be awesome. But I might find time this week, it doesn't seem daunting.

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Mon 28 Sep 2015 08:05:12 AM UTC, comment #5:

The check if it's a "normal" range or a named range currently is (for example in xl22oct.m line 151)

This doesn't work if the named range is "foo" for example.

Shouldn't be this

I tried to find the rules for named ranges but apparently there is no clear one which is implemented by gnumeric, libreoffice and excel.

What looks common between these 3 is:
Named ranges must start with a letter or an underline. Some allows period or blanks in their name but I wouldn't count on that.

Some testcases for me:

I'll create a patch as soon as I find some time...

Andreas Weber <andy1978>
Project Member
Wed 23 Sep 2015 08:48:19 PM UTC, comment #4:

Done, uploaded to repo.

Named ranges do not work for JOD (jOpenDocument), OTK (ODF Toolkit) and JXL (JExcelApi) due to bugs in those libraries. Currently stubs are implemented.
In addition, for UNO (LibreOffice 5), Named ranges only work with .xls / .xlsx, not with .ods.

I'll try to upload io-2.2.10 to the package release tracker asap

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Tue 22 Sep 2015 04:12:13 PM UTC, comment #3:

I've implemented it now for all interfaces except OTK (ODF Toolkit / odfdom) and JOD (jOpenDocument) as their named ranges Java methods simply don't work. I added / will add upstream bug reports.

The implementation was remarkably easier than I figured.

Just some texinfo polishing and it's done.

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Sun 20 Sep 2015 08:38:37 PM UTC, comment #2:

It took remarkably little time to implement this, it already works for OCT (.xlsx, .ods, .gnumeric), POI (.xlsx/.xls) & OXS. I took the liberty to amend your getDefinedNames function a little and morph it into _OCT_xlsx_getnmranges_.m.

Figuring out COM, JXL and UNO seems a bit harder, haven't looked at OTK an JOD interfaces yet.
What I wrote below about error checking in xls2oct etc is already largely covered by try-catches in xlsread etc.

xlsfinfo now returns named ranges when echoed to screen as I suggested in comment #1.

There are a few things to figure out:

A first little issue to tackle is the scope of named ranges. That seems to depend on the file type.
Some file types have ranges referring to either just one sheet (easy) or the entire workbook w/o any sheet reference (e.g., gnumeric). Elsewhere (e.g., OOXML) workbook scope cannot be figured out from the info I have. ODS has 3D-ranges covering multiple sheets; I saw that also mentioned in the OXS (OpenXLS) javadocs.
I have never used named ranges so some experimenting is needed here.
@Andy: would you have some advice?

A second issue is ambiguity when xlsread has to distinguish between a sheet name and a named range; either one can be 2nd input arg. It may be that this part must be done only after the named ranges are known, but currently xlsread isn't the place to figure that out - it's done in xls2oct/oct2xls and only IF a named range seems to have been specified (to avoid unneeded lookups). Likewise for xlswrite.
AFAIK Matlab's xlsread/xlswrite don't support named ranges at all, so we have to find a convenient yet robust and hopefully monkey-proof way to implement this in io-2.2.10+

Attached is a first batch of files to replace a few functions in an installed io package: xlsfinfo.m + several files that go into private/ (xlsread/-write are yet untouched, they just work with range names as well now).
Untar over your current io package and try it out. And please let me know what you think of it!

(file #34940)

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Fri 18 Sep 2015 08:22:18 PM UTC, comment #1:

(Maybe this rather belongs on the patch tracker.)

I have been thinking about his from the time of the very first spreadsheet interface (COM) I made for the io package.

It would be very nice to have, but then I'd like to have it work for all interfaces and file formats that support named ranges. I'm afraid that's almost all interfaces and formats, then; not only .xlsx.
IOW, this is a substantial bit of work.

In addition some rigid error checking needs to be in place. Nonexistent ranges should be catched and treated in an intelligent way. E.g., xls2oct shouldn't just error out because when it was called from xlsread, the file pointer would vanish from scope but the operating system and/or JVM could still have zombies with spreadsheet data becoming inaccessible until the next reboot. Instead xls2oct should rather return with an empty data array but emit some warning. Where I write xls2oct I also mean the related interface-specific private functions.
And so forth. Some more thinking is required before this can be implemented.
Another issue is scope: some names only pertain to individual sheets, others to the entire workbook. That has to be tackled as well.

I think get_definedNames had better be called something like "getNamedRanges" or so and should be a private func, to be called by the spreadsheet routines when some data range is specified by the user and allowing validation. It should branch to respective spreadsheet interface-specific subfunctions in private/ named like "__OCT_xlsx_nmranges__.m", "__JOD_ods_nmranges__.m", etc.

As to user exposure, I think returning info on named ranges would be a nice and well-fitting extension for xlsfinfo.m (and odsfinfo.m).
xlsfinfo.m already does more than its Matlab sibling (ie., it returns occupied data ranges) so even more useful info wouldn't hurt :-) it could return a 4th output arg with the named ranges.

All in all I consider this to be welcome support for an old plan :-)
Thanks!

I'll set status to "postponed", but I'll start working on it - yet at slow pace. If you want to cooperate, email me privately.

(and some nitty-gritty:
- when just reading, call xlsopen with 0 for 2nd arg, just to be safe
- personally I would replace the while loop by a regexp.
For the rest I think you've clearly catched the spirit of the io package programming style ;-) )

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Fri 18 Sep 2015 12:57:31 PM UTC, original submission:

xlsx let you define names for cells so that you can use "foo" instead of "B12" for example.

Since this isn't currently supported in io, I wrote a function to retrieve the definedNames and insert values. Attached 3 files, run insert_into_xlsx.m to add some values to wx.xlsx.

This isn't polished yet and I think xlswrite could be extended that it also accepts names instead of just ranges.

@Philip: What do you think?

Andreas Weber <andy1978>
Project Member

 

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

Attach File(s):
   
   
Comment:
   

Attached Files
file #34940:  io_namedrange_try1.tar.gz added by philipnienhuis (5KiB - application/x-gzip)
file #34920:  get_definedNames.m added by andy1978 (748B - text/x-objcsrc)
file #34921:  insert_into_xlsx.m added by andy1978 (435B - text/x-objcsrc)
file #34922:  wb.xlsx added by andy1978 (5KiB - application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)

 

Depends on the following items: None found

Items that depend on this one: None found

 

Carbon-Copy List
  • -unavailable- added by andy1978 (Submitted the item)
  •  

    Do you think this task is very important?
    If so, you can click here to add your encouragement to it.
    This task has 0 encouragements so far.

    Only project members can vote.

     

    Please enter the title of George Orwell's famous dystopian book (it's a date):

     

     

    Follow 11 latest changes.

    Date Changed By Updated Field Previous Value => Replaced By
    Wed 23 Sep 2015 08:48:19 PM UTCphilipnienhuisStatusIn Progress=>Fixed
      Open/ClosedOpen=>Closed
    Sun 20 Sep 2015 08:38:37 PM UTCphilipnienhuisAttached File-=>Added io_namedrange_try1.tar.gz, #34940
      StatusPostponed=>In Progress
    Fri 18 Sep 2015 08:22:18 PM UTCphilipnienhuisPriority5 - Normal=>3 - Low
      StatusNone=>Postponed
      Assigned toNone=>philipnienhuis
      Operating SystemGNU/Linux=>Any
    Fri 18 Sep 2015 12:57:31 PM UTCandy1978Attached File-=>Added get_definedNames.m, #34920
      Attached File-=>Added insert_into_xlsx.m, #34921
      Attached File-=>Added wb.xlsx, #34922

    Back to the top


    Powered by Savane 3.1-cleanup1