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:
comment: seems to probably work for me, but doesn't support Excel files of unknown dimensions (random number of rows and columns).
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).
comment: same problem as above.
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
See Question&Answers more detail:
os