Drexel dragonThe Math ForumDonate to the Math Forum



Search All of the Math Forum:

Views expressed in these public forums are not endorsed by Drexel University or The Math Forum.


Math Forum » Discussions » Software » comp.soft-sys.matlab

Topic: Reading Excel xlsread cell with formula in them
Replies: 9   Last Post: Jul 23, 2013 1:03 AM

Advanced Search

Back to Topic List Back to Topic List Jump to Tree View Jump to Tree View   Messages: [ Previous | Next ]
Marc

Posts: 108
Registered: 7/2/10
Re: Reading Excel xlsread cell with formula in them
Posted: Jul 23, 2013 1:03 AM
  Click to see the message monospaced in plain text Plain Text   Click to reply to this topic Reply

"Marc" wrote in message <kpogk0$s1j$1@newscl01ah.mathworks.com>...
> "Marc" wrote in message <kpofv5$qoj$1@newscl01ah.mathworks.com>...
> > "Steven_Lord" <slord@mathworks.com> wrote in message <kpn707$e2u$1@newscl01ah.mathworks.com>...
> > >
> > >
> > > <tolsthoorn@gmail.com> wrote in message
> > > news:20a162b8-1c1a-4d40-8aeb-efc8ade3dce6@googlegroups.com...

> > > > Whenever I read excel data info Matlab, and a cell contains a formula that
> > > > yields a text value, I get a NaN in Matlab. This happens even if I change
> > > > the format of the excel sheet cell to text. This way I can never read
> > > > dynamic texts from Excel, which largely reduces the value of xlsread.

> > >
> > > In order to evaluate that formula, you need to be running Microsoft Excel.
> > > Correct?
> > >
> > > As an analogy, I can write the formula:
> > >
> > > sin(0)
> > >
> > > inside a MATLAB program file. If I tried to read that program file in C++,
> > > all I'll get is the text string "sin(0)". In order to obtain the _value_ of
> > > that expression, I need to evaluate it in MATLAB.
> > >

> > > > For instance, a cell in Excel contains a formula yielding a text value
> > > > that I want to read into Matlab to use a header of a figure. The text in
> > > > Excel adapts automatically when I change the data to reflect some
> > > > alternative situation.

> > >
> > > The text may change, but only when the file is being processed by Excel and
> > > the formula is evaluated.
> > >

> > > > It happens when using xlsread on Mac OS X. Mac OS X is handicapped
> > > > enormously form the fact that xlsread on the Mac can only read old 95/5.0
> > > > Excel file versions. As long as the Mathworks refuses to update this for
> > > > its Mac users, we have to live with it. But it is mighty irritating. I
> > > > hope someone has solved this issue.

> > >
> > > As of release R2012b, according to the Release Notes for MATLAB:
> > >
> > > "The xlsread function now reads data from XLSM, XLTX, and XLTM files on all
> > > platforms. Previously, this functionality was available only on Microsoft
> > > Windows systems with Excel software."
> > >
> > > http://www.mathworks.com/help/matlab/release-notes.html
> > >
> > > I don't think that will resolve the problem you're experiencing, though,
> > > which is that you want MATLAB to be able to evaluate Excel "code" as part of
> > > the reading process. On Windows, if you have Microsoft Excel installed,
> > > MATLAB uses COM to interact with Excel and assuming that the file is set to
> > > automatically reevaluate that formula when the file is opened, you would be
> > > able to do what you want. But I'm not certain that approach is possible on
> > > Macs.
> > >
> > > --
> > > Steve Lord
> > > slord@mathworks.com
> > > To contact Technical Support use the Contact Us link on
> > > http://www.mathworks.com

> >
> > I apologize if my comment is treating you like a newbie but are you using the following format with xlsread()....
> >
> > [YourData, YourText] = xlsread();
> >
> > Any cell with text will give an NaN in YourData and will be shown in YourText.
> >
> > This works fine for me with Mac OS 10.8.3, Matlab 2012b and Office 2011 for Mac with .xlsx and .xlsm files. By default, I am saving everything as .xlsm, which seems to give me the most consistent results on my Macs. Here column B has the values and column C simply is "=B2...."
> >
> > tic
> > [YourData, YourText] = xlsread('TextVsNum.xlsm','Sheet1', 'B2:C7');
> > toc
> >
> > Elapsed time is 0.042532 seconds.

> > >> YourText
> >
> > YourText =
> >
> > 'Your data' 'Your data'
> > 'My data' 'My data'
> > '' ''
> > '' ''
> > 'five' 'five'
> >

> > >> YourData
> >
> > YourData =
> >
> > 2 2
> > 3 3
> > NaN NaN
> > 5 5

>
> I should have been clear w.r.t saving and consistency with .xlsm.... A lot of my excel files from work contain application based macros to get stuff from databases, historians and analytical equipment, so saving everything as .xlsm has worked best for me across both platforms.


Another minor point, brought up in several other threads, with 2012b and on, xlsread started shaving off leading and lagging data and text. In the above example, rows 2 through 7 should give "vectors" with 6 rows, but as you can see I ended up with 5 and 4 rows.

This has been a real pain and I am trying to find a link to the "answers" post were you can comment a line out of xlsread to make this fungible with previous matlab versions.

NOT THAT I SUPPORT THAT.... but everyone here are adults and can make there own choices.



Point your RSS reader here for a feed of the latest messages in this topic.

[Privacy Policy] [Terms of Use]

© Drexel University 1994-2014. All Rights Reserved.
The Math Forum is a research and educational enterprise of the Drexel University School of Education.