After reading this article, you’ll know a consistent way of identifying the row number of a cell in an Excel file.
The Problem
Over time, I figured out a robust way to find the row number of a specific cell in an Excel file in order to write something at that precise position. “Why was it time-consuming to figure this out?” you ask. Well, let’s dive in.
Assume you have the following Excel file named emptyRows.xlsx:
We want to figure out the row number of the cell containing “cellToFindOnRow7,” which is located—predictably—in row 7.
Let’s assume that you can’t modify it (e.g. because it’s the way your industry formats the content in this particular type of Excel file, or because it’s extracted from a database under this format) and that the empty rows at the beginning can’t be removed.
The most intuitive way to solve this would by using xlsread to extract the data from your Excel file and simply do a for loop looking for your desired content while iterating a variable to know the number of iterations of your loop before attaining the value of your cell. This iterative variable would be the row number where your value is. Here’s the code to do this:
fileName = 'emptyRows.xlsx'; [num, txt, raw] = xlsread(fileName) cellContent = 'cellToFindOnRow7'; for i = 1:size(txt, 1) if strcmp(txt{i, 1}, cellContent) break end end num txt raw rowNumber = i
This would have worked if the Excel file did not contain empty rows at the beginning. Instead, here’s the result:
As you can see, not only is it the wrong number, but the output arguments num, txt, and raw of the MATLAB function xlsread won’t help us figure out the number of empty rows in the Excel file. In this case, xlsread optimizes the content and gets rid of the empty rows (7 rows – 2 empty rows = a wrong rowNumber of 5), which makes our previous code obsolete.
One way to pass by that issue would be to add the number of rows that the xlsread MATLAB function got rid of, but there is no way to know this information. It seems that xlsread is not the solution here.
The solution: actxserver
The solution I found was to use actxserver. Basically, this involves using visual basic via MATLAB (sort of, I won’t bore you with the details). Here is the actual function:
function rowNumber = getRowNumberXlsx(fileName, sheetNumber, fullPath, columnNumber, value) Excel = actxserver('Excel.Application'); Excel.Visible = false; Workbook = Excel.Workbooks.Open([fullPath '\' fileName]); % activate the desired sheet eSheets = Excel.ActiveWorkBook.Sheets; eSheet = eSheets.get('Item', sheetNumber); eSheet.Activate; % find the row at the right value correspondingCell = Excel.ActiveSheet.Columns(columnNumber).Find(value); rowNumber = correspondingCell.Row; Workbook.Close; Excel.Quit; delete(Excel); return
Let’s explain the arguments of this function:
- fileName: name of the Excel file
- sheetNumber: number of the sheet where the value is located
- fullPath: path to your Excel file
- columnNumber: number of the column to scan for the value
- value: content of the cell of which we want the row number
Let’s try it on the previous example:
fileName = 'emptyRows.xlsx'; sheetNumber = 1; fullPath = pwd; columnNumber = 1; value = 'cellToFindOnRow7' rowNumber = getRowNumberXlsx(fileName, sheetNumber, fullPath, columnNumber, value)
We get
It works!
If you want to see an example of getting the row number of an Excel file to write data at the end of it, see Excel File and Row Number: Add a Row at the End of an Excel File from MATLAB (2/2).