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

vygi0913
 
on May 06, 2016 08:24 AM
Sample_192612.zip
7234 Views

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.

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on May 06, 2016 09:39 AM
on Sep 15, 2017 03:22 AM

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