Count used cells in row using Excel Interop

Question

I would like to get index of last used cell in each row in worksheet. I can get last used column in whole sheet, but I have table which has different used cells in every row, like this:

I tried this:

var excelApp = new Excel.Application();
var workBook = excelApp.Workbooks.Open(excelFilePath);
Excel._Worksheet worksheet = workBook.Worksheets[worksheetName];

int rowsCount = worksheet.UsedRange.Rows.Count;
for (int i = 1; i <= rowsCount; i++)
{               
    Excel.Range range = worksheet.Rows[i];
    int lastColumn = range.Columns.Count;
    Console.WriteLine(lastColumn);         
}

and I expect output:

3
5
2
7

but actual output is:

16384
16384
16384
16384

I use Excel Interop library. Any suggestion would be appreciated. Thanks!

Answer 1

You can use something like this:

var usedRange = worksheet.UsedRange;
int startRow = usedRange.Row;
int endRow = startRow + usedRange.Rows.Count - 1;
int startColumn = usedRange.Column;
int endColumn = startColumn + usedRange.Columns.Count - 1;
for (int row = startRow; row <= endRow; row++)
{
    Excel.Range lastCell = worksheet.Cells[row, endColumn];
    if (lastCell.Value2 == null)
        lastCell = lastCell.End[Excel.XlDirection.xlToLeft];
    var lastColumn = lastCell.Column;
    Console.WriteLine($"{row}: {lastColumn}");
}

Basically the trick is to get the last cell in a row and if it's empty, use Range.End property(or method?) with XlDirection.xlToLeft (the empty check is needed because seems like the starting cell is excluded from the End call).

Answer 2

The following shows logic to get one row's last used cell. It does not cycle through all rows but you should be able to work this in to a for iterator.

using System;
using Excel = Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
using System.IO;

namespace Example 
{
    public class ExcelUsed
    {
        /// <summary>
        /// Get last used column for a row
        /// </summary>
        /// <param name="fileName">Excel file to read</param>
        /// <param name="sheetName">Sheet to work on</param>
        /// <param name="row">Row in sheet to get last used column</param>
        /// <returns></returns>
        public int LastColumnForRow(string fileName, string sheetName, int row)
        {
            int lastColumn = -1;

            if (File.Exists(fileName))
            {
                Excel.Application xlApp = null;
                Excel.Workbooks xlWorkBooks = null;
                Excel.Workbook xlWorkBook = null;
                Excel.Worksheet xlWorkSheet = null;
                Excel.Sheets xlWorkSheets = null;

                xlApp = new Excel.Application();
                xlApp.DisplayAlerts = false;

                xlWorkBooks = xlApp.Workbooks;
                xlWorkBook = xlWorkBooks.Open(fileName);

                xlApp.Visible = false;

                xlWorkSheets = xlWorkBook.Sheets;

                for (int x = 1; x <= xlWorkSheets.Count; x++)
                {
                    xlWorkSheet = (Excel.Worksheet)xlWorkSheets[x];


                    if (xlWorkSheet.Name == sheetName)
                    {
                        Excel.Range xlCells = null;
                        xlCells = xlWorkSheet.Cells;

                        Excel.Range workRange = xlCells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell);
                        Excel.Range xlColumns = xlWorkSheet.Columns;

                        int count = xlColumns.Count;

                        Marshal.FinalReleaseComObject(xlColumns);
                        xlColumns = null;

                        Excel.Range xlLastRange = (Excel.Range)xlWorkSheet.Cells[row, count];
                        Excel.Range xlDirRange = xlLastRange.End[Excel.XlDirection.xlToLeft];

                        Marshal.FinalReleaseComObject(xlLastRange);
                        xlLastRange = null;

                        lastColumn = xlDirRange.Column;
                        Marshal.FinalReleaseComObject(xlDirRange);
                        xlDirRange = null;

                        Marshal.FinalReleaseComObject(workRange);
                        workRange = null;

                        Marshal.FinalReleaseComObject(xlCells);
                        xlCells = null;

                        break;
                    }

                    Marshal.FinalReleaseComObject(xlWorkSheet);
                    xlWorkSheet = null;

                }

                xlWorkBook.Close();
                xlApp.UserControl = true;
                xlApp.Quit();

                Release(xlWorkSheets);
                Release(xlWorkSheet);
                Release(xlWorkBook);
                Release(xlWorkBooks);
                Release(xlApp);

                return lastColumn;

            }
            else
            {
                throw new Exception("'" + fileName + "' not found.");
            }
        }
         /// <summary>
        /// 
        /// </summary>
        public void CallGarbageCollector()
        {
            GC.Collect();
            GC.WaitForPendingFinalizers();
        }
        /// <summary>
        /// Method to release object used in Excel operations
        /// </summary>
        /// <param name="sender"></param>
        private void Release(object sender)
        {
            try
            {
                if (sender != null)
                {
                    Marshal.ReleaseComObject(sender);
                    sender = null;
                }
            }
            catch (Exception)
            {
                sender = null;
            }
        }
    }
}

Example

int row = 1;
int results = eu.LastColumnForRow(fileName, sheetName,row);
MessageBox.Show($"Row {row}: {results}");

Try it out here https://code.msdn.microsoft.com/Excel-get-last-row-and-fe764cfc