How to read from XLSX (Excel)?

Question

I have a problem with reading from .xlsx (Excel) file. I tried to use:

var fileName = @"C:\automated_testing\ProductsUploadTemplate-2015-10-22.xlsx";
var connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", fileName);

var adapter = new OleDbDataAdapter("SELECT * FROM [workSheetNameHere$]", connectionString);
var ds = new DataSet();
adapter.Fill(ds, "XLSData");
DataTable data = ds.Tables["XLSData"];

// ... Loop over all rows.
StringBuilder sb = new StringBuilder();
foreach (DataRow row in data.Rows)
{
    sb.AppendLine(string.Join(",", row.ItemArray));
}

but if failed due to connectionString. So I updated the line to support .xlsx:

var connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;", fileName);

but I get:

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

(Problem here is that, I am not able to install new software on my remote-testing machine, so I am not able to fix it and need to find other solution.)

I do also need to be sure that imported data will be stored in some simple way (I am beginner programmer) to let me iterate through it i.e. to create objects with row's data.

Other approaches I checked:

https://bytescout.com/products/developer/spreadsheetsdk/read-write-excel.html

comment: seems to probably work for me, but doesn't support Excel files of unknown dimensions (random number of rows and columns).

https://exceldatareader.codeplex.com/

comment: doesn't support settings column names from different row than first one (in some of my Excel files, there are comments in 4-6 first rows and then is headers row and data below).

http://blog.fryhard.com/archive/2010/10/28/reading-xlsx-files-using-c-and-epplus.aspx

comment: same problem as above.

https://freenetexcel.codeplex.com/

comment: downloaded package weight was over 60MB and it requires me to install it on system, which is not possible in my situation. Anyway, people comment that it is limited to 150 rows.

Meanwhile I will try to check https://code.google.com/p/linqtoexcel/, but all other ideas are more than welcome!

EDIT: Just checked that LinqToExcel, same issue as above:

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

EDIT2: Ultimately, it seems that this solution solved my issue:

https://stackoverflow.com/a/19065266/3146582

@Fredou: doesn't it create spreadsheets? I need to read from one. Do you have any example of that? — Tomek, Oct 23 '15 at 12:31
it can also read excel file, check this stackoverflow question http://stackoverflow.com/questions/11685204/reading-excel-spreasheet-using-epplus or this blog entry http://blog.fryhard.com/archive/2010/10/28/reading-xlsx-files-using-c-and-epplus.aspx — Fredou, Oct 23 '15 at 12:34
@Fredou: now I see that I also gave that link. It could probably work, but I do need to know how many cells I do have filled in xlsx file before I use it, as it doesn't go row by row, but requires detailed cell address to be given in order to read its value. — Tomek, Oct 23 '15 at 12:37
Possible duplicate of [How to read data of an Excel file using C#?](http://stackoverflow.com/questions/657131/how-to-read-data-of-an-excel-file-using-c) — ManishChristian, Oct 23 '15 at 12:41
@Nelly27281: it seems so, but I am not able to mark it as duplicate now, after editing. — Tomek, Oct 23 '15 at 13:22

score 41 · Accepted Answer · edited Apr 17 '19 at 09:22

41

If you are reading data from Excel file, you can use EPPlus NuGet package, and use following code:

//using OfficeOpenXml;
using (ExcelPackage xlPackage = new ExcelPackage(new FileInfo(@"C:\YourDirectory\sample.xlsx")))
{
    var myWorksheet = xlPackage.Workbook.Worksheets.First(); //select sheet here
    var totalRows = myWorksheet.Dimension.End.Row;
    var totalColumns = myWorksheet.Dimension.End.Column;

    var sb = new StringBuilder(); //this is your data
    for (int rowNum = 1; rowNum <= totalRows; rowNum++) //select starting row here
    {
        var row = myWorksheet.Cells[rowNum, 1, rowNum, totalColumns].Select(c => c.Value == null ? string.Empty : c.Value.ToString());
        sb.AppendLine(string.Join(",", row));
    }
}

edited Apr 17 '19 at 09:22

Saikat

14,222
20
104
125

answered Oct 23 '15 at 13:24

Arghya C

9,805
2
47
66

Thanks! It's best solution for me. I just changed it a bit and replaced appending to stringbuilder with foreach. – Tomek Oct 23 '15 at 13:41
@BlackHat glad it helped :) – Arghya C Oct 23 '15 at 13:48
4

EPPlus was GNU GPL v3 licensed. – nyconing Nov 06 '17 at 08:16
2

EPPLUS now have a specific license for commercial use (Polyform Noncommercial 1.0.0). – dipi evil Mar 10 '23 at 11:14

score 4 · Answer 2 · answered Oct 23 '15 at 13:30

Reading Excel files with OLE provider is possible only if MS Jet engine (MS Access) is installed. I noticed that you decided to use .NET interop to API but this is not a good idea: it requires installed MS Excel and doesn't recommended to use for automation on servers.

If you don't need to support old (binary) Excel formats (xls) and reading XLSX is enough I recommend to use EPPlus library. It provides simple and powerful API for both reading and writing XLSX files (and has a lot of examples):

var existingFile = new FileInfo(filePath);
// Open and read the XlSX file.
using (var package = new ExcelPackage(existingFile)) {
   // access worksheets, cells etc
}

How to read from XLSX (Excel)?

2 Answers2

Linked