GemBox - For loop for rows and cols?

Question

I have a question. Is there a way that I could go through all the cols/rows in a spreadsheet using a for loop?? Right now I am using foreach loops like this in my code: (You can just ignore what's going on inside).

foreach (ExcelRow row in w1.Rows)
{
    foreach (ExcelCell cell in row.AllocatedCells)
    {

        Console.Write("row: {0}", globalVar.iRowActual);
        if (globalVar.iRowActual > 1)
        {
             cellValue = SafeCellValue(cell);
             Console.WriteLine("value is: {0}", cellValue);
        }


    }
    globalVar.iRowActual++;
}

The problem is that I would like to assign the value of each cell to a new variable and pass it to another method. I would like to use for loops for this and I know I can use CalculateMaxUsedColumns as the limit for the cols but is there a property like that, that I could use for the rows?!

This is what I would like to do:

 int columnCount = ws.CalculateMaxUsedColumns();
 int rowCount = ws.CalculateMaxUsedRows(); ------> PART I NEED HELP WITH
 for(int i=0; i <columnCount; i++){
     for(int j = 0; j<rowCount; j++){
           .....
     }
 }

Any kind of help would be greatly appreciated. Thanks!!!

Answer 1

Here is a way you can iterate in GemBox.Spreadsheet through all the columns / rows in a spreadsheet using a for loop. Go through the CellRange which is returned by ExcelWorksheet.GetUsedCellRange method.

ExcelFile workbook = ExcelFile.Load("Sample.xlsx");
ExcelWorksheet worksheet = workbook.Worksheets[0];

CellRange range = worksheet.GetUsedCellRange(true);
for (int r = range.FirstRowIndex; r <= range.LastRowIndex; r++)
{
    for (int c = range.FirstColumnIndex; c <= range.LastColumnIndex; c++)
    {
        ExcelCell cell = range[r - range.FirstRowIndex, c - range.FirstColumnIndex];

        string cellName = CellRange.RowColumnToPosition(r, c);
        string cellRow = ExcelRowCollection.RowIndexToName(r);
        string cellColumn = ExcelColumnCollection.ColumnIndexToName(c);

        Console.WriteLine(string.Format("Cell name: {1}{0}Cell row: {2}{0}Cell column: {3}{0}Cell value: {4}{0}",
            Environment.NewLine, cellName, cellRow, cellColumn, (cell.Value) ?? "Empty"));
    }
}

EDIT

In newer versions there are some additional APIs which can simplify this. For instance, you can now use foreach and still retreive the row and column indexes with ExcelCell.Row.Index and ExcelCell.Column.Index and you can retreive the names without using those static methods (without RowColumnToPosition, RowIndexToName and ColumnIndexToName).

ExcelFile workbook = ExcelFile.Load("Sample.xlsx");
ExcelWorksheet worksheet = workbook.Worksheets[0];

foreach (ExcelRow row in worksheet.Rows)
{
    foreach (ExcelCell cell in row.AllocatedCells)
    {
        Console.WriteLine($"Cell value:   {cell.Value ?? "Empty"}");
        Console.WriteLine($"Cell name:    {cell.Name}");
        Console.WriteLine($"Row index:    {cell.Row.Index}");
        Console.WriteLine($"Row name:     {cell.Row.Name}");
        Console.WriteLine($"Column index: {cell.Column.Index}");
        Console.WriteLine($"Column name:  {cell.Column.Name}");
        Console.WriteLine();
    }
}

Also, here are two other ways how you can iterate through sheet cells in for loop.

1) Use ExcelWorksheets.Rows.Count and ExcelWorksheets.CalculateMaxUsedColumns() to get the last used row and column.

ExcelFile workbook = ExcelFile.Load("Sample.xlsx");
ExcelWorksheet worksheet = workbook.Worksheets[0];

int rowCount = worksheet.Rows.Count;
int columnCount = worksheet.CalculateMaxUsedColumns();

for (int r = 0; r < rowCount; r++)
{
    for (int c = 0; c < columnCount; c++)
    {
        ExcelCell cell = worksheet.Cells[r, c];

        Console.WriteLine($"Cell value:  {cell.Value ?? "Empty"}");
        Console.WriteLine($"Cell name:   {cell.Name}");
        Console.WriteLine($"Row name:    {cell.Row.Name}");
        Console.WriteLine($"Column name: {cell.Column.Name}");
        Console.WriteLine();
    }
}

If you have a non-uniform spreadsheet in which rows have different column count (for instance, first row has 10 cells, second row has 100 cells, etc.), then you could use the following change in order to avoid iterating through non-allocated cells:

int rowCount = worksheet.Rows.Count;

for (int r = 0; r < rowCount; r++)
{
    ExcelRow row = worksheet.Rows[r];
    int columnCount = row.AllocatedCells.Count;

    for (int c = 0; c < columnCount; c++)
    {
        ExcelCell cell = row.Cells[c];

        // ...
    }
}

2) Use CellRange.GetReadEnumerator method, it iterates through only already allocated cells in the range.

ExcelFile workbook = ExcelFile.Load("Sample.xlsx");
ExcelWorksheet worksheet = workbook.Worksheets[0];

CellRangeEnumerator enumerator = worksheet.Cells.GetReadEnumerator();
while (enumerator.MoveNext())
{
    ExcelCell cell = enumerator.Current;

    Console.WriteLine($"Cell value:  {cell.Value ?? "Empty"}");
    Console.WriteLine($"Cell name:   {cell.Name}");
    Console.WriteLine($"Row name:    {cell.Row.Name}");
    Console.WriteLine($"Column name: {cell.Column.Name}");
    Console.WriteLine();
}