How to read from XLSX (Excel)?
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
}
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));
}
}