reading excel server-side

Question

I need to develop a service and install it into a w2003 box to read excel files a then process its info. The process is as follows, users will upload excel files using FTP and then my service must take those files, validate and then update a SQL Server DB.

The application runs fine on my computer but on the server it asks for the libraries but when I try to install the MS office 2003 Primary Interop Assemblies, system displays "Please install Microsoft Office 2003 before installing the product".

I'd prefer to stay away of any server upgrade as we should require OKs, etc.. so, is there a simple way to just read excel files without having to install any update in the server.

any comments are welcome.

Thanks, m0dest0.

ps. using vb.net and vs 2008.

I think you will need excel installed on the server machine if you are manipulating excel files using the Excel Object Library. — Matt Wilko, Oct 18 '11 at 16:31
If you're allowing users to upload files, are you limiting the version to only Office 2003? What if the user has a newer version of Office installed? — zeroef, Oct 18 '11 at 16:44
@zeroef, you are right, I must keep an eye on the excel versions to be supported, I wonder if the component mentioned below, Flexcel is able to handle any version. Thanks — m0dest0, Oct 18 '11 at 17:06

score 2 · Accepted Answer · answered Oct 18 '11 at 16:35

2

Using Interop on the server is NOT supported by MS - see http://support.microsoft.com/default.aspx?scid=kb;EN-US;q257757#kb2

Since Windows Vista MS introduced several security-related measures which prevent a Windows Service from doing "desktop-like" things... which means you would have to circumvent several security measures to get it to work (NOT recommended!).

To deal with Excel in a server-scenario there are several options (free and commercial) out there:

I can recommend Aspose.Cells and Flexcel... didn't try SpreadsheetGear but hear+read lots of good things about it...

Free options (though for the newer xlsx format only!) are for example OpenXML 2 from MS and EPPlus.

answered Oct 18 '11 at 16:35

Yahia

69,653
9
115
144

+1 for OpenXML - It's a little difficult to find great examples out there, but once you get some things setup you can do amazing things with it. – zeroef Oct 18 '11 at 16:37
@zeroef true, but the OP wrote Excel 2003 which means XLS not XLSX so using it depends on whether the OP can switch to the newer Excel format... the (commercial) libraries I mentioned can do both whic is whyt I included them... – Yahia Oct 18 '11 at 16:40
I must have glanced over that. Good catch – zeroef Oct 18 '11 at 16:43
@Yahia, seems that I will evaluate Flexcel, thanks a lot for tip!! Ps. I was a delphi programmer some years ago and TSM is a good company. – m0dest0 Oct 18 '11 at 17:37

dmarietta · Answer 2 · 2011-10-18T18:45:25.413

For a solution with nothing to install on any recent versions of Windows Server..... I'm not sure the exact VB.NET code, but you should easily be able to do this on any machine using the Microsoft OLEDB drivers that should be available on any recent version of windows server or can be installed from a free download off of the Microsoft website for very old versions of windows server. I'll try to pseudo code this, so you will have to adapt it for VB.NET. Note that in order to reference your fields by name, the first row of the selected area in the worksheet must contain the fieldnames in the column values. Otherwise you will simply have to use numeric values to index each returned field by column position.

Set objExcelConnection = CreateObject("ADODB.Connection")
objExcelConnection.Provider = "Microsoft.Jet.OLEDB.4.0"
objExcelConnection.ConnectionString = "Data Source=d:\path\to\excel\file\on\your\server.xls;Extended Properties=""Excel 8.0;IMEX=1;"";"
objExcelConnection.CursorLocation = 3
objExcelConnection.Open


sSQL = "select * from [worksheetname$]"
set rsWorksheet = objExcelConnection.Execute(sSQL)
do while not rsWorksheet.Eof
    sValue = rsWorksheet("FieldName")
    rsWorksheet.MoveNext
loop
rsWorksheet.Close

set objExcelConnection = nothing

score 0 · Answer 3 · answered Oct 18 '11 at 19:10

I use the Excel Data Reader (http://exceldatareader.codeplex.com/) when I need to process Excel files. It handles xls and xlsx files without a hitch, and I've got it running in a few applications on a server OS. It saves each sheet as a DataTable object, and each "cell" in the DataTable corresponds to the Excel cell with the same address. Depending on how you set up your SQL server link, there might not be too much conversion required to dump the contents into the DB.

reading excel server-side

3 Answers3