Read a .txt file and export to Excel using vb.net

[Techno]

true however if the business requirement, especially from a customer, is to export against a certain file type then because it maybe be "hard" to do so doesnt mean nor is it acceptable to say "its too hard, sorry, we are going to do it against a different file format".
just because its hard doesnt mean it should not be done nor investigated. it just doesnt work like that
furthermore, reading more and understanding more about using the PIA's against Word/Excel is what is required due to, as you have pointed out, the difficulty and given the fact that its quite complex.

there are 3rd party tools which do make it easier to export into Excel and should also be considered.

[kareninstructor]

If you are going to write for multiple versions of Excel (Office 2000, Office 2003, Office 2007) with no cell formatting CSV makes sense. If there is formatting involved for multiple versions of Excel then late binding makes sense.

An alternative for multiple versions with cell formatting using VS2010 (new to VS2010) is embedding multiple versions of Excel Interop into your application, which gives you the benefit of early binding which means tighter code.

My preference is working with a third party library called Aspose Cells which does not need Excel installed on the client machine and has the capabilities of Excel and then some. If interested Google Aspose Cells, download and try it out. The evaluation version has no restrictions other than it places footer at the bottom of each sheet indicating it is an evaluation. Once purchase a license file makes it a non-evaluation product. My preference for “simple”, no formatting would be either having Excel read a CSV file or using OleDb methods (see below).

You should also consider Shaggy Hiker’s code bank class also.

Working just with Office 2007 with cell formatting then there is OpemXML SDK from Microsoft, which as with Aspose library does not require Excel to be installed. The downside to OpenXML SDK is it does virtually nothing for you, instead you have full control with no limits in what you can do but comes with the price of you need to code everything. There is a free tool, which takes a workbook and writes code in C Sharp, which you can study then reproduce what you want. I have a code bank submission, which shows the basics. Just understand you cannot simply jump in and code but need to take time to understand the SDK.

OleDb data provider can write unformatted data to Excel using SQL insert statements to sheets and named ranges. If you need say column headers then create the column headers in an existing Excel file, place this file in a template folder. When ready to use it copy the template file to the location to write data too, write the data using OleDbConnection and OleDbCommand.

In regards to reading your text file there is TextFieldParser (Google this class) class in the .NET Framework which makes reading common delimited text files easy.

No matter which direction you decide on it would be best to work with this method outside of your project, get a feel for it with a small amount of data then a large data set so that if you run into roadblocks you are not dealing with surrounding code to solve the roadblock then move back to your project and code as needed.