I am currently trying to read from an Excel file with OleDB.
This is the Code:
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Text;
namespace WebApplication1.My_Logic.Worker
{
public class ExcelLoader
{
private string GetConnectionString()
{
Dictionary<string, string> props = new Dictionary<string, string>();
props["Provider"] = "Microsoft.ACE.OLEDB.12.0;";
props["Extended Properties"] = "Excel 12.0 XML";
props["Data Source"] = "C:\\MyExcel.xlsx";
StringBuilder sb = new StringBuilder();
foreach (KeyValuePair<string, string> prop in props)
{
sb.Append(prop.Key);
sb.Append('=');
sb.Append(prop.Value);
sb.Append(';');
}
return sb.ToString();
}
private DataSet ReadExcelFile()
{
DataSet ds = new DataSet();
string connectionString = GetConnectionString();
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
conn.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
// Get all Sheets in Excel File
DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
// Loop through all Sheets to get data
foreach (DataRow dr in dtSheet.Rows)
{
string sheetName = dr["TABLE_NAME"].ToString();
if (!sheetName.EndsWith("$"))
continue;
// Get all rows from the Sheet
cmd.CommandText = "SELECT * FROM [" + sheetName + "]";
DataTable dt = new DataTable();
dt.TableName = sheetName;
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(dt);
ds.Tables.Add(dt);
}
cmd = null;
conn.Close();
}
return ds;
}
}
}
System.Data.OleDb is greyd out (not used) and following classes cannot be found:
OleDbCommand
OleDbConnection
OleDbSchemaGuid
OleDbDataAdapter
When i look into the System.Data.OleDb there are only 2 classes shown by Visual Studio:
System.Data.OleDb.OleDbPermission
System.Data.OleDb.OleDbPermissionAttribute
Everywhere i read in the internet the only anwser was to add using System.Data.OleDb but i do have it and Visual Studio seems to know it.
Its a freshly generated ASP.NET Core 2.1 API Project from Visual Studio 2017.
