How to find the first empty row of a .xlsx-file?

18 次查看（过去 30 天）

Mikkel Eskildsen 2018-12-3

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/433506-how-to-find-the-first-empty-row-of-a-xlsx-file

评论： Guillaume 2019-11-2

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 个评论
显示 -1更早的评论隐藏 -1更早的评论

dpb 2018-12-3

在 MATLAB Online 中打开

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

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/433506-how-to-find-the-first-empty-row-of-a-xlsx-file#answer_350284

编辑：Guillaume 2018-12-3

在 MATLAB Online 中打开

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 个评论
显示 8更早的评论隐藏 8更早的评论

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.