Detecting Empty cell in Excel Range

Question

4.00/5 (1 vote)

See more:

Hello,

I have to get the information from a range of cells from an Excel file and transfer to a list to process it later on.

The case is that I do not know how long is the list of filled cells and I get an error when I read a cell further on the populated range.

The rest of the code works well until I reach the cell out of the range.

My code is as follows:

C#

// Get Data
string itemPartNumber;
int itemQuantity;
int iLoop = 2; // Skip the column title
bool exitLoop = false;

ClearList(ref bomList);  //Clear my destination list

while (!exitLoop)
{
    var itemPn = xlsSheet.Cells[iLoop,1].Value2;
    var itemQu = xlsSheet.Cells[iLoop,2].Value2;

    exitLoop = (itemPn == null);
    if (!exitLoop)
    {
        itemPartNumber = (string) itemPn;
        itemQuantity = Int32.Parse(itemQu.ToString());
        ComponentPNQ itemList = new ComponentPNQ(itemPartNumber, itemQuantity);
        bomList.Add(itemList);
        iLoop++;
    }
}

My problem is that I can not found the right comparison to exit the Loop, before I assing the values, that makes the error (it is marked on the "itemQuantity" row, that is an integer. No error with the string itemPartNumber, but then the loop never ends)

I have tryed several ways, but I am not able to solve it.

Can anyone give me an idea?

Thank you very much in advance.

David
PS: I would like to attach my excel file as example, but I do not get the way.

Posted 14-Mar-13 0:42am

NeueStudium2012

Updated 14-Mar-13 5:25am

Sarrrva

v2

Add a Solution

Comments

PeiWHoward 18-Jul-17 5:46am

Check this excel automation's alternative, it provides a strongly typed value that you can use, for instance like this:

ExcelFile xlsBook = ExcelFile.Load("Sample.xls");
ExcelWorksheet xlsSheet = xlsBook.Worksheets[0];

bool exitLoop = false;

while (!exitLoop)
{
ExcelCell cellPn = xlsSheet.Cells[iLoop, 0];
ExcelCell cellQu = xlsSheet.Cells[iLoop, 1];

exitLoop = cellPn.ValueType == CellValueType.Null;
// ...
}

Also check here how to use Excel's cells and ranges in C#, notice that you have a general ExcelCell.Value but you also have strongly typed ExcelCell.IntValue, ExcelCell.StringValue, etc.

4 solutions

Solution 1

Have a look on similar discussion:
How to check if an Excel Range is empty in C#[^]
Check if a cell is blank in excel[^]

Posted 14-Mar-13 0:46am

Prasad_Kulkarni

Comments

NeueStudium2012 14-Mar-13 9:37am

I have already found those two links, but unfortunately they do not solve my problem.
Thank you anyway.

Solution 3

Please, read this: var (C# reference)[^] and this: Implicity Typed Local Variables (C# programming guide)[^].

Try to change the type of variables declaration:

C#

string itemPn = xlsSheet.Cells[iLoop,1].Text;
string itemQu = xlsSheet.Cells[iLoop,2].Text;

then check if string IsNullOrEmpty[^].

;)

Posted 14-Mar-13 7:15am

Maciej Los

Comments

NeueStudium2012 15-Mar-13 3:13am

Hallo Maciej

It does not work, as the string throws an error for null values.
Thank you anyway.
David

Jegan Thiyagesan 15-Mar-13 6:06am

Hi Maciej,
The "xlsSheet.Cells[iLoop,1]" is a dynamic type, it returns a type during runtime, so during compile time you will not know what type is going to return, according to Microsoft we should be using "dynamic" type as
dynamic itemPn = xlsSheet.Cells[iLoop,1];

string str = (itemPn != null)? itemPn.ToString(): ""; // there is no reason why you shouldn't parse the type as it is, if you know the retuen value is text or a number, rather than converting to string.

http://msdn.microsoft.com/en-us/library/vstudio/ff926074.aspx // half way down the page "Avoid the use of var in place of dynamic".
http://msdn.microsoft.com/en-us/library/vstudio/dd264741.aspx

Solution 4

Here is my solution.
Thank you for all your contributions.

C#

string itemPartNumber;
int itemQuantity;
int iLoop = 2;
bool exitLoop = false;

ClearList(ref bomList);

while (!exitLoop)
 {
     var itemPn = xlsSheet.Cells[iLoop, partColumn].Value2;
     var itemQu = xlsSheet.Cells[iLoop, quantityColumn].Value2;

     exitLoop = string.IsNullOrEmpty(itemPn);
     if (!exitLoop)
     {
         itemPartNumber = itemPn;
         itemQuantity = Convert.ToInt32(itemQu);
         ComponentPNQ itemList = new ComponentPNQ(itemPartNumber, itemQuantity);
         bomList.Add(itemList);
         iLoop++;
     }
 }

Posted 14-Mar-13 21:16pm

NeueStudium2012

Comments

Jegan Thiyagesan 15-Mar-13 6:15am

Hi Dave,
You are not checking for itemQu for null, Convert.ToIn32(null) will throw exception.

Also according to you code if the itemPn is null, then you not using the itemQu, this is why I have optimized the code in my solution version 4 such that, if the itemPn is null the code will not check for itemQu and you will not need to allocate an extra variable 'exitLoop'. Remember, every line of code is going to allocate some memory and consume time. Remember the less line of code the better the throughput.

NeueStudium2012 18-Mar-13 8:30am

Hi Jegan,
Thank you for your Advice.
The rows that contain values in itemPn also do in itemQn (it is programmtically generated), so I do not take care about it.
Also I will use exitloop further on, but I think it is not advisable to write here the whole code.
Even though, I can save some lines.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jegan Thiyagesan · Accepted Answer · 2013-03-14T01:55:00

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

C#

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:

C#

string itemPartNumber;
int itemQuantity;
int iLoop = 2; // Skip the column title

ClearList(ref bomList);  //Clear my destination list

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

Detecting Empty cell in Excel Range

4 solutions

Solution 2

Solution 1

Solution 3

Solution 4

Add your solution here

Preview 0

Existing Members

...or Join us