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


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;
    DataTable dtExcelSchema;
    dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
    DataSet ds = new DataSet();
    string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
    cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
    da.SelectCommand = cmdExcel;
    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))
        nonEmptyCell += "TC NO " + tcNo + " has " + count + " non empty cells. \n";
    lblCount.Text = nonEmptyCell;