Discover MakerZone

MATLAB and Simulink resources for Arduino, LEGO, and Raspberry Pi

Learn more

Discover what MATLAB® can do for your career.

Opportunities for recent engineering grads.

Apply Today

Thread Subject:
How to Select Entire Row From Excel File Uisng Prompt?

Subject: How to Select Entire Row From Excel File Uisng Prompt?

From: Ryan

Date: 18 Nov, 2012 00:18:09

Message: 1 of 12

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?

[f, p] = uigetfile({'*.xls';'*.xlsx'},'Pick Excel File');
filename=fullfile(p,f);
 
% First, open an Excel Server.
e = actxserver('excel.application');
 
% Insert a new workbook.
eWorkbook = e.Workbooks.Add;
e.Visible = 1;
 
% Make the first sheet active.
eSheets = e.ActiveWorkbook.Sheets;
 
eSheet1 = eSheets.get('Item', 1);
eSheet1.Activate;
 
colEnd = sheetObj.Range(answer).End('xlToRight').Column
xlRange = xlRow + ':' + colEnd;
subsetA = xlsread(filename, sheet, xlRange)
 
 
 
%prompt={'Enter an Excel Row:'};
%title='Enter an Excel Row';
%answer=inputdlg(prompt,title);
 
 
%e = actxserver ('Excel.Application'); %# open Activex server
%ewb = e.Workbooks.Open(filename); %# open the file
%esh = ewb.ActiveSheet;
 
%excelObj = actxserver ('Excel.Application');
%excelWorkbook = excelObj.workbooks.Open(fileName);
%worksheets = excelObj.sheets;
 
%ewb = excelObj.Workbooks.Open(filename); %# open the file
%esh = worksheets.ActiveSheet;
 
 
 
%xlRow = answer;
 
% Row end, appears to work for rectangular data in sheet.
%rowEnd = sheetObj.Range('A1').End('xlDown').Row
% Column end for first row
%colEnd = sheetObj.Range('A1').End('xlToRight').Column
 
 
 
%excelObj = actxserver ('Excel.Application');
 
% Full path to your file required
%fileObj = excelObj.Workbooks.Open('c:\tmp\t.xlsx');
 
%sheetObj = excelObj.Worksheets.get('Item', 'Sheet1');
 
% Row end, appears to work for rectangular data in sheet.
%rowEnd = sheetObj.Range('A1').End('xlDown').Row
% Column end for first row
%colEnd = sheetObj.Range('A1').End('xlToRight').Column
 
 
%e = actxserver ('Excel.Application'); %# open Activex server
%filename = fullfile(pwd,'example2.xlsx'); %# full path required
 
 
%e = actxserver ('Excel.Application');
%filename = fullfile(pwd,'example2.xlsx');
%ewb = excelObj.Workbooks.Open(filename);
%esh = ewb.ActiveSheet;
 
%sheetObj = excelObj.Worksheets.get('Item', 'Sheet1');
%num_col = sheetObj.Range('A1').End(xlLeft).Column;
 
%intCol = Range('IV1').End(xlLeft).Col;
 
%excelObj = actxserver ('Excel.Application');
%filename = fullfile(pwd,'example2.xlsx');
%ewb = e.Workbooks.Open(filename);
%esh = ewb.ActiveSheet;
 
%sheetObj = e.Worksheets.get('Item', 'Sheet1');
%num_rows = sheetObj.Range('A1').End('xlDown').Row
 
%%sheet = 1;
 
%excelObj = actxserver ('Excel.Application');
 
% Full path to your file required
%fileObj = excelObj.Workbooks.Open(filename);
%num_col = sheetObj.Cells(sheetObj.Columns.Count, 1).End('xlLeft').Column
%sheetObj = excelObj.Worksheets.get('Item', 'Sheet1');

Subject: How to Select Entire Row From Excel File Uisng Prompt?

From: Phil Goddard

Date: 18 Nov, 2012 18:22:14

Message: 2 of 12

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

try
   % Reading 2007 and 2010 files
   row = xlsread('myfile.xls',sprintf('A%d:XFD%d',rowNumber));
catch ME %#ok
   try
      % read 2003 files
      row = xlsread('myfile.xls',sprintf('A%d:IV%d',rowNumber));
   catch ME
      rethrow(ME);
   end
end

% Strip nans from the result
% (This will strip all nans not just those at the end of the row.)
data = row(~isnan(row));

Phil.

Subject: How to Select Entire Row From Excel File Uisng Prompt?

From: Ryan

Date: 19 Nov, 2012 00:07:13

Message: 3 of 12

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.

excelObj = actxserver ('Excel.Application');
% Full path to your file required
fileObj = excelObj.Workbooks.Open('C:\Program Files\MATLAB\R2012a\StockSymbols.xls');
sheetObj = excelObj.Worksheets.get('Item', 'Symbols');
filename = 'C:\Program Files\MATLAB\R2012a\StockSymbols.xls';
 
 
% Row end, appears to work for rectangular data in sheet.
rowEnd = sheetObj.Range('A1').End('xlDown').Row
% Column end for first row
colEnd = sheetObj.Range('A1').End('xlToRight').Column
 
prompt={'Enter an Excel Row:'};
title='Enter an Excel Row';
answer=inputdlg(prompt,title);
 
sheet = 'Symbols';
xlRange = 'A16:S16';
 
 
subsetA = xlsread(filename, sheet, xlRange);


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.


"Phil Goddard" <phil@goddardconsulting.ca> wrote in message <k8b90m$4g8$1@newscl01ah.mathworks.com>...
> 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
>
> try
> % Reading 2007 and 2010 files
> row = xlsread('myfile.xls',sprintf('A%d:XFD%d',rowNumber));
> catch ME %#ok
> try
> % read 2003 files
> row = xlsread('myfile.xls',sprintf('A%d:IV%d',rowNumber));
> catch ME
> rethrow(ME);
> end
> end
>
> % Strip nans from the result
> % (This will strip all nans not just those at the end of the row.)
> data = row(~isnan(row));
>
> Phil.

Subject: How to Select Entire Row From Excel File Uisng Prompt?

From: Phil Goddard

Date: 19 Nov, 2012 03:43:10

Message: 4 of 12

I'm confused by your terminology.
I don't see anywhere in your code (or any reason in your description) why you are "... pass that into ColumA ..."
Nor "(this is what I pass to the InputBox)" -- surely you are reading from the InputBox not passing anything into it.

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,

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.

or

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.

Phil.

Subject: How to Select Entire Row From Excel File Uisng Prompt?

From: Ryan

Date: 19 Nov, 2012 13:23:07

Message: 5 of 12

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.

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.

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.

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.

Thanks!!


"Phil Goddard" <phil@goddardconsulting.ca> wrote in message <k8c9se$iqk$1@newscl01ah.mathworks.com>...
> I'm confused by your terminology.
> I don't see anywhere in your code (or any reason in your description) why you are "... pass that into ColumA ..."
> Nor "(this is what I pass to the InputBox)" -- surely you are reading from the InputBox not passing anything into it.
>
> 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,
>
> 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.
>
> or
>
> 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.
>
> Phil.

Subject: How to Select Entire Row From Excel File Uisng Prompt?

From: Phil Goddard

Date: 19 Nov, 2012 22:41:17

Message: 6 of 12

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):

requireDateNumber = %whatever your user enters

data = xlsread('myfile.xls');
rowIndex = (data(:,1) == requiredDateNumber);

if any(rowIndex)
  rowData = data(rowIndex,2:end);
else
  error('There is no corresponding row.');
end

Phil.

Subject: How to Select Entire Row From Excel File Uisng Prompt?

From: Ryan

Date: 20 Nov, 2012 03:11:09

Message: 7 of 12

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.

requireDateNumber = 'C';

data = xlsread('C:\Program Files\MATLAB\R2012a\StockSymbols.xls');
rowIndex = (data(:,1) == requiredDateNumber);

if any(rowIndex)
  rowData = data(rowIndex,2:end);
else
  error('There is no corresponding row.');
end

Here is the error:
Error in Read_Specific_Row (line 34)
rowIndex = (data(:,1) == requiredDateNumber);


'requireDateNumber' must be just a variable. It doesn't have to be a date, or numeric, or anything specific, right.


"Phil Goddard" <phil@goddardconsulting.ca> wrote in message <k8ecid$eop$1@newscl01ah.mathworks.com>...
> 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):
>
> requireDateNumber = %whatever your user enters
>
> data = xlsread('myfile.xls');
> rowIndex = (data(:,1) == requiredDateNumber);
>
> if any(rowIndex)
> rowData = data(rowIndex,2:end);
> else
> error('There is no corresponding row.');
> end
>
> Phil.

Subject: How to Select Entire Row From Excel File Uisng Prompt?

From: Phil Goddard

Date: 20 Nov, 2012 05:37:09

Message: 8 of 12

It needs to be a number (hence the name).

Phil.

Subject: How to Select Entire Row From Excel File Uisng Prompt?

From: Phil Goddard

Date: 20 Nov, 2012 05:54:09

Message: 9 of 12

Note that there is also a typo.

In one place I have
requireDateNumber.

It should be
requiredDateNumber.

Phil.

Subject: How to Select Entire Row From Excel File Uisng Prompt?

From: Ryan

Date: 21 Nov, 2012 02:54:15

Message: 10 of 12

Yeah, I missed that typo thing. Well, I'm trying this now; still not working:

requiredDateNumber = '11/2/2012';

data = xlsread('C:\Program Files\MATLAB\R2012a\Symbols.xls');
rowIndex = (data(:,1) == requiredDateNumber);

if any(rowIndex)
  rowData = data(rowIndex,2:end);
else
  error('There is no corresponding row.');
end

Error using ==
Matrix dimensions must agree.

Error in Read_Specific_Row (line 34)
rowIndex = (data(:,1) == requiredDateNumber);


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?????


"Phil Goddard" <phil@goddardconsulting.ca> wrote in message <k8f5u1$5vd$1@newscl01ah.mathworks.com>...
> Note that there is also a typo.
>
> In one place I have
> requireDateNumber.
>
> It should be
> requiredDateNumber.
>
> Phil.

Subject: How to Select Entire Row From Excel File Uisng Prompt?

From: Phil Goddard

Date: 21 Nov, 2012 17:08:08

Message: 11 of 12

'11/2/2012' is a string not a number.

>>requiredDateNumber = datenum('11/02/2012','mm/dd/yyyy');

Phil

Subject: How to Select Entire Row From Excel File Uisng Prompt?

From: Ryan

Date: 23 Nov, 2012 22:52:08

Message: 12 of 12

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!!


"Phil Goddard" <phil@goddardconsulting.ca> wrote in message <k8j1po$c8j$1@newscl01ah.mathworks.com>...
> '11/2/2012' is a string not a number.
>
> >>requiredDateNumber = datenum('11/02/2012','mm/dd/yyyy');
>
> Phil

Tags for this Thread

No tags are associated with this thread.

What are tags?

A tag is like a keyword or category label associated with each thread. Tags make it easier for you to find threads of interest.

Anyone can tag a thread. Tags are public and visible to everyone.

Contact us