xlsread returns NaN for cell references to string
6 vues (au cours des 30 derniers jours)
Afficher commentaires plus anciens
Anders Bergåker
le 12 Oct 2017
Commenté : Anders Bergåker
le 16 Oct 2017
I'm reading an excel document with xlsread with references to other cells that be either text or numeric values. The problem is that xlsread seems to interpret cells starting with an "=" as numeric even if is a reference to a cell with text. Thus the result of a reference to text cell returns NaN, even in the raw format. Is there any way to work around this? I've tried some tricks (see below) that forces Excel to treat the cell as text but it doesn't work with xlsread. The (very) simple example below illustrates the issue. Example:
A B C D
1 My age is 10
2 =A1 =B1&"" =TEXT(C1;0) =D1
> [num text raw] = xlsread('book1.xls')
num =
NaN NaN NaN 10
NaN NaN NaN 10
text =
1×3 cell array
'My' 'age' 'is'
raw =
2×4 cell array
'My' 'age' 'is' [10]
[NaN] [NaN] [NaN] [10]
Matlab 2016b, Excel 2010 (xls format), Linux.
9 commentaires
Walter Roberson
le 13 Oct 2017
The behavior is different in basic mode, which is for any system that is not windows with excel installed. The poster mentioned basic mode and Linux.
Réponse acceptée
Walter Roberson
le 13 Oct 2017
On MS Windows systems with Excel installed, it is possible to create an activexserver object to talk to excel in order to extract macro text or execute macros.
On non-Windows systems or MS Windows systems without Excel, or when 'basic' mode is specifically asked for, in order to read .xlsx files, a MATLAB routine xlsreadXLSX is called. The routine calls upon some java routines to extract portions of the .xlsx (because .xlsx are directories of .xml files that have been ZIP'd together) into text form. xml text form in hand, the MATLAB routine calls upon regexp() to parse the xml. Worst case, that code could be copied and hacked to know about formulae.
On non-Windows systems or MS Windows systems without Excel, or when 'basic' mode is specifically asked for, in order to read .xls files a MATLAB routine xlsreadBasic is called. It invokes the internal MATLAB routine biffread to do the binary read and discover the headers, but then it calls upon the built-in routine biffparse to turn the bytes into data. There is no configurability there. Handling macros would require writing or finding an xls parser. I seem to recall having encountered one in Java.
Plus de réponses (0)
Voir également
Catégories
En savoir plus sur Spreadsheets dans Help Center et File Exchange
Produits
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!