How to read an excel file in C# without using Microsoft.Office.Interop.Excel libraries

Solution 1:

I highly recommend CSharpJExcel for reading Excel 97-2003 files (xls) and ExcelPackage for reading Excel 2007/2010 files (Office Open XML format, xlsx).

They both work perfectly. They have absolutely no dependency on anything.

Sample using CSharpJExcel:

Workbook workbook = Workbook.getWorkbook(new System.IO.FileInfo(fileName));
var sheet = workbook.getSheet(0);
...
var content = sheet.getCell(colIndex, rowIndex).getContents();
...
workbook.close();

Sample using ExcelPackage:

using (ExcelPackage xlPackage = new ExcelPackage(existingFile))
{
  // get the first worksheet in the workbook
  ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets[1];
  int iCol = 2;  // the column to read

  // output the data in column 2
  for (int iRow = 1; iRow < 6; iRow++)
    Console.WriteLine("Cell({0},{1}).Value={2}", iRow, iCol, 
      worksheet.Cell(iRow, iCol).Value);

  // output the formula in row 6
  Console.WriteLine("Cell({0},{1}).Formula={2}", 6, iCol, 
    worksheet.Cell(6, iCol).Formula);

} // the using statement calls Dispose() which closes the package.

EDIT:

There is another project, ExcelDataReader, that seems to have the ability to handle both formats. It is also easy like the other ones I've mentioned.

There are also other libraries:

• NPOI: Port of the Apache POI library to .NET:
  Very powerfull, free, and open source. In addition to Excel (97-2010) it also supports Word and PowerPoint files.

• ExcelLibrary:
  It only support Excel 97-2003 (xls) files.

• EPPlus:
  An extension to ExcelPackage. Easier to use (I guess).

Solution 2:

var fileName = @"C:\ExcelFile.xlsx";
var connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text\""; ;
using (var conn = new OleDbConnection(connectionString))
{
    conn.Open();

    var sheets = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
    using (var cmd = conn.CreateCommand())
    {
        cmd.CommandText = "SELECT * FROM [" + sheets.Rows[0]["TABLE_NAME"].ToString() + "] ";

        var adapter = new OleDbDataAdapter(cmd);
        var ds = new DataSet();
        adapter.Fill(ds);
    }
}

Solution 3:

I would urge against using OleDB, especially if its going to be run on a server. Its likely to cost you more in the long run - eg we had a SSIS job calling a Stored Procedure with the OleDB reading an excel file in the sptroc and kept crashing the SQL box! I took the OleDB stuff out of the sproc and it stopped crashing the server.

A better method I've found is to do it with Office 2003 and the XML files - in respect of Considerations for server-side Automation of Office. Note: Office 2003 is a minimum requirement for this to fly:

Ref for reading from Excel: http://www.roelvanlisdonk.nl/?p=924 (please do more research to find other examples)

Ref for writing a Excel spreadsheet: http://weblogs.asp.net/jgaylord/archive/2008/08/11/use-linq-to-xml-to-generate-excel-documents.aspx

public void ReadExcelCellTest()
        {
            XDocument document = XDocument.Load(@"C:\BDATA\Cars.xml");
            XNamespace workbookNameSpace = @"urn:schemas-microsoft-com:office:spreadsheet";

            // Get worksheet
            var query = from w in document.Elements(workbookNameSpace + "Workbook").Elements(workbookNameSpace + "Worksheet")
                        where w.Attribute(workbookNameSpace + "Name").Value.Equals("Settings")
                        select w;
            List<XElement> foundWoksheets = query.ToList<XElement>();
            if (foundWoksheets.Count() <= 0) { throw new ApplicationException("Worksheet Settings could not be found"); }
            XElement worksheet = query.ToList<XElement>()[0];

            // Get the row for "Seat"
            query = from d in worksheet.Elements(workbookNameSpace + "Table").Elements(workbookNameSpace + "Row").Elements(workbookNameSpace + "Cell").Elements(workbookNameSpace + "Data")
                    where d.Value.Equals("Seat")
                    select d;
            List<XElement> foundData = query.ToList<XElement>();
            if (foundData.Count() <= 0) { throw new ApplicationException("Row 'Seat' could not be found"); }
            XElement row = query.ToList<XElement>()[0].Parent.Parent;

            // Get value cell of Etl_SPIImportLocation_ImportPath setting
            XElement cell = row.Elements().ToList<XElement>()[1];

            // Get the value "Leon"
            string cellValue = cell.Elements(workbookNameSpace + "Data").ToList<XElement>()[0].Value;

            Console.WriteLine(cellValue);
        }

Solution 4:

I recently found this library that converts an Excel workbook file into a DataSet: Excel Data Reader