how to import & export Excel files in VB.Net

[silverblatt]

I'm developing a WinForms desktop application using VB.Net, and one of the features I want to include is the ability to import from, and export to, Excel (both .xls and .xlsx), .csv and .psv files. When I had to implement similar functionality at my last job, it was usually pretty straightforward, but I occasionally ran into issues where the code worked properly on my development machine but not on an end user's machine. My recollection is that the problem SEEMED to be due to either the two machines having different versions of Office installed, or perhaps the end user's machine not having ANY version of Office installed. The application I'm building is for non-technical home users with no IT support, so it needs to just work. My questions are:
1. What's the best approach to implementing the Excel import/export functions to ensure that those functions work reliably regardless of which version of Excel the end user has installed (or even if they don't have Excel installed at all). Specifically, I'm asking which reference(s) I need to add to the project. My web research indicates that most folks are using various versions of the Microsoft Excel Object Library or Microsoft.Office.Interop.Excel.
2. Depending on the answer to question 1., I may need to install something else on my development machine, because the latest version of the Excel Object Library I have showing in my References window is 11.0, and I don't have any version of Microsoft.Office.Interop.Excel. So, if I do need to install something else, what is it and where can I download it from?

Thanks in advance for any assistance you can give.

[si_the_geek]

In order for your code to use .xls files the user must have Excel installed, as it is a proprietary file format. You might be able to make it work for multiple versions of Excel (to allow whichever the user has installed to work), but you might find that some versions (particularly the modern Office 365 version) doesn't work for you.

.xlsx and .csv (and probably .psv) are public formats, so there are lots of libraries you can use to work with those file types. I recommend looking at which NuGet packages are available, and picking whichever one(s) you think seem good.

[silverblatt]

Thanks, si_the_geek. You mentioned that the code would "use" .xls files. Just to be clear, I'm only talking about importing data from a pre-existing .xls file, without modifying it in any way, and exporting data to a new Excel file without any subsequent modification or other use of it. So, do I understand you correctly that even those limited actions cannot be performed by a VB.Net application unless Excel is installed on the system?

Also, regarding the NuGet packages for the .xlsx, .csv and .psv formats: do you recommend any specific package recommendations, or have any suggestions about how I might determine which one(s) seem good? I have virtually no experience selecting or working with NuGet packages.

[si_the_geek]

So, do I understand you correctly that even those limited actions cannot be performed by a VB.Net application unless Excel is installed on the system?

That is correct I'm afraid.

If you use Late Binding you should be able to use whichever version the user has installed (for most versions anyway), so for the people most likely to want it you can provide the functionality... but if you are providing .xlsx support (which has been available in Excel for over 15 years now), it probably isn't worth the effort to enable .xls files too.

Also, regarding the NuGet packages for the .xlsx, .csv and .psv formats: do you recommend any specific package recommendations, or have any suggestions about how I might determine which one(s) seem good? I have virtually no experience selecting or working with NuGet packages.

I don't know of any packages for this, but if you search within NuGet you can find ones that seem good based on description and ratings etc, and if you want you can do a web search to check if a package is popular/good.

[wes4dbt]

Thanks, si_the_geek. You mentioned that the code would "use" .xls files. Just to be clear, I'm only talking about importing data from a pre-existing .xls file, without modifying it in any way, and exporting data to a new Excel file without any subsequent modification or other use of it. So, do I understand you correctly that even those limited actions cannot be performed by a VB.Net application unless Excel is installed on the system?

Also, regarding the NuGet packages for the .xlsx, .csv and .psv formats: do you recommend any specific package recommendations, or have any suggestions about how I might determine which one(s) seem good? I have virtually no experience selecting or working with NuGet packages.

You can use OLEDB to read and write Excel files. Check this out,

https://www.codingame.com/playground...ithout-interop

Google something like "can oledb create lxs file" theres lots of examples.

[si_the_geek]

That's a good point, I'd managed to forget about that option.

It should be noted however that it only allows you to read/write the data (not the formatting etc), and that it only works if the data is in a 'table' format.

[techgnome]

Plenty of libraries out there...

-tg

[silverblatt]

I found some sample code using OLEDB, and got the import/export working for both .xls and .xlsx files. OLEDB 4.0 supports only .xls, while OLEDB 12.0 supports both .xls and .xlsx. And the .csv and .psv files can just be processed as text files, so I'm good to go. Thanks to all for your kind assistance.