Validate datatable to not upload values if column has blank values in asp.net

Question

Currently, I am uploading an Excel file to upload and display data in a form. So I am validating with basic that dt should not be null and count should be greater than 0. But also I want one validation that if dt column is bank for ex: STATE column first row in my below image then it should prompt an alert that excel has a blank column. How can I achieve it.

[![IMAGE][1]][1]

Below is what I tried but it is still excepting the blank values.

private void Import_To_Grid(string FilePath, string Extension, string isHDR)
    {
        string conStr = "";
        switch (Extension)
        {
            case ".xls": //Excel 97-03
                conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"]
                         .ConnectionString;
                break;
            case ".xlsx": //Excel 07
                conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"]
                          .ConnectionString;
                break;
        }

        conStr = String.Format(conStr, FilePath, isHDR);

        OleDbConnection connExcel = new OleDbConnection(conStr);
        OleDbCommand cmdExcel = new OleDbCommand();
        OleDbDataAdapter oda = new OleDbDataAdapter();
        DataTable dt = new DataTable();
        cmdExcel.Connection = connExcel;

        connExcel.Open();

        DataTable dtExcelSchema;
        dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
        connExcel.Close();            

        connExcel.Open();
        cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
        oda.SelectCommand = cmdExcel;
        oda.Fill(dt);
        connExcel.Close();

        if (dt != null && dt.Rows.Count > 0)
        {
            if (dt.Rows.Count <= Convert.ToInt32(ConfigurationManager.AppSettings["excelUploadSize_JioAF"]))  // check for 200 records
            {
                
            }
            else
            {
                ScriptManager.RegisterStartupScript(this, GetType(), "showalert", "alert('Max upload size is 200 rows');", true);
            }
        }
        else 
        {
            ScriptManager.RegisterStartupScript(this, GetType(), "showalert", "alert('Excel is blank..!!');", true);
        }

    }

Answer 1

You can iterate through the columns and rows to achieve the above requirement. The below code should help you to validate all the columns are rows.

bool hasBlankColumn = false;

foreach (DataColumn column in dt.Columns)
{
    bool isColumnBlank = false;

    foreach (DataRow row in dt.Rows)
    {
        if (string.IsNullOrWhiteSpace(row[column].ToString()))
        {
            isColumnBlank = true;
            break;
        }
    }

    if (isColumnBlank)
    {
        hasBlankColumn = true;
        ScriptManager.RegisterStartupScript(this, GetType(), "showalert", "alert('Excel has a blank column: " + column.ColumnName + "');", true);

        break; // Exit the loop if a blank column is found
    }
}
if (!hasBlankColumn)
{
    // Proceed with your logic for valid data
}