Row Looping in Excel

Question

How to loop a row in excel using c#?

Looping like using datatable

foreach(DataRow _dr in datatable.row)
{
  //data
}

I'm trying and got this one. But it loops per column

foreach (Excel.Range r in usedRange)
{
   // check condition:
   try
   {
      if (Convert.ToInt32(r.Value2.ToString()) == 0)
      {
        // if match, delete and shift remaining cells up:
         r.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
        break;
      }
    }
    catch { }
}

Answer 1

My bad, I confused your code blocks.

Assuming you have a worksheet called sheet, try this:

foreach (var row in sheet.UsedRange.Rows)
{
    // either put your logic here, 
    // or look at columns if you prefer 
    /*
    foreach (var cell in row.Columns)
    {
       // do something with cells 
    } 
    /*
}

Answer 2

Further to my comments

When you loop through a range, it always loops left to right and not up to down (unless the range has only one column)

Let's say your excel sheet looks like this

TRIED AND TESTED

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;

Namespace WindowsFormsApplication4
{
    public partial class Form1 : Form
    {
        Public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            Microsoft.Office.Interop.Excel.Application xlexcel;
            Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
            Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
            Microsoft.Office.Interop.Excel.Range xlRange;

            object misValue = System.Reflection.Missing.Value;

            xlexcel = new Excel.Application();
            xlexcel.Visible = true;

            // Open a File
            xlWorkBook = xlexcel.Workbooks.Open("C:\\Book1.xlsx", 0, true, 5, "", "", true,
            Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);

            // Set Sheet 1 as the sheet you want to work with
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            xlRange = xlWorkSheet.UsedRange;

            for (int i = 1; i <= xlRange.Rows.Count; i++)
            {
                for (int j = 1; j <=  xlRange.Columns.Count; j++)
                {
                    if (xlexcel.WorksheetFunction.CountIf(xlRange.Cells[i, j], "0") > 0)
                    {
                        MessageBox.Show("Row " + i + " has 0");
                        break;
                    }
                }
            }

            //Once done close and quit Excel
            xlWorkBook.Close(false, misValue, misValue);
            xlexcel.Quit();

            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlexcel);
        }

        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Unable to release the Object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }
    }
}

This is what you get