Check if a cell is blank in excel

toshesh · February 8th, 2006, 06:31 AM

Hi everyone,

I am having difficulty the the System.NullException error during runtime. As I parse through an excel spreadsheet, the program needs to detect whether a cell contains any value or not. Now I know the particular cell it is trying to read is blank and need to know how to avoid getting the nullexception error (should be some blank checking function). I am not sure if it's the way I search the net but had no luck finding an appropriate solution. Please someone help!!

Here is the code I wrote so far

Code:

                try {
                    if (((Excel.Range)xlSheet.Cells[i, "B"]).Value2.ToString() != null) {
                        System.Diagnostics.Trace.WriteLine(xlSheet.Cells[i, "B"].ToString());
                        nominalCode = ((Excel.Range)xlSheet.Cells[i, "B"]).Value2.ToString();
                        System.Diagnostics.Trace.WriteLine(nominalCode);
                    }
                    if (((Excel.Range)xlSheet.Cells[i, "D"]).Value2.ToString() != null) {
                        nominalCodeName = ((Excel.Range)xlSheet.Cells[i, "D"]).Value2.ToString();
                        System.Diagnostics.Trace.WriteLine(nominalCodeName);
                    }
                    if (((Excel.Range)xlSheet.Cells[i, "E"]).Value2.ToString() != null) {
                        dept = ((Excel.Range)xlSheet.Cells[i, "E"]).Value2.ToString();
                        System.Diagnostics.Trace.WriteLine(dept);
                    }
                    if (((Excel.Range)xlSheet.Cells[i, "K"]).Value2.ToString() != null) {
                        balance = Convert.ToInt32(((Excel.Range)xlSheet.Cells[i, "K"]).Value2.ToString());
                        System.Diagnostics.Trace.WriteLine(balance);
                    }
                    else {
                        balance = 0;
                    }
                    if (((Excel.Range)xlSheet.Cells[i, "L"]).Value2.ToString() != null) {

                        debit = Convert.ToInt32(((Excel.Range)xlSheet.Cells[i, "L"]).Value2.ToString());
                        System.Diagnostics.Trace.WriteLine(debit);
                    }
                    else {
                        debit = 0;
                    }
                    if (((Excel.Range)xlSheet.Cells[i, "Q"]).Value2.ToString() != null) {
                        credit = Convert.ToInt32(((Excel.Range)xlSheet.Cells[i, "Q"]).Value2.ToString());
                        System.Diagnostics.Trace.WriteLine(credit);
                    }
                    else {
                        credit = 0;
                    }
                    if (((Excel.Range)xlSheet.Cells[i, "S"]).Value2.ToString() != null) {
                        totalDebit = Convert.ToInt32(((Excel.Range)xlSheet.Cells[i, "S"]).Value2.ToString());
                        System.Diagnostics.Trace.WriteLine(totalDebit);
                    }
                    else { 
                        totalDebit = 0;
                    }
                    if (((Excel.Range)xlSheet.Cells[i, "X"]).Value2.ToString() != null) {
                        totalCredit = Convert.ToInt32(((Excel.Range)xlSheet.Cells[i, "X"]).Value2.ToString());
                        System.Diagnostics.Trace.WriteLine(totalCredit);
                    }
                    else {
                        totalCredit = 0;
                    }

                    DataRow row = fileTable.NewRow();

                    System.Diagnostics.Trace.WriteLine(nominalCode + " " + nominalCodeName + " " + dept + " " + balance + " " + totalCredit);
                    // Enter each data into struct

                    row["NC"] =                    nominalCode;
                    row["NCName"] =                nominalCodeName;
                    row["Dept"] =                dept;
                    row["Balance"] =            balance;
                    row["DebitCredit"] =        debit - credit;
                    row["totalDebitCredit"] =    totalDebit - totalCredit;
                    fileTable.Rows.Add(row);
                }
                catch (Exception err) {
                    System.Console.WriteLine("{0} Exception caught.", err);
                }

toshesh · February 8th, 2006, 10:50 AM

Found the solution, or atleast something that works.

Change the red bit to the following

Code:

Excel.Range)xlSheet.Cells[i, "E"]).Value2 != null

jcboles · November 9th, 2006, 08:44 AM

So, all you did was remove the .ToString() ?

Vijay123 · March 5th, 2009, 05:53 AM

Check the condition like this

((Excel.Range)xlSheet.Cells[i, "B"]).Value2 != null)

not like

((Excel.Range)xlSheet.Cells[i, "B"]).Value2.ToString() != null)

Vijay123 · March 5th, 2009, 05:54 AM

Check the condition like this

((Excel.Range)xlSheet.Cells[i, "B"]).Value2 != null)

not like

((Excel.Range)xlSheet.Cells[i, "B"]).Value2.ToString() != null)