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:
Reading part of excel spreadsheet

Subject: Reading part of excel spreadsheet

From: Øyvind

Date: 7 Oct, 2010 12:28:07

Message: 1 of 6

I need to read data from an excel file that is constantly being updated by a different process (not Matlab).

This is getting progressively slower as the excel file grows, and also tends to generate some errors (io-errors as well as data randomly being changed to NaNs). I really only need the last 30 or so rows for each read though, but I cannot find any way to tell xlsread to read only 'the last 30 rows' of the spreadsheet. I do not know the number of rows of the spreadsheet before reading, but I do know the number of columns (in fact I only need the last 30 rows of two of these columns).

Does anyone know how to accomplish this, either how to get the size of the spreadsheet or directly ask for the last N rows?

Subject: Reading part of excel spreadsheet

From: Ashish Uthama

Date: 7 Oct, 2010 13:45:57

Message: 2 of 6

You could use the actxserver function to run bits of Excel VB program in
MATLAB.

Here is something to get you started:

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

delete(excelObj);

Subject: Reading part of excel spreadsheet

From: Øyvind

Date: 7 Oct, 2010 16:18:04

Message: 3 of 6

Thanks! This actually works.

If it's not too much trouble, could you tell me how to do the actual reading of data? I can find the number of rows and then use xlsread,
>> [~,~,raw] = xlsread('myfile.xls',sprintf('H%d:H%d',rowEnd-29,rowEnd));
but I suspect that I could use the sheet object to read the data? I am finding the documentation hard to read on this point.


Ashish Uthama <first.last@mathworks.com> wrote in message <i8kiul$197$1@fred.mathworks.com>...
> You could use the actxserver function to run bits of Excel VB program in
> MATLAB.
>
> Here is something to get you started:
>
> 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
>
> delete(excelObj);

Subject: Reading part of excel spreadsheet

From: Ashish Uthama

Date: 7 Oct, 2010 18:38:54

Message: 4 of 6

Øyvind wrote:
> Thanks! This actually works.
I am equally surprised.



 > If it's not too much trouble, could you tell me how to do the actual
 > reading of data? I can find the number of rows and then use xlsread,
 >>> [~,~,raw] = xlsread('myfile.xls',sprintf('H%d:H%d',rowEnd-29,rowEnd));
 > but I suspect that I could use the sheet object to read the data? I am
 > finding the documentation hard to read on this point.
 >

Did you mean the MATLAB documentation? or Excel's VBA?

XLSREAD exposes Excel indexing which uses 'A1:B10' etc notation for
selecting a range. You could use it by manually creating the required
strings. But I dont see a easy general solution unless you have <26
columns. If you do, you could do this:

rowEnd = 230;
colEnd = 21;
numRows = 5; % number of last rows needed.

rangeString = ['a' num2str(rowEnd-numRows) ':' char('a' + colEnd -1)
num2str(rowEnd)]

And pass that along as the RANGE for XLSREAD.




On the other hand, if you want to use the act x interface... you would
have to wade through VBA documentation:


excelObj = actxserver ('Excel.Application');
fileObj = excelObj.Workbooks.Open('c:\tmp\t.xlsx');
sheetObj = excelObj.Worksheets.get('Item', 'Sheet1');

% Use http://pubs.logicalexpressions.com/pub0009/lpmarticle.asp?id=302
% to create VB code from excel macros. A good starting point before trying
% it out in MATLAB.

% Resulted in using this for last row/col. Works for non square data sheets
% Had to search the VB doc 'XlCellType Enumeration' to find that
% 'xlLastCell' mapped to integer value 11.
rowEnd = sheetObj.Cells.SpecialCells(11).Row
colEnd = sheetObj.Cells.SpecialCells(11).Column


% Now we need to use the 'Cells' concept to select the required range. I
% could not figure out what 'Cells' means in this context: (VBA code)
%
http://spreadsheetpage.com/index.php/tip/referring_to_ranges_in_your_vba_code/
% Range(Cells(1,1), Cells(12,10))
%

% From what I have read, this ought to work (I have no idea where to hang
% the 'Cells' call off of. VBA assumes its part of the current sheet, so I
% would expect sheetObj.Cells to work... but yeah, it doesnt.
%
% % Select a range for the last 10 rows.
% rangeObj = sheetObj.Range(Cells(1,1), Cells(rowEnd-10,colEnd)
% % Obtain its value
% data = rangeObj.Value.
%

% Some more useful info:
%
http://spreadsheetpage.com/index.php/tip/referring_to_ranges_in_your_vba_code/
% Info on 'Offset'
%
http://www.excel-vba-easy.com/vba-programming-range-excel-vba.html#excel-vba-range-examples



delete(excelObj);

Subject: Reading part of excel spreadsheet

From: Ashish Uthama

Date: 7 Oct, 2010 18:42:00

Message: 5 of 6

Never mind the XLSREAD part, I didnt read your line of code ( a better
approach).

Subject: Reading part of excel spreadsheet

From: Øyvind

Date: 8 Oct, 2010 07:44:06

Message: 6 of 6

I guess I meant the VBA documentation, though it didn't really occur to me at first that this would be outside the Matlab documentation (even though that should be obvious). The idea was that having already read the sheetObj into Matlab it would be faster and more elegant to use this than to reload the whole file using xlsread(filename). The xlsread approach will serve.

Thanks again.

Tags for 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