none
Get Filtered Rows of Excel but not loose actual row counts RRS feed

  • Question

  • Hi,

    I am developing an application which requires Excel sheet to be downloaded or uploaded (using OpenXML). There might be cases where some rows entered by user might be blank, say rows 1 and 2 are filled, rows 3 and 4 is left blank, and rows 5 and 6 is again filled. On upload I filter the blank rows and get only the filled rows (say 4 as per above example).

    The problem is I do validation against the specific column values based on the data entered in individual rows. The filtered row count is 4. If there is a validation error on say row 6, which is the last column (as per original sheet count), my application would show error in Row 4, as that is the last number I get after filtering.

    Is there any fix or workaround for this?.

    I am trying to convert my excel to datatable, and then use the following line code to get the filtered rows.

    var filteredRows = dt.Rows.Cast<DataRow>().Where(row => row.ItemArray.Any(field => !(field is System.DBNull)));

    Any help will be much appreciated.

    Thanks In Advance.

    Thursday, January 18, 2018 11:41 AM

All replies

  • Hi Siddy Boy,

    Thank you for posting here.

    For your question, if you want to convert excel to datatable without blank rows, you could try the code below. For better understanding, I use a datagridview to show the value of datatable.

       private void button1_Click(object sender, EventArgs e)
            {
                string name = "Sheet2";
              
                string constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @"New.xlsx" + "; Extended Properties = 'Excel 8.0;HDR=NO;IMEX=1;';";
    
                OleDbConnection con = new OleDbConnection(constr);
                OleDbCommand oconn = new OleDbCommand("Select * From [" + name + "$]", con);
                con.Open();
    
                OleDbDataAdapter sda = new OleDbDataAdapter(oconn);
                DataTable data = new DataTable();       
              
                sda.Fill(data);
                dataGridView1.DataSource = StripEmptyRows(data);  
            }
    
            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 r.ItemArray) if (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;
            }

    Excel

    Best Regards,

    Wendy


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 22, 2018 1:14 AM
    Moderator