Count Number of none empty cells in Excel using C# in ASP.Net

Last Reply on May 06, 2016 09:39 AM By dharmendr

Posted on May 06, 2016 08:24 AM

I use OLEDB to connect and read the values from the Excel.

If you see on the 1st Column has 4 Values are given and rest are empty..
So I need the empty cell neglected and get the count of the values present.
Similarly for the next aswell.

Kindly help me on it.

Posted on May 06, 2016 08:35 AM

Will you please explain in more details and what about the value of X.


Posted on May 06, 2016 08:37 AM

X is also a value it is not empty... The count must include that also.. 


Posted on May 06, 2016 08:39 AM

Then for first ro it is 5 and for second row it is 6.


Posted on May 06, 2016 08:45 AM

For the first row it should be 4 and for second row it is 5.

TC No is the for like S.NO so you can ignore it. 


Posted on May 06, 2016 09:39 AM Modified on one year ago

Hi vygi0913,

Here i have created sample that full fill your requirement. You can refer the below article to read the data from excel to dataset.

Read Excel file using OLEDB Data Provider in C# .Net

Code

private void btnGetCount_Click(object sender, EventArgs e)
{
    string strExcelConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source="
        + @"D:\Test.xls"
        + "; Extended Properties='Excel 8.0;HDR=Yes'";
    OleDbConnection connExcel = new OleDbConnection(strExcelConn);
    OleDbCommand cmdExcel = new OleDbCommand();
    OleDbDataAdapter da = new OleDbDataAdapter();
    cmdExcel.Connection = connExcel;
    connExcel.Open();
    DataTable dtExcelSchema;
    dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
    connExcel.Close();
    DataSet ds = new DataSet();
    string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
    cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
    da.SelectCommand = cmdExcel;
    da.Fill(ds);
    string nonEmptyCell = string.Empty;
    for (int row = 0; row < ds.Tables[0].Rows.Count; row++)
    {
        string tcNo = ds.Tables[0].Rows[row].ItemArray[0].ToString();
        int count = 0;
        for (int column = 1; column < ds.Tables[0].Rows[row].ItemArray.Length; column++)
        {
            string value = ds.Tables[0].Rows[row][column].ToString();
            if (!string.IsNullOrEmpty(value))
            {
                count++;
            }
        }
        nonEmptyCell += "TC NO " + tcNo + " has " + count + " non empty cells. \n";
    }
    lblCount.Text = nonEmptyCell;
}

Screenshot