Importing Excel spreadsheet into DataGridView and completing search

Question

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:

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).
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
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.

https://drive.google.com/file/d/1SiFZQiz8gsHfKgGIJZmLfrbmfhQkot7B/view?usp=sharing]1

This is what the list view looks like:

score 1 · Answer 1 · edited Mar 23 '20 at 12:58

We don't really answer questions this large on SO; there's an entire app here and you're asking for advice on how to code it all from start to finish, which includes a good chunk of design work (as opposed to implementation) - a subjective or opinion based thing in itself. I'll answer so as to give some pointers for a direction you can head in, but you should aim to keep future questions quite narrowly focused.

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).

Use an OpenFileDialog to have the user select the file or hard code some logic to find it at a fixed location. Take a look on connectionstrings.com for the connection string you'll need to make the Access/JET database driver connect to the file (it can read Excel files and treat them like DBs). Carl Prothman has a usable looking block of code here - it's C# but you can either convert it to VB using an online converter or just read and understand the general flow of "make new DataTable, make new DataAdapter, set connection string and SQL string of DataAdapter to something sensible, call DataAdapter.fill" and write the VB

Read the file using a SQL query (SELECT * FROM sheetname), and use a DataAdapter to fill a DataTable with the contents of the file

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

Provide a text box on the form for the user to type in. Have a button or keypress of return initiate a search. To perform the search simply set the .Filter property of the DataView object returned by the DataTable's DefaultView property. You can find more information on what syntax the Filter property requires in the documentation for the .Expression property of a data column - sounds like an odd place to keep it but it'll make sense later!

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').

"Sort the data into fields" doesn't really make sense in the context of what I'm about to tell you, and sorting as a phrase typically refers to changing the order of rows of data rather than assigning data into columns (which is what I think you mean)

To get your data in the DataTable to show up in a DataGridView the only thing you need to do is set the .DataSource property of the DGV to equal the DataTable instance. The DGV will actually bind to the DefaultView so any filters will take effect. The DGV will automatically render the contents of the table once data binding is set up with this single line of code

All in I would expect this app would be possible to accomplish in around 10 lines of code:

'make new DataTable, possibly as a class level variable for ease 

'event handler for read_file button click
'make new DataAdapter, with connection string pointing to the excel file and SQL string to select the data
'fill DataTable
'assign data source of DataGridView, to filled DataTable


'event handler for filter button click
'set .filter property of class level DataTable.DefaultView property

I appreciate this is a lot to ask for, but I really need some example code. — Josh Face, Mar 14 '20 at 22:22
@JoshFace which line of the final listing are you not able to accomplish? — Mary, Mar 16 '20 at 07:51
@Mary All of it to be honest. I could do it but I don't know how to import the excel file and then use the data. I mostly know the code for the search. — Josh Face, Mar 16 '20 at 20:26
To be fair, I DID link to another SO question where the guy is reading from an Excel file using a db driver (see the bit in my answer where I mention Carl Prothman. Here's another example I found along the same lines: https://stackoverflow.com/questions/18511576/reading-excel-file-using-oledb-data-provider — Caius Jard, Mar 16 '20 at 20:29
Thing is, you've rocked up and said "I need an app that does X, giv me teh codez" and we aren't a code writing service - there are hire-a-freelancer sites for that. Ordinarily questions as broad as you've asked are just closed outright as "too broad" but I figured instead I'd put some time to guiding you on how to design an app to meet your needs, including how to sketch out the algorithms. I did this because though the question is broad, it can be done in a few lines of code and it represents a great learning oppo for you to get to grips with db drivers, data binding, ui design etc. Have a go — Caius Jard, Mar 16 '20 at 20:36
@CaiusJard The question you've linked to is C# - how can I use that? I'm fine with writing search algorithm code, but I can't get a thing to work with regards to importing the data from the Excel sheet and having it be 'usable'. — Josh Face, Mar 16 '20 at 21:15
@JoshFace Do you know what to do with the first line? "make new datatable, possibly as a class level variable for ease " — Mary, Mar 16 '20 at 22:07
You can use it by reading it and reimplementing it in vb or (as I wrote in the answer) use a converter to turn the C# into VB. Ultimately VB and C# aren't very far apart, just a few keywords and symbols really: `Dim s As String = "hello " & "world"` vs `string s = "hello " + world";` or `Dim x = sqlCommand.ExecuteNonQuery()` vs `var x = sqlCommand.ExecuteNonQuery();`. — Caius Jard, Mar 16 '20 at 23:38
@CaiusJard Tried the converter and it throws an error. Tried converting other code, using other code and nothing works. — Josh Face, Mar 17 '20 at 12:24
It still feels like you're approaching this like you just want to click a button and have everything done for you.. https://codeconverter.icsharpcode.net/ worked just fine to convert the code in the question linked in the comments above, to VB - and that code looks sensible: use an IF to choose one of two connection strings, create a SqlCOnnection, a SqlCOmmand, pass them to DataAdapter and fill a new DataTable. None of this is particularly difficult C# and it doesn't need a converter, you can use READ the C# `if(fileExtension == ".xls")` and IMAGINE the VB `If(fileExtension = ".xls") Then`..! — Caius Jard, Mar 17 '20 at 14:05
In your case, you don't even need the IF if you're not dealing with XLS/XLSX, you're just having to make new objects in a `Using` which you read in C# as `using (OleDbConnection conn = new OleDbConnection()){` and write in VB as `Using conn As OleDbConnection = New OleDbConnection()` - they're virtually identical, and no-one is going to take you seriously if you say "i can't possibly read and understand that code - it's C# and I can only read VB" - you only need to be able to read just enough of it to understand what you need to do in VB — Caius Jard, Mar 17 '20 at 14:10

Importing Excel spreadsheet into DataGridView and completing search

1 Answers1