I am reading excel file through read range and then placed “For Each Row Loop” and inside this loop, I have put the If condition, pl. see below:
If row(“Excel_Column_Name”).Equals(0) Then Message Box.
But before or after this If condition I want to convert each cell value to an integer so that I should make further calculations. My Excel column’s data type is General not number.
@pravin_calvin : Really grateful to you. Mission accomplished. But could you please also guide how to handle Null values? Because in my excel column there are null values exist other than zero.
@Jobin_Joy : Really grateful to you. Mission accomplished. But could you please also guide how to handle Null values? Because in my excel column there are null values exist other than zero.
ExcelData.Rows.Cast(Of DataRow)().Where(Function(row) Not row.ItemArray.All(Function(field) field Is DBNull.Value Or field.Equals(“”))).CopyToDataTable()
@pravin_calvin : Thanks but I have a column, pl. see below and I want wherever my cell is empty/null or having zero value, I should replace with the value available in other column.
That’s only in excel file point of view, once the excel data loaded into DataTable things will change. In the DataTable, the data type will be DataColumnCollection object, then we are parsing to specific type. Ex: Int, Double etc based on our need/data.
string.IsNullOrWhiteSpace(row(“My_Excel_Column”).ToString) Or String.IsNullOrEmpty(row(“My_Excel_Column”).ToString) Or Cint(row(“My_Excel_Column”).ToString).Equals(0)
But, It captures only zero cell values and replace the value. The last command is running and ignoring the two conditions, i.e., IsNullOrWhiteSpace and IsNullOrEmpty.