For this round of "interesting" web development tasks, I was asked to allow
ConTrak Systems to upload and subsequently import data from MS Excel workbooks.
That sounds easy enough, right? To put it simply, I use my uploader
control, save the file to a temporary location, open it, and read in the data
like a database. Well... It's not so easy.
To open the MS Excel file like a database, I need to know the table names.
And if the Excel workbook comes from end users, I can never be certain of how
worksheets are named. So how does one go about obtaining a list of
worksheet names? I created a Windows application a month ago that
would read any MS Excel file and display worksheet contents in a grid.
That allowed me some experience at handling the MS Office / Excel Interop
library. This library allowed me to open MS Excel files, read the
worksheet names and even read worksheet contents. That sounds promising.
My code to access an MS Excel application began with the following lines of
code:
protected string[] DefineSheets(string theFilePath)
{
Microsoft.Office.Interop.Excel.Application xlApp =
new
Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel._Workbook book =
(Microsoft.Office.Interop.Excel._Workbook)
(xlApp.Workbooks.Add(theFilePath));
Microsoft.Office.Interop.Excel.Sheets sheets =
(Microsoft.Office.Interop.Excel.Sheets)book.Worksheets;
string[] sheetnames = new string[sheets.Count];
for (int x=1; x<=sheets.Count; x++)
{
//Base-1 item:
Microsoft.Office.Interop.Excel._Worksheet sheet =
(Microsoft.Office.Interop.Excel._Worksheet)
(sheets.get_Item(x));
sheetnames[x-1] = (sheet.Name);
}
xlApp.Quit();
return sheetnames;
}
|
To sum it up, this method executes MS Excel (first line), opens the
workbook/file (second), and adds all the workbook's worksheets to the variable
"sheets". From there, the "for loop" will extract each sheet's name and
place it in an array. I close the Excel application after that and return
the discovered sheet names.
From here, I can easily open the Excel workbook as a database and dump
everything into a DataSet or DataReader for easier processing and integration
with my ConTrak class library (especially the newer, smarter classes I have
designed on top of the "old" architecture of July-October).
However, it's not that easy. I had to make a concession to run the ASP
.Net application in what's called "impersonation" mode. The mode had to be
initiated so ASP .Net could access the MS Excel application as a standard user
account on the server. This encouraged some problems with the main MS
Access database that runs ConTrak because when ASP .Net impersonates a normal
user it also foregoes its privileged user account. That's not even the
main problem; the entire application hangs once the first line of code in this
method executes. A quick look at Task Manager reveals MS Excel is indeed
running in the background.
The conclusion reached is that I cannot open an Excel application while the
MS Office Interop is being used in an ASP .Net application. Since I cannot
install custom applications on the server that employs ConTrak, I cannot adapt
my Windows application to watch for and interface via web service to ConTrak
with the goal of sending worksheet names. Instead, I had to require users
to name the worksheet before uploading the Excel file. Imposing anything
on end users is never a good thing because they will object--and badly at that.
However, this is one concession I cannot make.
