How do i find the last used column in a row? C#

Question

I have an excel sheet that I am reading. it contains multiple rows (No headers) each row containing a different number of populated columns. i.e. row 1 has data in the first three columns. row 2 has data in the first 140 columns. row 3 has data in the first 32 columns. etc etc.

If I need to find out how many columns row 2 uses (How many contain data), how would I start.

I am teaching myself c sharp at the moment and am jargon illiterate, so please be as "Layman" as possible.

Any help would be greatly appreciated.

Thank you.

Edit ------------------------

Had a quick look through some bookmarks and have a rough idea of the code i used to get the row number containing the columns i need to count. (i cant test as i don't have VS installed at home. so dont take this as gospel, but it seems "approximately" correct, sorry.)

private static Excel.Application HCObjApp = null;
private static Excel.Workbook HCBook = null; 
private static Excel.Worksheet HCSheet = null;

XLApp = new Excel.Application();
HCBook = MyApp.Workbooks.Open(@"WBPATH\WB.exe");
HCSheet = HCBook.Sheets[1];
lastRow = MySheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row; 

Excel.Range range = HCSheet.Columns["A", Type.Missing];

currentFind = range.Find("Australia",HCSheet.Cells[lastrow, 1]);

from here i need to find the number of used columns in row CurrentFind.

--------------------Edit-------------------------------- Finally back in the office, This was the code i had to find the row number;

        EHObjApp.Visible = true;

        string ColumnHeader = ResourceDetails.ResourceHolder("Root") + ResourceDetails.ResourceHolder("ResourceFolder") + ResourceDetails.ResourceHolder("ColumnHeaders");
        HCBook = EHObjApp.Workbooks.Open(ColumnHeader);
        HCSheet = HCBook.Worksheets.get_Item(1);
        InputBook = EHObjApp.Workbooks.Open(ResourceDetails.ResourceHolder("Root") + @"\Sales\Output\Zoho_Upload\ZOHOSales.xlsx");
        InputSheet = InputBook.Worksheets.get_Item(1);
        long ExRownumber = HCSheet.Range["A:A"].Find("SalesAustralia").Row + 1;
        Range NewColumns = InputSheet.Range["A1:A" + InputSheet.UsedRange.Columns.Count];

With Many thanks to @Hambone i found the solution (more accurately i found 0.1% of the solution. Hambone found the rest);

            Excel.Range ur = HCSheet.UsedRange;
            Excel.Range r = HCSheet.Cells[ExRownumber, ur.Columns.Count +1];
            r = r.Columns.End[Excel.XlDirection.xlToLeft];
//            r = r.get_End(Excel.XlDirection.xlUp);
            long ExistingColumns = r.Column;


            MessageBox.Show(lastCol.ToString());

This will find the last used column in the row.

You guys have been an amazing help!

Thank you

----------Addition--------------- When finding the smaller column numbers, i.e. rows of 30 or 31 columns etc, this worked fine. I ran into an issue when trying to find the larger numbers, i.e. rows of 140 columns. Changing xlToLeft back to xlUp solved this issue.

Thought i would add this in for anyone that requires this solution in the future.

Answer 1

This my simplicity function get last used row and col with Excel Package

public int GetLastUsedRow(ExcelWorksheet sheet)
        {
            if (sheet.Dimension == null) { return 0; } // In case of a blank sheet
            var row = sheet.Dimension.End.Row;
            while (row >= 1)
            {
                var range = sheet.Cells[row, 1, row, sheet.Dimension.End.Column];
                if (range.Any(c => !string.IsNullOrEmpty(c.Text)))
                {
                    break;
                }
                row--;
            }
            return row;
        }
        public int GetLastUsedColumn(ExcelWorksheet sheet)
        {
            int count=0;
            int i = 1;
            while(sheet.Cells[1, i].Value != null)
            {
                count++;
                i++;
            }
            return count;
        }

Answer 2

I've seen this technique used in VBA to find the last row in a range, and it ports pretty nicely to C#:

Excel.Range ur = HCSheet.UsedRange;

Excel.Range r = HCSheet.Cells[2, ur.Columns.Count];
r = r.get_End(Excel.XlDirection.xlToLeft);

The value of r at this point will be the last populated cell in row 2. To get a row other than 2, you would simply change the first parameter in the .Cells indexer.

To prove it works, you can do something like this:

r.Value = "Gotcha";

To find the last column number, which I think was your question:

int lastCol = r.Column;

Answer 3

Excel automation is a ambitious endeavor for just starting with any .NET languages. Upfront, many examples on the web don't take into consideration properly releasing memory and will resort to using calls to the garbage collector when not needed. In the example code below the method UsedRowsColumns does not need any calls to the garbage collector simply because I followed what I call the two dot rule (which 99.99 percent of the time releases all object, but as you will see not always). You will note clicking on kevininstructor on that page for the two dot rule will fail, I changed it as I changed to kareninstructor.

• The classes shown below, I would test them and if they suit your needs place them into a class project so if you want to use this code in other projects you simply add a reference to those projects instead of copying code from project to project.
• The Console.WriteLine in button1 uses VS2015 syntax, if using a lower version then adjustments such as this would be needed Console.WriteLine("{0} {1}",....

Unlike UsedRowsColumns method, LastColumnForRow (which should answer your question) must do some extra work in that the garbage collector needs to be called but not in the same method so I setup the class to have a method CallGarbageCollector which after making the call to LastColumnForRow I call this to release the Range xlColumns which would not release even when doing the two dot rule.

Form code to test, button1 makes a call to get the last column for row 2.

using System;
using System.Windows.Forms;
using System.IO;

namespace UsedRowsCols
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        private string fileName = Path.Combine(
            AppDomain.CurrentDomain.BaseDirectory, "Demo.xlsx");
        private string sheetName = "Sheet1";
        /// <summary>
        /// Get last row and last column for a worksheet.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button1_Click(object sender, EventArgs e)
        {
            ExcelUsed eu = new ExcelUsed();
            ExcelLast results = eu.UsedRowsColumns(fileName, sheetName);
            // send results to Visual Studio Output window
            Console.WriteLine($"{results.Row} {results.Column}");
        }

        /// <summary>
        /// Get last used column for a specific row
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button2_Click(object sender, EventArgs e)
        {
            ExcelUsed eu = new ExcelUsed();
            try
            {
                int results = eu.LastColumnForRow(fileName, sheetName,2);
                // send results to Visual Studio Output window
                Console.WriteLine(results);
            }
            finally
            {
                eu.CallGarbageCollector();
            }
        }
    }
}

Class responsible for obtaining used column in button1 above and used rows and columns in button2 above.

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

namespace UsedRowsCols
{
    public class ExcelUsed
    {
        /// <summary>
        /// Get last used column for a row
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="sheetName"></param>
        /// <param name="row"></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>
        /// <param name="fileName">file to get information form</param>
        /// <param name="sheetName">valid sheet name to get last row and column</param>
        /// <returns>ExcelLast</returns>
        public ExcelLast UsedRowsColumns(string fileName, string sheetName)
        {

            int RowsUsed = -1;
            int ColsUsed = -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);

                        RowsUsed = workRange.Row;
                        ColsUsed = workRange.Column;

                        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 new ExcelLast() { Row = RowsUsed, Column = ColsUsed };

            }
            else
            {
                throw new Exception("'" + fileName + "' not found.");
            }
        }
        public void CallGarbageCollector()
        {
            GC.Collect();
            GC.WaitForPendingFinalizers();

        }
        private void Release(object sender)
        {
            try
            {
                if (sender != null)
                {
                    Marshal.ReleaseComObject(sender);
                    sender = null;
                }
            }
            catch (Exception)
            {
                sender = null;
            }
        }
    }
    public class ExcelLast
    {
        /// <summary>
        /// Last used row in specific sheet
        /// </summary>
        public int Row { get; set; }
        /// <summary>
        /// Last used column in specific sheet
        /// </summary>
        public int Column { get; set; }
    }
}