CODEWITHSUNDEEP
c#excelcell
Daniele
Daniele

Reputation: 51

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

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.

Upvotes: 5

Views: 73532

Answers (6)

Alatey
Alatey

Reputation: 379

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
}

Upvotes: 2

Jarus Rev
Jarus Rev

Reputation: 19

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

Upvotes: 0

Joshua Ferdinand
Joshua Ferdinand

Reputation: 1

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

Upvotes: -1

thedrs
thedrs

Reputation: 1464

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”);

Upvotes: 2

Grant Bagwell
Grant Bagwell

Reputation: 53

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.

Upvotes: 1

matzone
matzone

Reputation: 5719

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

while (xlCell.Value2 == null);

with

while (! IsNull(xlCell.Value2));

Upvotes: 4

Related Questions