So entfernen Sie leere Zeilen aus der DataTable

Versuchen.

public bool InsertRowsToDataBase()
{
    try
    {
        DataTable excelTable = new DataTable();

        string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + txtExcelFile.Text + "';Extended Properties= 'Excel 8.0;HDR=Yes;IMEX=1'";
        using (OleDbConnection cnn = new OleDbConnection(connString))
        {
            string query = "select * from [Customers$]";
            using (OleDbDataAdapter data = new OleDbDataAdapter(query, cnn))
            {
                data.Fill(excelTable);
            }
        }
        dgvCustomers.ColumnHeadersVisible = false;

        connString = "Data Source=COMPUTER-8EB749;Initial Catalog=KITS;Integrated Security=true";
        using (SqlConnection connection = new SqlConnection(connString))
        {
            connection.Open();
            for (int i = 0; i < excelTable.Rows.Length; i++)
            {
                //takes from the 3rd row
                if (i > 1)
                {
                    DataRow row = excelTable.Rows[i];
                    object ID = row[0];
                    if (ID != null && !String.IsNullOrEmpty(ID.ToString().Trim()))
                    {
                        Int16 CustID = Convert.ToInt16(ID);
                        string CustName = row[1].ToString();
                        string CardScheme = row[2].ToString();
                        string Outlet = row[3].ToString();
                        string TerminalNum = row[4].ToString();
                        Int32 Terminal = Convert.ToInt32(TerminalNum);
                        string Date1 = row[5].ToString();
                        DateTime Date = Convert.ToDateTime(Date1);
                        string Time = row[6].ToString();
                        DateTime DateTime = Convert.ToDateTime(Time);
                        string Amount1 = row[7].ToString();
                        double Amount = Convert.ToDouble(Amount1);

                        string columnNames = "CustID,CustName,CardScheme,Outlet,TerminalNum,TranDate,TranDateTime,Amount";
                        string query = String.Format("insert into Customer(0}) values ('{1}', '{2}','{3}','{4}','{5}','{6}','{7}','{8}')",
                            columnNames, CustID, CustName, CardScheme, Outlet, Terminal, Date, DateTime, Amount);
                        using (SqlCommand com = new SqlCommand(query, connection))
                        {
                            com.ExecuteNonQuery();
                        }
                    }
                }
                //this is your last row. do whatever you want with this
                DataRow lastRow = excelTable.Rows[excelTable.Rows.Count - 1];
            }
        }
        return true;
    }
    catch (Exception exception)
    {
        Elmah.ErrorSignal.FromCurrentContext().Raise(exception);
        return false;
    }
}

Bitte beachten Sie, dass ich nur überprüfen, ob die ID null ist und nicht das einfügen solcher Zeilen als ID wird der PK in der Tabelle.

try
{
    OpenOleDBConnection();
    OleDbDataAdapter dataAdapter = new OleDbDataAdapter("select * from [" + SelectedSheet + "]", Connection);

    dataAdapter.Fill(DataTable);

    if ((DataTable != null) && (DataTable.Rows != null) && (DataTable.Rows.Count > 0))
    {
        List<System.Data.DataRow> removeRowIndex = new List<System.Data.DataRow>();
        int RowCounter = 0;
        foreach (System.Data.DataRow dRow in DataTable.Rows)
        {                            
            for(int index = 0; index < DataTable.Columns.Count; index++)
            {
                if (dRow[index] == DBNull.Value)  
                {
                    removeRowIndex.Add(dRow);
                    break;
                }
                else if (string.IsNullOrEmpty(dRow[index].ToString().Trim()))
                {
                    removeRowIndex.Add(dRow);
                    break;
                }
            }
            RowCounter++;
        }
        //Remove all blank of in-valid rows
        foreach (System.Data.DataRow rowIndex in removeRowIndex)
        {
            DataTable.Rows.Remove(rowIndex);
        }
    }
}
catch(Exception e)
{
    WPFMessageBox.Show(e.Message, Globalization.GetValue("Import_ImportOption_FormHeader"), WPFMessageBoxButtons.OK, WPFMessageBoxImage.Error);
}
finally
{
    CloseOleDBConnection();
}

Hier ich m auch das überspringen der Zeilen, wenn Sie mit einer leeren Eintrag in einer der Zeile.

Warum nicht einfach ignorieren leere Zeilen, die direkt vor der Sie einfügen?

if(string.IsNullOrEmpty(ID + CustName + CardScheme /*.. and so on */))
{
    continue;
}

Wie diese:

for (int i = 0; i < dsExcel.Tables[0].Rows.Count; i++)
{
    string ID = ds.Tables[0].Rows[i][0].ToString();
    Int16 CustID = Convert.ToInt16(ID);
    string CustName = dsExcel.Tables[0].Rows[i][1].ToString();
    string CardScheme = dsExcel.Tables[0].Rows[i][2].ToString();
    string Outlet = dsExcel.Tables[0].Rows[i][3].ToString();
    string TerminalNum = dsExcel.Tables[0].Rows[i][4].ToString();
    Int32 Terminal = Convert.ToInt32(TerminalNum);
    string Date1 = dsExcel.Tables[0].Rows[i][5].ToString();
    DateTime Date = Convert.ToDateTime(Date1);
    string Time = dsExcel.Tables[0].Rows[i][6].ToString();
    DateTime DateTime = Convert.ToDateTime(Time);
    string Amount1 = ds.Tables[0].Rows[i][7].ToString();
    double Amount = Convert.ToDouble(Amount1);

    /*** Add this if-statement to you code! ***/
    if(string.IsNullOrEmpty(ID + CustName + CardScheme + Outlet + TerminalNum + Date1 + Time + Amount1))
    {
        continue;
    }

    SqlCommand com = new SqlCommand("insert into Customer(CustID,CustName,CardScheme,Outlet,TerminalNum,TranDate,TranDateTime,Amount) values ('" + CustID + "','" + CustName + "','" + CardScheme + "','" + Outlet + "','" + Terminal + "','" + Date + "','" + DateTime + "','" + Amount + "')", connection);
    com.ExecuteNonQuery();
}

Ich geändert Cfrim Antwort. Sie müssen prüfen, beide leer und whitespace-Zeichenketten. Der weiße Raum kommt aus dem gelöschten Zellen und dem leeren Raum kommt von gelöschten Daten.

private DataTable StripEmptyRows(DataTable dt)
        {
            List<int> rowIndexesToBeDeleted = new List<int>();
            int indexCount = 0;
            foreach(var row in dt.Rows)
            {
                var r = (DataRow)row;
                int emptyCount = 0;
                int itemArrayCount = r.ItemArray.Length;
                    foreach (var i in dr.ItemArray)
                    {
                        if (string.IsNullOrEmpty(i.ToString()) || string.IsNullOrWhiteSpace(i.ToString()))
                            emptyCount++;
                    }

                if(emptyCount == itemArrayCount) rowIndexesToBeDeleted.Add(indexCount);

                indexCount++;
            }

            int count = 0;
            foreach(var i in rowIndexesToBeDeleted)
            {
                dt.Rows.RemoveAt(i-count);
                count++;
            }

            return dt;
        }

public static DataTable RemoveEmptyRows(DataTable dt) 
{ 
    List removeRowIndex = new List(); 

    foreach (DataRow dRow in dt.Rows) 
    { 
        for (int index = 0; index < dt.Columns.Count; index++) 
        { 
            if (string.IsNullOrEmpty(dRow[index].ToString().Trim())) 
            { 
                removeRowIndex.Add(dRow); 
                break; 
            } 
            else if (dRow[index] == DBNull.Value) 
            { 
                removeRowIndex.Add(dRow); 
                break; 
            } 
        } 
    } 

    foreach (DataRow rowIndex in removeRowIndex) 
    { 
        dt.Rows.Remove(rowIndex); 
    } 

    return dt; 
}