c# - 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_testingProductsUploadTemplate-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

Answer 1

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:YourDirectorysample.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));
    }
}