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

excelMATLAB

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

Best Answer

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.

Related Solutions

MATLAB: How to rename a sheet in Excel using the COM interface in MATLAB

You can rename an Excel sheet using the "Name" property of a sheet object. For example the following renames the first sheet of an Excel file:

filename = 'C:\SomeExcelFile.xls';
% Open Excel Automation server
Excel = actxserver('Excel.Application');
% Make Excel visible
Excel.Visible=1;
% Open Excel file
Workbook = Excel.Workbooks.Open(filename);
% Get the list of sheets in the workbook
Sheets = Excel.ActiveWorkbook.Sheets;
% Rename the first sheet
Sheets.Item(1).Name = 'This is sheet 1';

The following code then saves the Workbook, quits Excel and removes the COM server:

% Save the file
Workbook.Save();
% Quit Excel, remove the COM server and delete the related objects
Excel.Quit();
Excel.delete();
clear Excel;
clear Workbook;
clear Sheets;

MATLAB: How to use xltoRight, xlDown and similar utilities from MATLAB

The following example uses the Excel functions xltoRight and xlDown to select a contiguous block of data. For more information on how these functions work, consult your Microsoft documentation.

Note that there must be a block of data around the specified cell B9; otherwise, these Excel functions will select the entire worksheet, as detailed in the Microsoft documentation.

e = actxserver ('Excel.Application') % open Activex server
eWorkbooks = e.Workbooks
exlFile = eWorkbooks.Open('C:\MyFile.xls')%open file
exlSheet1 = exlFile.Sheets.Item('Tabelle1')%Choose Sheet
lastCol = exlSheet1.Range('B9').End('xltoRight').Column % find last column
% Convert column number to Excel column code
if (lastCol <= 26)
      lastColchar=char(96+lastCol)
else
      lastColchar = [char(96+floor((lastCol-1)/26)) char(97+rem(lastCol-1,26))]
end
lastrow = exlSheet1.Range('B9').End('xldown').Row % find last row
myrange=[lastColchar num2str(lastrow)]
data_ran=['b9:' myrange]
data=exlSheet1.Range(data_ran).Value% These are the  requested data

Best Answer

Related Solutions

MATLAB: How to rename a sheet in Excel using the COM interface in MATLAB

MATLAB: How to use xltoRight, xlDown and similar utilities from MATLAB

Related Question