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

Question

I have a .Net-Windows application in C#. I need to open an excel and process it. How can I do this without using Microsoft.Office.Interop.Excel libraries?

Mohammad Dehghan · Answer 1 · 2018-12-14T21:06:47.243

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).

This ExcelDataReader is awesome and ridiculously faster than OLEDB. — c-sharp, Feb 29 '16 at 02:12
ExcelDataReader is migrating to GitHub : https://github.com/ExcelDataReader/ExcelDataReader — Dude Pascalou, May 16 '17 at 07:30

score 49 · Accepted Answer · answered Feb 06 '12 at 05:04

49

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);
    }
}

answered Feb 06 '12 at 05:04

Aleksandar Vucetic

14,715
9
53
56

3

Note- OLEDB doesn't work when your sheet is password protected. – Sangram Nandkhile Jul 04 '13 at 07:01
@Aleksandar , does this solution also works on linux? – Dalton Cézane Mar 07 '18 at 21:54
What is the namespace required ? – Lorenzo Bassetti Oct 23 '21 at 17:13
When using this, careful with the Platform. If you have Office x64 providers installed, your application can only run as x64. the same with 32-bit. If you have office 32-bit providers, you application can only run as 32-bit (x86). – midspace Oct 25 '22 at 02:54
Does this require Excel Application to be installed on the machine? – justanotherguy Apr 04 '23 at 08:26

score 3 · Answer 3 · answered Feb 06 '12 at 04:58

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);
        }

It's not so much OleDB that's a risk to your server; it's running anything external-to-SQL-Server via a Stored Proc that's really dangerous. — philu, Jul 15 '15 at 06:05

score 3 · Answer 4 · answered Feb 06 '12 at 05:01

3

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

answered Feb 06 '12 at 05:01

Hand-E-Food

12,368
8
45
80

score 2 · Answer 5 · answered Aug 28 '12 at 20:07

2

If you need to open XLS files rather than XLSX files, http://npoi.codeplex.com/ is a great choice. We've used it to good effect on our projects.

answered Aug 28 '12 at 20:07

Pete McKinney

1,211
1
11
21

score 2 · Answer 6 · edited May 27 '16 at 14:14

Look for GSpread.NET. It's also an OpenSource project and it doesn't require Office installed. You can work with Google Spreadsheets by using API from Microsoft Excel. If you want to re-use the old code to get access to Google Spreadsheets, GSpread.NET is the best way. You need to add a few row:

Set objExcel = CreateObject("GSpreadCOM.Application")
// Name             - User name, any you like
// ClientIdAndSecret - `client_id|client_secret` format
// ScriptId         - Google Apps script ID
app.MailLogon(Name, ClientIdAndSecret, ScriptId);

Further code remain unchanged.

http://scand.com/products/gspread/index.html

score 1 · Answer 7 · answered Dec 18 '15 at 07:17

1

I have used Excel.dll library which is:

open source
lightweight
fast
compatible with xls and xlsx

The documentation available over here: https://exceldatareader.codeplex.com/

Strongly recommendable.

answered Dec 18 '15 at 07:17

Kunal Kakkad

653
6
19

score 1 · Answer 8 · answered May 11 '16 at 14:00

1

Ive just been searching for a solution and come across Spreadsheetlight

which looks very promising. Its open source and available as a nuget package.

answered May 11 '16 at 14:00

Nick

3,454
6
33
56

It easily corrupts existing XLSX files!! Be careful. – Lorenzo Bassetti Oct 23 '21 at 17:31

score 1 · Answer 9 · answered Feb 06 '12 at 04:50

You can try OleDB to read data from excel file. Please try as follow..

DataSet ds_Data = new DataSet();
OleDbConnection oleCon = new OleDbConnection();

string strExcelFile = @"C:\Test.xlsx";
oleCon.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strExcelFile + ";Extended Properties=\"Excel 12.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text\"";;

 string SpreadSheetName = "";

OleDbDataAdapter Adapter = new OleDbDataAdapter();
OleDbConnection conn = new OleDbConnection(sConnectionString);

string strQuery;
conn.Open();

int workSheetNumber = 0;

DataTable ExcelSheets = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

SpreadSheetName = ExcelSheets.Rows[workSheetNumber]["TABLE_NAME"].ToString();

strQuery = "select * from [" + SpreadSheetName + "] ";
OleDbCommand cmd = new OleDbCommand(strQuery, conn);
Adapter.SelectCommand = cmd;
DataSet dsExcel = new DataSet();
Adapter.Fill(dsExcel);
conn.Close();

score 0 · Answer 10 · answered Aug 28 '12 at 20:00

You could also do what I do and by a commercial control like this one: http://www.syncfusion.com/products/reporting-edition/xlsio

I have been struging for years before ending with a commercial solution. I first tried the OLEDB approach that is very easy to use in my development environment but can be a knightmare to deploy. Then I tried the open source solutions but most of the are outdated and have bad support.

The xlsio controls from syncfusion are just the ones I use and are happy with but others exists. If you can affort it, do not hesitate, it's the best solution. Why? Because it has no dependencies with the system and supports all version of office right away. Among other advantages like, it's really fast.

And no, I do not work for synfusion ;)

score 0 · Answer 11 · answered Feb 06 '12 at 15:46

If you don't wish to use interop, you may want to try out OfficeWriter. Depending on how much processing you really need to do on the file, it might be overkill though. You can request a free trial. There's a fully documented api available at the documentation site.

DISCLAIMER: I'm one of the engineers who built the latest version.

score -1 · Answer 12 · answered Jan 25 '22 at 18:08

for someone need, open excel with Interop.Excel without office installed. You can add Interop.Excel with nuget.

public DataTable ReadExcel(string fileName)
    {
        Excel.Application FExcelObject = new Excel.Application();

        var FWorkbookObject = FExcelObject.Workbooks.Open(fileName,
                Type.Missing,
                Type.Missing,
                Type.Missing,
                Type.Missing,
                Type.Missing,
                Type.Missing,
                Type.Missing,
                Type.Missing,
                Type.Missing,
                Type.Missing,
                Type.Missing,
                Type.Missing);
        //var sheets = FWorkbookObject.Sheets[1];
        foreach (Excel.Worksheet sheets in FWorkbookObject.Sheets)
        {
            System.Data.DataTable dt = new System.Data.DataTable(sheets.Name);
            DataRow dr;
            StringBuilder sb = new StringBuilder();
            int jValue = sheets.UsedRange.Cells.Columns.Count;
            int iValue = sheets.UsedRange.Cells.Rows.Count;
            for (int j = 1; j <= jValue; j++)
            {
                dt.Columns.Add("column" + j, System.Type.GetType("System.String"));
            }
            for (int i = 1; i <= iValue; i++)
            {
                dr = dt.NewRow();
                for (int j = 1; j <= jValue; j++)
                {
                    var oRng = (Microsoft.Office.Interop.Excel.Range)sheets.Cells[i, j];
                    string strValue = oRng.Text.ToString();
                    dr["column" + j] = strValue;
                }
                dt.Rows.Add(dr);
            }
            return dt;
        }
        return new DataTable();
    }
}

That only works to _compile_ the code without having Excel installed but you cannot _run_ it. — Christoph, Mar 08 '23 at 10:58

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

12 Answers12

Linked

Related