My main goal is to simplify the opening of pipe delimited data files in excel that need columns imported as “Text”. I wanted my method or console app to open excel and import all the data for me.

First you’ll need to add the proper imports. Add to the references of your project Microsoft.Office.Interop.Excel and add this code to the top of your application:

using Excel = Microsoft.Office.Interop.Excel;

Then instantiate your excel application object and define a basic missing value to fill in the parameters we won’t be needing for this app:

Excel.Application excelApp = new Excel.Application();

var missingValue = System.Reflection.Missing.Value;

While there are lots of examples out there using excelApp.Workbooks.Open(), I couldn’t find one using excelApp.Workbooks.OpenText that was properly converting columns into the value you need. For my example we’re using text.

The next thing we’ll do is setup the multidimensional array that Microsoft expects you to pass into OpenText to properly convert each column.

int[,] fieldInfoArray = new int[800,2];

for (int i = 1; i <= 800; i++)
{
 fieldInfoArray[i – 1, 0] = i;
 fieldInfoArray[i – 1, 1] = 2;
}

This generates an array of 800 rows that will represent the first 800 columns in an excel spreadsheet. Each row has 2 columns for that array. The first is the column number, and the second is the XIColumnDataType as an int. This can be replaced with any of the following values:

1=xlGeneralFormat General

2=xlTextFormat Text

3=xlMDYFormat MDY date

4=xlDMYFormat DMY date

5=xlYMDFormat YMD date

6=xlMYDFormat MYD date

7=xlDYMFormat DYM date

8=xlYDMFormat YDM date

9=xlEMDFormat EMD date

10=xlSkipColumn Skip Column

For me, this was the hardest to setup. I felt most articles didn’t set it up properly or the conversion of the columns to a specific data type was completely left out. In my experience the order doesn’t matter so long as the proper column number is used in the first value of the array. You don’t really have to create a loop like I did if you know exactly what columns are coming across, that was just because the data I’m using can have up to 800 columns.

Next we’ll set the excel app to visible so the user can see it.

excelApp.Visible = true;

Lastly, we’ll make the call to open the text file:

try
{
excelApp.Workbooks.OpenText(workbookPath, missingValue, missingValue,
 Excel.XlTextParsingType.xlDelimited,              Excel.XlTextQualifier.xlTextQualifierNone,
 missingValue, missingValue, missingValue, missingValue, missingValue,
 true, “|”, fieldInfoArray);
}
catch (Exception)
{
 excelApp.Quit();
}

Here we’re passing in our parameters then properly quitting the app in case there’s an error so the user isn’t left with hanging processes. I’ve noticed that if you don’t make sure to quit them you’ll see lots of excel.exe processes in the task manager and no excel documents open to close them. You’ll have to force close them and user’s aren’t going to tolerate that :).

For more specific details about each of the parameters needed for this I recommend the msdn article found here.

Thanks and Hope this helps someone…