Empty Cell not included in Cells collection in For Each loop

Hi,

I am processing rows of data into objects using a foreach loop like:

foreach (WorksheetCell theWorksheetCell in theWorksheetRow.Cells)
{
object theValue = theWorksheet.Rows[theRowCounter].Cells[theCellCounter].Value;

if (theValue != null)
{
try
{
theDataRow[theCellCounter] = theValue;
}
catch { /* blank catch because we are skipping bad rows */ }
}

}

So if we have 6 cells and cell 3 is empty then theWorksheetRow.Cells only has 5 cells. If we put a value then it has 6. Is this how it is supposed to function?

Thanks,

M.

Hi,

   Yes the way you are experiencing it is the way it is intended to work.  We only add the Rows and Cells to the collection when they have a value. So if you are doing a foreach loop through them, you will skip over blank rows and cells. Conversely, when you access the Rows and Cells via the index, if one does not already exist with a RowIndex\ColumnIndex matching that ones given, it will add the Row or Cell to the appropriate collections, assigning it the RowIndex and ColumnIndex values it was used to reference it.

Your snippet seems to have a mix of loop types. You are doing a foreach but then not using theWorksheetCell that it puts out, you are then referencing the Rows\Cells by an index that you don't show how they are initiated or advanced. So it's hard to follow exactly your intent.

Below is a quick snippet that should demonstrate how it works.

Let me know if this helps,

using Infragistics.Documents.Excel;
using System.Diagnostics;

//..

private void Form1_Load(object sender, EventArgs e)
{
 Workbook wb = new Workbook();
 wb.Worksheets.Add("Sheet1");
 var ws = wb.Worksheets[0];

 ws.GetCell("C3").Value = 1;
 OutputWorksheetState(ws);

 ws.Rows[2].Cells[2].Value = 1.1;
 OutputWorksheetState(ws);

 ws.Rows[6].Cells[2].Value = 2;
 OutputWorksheetState(ws);

 ws.GetCell("C7").Value = 2.1;
 OutputWorksheetState(ws);

 ws.GetCell("C33").Value = 3;
 ws.GetCell("C11").Value = 4;
 ws.GetCell("A3").Value = 5;
 ws.GetCell("B7").Value = 6;
 ws.GetCell("H33").Value = 7;
 ws.GetCell("Z11").Value = 8;

 OutputWorksheetState(ws);
}

private void OutputWorksheetState(Worksheet ws)
{
 int eRowIndex = 0;
 int eCellIndex = 0;

 Debug.WriteLine("--------------");
 foreach (WorksheetRow row in ws.Rows)
 {
  eCellIndex = 0;
  foreach (WorksheetCell cell in row.Cells)
  {
   Debug.WriteLine("Cell e[{0},{1}] - a[{2}, {3}] = {4}", eRowIndex, eCellIndex, cell.RowIndex, cell.ColumnIndex, cell.Value);
   eCellIndex++;
  }
  eRowIndex++;
 }
}