How to find the first empty row of a .xlsx-file?
26 次查看(过去 30 天)
显示 更早的评论
I have a script in which I use writetable() to write data to a row in an excel spreadsheet. I need to get the row number of the next empty row in the file, but I haven't found a solution on this site.
Kind regards
Mikkel
1 个评论
dpb
2018-12-3
That's an Excel Q? more than Matlab; one syntax that works as macro is
unusedRow = Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 0).Row
Use COM to implement via ML.
Alternatively, read the data and find the size of the array and use that or keep a running counter and define a location in the spreadsheet that stores the information so can just retrieve it.
There is no magic query built into the ML interface, however.
采纳的回答
Guillaume
2018-12-3
编辑:Guillaume
2018-12-3
So, in matlab, the unusedRow = Cells.SpecialCells(xlCellTypeLastCell) that dpb mentions, would be implemented as:
function lastrow = findLastRow(excelfile, sheetname)
%find last used row of an exel workbook
%excelfile: FULL PATH of excel workbook. Required, char vector or scalar string
%sheetname: sheet name or number. optional (default is 1st sheet). char vector, scalar string or scalar numeric
%TODO: add input validation
if nargin < 2
sheetname = 1;
end
excel = actxserver('Excel.Application'); %start excel
cleanup = onCleanup(@() excel.Quit); %make sure to close excel even if an error occurs. Will also close the workbook if it is open since it never gets modified
workbook = excel.Workbooks.Open(excelfile); %open workbook
worksheet = workbook.Worksheets.Item(sheetname); %get worksheet
lastrow = worksheet.Cells.SpecialCells('xlCellTypeLastCell').Row; %get last used row
workbook.Close;
end
Code untested. If there are bugs in the code, blame me. If Cells.SpecialCells(xlCellTypeLastCell) doesn't work, blame dpb.
10 个评论
Ajinkya Bankar
2019-11-2
编辑:Ajinkya Bankar
2019-11-2
Hello Sir, I am trying to use this function to find last row in excel file but I am getting error as shown in the screenshot. I do have A.xslx file in the same directory. Can you please help to find the error? Thank you.
![](https://www.mathworks.com/matlabcentral/answers/uploaded_files/246138/image.png)
Guillaume
2019-11-2
You have to give the full path of the file. It's excel opening the file and excel doesn't know what matlab's current directory is.
更多回答(0 个)
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!