如何在c#中使用oledb只上传非空的Excel电子表格行?

I am importing excel sheet to DataTable using oledb connection as below.

我正在使用oledb连接将excel表导入DataTable，如下所示。

private static DataTable UploadExcelSheet(string fileName)
    {
        DataTable uploadDataTable;
        using (OleDbConnection objXConn = new OleDbConnection())
        {
            objXConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName +
                                            ";Extended Properties=\"Excel 12.0;IMEX=1\"";

            objXConn.Open();

            OleDbCommand objCommand =
                new OleDbCommand("SELECT * FROM Template$ ", objXConn);
            OleDbDataAdapter objDataAdapter = new OleDbDataAdapter();

            // retrieve the Select command for the Spreadsheet
            objDataAdapter.SelectCommand = objCommand;

            // Create a DataSet
            DataSet objDataSet = new DataSet();

            // Populate the DataSet with the spreadsheet worksheet data
            objDataAdapter.Fill(objDataSet);
            uploadDataTable = objDataSet.Tables[0];
        }

        return uploadDataTable;
    }

Everything is working fine but problem comes when user delete content of few rows before uploading the excel. It reads those empty rows as well along with non empty rows, and saving data in database fails because of business rule violation (mandatory field missing). What I tried is putting where condition in query :

一切正常，但当用户在上传excel前删除几行内容时，问题就出现了。它也读取这些空行和非空行，并且由于业务规则违反(强制字段丢失)，在数据库中保存数据失败。我尝试的是在查询中加入where condition:

"SELECT * FROM  WHERE  not [CandidateId*] = 0 or not [Firstname*] = '' or not [Lastname] = '' or not [type*] = '' or not [DOB*] =" + DBNull.Value

So it will select only those rows which has data. But I am not able to compare non string field i.e. Date, Integer etc. Which are comming as DBNull when empty. Can any one please suggest the way to do it, I dont want to use DataReader.

它只会选择那些有数据的行。但是我不能比较非字符串字段，如日期、整数等，它们在空时被称为DBNull。我不想用DataReader，您能给我建议一下怎么做吗?

4 个解决方案

#1

Use

使用

".. WHERE NOT ([Lastname] = '' OR [DOB*] IS NULL OR ... )

#2

Expanding on vc's answer, this will remove all rows that which each of it's columns contain either nothing or white space:

扩展到vc的答案，这将删除它的每一列包含的所有行，要么不包含任何内容，要么不包含空格:

dataTable = dataTable.Rows.Cast<DataRow>().Where(row => !row.ItemArray.All(field => field is System.DBNull || string.Compare((field as string).Trim(), string.Empty) == 0)).CopyToDataTable();

#3

How about filtering the rows after the query has executed using Linq to object:

如何使用Linq to object过滤查询执行后的行:

var filteredRows = uploadDataTable.Rows.Cast<DataRow>().Where(
  row => row.ItemArray.Any(field => !(field is System.DBNull)));

#4

Expanding on the previous answers, this worked for me. Delete rows where all fields are null.

扩展之前的答案，这对我很有效。删除所有字段为空的行。

Dim deleteRows = From row In result.AsEnumerable
                 Where row.ItemArray.All(Function(field) Equals(field, DBNull.Value))

For Each deleteRow In deleteRows
    deleteRow.Delete()
Next

#1

Use

使用

".. WHERE NOT ([Lastname] = '' OR [DOB*] IS NULL OR ... )

#2