Reputation: 49
I'm currently trying to access data from an Excel file in my C# Code. Thats my try:
public static void Main(string[] args)
{
var filepath= ".../0f351ee0-0e7b-488b-80c5-db5da81f4bb5.xlsx";
ReadExcel(file_path, ".xlsx");
Console.ReadLine();
}
enter code here
public static DataTable ReadExcel(string fileName, string fileExt)
{
string conn = string.Empty;
DataTable dtexcel = new DataTable();
if (fileExt.CompareTo(".xls") == 0)
conn = @"provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties='Excel 8.0;HRD=Yes;IMEX=1';"; //for below excel 2007
else
conn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=NO';"; //for above excel 2007
using (OleDbConnection con = new OleDbConnection(conn))
{
try
{
OleDbDataAdapter oleAdpt = new OleDbDataAdapter("select * from [Sheet1$]", con); //here we read data from sheet1
oleAdpt.Fill(dtexcel); //fill excel data into dataTable
}
catch { }
}
Console.WriteLine(dtexcel);
return dtexcel;
the problem is that the DataTable is always empty. Where exactly can I solve this problem?
Upvotes: 1
Views: 19251
Reputation: 11
If in the line reader.AsDataSet();
is an error The specified method is not supported
.
You must install the NuGet Package ExcelDataReader.DataSet
Upvotes: 1
Reputation: 131374
While the JET/Access driver can read Excel files as if they were databases, it has several problems, especially in the .NET Core era:
There are libraries that can read Excel files directly. One such option is ExcelDataReader, which opens a DbDataReader
over an Excel sheet. It can handle both the obsolete xls
format and the 16 year old xlsx
format (yes, the "new" xlsx format was introduce in 2006, 16 years ago).
The generated data reader can be used to read the data or load a DataTable the same as any other data reader.
using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
var table=new DataTable();
table.Load(reader);
...
}
}
ExcelDataReader has an extension that allows reading all sheets in a workbook into a DataSet, with one DataTable for each sheet.
using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
var dataset= reader.AsDataSet();
// The result of each spreadsheet is in dataset.Tables
}
}
Upvotes: 4
Reputation: 1
This is how I read from an Excel file:
public static DataSet Import(string path)
{
var dataStructure = new DataSet();
// Create the connection string and connect to the excel table by OleDbConnection.
string connectionString = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={path};Extended Properties=\"Excel 12.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text\"";
using (var conn = new OleDbConnection(connectionString))
{
try
{
conn.Open();
}
catch (Exception e)
{
MessageBox.Show($"Cannot connect to the OLEDB (Excel) driver with the connection string \"{connectionString}\".\n{e}");
return null;
}
DataTable sheets = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
using (OleDbCommand cmd = conn.CreateCommand())
{
foreach (DataRow row in sheets.Rows)
{
var tableName = row["TABLE_NAME"].ToString();
string sql = $"SELECT * FROM [{tableName}]";
var oleDbDataAdapter = new OleDbDataAdapter(sql, conn);
oleDbDataAdapter.Fill(dataStructure, tableName);
}
}
conn.Close();
}
return dataStructure;
}
I would highly recommend you to use the Open-XML-SDK for reading Excel files instead: Open-XML-SDK nuget This will make life a bit easier. e.g.:
SpreadsheetDocument.Open(fileName, isEditable);
Upvotes: 0