ASP.NET中验证DataTable：列含空值时禁止上传

我正在实现Excel文件上传并在表单中展示数据的功能，已经完成基础验证：确保DataTable不为空且行数大于0。现在需要添加一项验证：如果DataTable中存在列空值（例如某行的STATE列为空），则弹出提示告知用户Excel存在空列。我尝试了现有代码，但仍允许含空值的文件上传，请问该如何实现这项验证？

现有代码 #

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

要实现空值验证，需要遍历DataTable的每一行和每一列，检查是否存在DBNull.Value或空白字符串，一旦发现立即终止验证并弹出提示。

修改后的完整代码 #

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)
    {
        // 新增：检查是否存在空值
        bool hasEmptyValue = false;
        foreach (DataRow row in dt.Rows)
        {
            foreach (DataColumn col in dt.Columns)
            {
                object value = row[col];
                if (value == DBNull.Value || string.IsNullOrWhiteSpace(value?.ToString()))
                {
                    hasEmptyValue = true;
                    break; // 找到空值，跳出列循环
                }
            }
            if (hasEmptyValue) break; // 找到空值，跳出行循环
        }

        if (hasEmptyValue)
        {
            ScriptManager.RegisterStartupScript(this, GetType(), "showalert", "alert('Excel中存在空列，请检查后重新上传');", true);
            return; // 终止后续逻辑
        }

        // 原有行数检查逻辑
        if (dt.Rows.Count <= Convert.ToInt32(ConfigurationManager.AppSettings["excelUploadSize_JioAF"]))
        {
            // 此处添加数据展示到表单的逻辑
        }
        else
        {
            ScriptManager.RegisterStartupScript(this, GetType(), "showalert", "alert('最多只能上传200行数据');", true);
        }
    }
    else 
    {
        ScriptManager.RegisterStartupScript(this, GetType(), "showalert", "alert('Excel文件为空..!!');", true);
    }
}

关键说明 #

• 空值检查同时覆盖了数据库空值（DBNull.Value）和空白字符串的场景
• 一旦发现空值立即终止遍历，减少不必要的性能消耗
• 验证失败后直接返回，跳过后续的行数检查和数据展示逻辑

内容的提问来源于stack exchange，提问作者Nadeem