Importing Excel spreadsheet into DataGridView and completing search

[joshface98]

I am converting an old VBA application to VB.NET. This application contains several tools, and I need some help converting one of them over.

Essentially, there is an Excel spreadsheet full of customer information such as the customer's company name, model number, serial number, warranty end date, plan end date, city/country, alt company name and comments.

The tool has a list view control with all the matching columns, same as the Excel spreadsheet, and there is a text box in which the user can type some search criteria, such as a serial number or company name. Any matching results from the Excel spreadsheet are put into the list view with the data from the columns of the spreadsheet, sorted into the correct matching column for the list view control.

The current VBA tool is easily able to import this data into the form as it (the form) has easy access to the spreadsheet data - however; in my case, with my program being external to Excel and VBA - I can't do this without importing the data first.

So my question is:

1. How do I import an excel spreadsheet into my VB.NET form to use the data (this file is always in the exact same directory on every machine so a file browse dialog is not necessary, just storing the directory of the Excel spreadsheet should do fine).
2. How do I make it/what would be some appropriate code that allows the user to enter a search criteria, and populate the list view with matching results
3. When the user completes a search and the list view is populated with matching data from the Excel spreadsheet, how do I sort the data into the fields (sort the data in the columns in Excel into the columns in VB.NET list view, so matching result in Company column from Excel would go into a list view column named 'Company').

I have attached an example of the Excel spreadsheet which contains the data.

Data.xlsx]1

 

[jmcilhinney]

There are three main options for reading Excel data into a VB application:

1. Office Automation: Effectively remote control an instance of the Excel application.
2. ADO.NET: Treat the Excel file as a database and read data in pretty much the same way you would for an Access data file.
3. Open XML: The Open XML SDK allows you to work directly with new-format Office, including DOCX and XLSX, which are XML inside ZIP.

Your first task is to decide which of these methods to use. The first requires Excel to be installed while the second requires the ACE OLE DB provider to be installed. If I'm not mistaken, the third only requires a NuGet package to be added to your project. Note that Excel can be very lax with data types so using ADO.NET can sometimes have issues, e.g. you may get dates as numbers and have to convert locally.

Once you've decided how you're going to read it, you need to decide what you're going to read it into. You might choose a DataTable, which is the obvious choice for ADO.NET and would still work for the other options, but you may also prefer your own dedicated types. Once you have an appropriate list of objects, you simply bind it to your DataGridView, just as in any other case.