我正在使用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;
}
一切都正常,但用户在上传excel之前删除几行的内容时会出现问题.它读取这些空行以及非空行,并且由于违反业务规则(缺少必填字段),数据库中的保存数据将失败.
我试过的是把条件放在查询中：
"SELECT * FROM WHERE not [CandidateId*] = 0 or not [Firstname*] = '' or not [Lastname] = '' or not [type*] = '' or not [DOB*] =" + DBNull.Value
所以它只会选择那些有数据的行.
但是我无法比较非字符串字段,即Date,Integer等.当空的时候,它们作为DBNull进行比较.
可以任何一个建议的方式来做,我不想使用DataReader.
解决方法
使用
“.. WHERE NOT([Lastname] =”OR [DOB *] IS NULL OR …)
