How can I search through an Excel sheet in a C# GUI?

Question

I have this code

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace TabeleExcel
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void cboSheet_SelectedIndexChanged(object sender, EventArgs e)
        {
            DataTable dt = tableCollection[cboSheet.SelectedItem.ToString()];
            dataGridView1.DataSource = dt;
        }

        DataTableCollection tableCollection;

        private void btnBrowse_Click(object sender, EventArgs e)
        {
            using(OpenFileDialog openFileDialog=new OpenFileDialog() { Filter="Excel|*.xls|Excel|*.xlsx" })
            {
                if(openFileDialog.ShowDialog()==DialogResult.OK)
                {
                    txtFilename.Text = openFileDialog.FileName;
                    using(var stream=File.Open(openFileDialog.FileName, FileMode.Open, FileAccess.Read))
                    {
                        using (IExcelDataReader reader = ExcelReaderFactory.CreateReader(stream))
                        {
                            DataSet result = reader.AsDataSet(new ExcelDataSetConfiguration()
                            {
                                ConfigureDataTable=(_)=>new ExcelDataTableConfiguration() { UseHeaderRow=true }
                            });
                            tableCollection = result.Tables;
                            cboSheet.Items.Clear();
                            foreach (DataTable table in tableCollection)
                                cboSheet.Items.Add(table.TableName);
                        }
                    }
                }
            }
        }

        private void textBox1_TextChanged(object sender, EventArgs e)
        {


        }
    }
}

The code makes a GUI in .NET framework and it lets you upload an .xls or .xlsx, the Excel file will be shown as a DataGrid view. I implemented a new textbox ( the textBox1_TextChanged(object sender, EventArgs e) method), and based on the user's input, I want to it to display only the rows that correspond with the input. How am I supposed to do that? Thanks in advance!

Answer 1

You have 3 options for working with office formats:

• if you only need to support the new formats (.xslx) you can use the OpenXML SDK, any of the wrappers people wrote for it. Or even the ZipArchive and XMLReader classes. It is well known and easily processed
• If you need to support the old format as well (.xls) you have to use the (t)rusty Office COM interop. That one has the usualy issues of COM interop, plus some unique problems. Like requiring a Interactive Session due to poor design choices.
• For any given problem, any given file format, any given GUI technology there might be another way. But those are far and few in between

My standing advice is to use the OpenXML way and learn to live without the old formats altogether. They are way more trouble to support then it is worth.