Why is detectImportOptions not sheet agnostic with a named Range?
7 visualizzazioni (ultimi 30 giorni)
Mostra commenti meno recenti
I can use the following line
opts = detectImportOptions(workbookFile,'Sheet',sheetName,'Range',Range);
containing an excel range name (see name manager) in Range and the corresponding sheet name in sheetName. The thing is, excel already allows me to set range names for the whole workbook. So, no need to specify the sheet name. I'd like my script to be agnostic to changes in sheet names or positions.
How can I do that?
If I launch without sheetName
opts = detectImportOptions(workbookFile,'Range',Range);
Matlab doesn't find my range, although the range name in excel is given with "Scope: Workbook".
4 Commenti
Risposte (2)
dpb
il 20 Lug 2024
Modificato: dpb
il 21 Lug 2024
Because the whole suite for workbooks is designed around working with a single worksheet at a time; none of the functions that use an importoptions object support reading more than a single sheet at a tme.(*)
Conceivabley readmatrix or readcell could return a 3D array, but it's a functionality not envisioned by the developers.
(*)However, as a quality of implementation detail, I would have expected the second form to have read the range for the first sheet in the workbook, presuming it (the range) is defined for it since the default behavior is to read the first sheet. But I would have no expectation of any of the routines returning data from more than one sheet on a single call; they are not documented to do that; the sheet input parameter is always defined as a single sheet either by name or number or an implied 1 if not given.
ADDENDUM/ERRATUM:
I was thinking incorrectly as @Stephen23 pointed out regarding the multiple sheet data return, sorry.
Does the present implementation require the sheet name be explicit to a particular sheet to return the correct data? If the range definition is global, then the internal reference should be explicit as to which sheet actually contains the data range and it should return the correct value regardless which sheet is used to satisfy the input requirement. I've not poked at the API to see how it operates internally; I've written everything I've done such that it finds things by column headers rather than by naming ranges that's just another step in creating the Excel sheet...if it doesn't work with a universal 'Sheet',1 as the input for the sheet, then I'd amend the previous to the level of a bug instead of an enhancement.
The possible issue and complicating factor on the development side is the existence of a named range that is local in scope but on multiple sheets. I can see why Mathworks may have chosen to not deal with it for that reason and I kinda' expect the response to the enhancement request will be "nyet!".
3 Commenti
dpb
il 21 Lug 2024
Yeah, I see that now...I was thinking it would be similar functionality as to the sheet scope, my bad.
dpb
il 21 Lug 2024
Modificato: dpb
il 23 Lug 2024
Potential workaround/addition---
I created a rudimentary ActiveX component to query a workbook for the contained named ranges; it isn't complete and I've not tested just how the difference between global and worksheet names is differentiated; I've not found a property that indicates which and I've only tested and the attached code only references the first sheet in the workbook I tested it on; I didn't have any others with already builtin names at hand for testing.
This returns a cell array of the names, addresses and name scope found; your mission if you choose to accept is to adapt to your situation and augment the range in your application to allow the generic import you're looking for.
function [name,range,scope]=GetAllNames(excel)
% return all workbook defined names
wbk=excel.ActiveWorkbook;
nms=wbk.Names;
nNames=nms.Count;
wks=wbk.Worksheets(1);
Cells=cell(nNames,3);
for r=1:nNames
Cells(r,1)= {nms.Item(r).Name};
try
Cells(r,2)={nms.Item(r).RefersToRange.Address};
Cells(r,3)={nms.Item(r).Parent.Name};
if matches(wbk.Name,Cells(r,3)), Cells(r,3)={'Global'}; end
catch
err=lasterror;
if contains(err.message,'0x800A03EC')
Cells(r, 2)= {'#REF!'};
Cells(r,3)={nms.Item(r).Parent.Name};
if matches(wbk.Name,Cells(r,3)), Cells(r,3)={'Global'}; end
continue
else
excel.ActiveWorkbook.Close
excel.Quit
warning('Excel GetAllNames Failure. Returning')
name=Cells(1:r,1); range=Cells(1:r,2); scope=Cells(1:r,3);
return
end
end
end
name=Cells(:,1);
range=Cells(:,2);
scope=Cells(:,3);
end
The only way I found to be able to determine scope was by querying the .Parent property; this returns the file name for global variables; I then turned that into the string "Global" instead of returning the file name; the sheet-specific return the sheet name.
I incorporated this in a enhanced Excel_utils class that works by first opening the ActiveX session and passing in the handle to the COM process. I wrote this to return the names from the active workbook already open, it can be easily modified to open the file passed to it instead.
To use this, the preamble needs would be
File='FullyQualifiedFileName.xlsx'; % must be fully qualified, no shortcuts will do
excel=actxserver('Excel.Application') % create the COM process
excel.Workbooks.Open(File) % open the desired file
[names,ranges]=GetAllNames(excel); % do the magic
excel.ActiveWorkbook.Close; % close the file
excel.Quit % stop the connection
delete(excel) % destroy the process
clear excel % don't leave hanging around in workspace
The error handling inside the function will catch a name with a bad reference and return "#REF!" for the address; any other error will go ahead and close the file and shut down COM connection so if it gives you the warning message then the last steps won't be needed (hopefully). If you play further, you'll undoubtedly crash and burn more than once so be prepared to occasionally have to kill Excel with task manager. Using breakpoints in debugger will help immeasureably in that regards, you can play at the command line in the debugger and it will trap the errors while you're testing new features; if you just let it run then it'll crash before you can do anything and leave the file open, etc., ...
A sample output at the debugger level after I did ad a couple of sheet scope variables to the test workbook looks like:
K>> Cells
Cells =
36×3 cell array
{'ActivityFee' } {'$H$5' } {'Global' }
...
{'Budget_Year' } {'$U$1' } {'Global' }
{'Sheet1!CellA1' } {'$A$1' } {'Sheet1' }
{'Cheer' } {'$U$11' } {'Global' }
{'County' } {'$C$5' } {'Global' }
{'OutState' } {'$C$8' } {'Global' }
{'Complete!Print_Area' } {'$A$3:$N$130'} {'Complete' }
{'RateBorder' } {'$B$7' } {'Global' }
...
{'RateInState' } {'$B$6' } {'Global' }
{'RateOnline' } {'$B$9' } {'Global' }
{'Soccer_Programs' } {'$U$9' } {'Global' }
...
{'Athletics!TotalInStateAthletics' } {'$H$21' } {'Athletics'}
{'Athletics!TotalOutStateAthletics'} {'$H$10' } {'Athletics'}
K>>
The named sheets show the names for the sheets while the added new sheet reflects the default 'Sheet1' string.
Good luck...
2 Commenti
dpb
il 22 Lug 2024
ADDENDUM
"... just how the difference between global and worksheet names is differentiated; I've not found a property that indicates which "
OK, closer observation shows that the local names are prefixed with "Sheetname!" in the address so simply checking for the "!" in the address or matching a sheet name works as well without querying the parent value.
Vedere anche
Categorie
Scopri di più su Spreadsheets in Help Center e File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!