Having trouble getting filtered data from a excel file to datagridview using c#

Question

Say I've got a big excel file with multiple row & column (sample attached):

I'm trying to open this file in read-only mode then apply a autofilter and then add that filtered data to a datagridview (minus some columns e.g. Row ID and State). I've created a windows form app with a datagridview and 2 buttons namely Show and Exit

I've done:

using System;  
using System.Collections.Generic;  
using System.IO;  
using System.Linq;  
using System.Runtime.InteropServices;  
using System.Data;  
using System.Threading.Tasks;  
using Excel = Microsoft.Office.Interop.Excel;  
using System.Windows.Forms;  
  
namespace FinalExcel  
{  
	/// <summary>  
	/// Description of MainForm.  
	/// </summary>  
	public partial class MainForm : Form  
	{  
		public MainForm()  
		{  
			InitializeComponent();  
		}  
  
		private Excel.Range filteredRange=null;  
		  
		  
		void ButtonShowClick(object sender, EventArgs e)  
		{  
			  
			try {  
				  
				//Create COM Objects. Create a COM object for everything that is referenced  
				Excel.Application xlApp = new Excel.Application();  
				Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"D:\DD21\tempdata.xlsx");  
				Excel.Worksheet xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkbook.Sheets[1];  
				Excel.Range xlRange = xlWorksheet.UsedRange;  
				  
				  
				  
				xlRange.AutoFilter(4, "Florida", Excel.XlAutoFilterOperator.xlFilterValues, Type.Missing, true);  
  
				filteredRange = xlRange.SpecialCells(Excel.XlCellType.xlCellTypeVisible, Excel.XlSpecialCellsValue.xlTextValues);  
  
				if (filteredRange != null)  
				{  
					DataTable dt = ConvertRangeToDataTable();  
					if (dt != null) { dataGridView1.DataSource = dt; }  
				}  
				  
				//cleanup  
				GC.Collect();  
				GC.WaitForPendingFinalizers();  
  
				//release com objects to fully kill excel process from running in the background  
				Marshal.ReleaseComObject(xlRange);  
				Marshal.ReleaseComObject(xlWorksheet);  
  
				//close and release  
				xlWorkbook.Close();  
				Marshal.ReleaseComObject(xlWorkbook);  
  
				//quit and release  
				xlApp.Quit();  
				Marshal.ReleaseComObject(xlApp);  
			}  
			  
			catch (Exception ex)  
			{  
				  
				MessageBox.Show(ex.ToString());  
			}  
		}  
		private DataTable ConvertRangeToDataTable() {  
			try {  
				DataTable dt = new DataTable();  
				int ColCount = filteredRange.Columns.Count;  
				int RowCount = filteredRange.Rows.Count;  
  
				for (int i = 0; i < ColCount; i++) {  
					DataColumn dc = new DataColumn();  
					dt.Columns.Add(dc);  
				}  
				for (int i = 1; i <= RowCount; i++) {  
					DataRow dr = dt.NewRow();  
					for (int j = 1; j <= ColCount; j++) { dr[j - 1] = ((Excel.Range)filteredRange.Cells[i, j]).Value2; }  
					dt.Rows.Add(dr);  
				}  
				return dt;  
			}  
			catch { return null; }  
		}  
		void ButtonExitClick(object sender, EventArgs e)  
		{  
  
			this.Close();  
		}  
	}  
}  

But when I hit the Show button, the datagridview shows only the header columns and the program freezes. I think there is a problem in my ConvertRangeToDataTable method but being new to this I cannot figure out how to solve this.

Please help...

Further, I wish to show only few of the columns from the excel after the autofilter to the datagridview, how can I do that as well?

Thanks in advance

Answer 1

The filteredRange obtained after the execution of AutoFilter has two Areas, the first is the header row, and the second is the data rows.

If we get the value directly from the filteredRange without any operation, we will only get the first one, so no matter how much data is left after filtering, you will only get the header row.

Moreover, from the picture, you seem to be filtering the State column, that is, the 10th column, and the parameter passed in your code is 4.

ConvertRangeToDataTable does need some modification to resolve those two Areas.

My modified sample code is as follows:

        private Range filteredRange = null;  
        private void button1_Click(object sender, EventArgs e)  
        {  
            Application xlApp = new Application();  
            Workbook xlWorkbook = xlApp.Workbooks.Open(@"D:\test\excel\1.xlsx");  
            Worksheet xlWorksheet = (Worksheet)xlWorkbook.Sheets[1];  
            try  
            {  
                Range xlRange = xlWorksheet.UsedRange;  
  
                //In the sample file I created, State is in the 4th column, you should use 10.  
                xlRange.AutoFilter(4, "Florida", XlAutoFilterOperator.xlFilterValues, Type.Missing, true);  
                filteredRange = xlRange.SpecialCells(XlCellType.xlCellTypeVisible, XlSpecialCellsValue.xlTextValues);  
  
                if (filteredRange != null)  
                {  
                    System.Data.DataTable dt = ConvertRangeToDataTable();  
                    if (dt != null) { dataGridView1.DataSource = dt; }  
                }  
            }  
  
            catch (Exception ex)  
            {  
                MessageBox.Show(ex.ToString());  
            }  
            finally  
            {  
                xlWorkbook.Save();  
                xlWorkbook.Close();  
                xlApp.Quit();  
            }  
        }  
        private System.Data.DataTable ConvertRangeToDataTable()  
        {  
            try  
            {  
                System.Data.DataTable dt = new System.Data.DataTable();  
                 
                Range titleRange = filteredRange.Areas[1];  
                Range valueRange = filteredRange.Areas[2];  
  
                int ColCount = valueRange.Columns.Count;  
                int RowCount = valueRange.Rows.Count;  
                for (int i = 1; i <= ColCount; i++)  
                {  
                    dt.Columns.Add(((Range)titleRange.Cells[1, i]).Value2.ToString());  
                }  
                 
                var re = valueRange.Value2;  
                for (int i = 1; i <= RowCount; i++)  
                {  
                    DataRow dr = dt.NewRow();  
                    for (int j = 1; j <= ColCount; j++) { dr[j - 1] = ((Range)valueRange.Cells[i, j]).Value2; }  
                    dt.Rows.Add(dr);  
                }  
  
                return dt;  
            }  
            catch   
            {  
                //do something here.  
                return null;  
              
            }  
        }  

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.