Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
241 views
in Technique[技术] by (71.8m points)

c# - How to read from XLSX (Excel)?

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...