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…

 

Over the last few weeks I’ve been in the mood for a bit of reminiscent gaming. Specifically…Everquest. So I downloaded EQ Titanium, followed the walkthrough on project1999.com and created TimTheEnchanter.

The startup and character screen music brought back an avalanche of memories. I really had no idea how much I actually missed the original game…the original meaning not the bloated monster that’s currently out there for mostly free by Daybreak or whoever.

The game didn’t load in full screen but I quickly found that Alt+Enter would fix that. The load screen didn’t have a display option for 1920 x 1080 so I edited the eqclient.ini in the install directory so it was a little closer to fitting, but sometimes the page would still bleed off the edge of the screen.

I finally found under “options” while ingame, you could click “display” tab, then click the full screen button, then adjust the display to 1920 x 1080 on the button right next to it and this will realign your screen so that everything fits nicely. Unfortunately, if you alt-tab you have to do this all over again. I’m still searching for a better workaround but until I find one…I guess it works. Hope this helps someone else fiddling with display on EQ.

Cheers…