How to get the row count from a datatable without space while reading from excel in ASP.NET with C#?

Question

0.00/5 (No votes)

See more:

How to get the row count from a data table without empty rows while reading from excel in asp.net with c#?

I am reading the data from excel sheet using OledbConnection. But empty rows also are coming in data table. I want to avoid the same.

My code to read data from excel is as below:

C#

System.Data.OleDb.OleDbConnection MyConnection;
System.Data.DataSet ds;
System.Data.OleDb.OleDbDataAdapter MyCommand;
MyConnection = new System.Data.OleDb.OleDbConnection(connStr);
MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
ds = new System.Data.DataSet();
MyCommand.Fill(dt);
MyConnection.Close();



ds.Tables.Add(dt);
oItem = dt.Rows.Count;

What I have tried:

My code to read data from excel is as below:

C#

System.Data.OleDb.OleDbConnection MyConnection;
System.Data.DataSet ds;
System.Data.OleDb.OleDbDataAdapter MyCommand;
MyConnection = new System.Data.OleDb.OleDbConnection(connStr);
MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
ds = new System.Data.DataSet();
MyCommand.Fill(dt);
MyConnection.Close();



ds.Tables.Add(dt);
oItem = dt.Rows.Count;

Posted 6-Jul-16 18:17pm

Ema112

Updated 6-Jul-16 20:38pm

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Karthik_Mahalingam · Answer 1 · 2016-07-06T19:57:00

refer this

C#

DataTable dt = new DataTable();
        dt.Columns.Add("ID");
        dt.Columns.Add("Name");
        dt.Columns.Add("Address");
        dt.Rows.Add(null, null, DBNull.Value);
        dt.Rows.Add(1, 2, "a");
        dt.Rows.Add(12, 23, "b");

        object[] possibleNullOrEqualentValues = {null,"",DBNull.Value};

        DataTable dtTemp = dt;
        for (int i = 0; i < dtTemp.Rows.Count; i++)
        {
            DataRow currentRow = dtTemp.Rows[i];
            DataRow targetRow = dt.Rows[i];
           bool isEmptyRow =  currentRow.ItemArray.All(k => possibleNullOrEqualentValues.Contains(k));
           if (isEmptyRow)
               targetRow.Delete(); 
        }

        dt.AcceptChanges();
        int RowCount = dt.Rows.Count;

koolprasad2003 · Answer 2 · 2016-07-06T18:51:00

First clone your datatable and delete empty rows, then after you can count rows
see below snippet

C#

DataTable dt1= dt.Clone();
for (int i = dt1.Rows.Count - 1; i >= 0; i--)
{
    if (dt1.Rows[i]["col1"] == DBNull.Value && dt1.Rows[i]["col2"] == DBNull.Value)
     {
        dt1.Rows[i].Delete();
     }
}
dt1.AcceptChanges();
int RowCount = dt1.Rows.Count;

vijeshkumarvijayan · Answer 3 · 2016-07-06T20:38:00

Solution 3

can you refer the given link
c# - how to remove empty rows from datatable - Stack Overflow[^]

Posted 6-Jul-16 20:38pm

vijeshkumarvijayan

How to get the row count from a datatable without space while reading from excel in ASP.NET with C#?

3 solutions

Solution 2

Solution 1

Solution 3

Add your solution here

Preview 0