Hi,
you are using the implicit type var against the dynamic type Value2. the Value2 will return the type whether it is an int, string, or DateTime. Parsing an dynamic type to implicit type, you will not know what you are actually getting.
if you are sure that the item quantity is number, then you can explicitly define the type
int itemQu = xlsSheet.Cells[iLoop,2].Value2;
So if the returned value type is wrong, then you code fail in that point, rather than failing later.
Also you have another problem of converting to string, if the value returned is null, and converting a null to string will throw exception. if I know the type, I wouldn't convert it for no reason.
Stick a break point on the ItemPn and ItemQu, make sure the type you are receiving, make those variable explicit, cast the Value2 to its correct type if you need to, the both your while loop and the parsing of the quantity will work.
Edit:
If the cell value is text then the Value2 will return type string, if the cell value is number then the Value2 will return type double. if the cell is empty the return type is COM object null.
so you need amend your code as follows:
string itemPartNumber;
int itemQuantity;
int iLoop = 2;
ClearList(ref bomList);
while (!string.IsNullOrEmpty(itemPartNumber = xlsSheet.Cells[iLoop,1].Value2))
{
itemQuantity = (int) (xlsSheet.Cells[iLoop,2].Value2 ?? 0);
ComponentPNQ itemList = new ComponentPNQ(itemPartNumber, itemQuantity);
bomList.Add(itemList);
iLoop++;
}
Regards
Jegan