How to iterate all cells in a row EXCEPT the last one?-CodePudding

I have this bit of C# code, using the ClosedXML library to read an Excel spreadsheet:

using ClosedXML.Excel;
...

var workbook = new XLWorkbook(new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite));
var ws1 = workbook.Worksheet("SHEET A");
var rows = ws1.RangeUsed().RowsUsed();

// Now we parse the first sheet for Faction Data
foreach (var row in rows)
{
    int cellNum = 1;
    foreach (IXLCell cell in row.Cells())
    {
        if( cellNum > 1 )
        {
            try
            {
                System.Console.WriteLine( Int32.Parse( cell.Value.ToString() ) );
            }
            catch (FormatException e)
            {
                System.Console.WriteLine("ERROR!!!"   e);
            }
        }
        cellNum  ;
    }
}

The code opens an Excel spreadsheet as ReadOnly, goes to tab "SHEET A", then for each row, reads each cell in that row. Furthermore, note that after the cell in Column A is read, the code assumes it is reading an integer, and does the String-to-Integer data type conversion. This code will work great, assuming my Excel spreadsheet looks like this:

         Apples  Bananas  Oranges  Grapes
Store A    10       20      30       40  
Store B    15       20      35        5  
Store C     5       25      30       35

(Yes, I'm omitting the bit of code that skips reading the column names (Apples, Bananas, Oranges, Grapes)

So far, so good. But now suppose I want to add a NOTES column to the far right:

         Apples  Bananas  Oranges  Grapes  NOTES
Store A    10       20      30       40    
Store B    15       20      35        5    Store B customers want more grapes
Store C     5       25      30       35

This will break my code, because the code will attempt to translate whatever it finds in the new column ("Store B customers want more grapes") into an integer. No good!

So... how to essentially tell my code "in every row, translate the cell data into integers except the first column cell and the last column cell"? I can't have the code count the number of columns as it iterates, because I might add more fruit columns in the future. (e.g. Papayas, Melons, Plums, etc.)

One idea I had is basically a hack: Insert a blank column between the last fruit column and the NOTES column:

         Apples  Bananas  Oranges  Grapes  (*blank*)  NOTES
Store A    10       20      30       40    (*blank*)
Store B    15       20      35        5    (*blank*)  Store B customers want more grapes
Store C     5       25      30       35    (*blank*)

My hope that that the line of code that sends the iterator down the row...

foreach (IXLCell cell in row.Cells())

...would see the blank cell after Grapes and conclude the row was over. But that doesn't work.

So: Does anyone know how to iterate every cell in a row except the last one?

CodePudding user response：

What if you just had it skip any columns that weren't integer values?

Your code:

foreach (var row in rows)
{
    int cellNum = 1;
    foreach (IXLCell cell in row.Cells())
    {
        if( cellNum > 1 )
        {
            try
            {
                System.Console.WriteLine( Int32.Parse( cell.Value.ToString() ) );
            }
            catch (FormatException e)
            {
                System.Console.WriteLine("ERROR!!!"   e);
            }
        }
        cellNum  ;
    }
}

Making use of Int32.TryParse it could be changed to:

foreach (var row in rows)
{
    int cellNum = 1;
    foreach (IXLCell cell in row.Cells())
    {
        // if we are actually able to parse a result int from our value
        if(Int32.TryParse(cell.Value.ToString(), out int result))
        {
                // then we can 
                // perform anything you want for the value here
                System.Console.WriteLine(result);
        }
    }
}

This should allow you to insert new columns in the future, and any that can be interpreted as an int would be evaluated.