How to check if a cell is empty (Excel\VisualC#)

Question

My aim is to check line per line in the Sheet1 in order to discover how many rows are, so i put a do\while that should stop once it reaches a blank cell

Example:

row1 data
row2 data
row3 data
row4 data
row5 data

row6 data
row7 data

In this case I need only the first 5 rows, so the do\while check is intended to stop once it reaches the blank cell. This doesn't happens, because the check doesn't loop (it stops after completing a circle like it finds a blank cell even if it is filled with data).

string str;
int rCnt = 11; //the data I need is after the 10th row in excel
int cCnt = 1;
int valori = 1;

Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(label4.Text, 0, false, 5, "", "", 
                                                 false, Excel.XlPlatform.xlWindows, 
                                                 "", true, false, 0, true, false, 
                                                 false);
Excel.Sheets xlsheet = xlWorkbook.Worksheets;
string sheet1 = "Sheet1";
Excel.Worksheet xlWorksheet = (Excel.Worksheet)xlsheet.get_Item(sheet1);
Excel.Range xlCell;   

do
{
    rCnt++;
    str = "A" + rCnt;
    xlCell = (Excel.Range)xlWorksheet.get_Range(str, str);
} while (xlCell.Value2 == null);
    

I tried changing Value2 to Value or Text and trying to set == "" instead of null.

Displayed

you can use string.NullorEmpty method – Blab

I tried "while (string.IsNullOrEmpty(xlCell.Value2));" but it returns this error: The best overloaded method match has some invalid arguments – Displayed

Answer 1

If you want the loop stop when reach blank cell then .. Try to change

while (xlCell.Value2 == null);

with

while (! IsNull(xlCell.Value2));

Pinchcock

I tried this too, but it seems it doesn't recognize "IsDBNull" word. Is there something I have to add? – Displayed

Answer 2

Simple way to check a cell is empty:

 if (sheet.Cells[4,3] == null || sheet.Cells[4,3].Value2 == null || sheet.Cells[4,3].Value2.ToString() == "")
   MessageBox.Show(“cell on row 4 col 3 is empty”);

Wetmore

Answer 3

You can use the Text property of the selected cell. It can be converted to a string type via "as". The result string can already be checked as usual in C#, like not-empty

string TempText = excelWorksheet.Cells[1, 1].Text as string;
if (!string.IsNullOrWhiteSpace(TempText)){
// actions
}

Chaker

Code dumps without any explanation are rarely helpful. Stack Overflow is about learning, not providing snippets to blindly copy and paste. Please edit your question and explain how it works better than what the OP provided. – Catharina

Careful using Text for at times it may have the text of "#####" instead of the value being sought. Better to use Value2 instead. – Trothplight

Answer 4

The issue mainly comes from when you don't know what sort of data to expect. When I work with Excel reads I often do something similar to:

var _cell = range.Cells[1, 2].Value2;
if (_cell.GetType() != typeof(Double))

In your instance if you always are getting a string returned then you should be able to assume the cast:

string _str = (string)(range.Cells[str, str] as Excel.Range).Value2;

and then check that is not empty.

Hearken

Answer 5

This is working for me:

using Excel = Microsoft.Office.Interop.Excel; to read excelsheet:

var excelApp = new Excel.Application();
Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(path, 0, false, 5, "", "", false, XlPlatform.xlWindows, "", true, false, 0, true, false, false);

Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelWorkbook.Sheets[2];

Excel.Range excelRange = excelWorksheet.UsedRange;
int rowCount = excelRange.Rows.Count;
int colCount = excelRange.Columns.Count;
string wwdEmpty = Convert.ToString(excelRange.Cells[5, 14].value2);
// this is working code with NULL Excell cell 

Tangy

This is not proper code formatting. Please refer to the code formatting documentation, which outlines how to format code using indents and/or code fences. – Migrate

Answer 6

do
{
    rCnt++;
    str = Sheet.Cells[rCnt, 5].Value;
} while (str != null);

Rhodes

Sheet is defined as Excel._Worksheet Sheet; and it represent your sheet in excel workbook – Rhodes

A bit of explanation in your answer would be helpful? Consider editing your answer (click the "edit" link) – Exeat