http://www.mathworks.com/matlabcentral/newsreader/view_thread/324571
MATLAB Central Newsreader  How to Select Entire Row From Excel File Uisng Prompt?
Feed for thread: How to Select Entire Row From Excel File Uisng Prompt?
enus
©19942015 by MathWorks, Inc.
webmaster@mathworks.com
MATLAB Central Newsreader
http://blogs.law.harvard.edu/tech/rss
60
MathWorks
http://www.mathworks.com/images/membrane_icon.gif

Sun, 18 Nov 2012 00:18:09 +0000
How to Select Entire Row From Excel File Uisng Prompt?
http://www.mathworks.com/matlabcentral/newsreader/view_thread/324571#891905
Ryan
Hello Matlab experts. I’m trying to figure out a way to enter a number/value into a prompt, and pass that value into Excel ColumnA, and read that entire used range into Matlab. Here is the code that I’m working with (many lines are commented out as I was trying several ideas, but nothing worked). Basically, I want to enter a number/value, and have the entire row imported. How can I do that?<br>
<br>
[f, p] = uigetfile({'*.xls';'*.xlsx'},'Pick Excel File');<br>
filename=fullfile(p,f);<br>
<br>
% First, open an Excel Server.<br>
e = actxserver('excel.application');<br>
<br>
% Insert a new workbook.<br>
eWorkbook = e.Workbooks.Add;<br>
e.Visible = 1;<br>
<br>
% Make the first sheet active.<br>
eSheets = e.ActiveWorkbook.Sheets;<br>
<br>
eSheet1 = eSheets.get('Item', 1);<br>
eSheet1.Activate;<br>
<br>
colEnd = sheetObj.Range(answer).End('xlToRight').Column<br>
xlRange = xlRow + ':' + colEnd;<br>
subsetA = xlsread(filename, sheet, xlRange)<br>
<br>
<br>
<br>
%prompt={'Enter an Excel Row:'};<br>
%title='Enter an Excel Row'; <br>
%answer=inputdlg(prompt,title);<br>
<br>
<br>
%e = actxserver ('Excel.Application'); %# open Activex server<br>
%ewb = e.Workbooks.Open(filename); %# open the file<br>
%esh = ewb.ActiveSheet;<br>
<br>
%excelObj = actxserver ('Excel.Application');<br>
%excelWorkbook = excelObj.workbooks.Open(fileName);<br>
%worksheets = excelObj.sheets;<br>
<br>
%ewb = excelObj.Workbooks.Open(filename); %# open the file<br>
%esh = worksheets.ActiveSheet;<br>
<br>
<br>
<br>
%xlRow = answer;<br>
<br>
% Row end, appears to work for rectangular data in sheet.<br>
%rowEnd = sheetObj.Range('A1').End('xlDown').Row<br>
% Column end for first row<br>
%colEnd = sheetObj.Range('A1').End('xlToRight').Column<br>
<br>
<br>
<br>
%excelObj = actxserver ('Excel.Application');<br>
<br>
% Full path to your file required<br>
%fileObj = excelObj.Workbooks.Open('c:\tmp\t.xlsx');<br>
<br>
%sheetObj = excelObj.Worksheets.get('Item', 'Sheet1');<br>
<br>
% Row end, appears to work for rectangular data in sheet.<br>
%rowEnd = sheetObj.Range('A1').End('xlDown').Row<br>
% Column end for first row<br>
%colEnd = sheetObj.Range('A1').End('xlToRight').Column<br>
<br>
<br>
%e = actxserver ('Excel.Application'); %# open Activex server<br>
%filename = fullfile(pwd,'example2.xlsx'); %# full path required<br>
<br>
<br>
%e = actxserver ('Excel.Application');<br>
%filename = fullfile(pwd,'example2.xlsx');<br>
%ewb = excelObj.Workbooks.Open(filename);<br>
%esh = ewb.ActiveSheet;<br>
<br>
%sheetObj = excelObj.Worksheets.get('Item', 'Sheet1');<br>
%num_col = sheetObj.Range('A1').End(xlLeft).Column;<br>
<br>
%intCol = Range('IV1').End(xlLeft).Col;<br>
<br>
%excelObj = actxserver ('Excel.Application');<br>
%filename = fullfile(pwd,'example2.xlsx');<br>
%ewb = e.Workbooks.Open(filename);<br>
%esh = ewb.ActiveSheet;<br>
<br>
%sheetObj = e.Worksheets.get('Item', 'Sheet1');<br>
%num_rows = sheetObj.Range('A1').End('xlDown').Row<br>
<br>
%%sheet = 1;<br>
<br>
%excelObj = actxserver ('Excel.Application');<br>
<br>
% Full path to your file required<br>
%fileObj = excelObj.Workbooks.Open(filename);<br>
%num_col = sheetObj.Cells(sheetObj.Columns.Count, 1).End('xlLeft').Column<br>
%sheetObj = excelObj.Worksheets.get('Item', 'Sheet1');

Sun, 18 Nov 2012 18:22:14 +0000
Re: How to Select Entire Row From Excel File Uisng Prompt?
http://www.mathworks.com/matlabcentral/newsreader/view_thread/324571#891931
Phil Goddard
You could spend time digging into the file to see how many columns of the specified row are actually used and then just extract those cells, but it may be just as quick to use a sledge hammer approach<br>
<br>
try<br>
% Reading 2007 and 2010 files<br>
row = xlsread('myfile.xls',sprintf('A%d:XFD%d',rowNumber));<br>
catch ME %#ok<br>
try<br>
% read 2003 files<br>
row = xlsread('myfile.xls',sprintf('A%d:IV%d',rowNumber));<br>
catch ME<br>
rethrow(ME);<br>
end<br>
end<br>
<br>
% Strip nans from the result<br>
% (This will strip all nans not just those at the end of the row.)<br>
data = row(~isnan(row));<br>
<br>
Phil.

Mon, 19 Nov 2012 00:07:13 +0000
Re: How to Select Entire Row From Excel File Uisng Prompt?
http://www.mathworks.com/matlabcentral/newsreader/view_thread/324571#891939
Ryan
Thanks for the help Phil, but that didn't work for me. I made some changes to the code, and I'm working with this now (code below). However, it’s definitely NOT dynamic.<br>
<br>
excelObj = actxserver ('Excel.Application');<br>
% Full path to your file required<br>
fileObj = excelObj.Workbooks.Open('C:\Program Files\MATLAB\R2012a\StockSymbols.xls');<br>
sheetObj = excelObj.Worksheets.get('Item', 'Symbols');<br>
filename = 'C:\Program Files\MATLAB\R2012a\StockSymbols.xls';<br>
<br>
<br>
% Row end, appears to work for rectangular data in sheet.<br>
rowEnd = sheetObj.Range('A1').End('xlDown').Row<br>
% Column end for first row<br>
colEnd = sheetObj.Range('A1').End('xlToRight').Column<br>
<br>
prompt={'Enter an Excel Row:'};<br>
title='Enter an Excel Row'; <br>
answer=inputdlg(prompt,title);<br>
<br>
sheet = 'Symbols';<br>
xlRange = 'A16:S16';<br>
<br>
<br>
subsetA = xlsread(filename, sheet, xlRange);<br>
<br>
<br>
Basically, what I’m trying to do is enter a value into an InputBox and pass that into ColumA of the Excel file, and copy the data from that specific row (xlRange) into a Matlab variable named subsetA. So, it’s almost like a Vlookup from Matlab into Excel, and then copy the range (the entire row) into a Matlab variable. All values in ColumnA are dates (this is what I pass to the InputBox). I want to import the different stock prices.<br>
<br>
<br>
"Phil Goddard" <phil@goddardconsulting.ca> wrote in message <k8b90m$4g8$1@newscl01ah.mathworks.com>...<br>
> You could spend time digging into the file to see how many columns of the specified row are actually used and then just extract those cells, but it may be just as quick to use a sledge hammer approach<br>
> <br>
> try<br>
> % Reading 2007 and 2010 files<br>
> row = xlsread('myfile.xls',sprintf('A%d:XFD%d',rowNumber));<br>
> catch ME %#ok<br>
> try<br>
> % read 2003 files<br>
> row = xlsread('myfile.xls',sprintf('A%d:IV%d',rowNumber));<br>
> catch ME<br>
> rethrow(ME);<br>
> end<br>
> end<br>
> <br>
> % Strip nans from the result<br>
> % (This will strip all nans not just those at the end of the row.)<br>
> data = row(~isnan(row));<br>
> <br>
> Phil.

Mon, 19 Nov 2012 03:43:10 +0000
Re: How to Select Entire Row From Excel File Uisng Prompt?
http://www.mathworks.com/matlabcentral/newsreader/view_thread/324571#891945
Phil Goddard
I'm confused by your terminology.<br>
I don't see anywhere in your code (or any reason in your description) why you are "... pass that into ColumA ..."<br>
Nor "(this is what I pass to the InputBox)"  surely you are reading from the InputBox not passing anything into it.<br>
<br>
If you mean that you are just wanting to compare a number(date?) that a user enters to the values(dates) in the first column of the spreadsheet and then extract the corresponding row, then why not either,<br>
<br>
a) read the first column into MATLAB; determine which element (and hence row) corresponds to the value entered by the user; then use my previous code to read the specified row.<br>
<br>
or<br>
<br>
b) read everything from the specified sheet into MATLAB; then determine which row you want and extract it from the whole matrix that was read in.<br>
<br>
Phil.

Mon, 19 Nov 2012 13:23:07 +0000
Re: How to Select Entire Row From Excel File Uisng Prompt?
http://www.mathworks.com/matlabcentral/newsreader/view_thread/324571#891987
Ryan
Well, I'm entering a value (date) into the InputBox, and then reading from the InputBox to ColumnA. You're correct. I didn't phrase it as well as I could have.<br>
<br>
I believe both the options that you mentioned would work fine. I'm not sure how to do the coding. Surely, you know Matlab better than I know it.<br>
<br>
I never got your code sample to work. It said something about the Try Catch being incorrect. It looks fine to me. I didn't know how to interpret that error.<br>
<br>
Do you have an idea what the actual code will look like? If not, I'll have to do a lot more fiddling with this thing. I spent a while on it yesterday, and got as far as my last post.<br>
<br>
Thanks!!<br>
<br>
<br>
"Phil Goddard" <phil@goddardconsulting.ca> wrote in message <k8c9se$iqk$1@newscl01ah.mathworks.com>...<br>
> I'm confused by your terminology.<br>
> I don't see anywhere in your code (or any reason in your description) why you are "... pass that into ColumA ..."<br>
> Nor "(this is what I pass to the InputBox)"  surely you are reading from the InputBox not passing anything into it.<br>
> <br>
> If you mean that you are just wanting to compare a number(date?) that a user enters to the values(dates) in the first column of the spreadsheet and then extract the corresponding row, then why not either,<br>
> <br>
> a) read the first column into MATLAB; determine which element (and hence row) corresponds to the value entered by the user; then use my previous code to read the specified row.<br>
> <br>
> or<br>
> <br>
> b) read everything from the specified sheet into MATLAB; then determine which row you want and extract it from the whole matrix that was read in.<br>
> <br>
> Phil.

Mon, 19 Nov 2012 22:41:17 +0000
Re: How to Select Entire Row From Excel File Uisng Prompt?
http://www.mathworks.com/matlabcentral/newsreader/view_thread/324571#892015
Phil Goddard
You need to do error checking, and I am assuming your file is as per one of your previous posts whether the first column is read in as integer numbers (which can be interpreted as dates):<br>
<br>
requireDateNumber = %whatever your user enters<br>
<br>
data = xlsread('myfile.xls');<br>
rowIndex = (data(:,1) == requiredDateNumber);<br>
<br>
if any(rowIndex)<br>
rowData = data(rowIndex,2:end);<br>
else<br>
error('There is no corresponding row.');<br>
end<br>
<br>
Phil.

Tue, 20 Nov 2012 03:11:09 +0000
Re: How to Select Entire Row From Excel File Uisng Prompt?
http://www.mathworks.com/matlabcentral/newsreader/view_thread/324571#892025
Ryan
Sorry to bother you, but I think there is something wrong with your function. At first I thought it may have something to do with the dates. I tried integers and text; nothing worked.<br>
<br>
requireDateNumber = 'C';<br>
<br>
data = xlsread('C:\Program Files\MATLAB\R2012a\StockSymbols.xls');<br>
rowIndex = (data(:,1) == requiredDateNumber);<br>
<br>
if any(rowIndex)<br>
rowData = data(rowIndex,2:end);<br>
else<br>
error('There is no corresponding row.');<br>
end<br>
<br>
Here is the error:<br>
Error in Read_Specific_Row (line 34)<br>
rowIndex = (data(:,1) == requiredDateNumber);<br>
<br>
<br>
'requireDateNumber' must be just a variable. It doesn't have to be a date, or numeric, or anything specific, right.<br>
<br>
<br>
"Phil Goddard" <phil@goddardconsulting.ca> wrote in message <k8ecid$eop$1@newscl01ah.mathworks.com>...<br>
> You need to do error checking, and I am assuming your file is as per one of your previous posts whether the first column is read in as integer numbers (which can be interpreted as dates):<br>
> <br>
> requireDateNumber = %whatever your user enters<br>
> <br>
> data = xlsread('myfile.xls');<br>
> rowIndex = (data(:,1) == requiredDateNumber);<br>
> <br>
> if any(rowIndex)<br>
> rowData = data(rowIndex,2:end);<br>
> else<br>
> error('There is no corresponding row.');<br>
> end<br>
> <br>
> Phil.

Tue, 20 Nov 2012 05:37:09 +0000
Re: How to Select Entire Row From Excel File Uisng Prompt?
http://www.mathworks.com/matlabcentral/newsreader/view_thread/324571#892037
Phil Goddard
It needs to be a number (hence the name).<br>
<br>
Phil.

Tue, 20 Nov 2012 05:54:09 +0000
Re: How to Select Entire Row From Excel File Uisng Prompt?
http://www.mathworks.com/matlabcentral/newsreader/view_thread/324571#892040
Phil Goddard
Note that there is also a typo.<br>
<br>
In one place I have<br>
requireDateNumber.<br>
<br>
It should be<br>
requiredDateNumber.<br>
<br>
Phil.

Wed, 21 Nov 2012 02:54:15 +0000
Re: How to Select Entire Row From Excel File Uisng Prompt?
http://www.mathworks.com/matlabcentral/newsreader/view_thread/324571#892089
Ryan
Yeah, I missed that typo thing. Well, I'm trying this now; still not working:<br>
<br>
requiredDateNumber = '11/2/2012';<br>
<br>
data = xlsread('C:\Program Files\MATLAB\R2012a\Symbols.xls');<br>
rowIndex = (data(:,1) == requiredDateNumber);<br>
<br>
if any(rowIndex)<br>
rowData = data(rowIndex,2:end);<br>
else<br>
error('There is no corresponding row.');<br>
end<br>
<br>
Error using == <br>
Matrix dimensions must agree.<br>
<br>
Error in Read_Specific_Row (line 34)<br>
rowIndex = (data(:,1) == requiredDateNumber);<br>
<br>
<br>
I did some research on this. I can't figure out what the problem is. Yes, I'm sure 'Matrix dimensions must agree', but what are these Matrix dimensions?????<br>
<br>
<br>
"Phil Goddard" <phil@goddardconsulting.ca> wrote in message <k8f5u1$5vd$1@newscl01ah.mathworks.com>...<br>
> Note that there is also a typo.<br>
> <br>
> In one place I have<br>
> requireDateNumber.<br>
> <br>
> It should be<br>
> requiredDateNumber.<br>
> <br>
> Phil.

Wed, 21 Nov 2012 17:08:08 +0000
Re: How to Select Entire Row From Excel File Uisng Prompt?
http://www.mathworks.com/matlabcentral/newsreader/view_thread/324571#892134
Phil Goddard
'11/2/2012' is a string not a number.<br>
<br>
>>requiredDateNumber = datenum('11/02/2012','mm/dd/yyyy');<br>
<br>
Phil

Fri, 23 Nov 2012 22:52:08 +0000
Re: How to Select Entire Row From Excel File Uisng Prompt?
http://www.mathworks.com/matlabcentral/newsreader/view_thread/324571#892268
Ryan
Due to the Thanksgiving holiday, it was hard to get back to you until now. This works perfect. I learned a lot from this exercise. Thanks so much!!<br>
<br>
<br>
"Phil Goddard" <phil@goddardconsulting.ca> wrote in message <k8j1po$c8j$1@newscl01ah.mathworks.com>...<br>
> '11/2/2012' is a string not a number.<br>
> <br>
> >>requiredDateNumber = datenum('11/02/2012','mm/dd/yyyy');<br>
> <br>
> Phil