problem with xlsread:Bizarre results

Question

Sabbas 2012-7-4

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/42710-problem-with-xlsread-bizarre-results

Dear all,

I have an excel file that contains 3 sheets

when I look at each of these sheets  inside the excel file they all have the same number of rows( 245)  but when I use the command

[num1 ,txt1 ,raw1 ] = xlsread(xlfilename,1);

[num2 ,txt2 ,raw2 ] = xlsread(xlfilename,2);

[num3 ,txt3 ,raw3 ] = xlsread(xlfilename,3);

I get that raw1 has 266 rows

raw2 has 267 rows

raw3 has 279 rows

I also noticed that inside the workspace matlab adds arbitrarily different number of rows of NaN at the end of the raw1 raw2 raw3

Bear also in mind that “raw1” “raw2” “raw3” contains string numbers and empty cells

Unfortunately it is not convenient for my purposes to use “num1““num1“ “num1“

Thank you

4 个评论
显示 2更早的评论隐藏 2更早的评论

Tom 2012-7-4

在 MATLAB Online 中打开

xlsread(filename,-1)

Lets you select the data by going into the spreadsheet.

Sabbas 2012-7-4

编辑：Sabbas 2012-7-4

I tried but does not help because then I have to select the area I am interested in. But my excel file contains39 sheets. So it does not make sense

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Mark Whirdy 2012-7-4

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/42710-problem-with-xlsread-bizarre-results#answer_52568

在 MATLAB Online 中打开

Hi Sabbas I think its maybe impossible to debug this from here - all I can suggest is that the rows below are not actually empty but contain some character somewhere, putting a breakpoint in xlsread and stepping through should clarify this.

In general though, I think that you shouldn't be using xlsread at all (especially when you are running several of them in quick succession) as it instantiates an excel COM application object with each run. Use the Activex object directly and capture the data by invoking the VBA-type properties and methods of the excel COM Object, its much faster and you have more visibility on what is happening.

Below is a code snippet that you can adapt:

All the best,

Mark Whirdy

xlApp = actxserver('Excel.Application');
xlApp.Visible = 1;
xlWorkbook = xlApp.workbooks.Open(fullfile(xlFilePath,xlFileName),0,true);
xlSheets = xlWorkbook.Sheets;
xlSheetNamesArray = cell(xlSheets.Count,1); 
for i = 1:xlSheets.Count
    xlSheetNamesArray{i} = xlSheets.Item(i).Name; % sheet-order is not guaranteed so must build array
end
[~,idx] = ismember('Price',xlSheetNamesArray);
xlSheets.Item(idx).Activate
xlActiveSheet = xlWorkbook.ActiveSheet;
xlCurrRange = xlActiveSheet.Range('C4:C33');
priceVector = xlCurrRange.Value2;
priceVector = cell2mat(priceVector);
[~,idx] = ismember('Portfolios',xlSheetNamesArray);
xlSheets.Item(idx).Activate
xlActiveSheet = xlWorkbook.ActiveSheet;
xlCurrRange = xlActiveSheet.Range('B4:B22');
isinVector = xlCurrRange.Value2;
xlCurrRange = xlActiveSheet.Range('C3:G3');
ptfNumVector = cell2mat(xlCurrRange.Value2)';
xlCurrRange = xlActiveSheet.Range('C4:G22');
dataMatrix = xlCurrRange.Value2;
isnanMatrixMask = strcmp(dataMatrix,'ActiveX VT_ERROR: '); % handle missing data - assume as no position
dataMatrix(isnanMatrixMask) = {0};
dataMatrix = cell2mat(dataMatrix);

4 个评论
显示 2更早的评论隐藏 2更早的评论

Mark Whirdy 2012-7-5

在 MATLAB Online 中打开

Hi Sabbas

I’m not sure I understand. By “I have to incert cells like B4:B22.” are you saying that you are reading from excel into Matlab and don’t want to specify the exlce range reference bounds explicitly but want Matlab/excel to “know” what the relevant (excel cells containing filled values) range of the spreadsheet is in advance to avoid reading in the entire sheet as a cell-array?

If so, then you may as one solution use the “UsedRange” property of the Sheet object as per http://msdn.microsoft.com/en-us/library/aa215923(v=office.11).aspx This is what the xlsread function itself does.

DataRange = xlApp.ActiveSheet.UsedRange;
DataRange.Value;

However, some funky behaviour around this UsedRange excel property (or in cells adjacent to the intended used-range) may be what was originally causing the 267, 279 arbitrary length problem in the first place. You should be able to see if those extra cells are really empty by validations such as cellfun(@isempty,raw(:,1)) and cellfun(@isnumeric,raw(:,1)) and maybe spot the problem from there.

Another useful VBA property you can invoke in Matlab is the “xlToRight” and “xldown” as per http://www.mathworks.co.uk/support/solutions/en/data/1-3QI7UR/index.html?solution=1-3QI7UR You can identify a range of filled cell values in excel this way also.

Honestly, I don’t see the problem with specifying the range-bounds explicitly though as you should have some knowledge of this at the start, and then be able to remove empty/useless rows from the bottom and side of the cell-array (e.g. A1:BA1000) as F. is saying below.

If I have misunderstood what you’re saying, please expand in as much detail & clarity as possible on your previous point.

P.S. forget Matlab for a minute, go into excel to cell A1 (or whatever the upper-rightmost filled cell is) in each sheet and hit Ctrl+Shift+DownCursor – does the active range extend beyond what appears to be visibly filled? If so, the cells are not empty.

P.P.S. If you still have a problem after this please email the xls to mark.whirdy@gmail.com

Best, Mark

Mark Whirdy 2012-7-5

typo: " upper-rightmost " --> " upper-leftmost "

Sabbas 2012-7-5

thanks Mark!

请先登录，再进行评论。

Answer 2

F. 2012-7-4

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/42710-problem-with-xlsread-bizarre-results#answer_52570

在 MATLAB Online 中打开

Hello,

I think the problem is with Excel.

I think you should have at a moment more than 245 lines, and you should have deleted them. You don't see these old lines in excel, but the application remains it. When you are using xlsread, you import these empty lines. You can see this with raw datas.

When you want import data from excel, 2 solutions:

First, in excel you select all lines ( and after all columns ) after your data and you remove them (not only delete)
!the other one, with raw data you can define lines and columns with no data or NaN data, and you delete them :

[ N, T, R ] = xlsread( ‘toto.xls’ );
Tmp = cellfun( @isnan, R, ‘UniformOuput’, true);
R( all( Tmp,1), : ) = [] ;
R( : , all( Tmp,2) ) = [] ;

Or something like this (I’m sorry but I don’t have Matlab on my Pc today) (it’s the same thing with “empty” )

8 个评论
显示 6更早的评论隐藏 6更早的评论

Sabbas 2012-7-5

Thanks F!

Mark Whirdy 2012-7-9

cell2mat

请先登录，再进行评论。

problem with xlsread:Bizarre results

4 个评论
显示 2更早的评论隐藏 2更早的评论

采纳的回答

4 个评论
显示 2更早的评论隐藏 2更早的评论

更多回答（1 个）

8 个评论
显示 6更早的评论隐藏 6更早的评论

另请参阅

类别

标签

Community Treasure Hunt

problem with xlsread:Bizarre results

4 个评论 显示 2更早的评论隐藏 2更早的评论

采纳的回答

4 个评论 显示 2更早的评论隐藏 2更早的评论

更多回答（1 个）

8 个评论 显示 6更早的评论隐藏 6更早的评论

另请参阅

类别

标签

Community Treasure Hunt

4 个评论
显示 2更早的评论隐藏 2更早的评论

4 个评论
显示 2更早的评论隐藏 2更早的评论

8 个评论
显示 6更早的评论隐藏 6更早的评论