Member Avatar for Tank50

Hi

I use C# to read excel file.So user has to enter path of the excel file into application.However problem is, if there is null value in excel sheet for particular column then it generate the i want to create another application that check null ,how do I find the null value in excel sheet.

This is way I get the value ,

String value = worksheet.get_Range(col.columnCode + i, col.columnCode + i).Value2.ToString();

Thanks
Tank50

  • 2 Contributors
  • 2 Replies
  • 224 Views
  • 11 Hours Discussion Span
  • Latest Post Latest Post by Tank50

Dani AI

Generated

Nice progress — already found that the crash comes from calling ToString() on a null Excel value, and is right that string.IsNullOrEmpty is useful once you have a string. A few practical notes and a safer pattern that will avoid surprises when reading Excel with Interop.

Read a block once, treat each cell as an object, and check for null/DB null before converting:

object[,] block = (object[,])worksheet.get_Range("A1", "C100").Value2;
for (int r = 1; r <= block.GetLength(0); r++)
{
    for (int c = 1; c <= block.GetLength(1); c++)
    {
        object cell = block[r, c];    // returned array is 1-based
        if (cell == null || cell == DBNull.Value) continue; // empty
        string text = cell.ToString(); // safe now
        // call string.IsNullOrEmpty / IsNullOrWhiteSpace as needed
    }
}

Tips and gotchas:

  • Range.Value2 returns null for truly empty cells. If a formula returns "" you will get an empty string, not null, so check both null and empty. If you read via OleDb you may get DBNull.Value, so include that check.
  • Dates/numbers: Value2 commonly returns doubles for dates (OLE date). Value may return a DateTime; be explicit if you need date semantics.
  • Performance: iterating cell-by-cell with many COM calls is slow. Read ranges into an object[,] and work that array in memory (as above).
  • Cleanup: Excel Interop is COM-based — release Range/Worksheet/Application COM objects (Marshal.ReleaseComObject, set to null) and run GC.Collect/WaitForPendingFinalizers to avoid orphan Excel processes.

If the app must run without Excel installed or needs higher throughput, consider using a managed reader (ExcelDataReader, EPPlus, ClosedXML, NPOI) instead of Interop.

Recommended Answers

All 2 Replies

Member Avatar for serkan sendur

i didnt get the question clearly though, but if you want to check if string is null or empty, there is a function : string.IsNullOrEmpty

Member Avatar for Tank50

HI

Thanks serkan.I got the way how to do that,here is coding

Excel.Range cell1 = (Excel.Range)xlWorkSheet .get_Range("A20", "A20");
            
            if(cell1.Value2==null){
                MessageBox.Show("Null");
            }

Thanks
Tank50

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.