Getting a value from an Excel sheet column

Posted on

Problem

The code below gets value from a column in an Excel sheet. The values I get are B1, B2, B3, B4…., B100, …Bn. All I need to do is strip out the char ‘B’ and convert the numeric string to integer and count how many times a particular integer has repeated.

The snippet below shows the string stripping the char ‘B’. However I feel it can be done in better way but I don’t know how.

if (range.Text.ToString() == "BayID")
{
    range = null;
    range = readSheet.get_Range(cell, Missing.Value);
    while (range.Text.ToString() != "")
    {
        range = null;
        string A1 = String.Empty, Val = String.Empty;
        char[] value = null;
        range = readSheet.get_Range(cell, Missing.Value);
        A1 = range.Text.ToString();
        value = A1.ToCharArray();
        j++;
        cell = "D" + j;
        for (int i = 1; i < value.Length; i++)
        { 
            Val += value[i]; 
        }
   }
}

Solution

Don’t be afraid of having more variables. I suspect that the value in range has a different contextual meaning when it is used in the first if clause vs the inside of the while loop. If this is the case, make two different variables with names that describe what the contextual meaning is for the specific variable. The type system will help you know the variable is a Range. A name like price or id (it all depends on what you are using it for) will make the code easier to read.


Building on what @Malachi said: You don’t need to define your variables at the beginning and assign them a default value.

string A1 = String.Empty, Val = String.Empty;
char[] value = null;
range = readSheet.get_Range(cell, Missing.Value);
A1 = range.Text.ToString();
value = A1.ToCharArray();

//is the same as

range = readSheet.get_Range(cell, Missing.Value);
string A1 = range.Text.ToString();
char[] value = A1.ToCharArray();

you have this line

range = null;

and this line

range = readSheet.get_Range(cell, Missing.Value);

both inside and outside of your While Statement. this seems Odd to me?
from the code that you have given it looks like you can get rid of

range = null; 

both times that it shows up.

your range variable is already created outside of this if statement, I don’t think that you should need to set it to null everytime that you want to change it.

that paired with the Substring() Method that @svick mentioned would clean this up a bit I think.

strip out the char ‘B’ and convert the numberic string to integer and count how many times a particular integer has repeated

// the integer, it's count
Dictionary<int,int> integerCounter = new Dictionary<int,int>;
Regex stripB = new Regex(@"d+");
int capturedNumber;

// may not be correct use of Worksheet properties
foreach (var cell in range) {
    if (Int32.TryParse(stripB.Match(cell.Text), out capturedNumber)) {

        if (!integerCounter.Contains(capturedNumber)) integerCounter.Add(capturedNumber, 1)
        else
            integerCounter[capturedNumber]++;
   }
}

Leave a Reply

Your email address will not be published. Required fields are marked *