CODEWITHSUNDEEP
c#excelwindows-services
Ian
Ian

Reputation: 34489

Reading Excel Files as a Server Process

I'm trying to find an appropriate way to read the contents of an Excel file on an NT server operating system. I have numerous problems using the Excel API and then came across the official Microsoft on Office Automation which states that the Excel API is not suitable for Excel automation. The sorts issues that I saw were similar to those described in the article.

Is there another way that I can read an Excel file (xls, xlsx, xlsm) on a server (no UI) in such a way that doesn't suffer the same sort of threading/security/license issues imposed within the Excel API?

Upvotes: 11

Views: 9324

Answers (8)

Jack7
Jack7

Reputation: 1341

I think for the benefit of this thread I have to mention EPPlus. Its an opensource project available in codeplex.

I've used this in multiple projects and never had issues with it.

Upvotes: 1

Ian
Ian

Reputation: 34489

There were a number of libraries that were highlighted by different users that would allow the sort of functionality required. I've listed them here and some of these were evaluated so where appropriate I've tried to put down interesting comments for comparing them. The details I've included are completely opinion based, however any of these libraries would probably achieve the required goal.

SpreadsheetGear.Net
(Didn't evaluate due to high purchase cost)

Aspose.Cells
(Evaluated by a collegue. Appeared to be fairly simple to implement, performance comparable to Excel Interop).

GemBox
(Didn't evaluate)

Excel Services
(Seems only to be included in SharePoint 2007)

Excel Mapper (Didn't evaluate because it requires strongly typed objects to import into which didn't fit my requirement).

SmartXls
(Didn't evaluate because it requires strongly typed objects to import into which didn't fit my requirement).

ActiveXls
(Fairly easy to use, lack of Properties raises questions, they have a preference of Methods for trivial actions. Despite it's claim of 1M records a second was out performed by cheaper FlexCel. Have decided that the help/API manual is almost useless.)

Koogra
(Didn't evaluate due to finding no documentations/information)

FileHelpers
(Didn't evaluate)

Flexcel
(Lowest cost solution found, good performance and was simple to implement with a close proximity to Excel Interop structure. Also received quick response to technical question from support. Probably my pick of the bunch.)

SyncFusion BackOffice
(Medium cost and had a reasonable structure. Unfortunately had more difficulty implementing and inconsistent results when running unit tests. Also received a number of 'Attempted to read protected memory' errors, which didn't encourage me with purely managed library.)

Upvotes: 20

Mark Nold
Mark Nold

Reputation: 5698

To read Excel files with C# without installing Excel you could use Apache's POI.

It's a java library for reading and writing MS Office formats. Since it's java and you're using C# you'll need IKVM and the java classes from the POI Project.

However, the easiest way is to just download Jon Iles excelent MPXJ project and you've got it all. Just set a reference to IKVM.OpendJDK.ClassLibrary.dll, IKVM.Runtime.dll, poi-3.2-FINAL-20081019.dll

I've hacked together a quick console app to show you an simple way to read an Excel .xls sheet. It only reads the first sheet and doesn't use the row or cell iterators, but it does the job well.

NB: You'll need to look at org.apache.poi.ss to make sure you can read .xlsx etc files. 

//C# code for using the Apache POI libraries
using System;
using System.Collections.Generic;
using System.Text;


// poi for xls
using org.apache.poi;
using org.apache.poi.poifs;
using org.apache.poi.poifs.filesystem;
using org.apache.poi.hssf;
using org.apache.poi.hssf.usermodel;
using org.apache.poi.ss;

namespace ConsoleApplication1
{
    class Test
    {
        static void Main(string[] args)
        {

            if (args.Length != 1)
            {
                Console.Out.WriteLine("Usage: XLSReadTest <xls file>");
            }
            else
            {
                XLSRead x = new XLSRead();
                x.Process(args[0]);
                //x.Process("c:\\temp\\testfile.xls");
            }




        }
    }


    class XLSRead
    {
        public void Process(string inputFile)
        {


            int r = 0;


            Console.Out.WriteLine("Reading input file started.");
            DateTime start = DateTime.Now;

            java.io.InputStream inputStream = new java.io.FileInputStream(inputFile);
            POIFSFileSystem fs = new POIFSFileSystem(inputStream);

            HSSFWorkbook wb = new HSSFWorkbook(fs);
            HSSFSheet sh = wb.getSheetAt(0);


            r = sh.getFirstRowNum();
            while (r <= sh.getLastRowNum())
            {
                HSSFRow row = sh.getRow(r);
                int c = row.getFirstCellNum();
                string val = "";

                while (c < row.getLastCellNum())
                {
                    HSSFCell cell = row.getCell(c);
                    switch(cell.getCellType())
                    {
                      case HSSFCell.CELL_TYPE_NUMERIC:
                          val = cell.getNumericCellValue().ToString();
                          break;
                      case HSSFCell.CELL_TYPE_STRING:
                          val = cell.getStringCellValue();
                          break;
                    }
                    Console.Out.WriteLine("Row: " + r + ", Cell: " + c + " = " + val);
                    c++;
                }
                r++;
            }

            long elapsed = DateTime.Now.Ticks - start.Ticks;
            String seconds = String.Format("{0:n}", elapsed / 1000000);
            Console.Out.WriteLine("\r\n\r\nReading input file completed in " + seconds + "s." + "\r\n");



        }
    }
}

Upvotes: 2

liya
liya

Reputation: 792

Had you tried SmartXLS for .net?

or ActiveXLS?

Upvotes: 1

anonymous
anonymous

Reputation:

Here is something that can read .xls and xlsx files. It is open source. http://code.google.com/p/excelmapper/.

It is basically an ORM, so you have to just deal with objects instead of OleDB.

Upvotes: 1

Jeff
Jeff

Reputation: 2248

I have used ADO.NET get data out of an xls before. I'm not sure all the Excel Doc types this would support but check out Reading and Writing Excel Spreadsheets Using ADO.NET C# DbProviderFactory

Here is some code from a SO question as well.

I was able to read and write to excel without having to install Office or any 3rd party tools.

Upvotes: 3

Scott Weinstein
Scott Weinstein

Reputation: 19117

Excel.

After of years of trying to stop folks from using Excel on the server, they've given up/embraced the market need and have started to support this.

2007 has some impovement for this, and Excel 2010 is supposed to have even more.

Upvotes: 1

shahkalpesh
shahkalpesh

Reputation: 33476

I am not sure why the ad on SO is not appearing in relevance to your question.

There is something similar called Excel Services from microsoft. I suppose, this lets you use Excel in a webservice style. I think SpreadSheetGear is what microsoft used, as the ad used to display. :)

Upvotes: 1

Related Questions