How to Join data from different Excel Files

Question

I have 3 Excel files to manipulate and I want to generate a single Excel file based on them. I need to join the third column of the first file with the first column of the second and third Excel file. What would be the best way to do it in C#.

Answer 1

Believe it or not, you may don't have a big project on your hand. If you use Microsoft.Ace.OleDb provider to connect to your excel, you will be on your way to success. Check this To create a new file we simply provide the name of a non existent file in the connection string. This will create a new file.

So, you can create and read Excel files with provider above. This is thin, nothing like interop, and can be safely used on server, like web server.

Since you know how to use your provider [it is literally just like DB], the logic below is - how to join data

1. Read each sheet into a single DataTable of a DataSet
2. Create relation between data tables
3. Issue joined select
4. Write your resulted row collection into your new Excel file (the link on top explains creation of file and structures in it using provider)