The behavior I observe in R2023b is that missing rows are included. It also seems to be the same when run here online:
readcell('tst.xlsx')
How to read all rows of an Excel with missing rows included?
19 ビュー (過去 30 日間)
古いコメントを表示
My goal is to read all contents of an Excel file as cells using the function "readcell". However, every time when I used this function, it automatically eliminates any rows with missing contents above. How do I force this function to read the entire content of an Excel file with the missing rows above included?
4 件のコメント
Image Analyst
2025 年 3 月 9 日
編集済み: Image Analyst
2025 年 3 月 9 日
If you have any more questions, then attach your data and code to read it in with the paperclip icon after you read this:
採用された回答
dpb
2025 年 3 月 9 日
編集済み: dpb
2025 年 3 月 9 日
Actually, I had forgotten until the error message above reminded me...there is one way without the import options struct, but it uses an undocumented argument list named parameter pair...so it's unlikely one will think of trying it.
fn='test2.xlsx';
c=readcell(fn,'DataRange','A1')
The documentation still has only 'Range' as the named parameter and indicates that should control where the data are read from, but as we've seen here, it doesn't.
I haven't tried but I presume that maybe one can specify any fieldname that exists in the appropriate type of import object struct; note that it is 'DataRange' there.
I don't recall if I've submitted this as bug/enhancement/documentation error or not...it really should be on their hit list of fixes one way or another.
I just checked to see if newer documentation showed anything different, but it still has the same descripation at readcell
その他の回答 (3 件)
dpb
2025 年 3 月 9 日
編集済み: dpb
2025 年 3 月 9 日
You can see if it has been fixed (I submitted bug/enhancement report quite some time ago), but I'm still at R2021b, but
readcell('tst.xlsx','range','A1')
should work, but it didn't as of R2021b
The simplest workaround uses an import data options object--
opt=detetctimportoptions('test.xlsx'); % create the base default import struct
opt.DataRange='A1'; % tell it to read data from the start
readcell('test.xlsx',opt) % read using the import options
Locally, R2021b
>> readcell('test.xlsx','range','A1')
ans =
3×3 cell array
{[ 1.00]} {[ 2.00]} {[ 3.00]}
{1×1 missing} {1×1 missing} {1×1 missing}
{[ 4.00]} {[ 5.00]} {[ 6.00]}
>> opt=detectImportOptions('test.xlsx')
opt =
SpreadsheetImportOptions with properties:
Sheet Properties:
Sheet: ''
Replacement Properties:
MissingRule: 'fill'
ImportErrorRule: 'fill'
Variable Import Properties: Set types by name using setvartype
VariableNames: {'Var1', 'Var2', 'Var3'}
VariableTypes: {'double', 'double', 'double'}
SelectedVariableNames: {'Var1', 'Var2', 'Var3'}
VariableOptions: Show all 3 VariableOptions
Access VariableOptions sub-properties using setvaropts/getvaropts
VariableNamingRule: 'modify'
Range Properties:
DataRange: 'A2' (Start Cell)
VariableNamesRange: ''
RowNamesRange: ''
VariableUnitsRange: ''
VariableDescriptionsRange: ''
To display a preview of the table, use preview
>> opt.DataRange='A1';
>> readcell('test.xlsx',opt)
ans =
4×3 cell array
{1×1 missing} {1×1 missing} {1×1 missing}
{[ 1.00]} {[ 2.00]} {[ 3.00]}
{1×1 missing} {1×1 missing} {1×1 missing}
{[ 4.00]} {[ 5.00]} {[ 6.00]}
>>
1 件のコメント
dpb
2025 年 3 月 9 日
readcell('test2.xlsx') % default fails to read blank line
readcell('test2.xlsx','range','A1') % has starting range been fixed yet???
No, it hasn't.
readcell('test2.xlsx','numheaderlines',0)
opt=detectImportOptions('test2.xlsx')
opt.DataRange='A1';
readcell('test2.xlsx',opt)
readcell('test2.xlsx','numheaderlines',0,'range','A1')
Sulaymon Eshkabilov
2025 年 3 月 9 日
You can consider specifying which sheet and which range of cells to be read/imported using readcell() or readtable():
% READCELL()
D1 = readcell("tst.xlsx", Sheet=1, Range = 'A1:C5')
% READTABLE()
D2 = readtable("tst.xlsx", Sheet=1, Range = 'A1:C5')
4 件のコメント
Sulaymon Eshkabilov
2025 年 3 月 9 日
移動済み: Matt J
2025 年 3 月 9 日
Note that I am using this online MATLAB.
dpb
2025 年 3 月 9 日
No, it doesn't read the initial opening empty lines per OP's request in any of the above, the metainfo data is actually the fourth line in the Excel file, the three blank lines preceding it have not been returned.
See the <examples I added as a comment to my earlier Answer> to see the actual content of the beginning of the file.
Matt J
2025 年 3 月 9 日
編集済み: Matt J
2025 年 3 月 9 日
ActiveX can be used to determine the last row/column of the worksheet: Then, you can force readcell to read in a larger range with the Range option:
[lastRow,lastCol] = getLastCell('TST.xlsx')
lastRow =
3
lastCol =
4
readcell('TST.xlsx',Range=[1,1,lastRow,lastCol])
ans =
3×4 cell array
{[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]}
{[ 1]} {[ 2]} {[ 3]} {[ 50]}
{[ 4]} {[ 5]} {[ 6]} {[ 70]}
function [lastRow,lastCol] = getLastCell(filename)
sheet = 1; % Change if needed
% Open Excel via ActiveX
excel = actxserver('Excel.Application');
wb = excel.Workbooks.Open(fullfile(pwd, filename));
ws = wb.Sheets.Item(sheet);
% Get the UsedRange
range = ws.UsedRange;
% Get the first used row/column index
firstRow = range.Row; % First used row index
firstCol = range.Column; % First used column index
% Get number of rows and columns in the UsedRange
numRows = range.Rows.Count;
numCols = range.Columns.Count;
% Compute the last used row/column index
lastRow = firstRow + numRows - 1;
lastCol = firstCol + numCols - 1;
% Clean up
wb.Close(false);
excel.Quit();
delete(excel);
end
7 件のコメント
Matt J
2025 年 3 月 9 日
It's not a manipulation. The use case given by the OP was that the spreadsheet my contain blank initial lines.
参考
カテゴリ
Help Center および File Exchange で Spreadsheets についてさらに検索
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
Translated by 
また、以下のリストから Web サイトを選択することもできます。
南北アメリカ
- América Latina (Español)
- Canada (English)
- United States (English)
ヨーロッパ
- Belgium (English)
- Denmark (English)
- Deutschland (Deutsch)
- España (Español)
- Finland (English)
- France (Français)
- Ireland (English)
- Italia (Italiano)
- Luxembourg (English)
- Netherlands (English)
- Norway (English)
- Österreich (Deutsch)
- Portugal (English)
- Sweden (English)
- Switzerland
- United Kingdom(English)
アジア太平洋地域
- Australia (English)
- India (English)
- New Zealand (English)
- 中国
- 日本Japanese (日本語)
- 한국Korean (한국어)