problem with xlsread:Bizarre results

6 次查看(过去 30 天)
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 个评论
Tom
Tom 2012-7-4
xlsread(filename,-1)
Lets you select the data by going into the spreadsheet.
Sabbas
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

请先登录,再进行评论。

采纳的回答

Mark Whirdy
Mark Whirdy 2012-7-4
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 个评论
Mark Whirdy
Mark Whirdy 2012-7-5
typo: " upper-rightmost " --> " upper-leftmost "

请先登录,再进行评论。

更多回答(1 个)

F.
F. 2012-7-4
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 个评论

请先登录,再进行评论。

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by