Click here to Skip to main content
15,742,120 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i want to display data in datatable which is get from excel sheet . if any row is empty or any cell(column value ) is empty then i want to delete that respected row.
string strNewPath = Server.MapPath("~/VendorUploads/" + strusername + ".xls");
                   strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strNewPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
                   strQuery = "SELECT  FROM [Sheet1$]";
                   oleDbConnection = new OleDbConnection(strConnection);
                   if (oleDbConnection.State == ConnectionState.Closed) oleDbConnection.Open();
                   oleDbCommand = new OleDbCommand(strQuery, oleDbConnection);
                   oleDbDataAdapter = new OleDbDataAdapter(oleDbCommand);
                   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();

Right now i am using a code which will delete empty row .But i want to delete the row when one of the column value in respected row is empty
Can any one please heip me

1 solution

change row.ItemArray.All to row.ItemArray.Any

dataTable = dataTable.Rows.Cast<DataRow>().Where(row =>
                    !row.ItemArray.Any(field => field is System.DBNull || string.IsNullOrEmpty(field as string))).CopyToDataTable();
Share this answer
Bajid Khan 20-May-14 8:21am    
thankyou for replay DamithSL , But it is not working . If i use "row.ItemArray.Any" it show an error "Object reference not set to an instance of an object."
DamithSL 20-May-14 8:28am    
is it working with row.ItemArray.All? without error?
Bajid Khan 20-May-14 8:59am    
YES ,When complete row is empty . when any coloum is null or empty its raises to exception
DamithSL 20-May-14 9:14am    
check my updated answer
Bajid Khan 20-May-14 9:52am    
Again getting another error "The source contains no DataRows."

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900