Not sure what I am doing wrong, each way I tried has failed. Currently stuck on this code variation... Compiles fine, except when reading the Excel file, my application freezes and then crashes. (Note: I don't care how to do this, I have a very simple task see the bottom of the question if you have better suggestions)
string filePath = string.Empty;
string fileExt = string.Empty;
OpenFileDialog file = new OpenFileDialog(); //open dialog to choose file
if (file.ShowDialog() == System.Windows.Forms.DialogResult.OK) //if there is a file chosen by the user
{
filePath = file.FileName; //get the path of the file
fileExt = Path.GetExtension(filePath); //get the file extension
if (fileExt.CompareTo(".xls") == 0 || fileExt.CompareTo(".xlsx") == 0)
{
try
{
string sSheetName = null;
string sConnection = null;
DataTable dtTablesList = default(DataTable);
OleDbCommand oleExcelCommand = default(OleDbCommand);
OleDbDataReader oleExcelReader = default(OleDbDataReader);
OleDbConnection oleExcelConnection = default(OleDbConnection);
sConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + @";Extended Properties='Excel 12.0;HDR=NO;IMEX=1;';";
oleExcelConnection = new OleDbConnection(sConnection);
oleExcelConnection.Open();
dtTablesList = oleExcelConnection.GetSchema("Tables");
if (dtTablesList.Rows.Count > 0)
{
sSheetName = dtTablesList.Rows[0]["TABLE_NAME"].ToString();
}
dtTablesList.Clear();
dtTablesList.Dispose();
if (!string.IsNullOrEmpty(sSheetName))
{
oleExcelCommand = oleExcelConnection.CreateCommand();
oleExcelCommand.CommandText = "Select * From [" + sSheetName + "]";
oleExcelCommand.CommandType = CommandType.Text;
oleExcelReader = oleExcelCommand.ExecuteReader();
int nOutputRow = 0;
while (oleExcelReader.Read())
{
}
oleExcelReader.Close();
}
oleExcelConnection.Close();
My task is really simple. I have to read an excel file (only two columns, infinite rows) and add it to a data table. There are sheet numbers and sheet titles under different categories. Later on, I plan on making an algorithm that will read the data table, and create different dictionaries (for each category) containing sheet numbers and sheet titles.
Here is a sample of an excel file:
EDIT:
Application crashes without any exceptions I can 'catch', but here are the errors & warnings from Visual Studio debugger:
System.Windows.ResourceDictionary Warning: 9 : Resource not found; ResourceKey='ClientAreaBackgroundBrush'
System.Windows.ResourceDictionary Warning: 9 : Resource not found; ResourceKey='ClientAreaForegroundBrush'
System.Windows.ResourceDictionary Warning: 9 : Resource not found; ResourceKey='ClientAreaForegroundBrush'
System.Windows.ResourceDictionary Warning: 9 : Resource not found; ResourceKey='Common-SavedSelections'
System.Windows.ResourceDictionary Warning: 9 : Resource not found; ResourceKey='Common-RuleBasedFilters'
System.Windows.ResourceDictionary Warning: 9 : Resource not found; ResourceKey='ActiveTabBackgroundBrush'
System.Windows.ResourceDictionary Warning: 9 : Resource not found; ResourceKey='ClientAreaBackgroundBrush'
System.Windows.ResourceDictionary Warning: 9 : Resource not found; ResourceKey='ClientAreaForegroundBrush'
System.Windows.ResourceDictionary Warning: 9 : Resource not found; ResourceKey='ClientAreaForegroundBrush'
System.Windows.ResourceDictionary Warning: 9 : Resource not found; ResourceKey='ActiveTabBackgroundBrush'
System.Windows.ResourceDictionary Warning: 9 : Resource not found; ResourceKey='ClientAreaBackgroundBrush'
System.Windows.ResourceDictionary Warning: 9 : Resource not found; ResourceKey='ClientAreaForegroundBrush'
System.Windows.ResourceDictionary Warning: 9 : Resource not found; ResourceKey='ClientAreaForegroundBrush'
System.Windows.ResourceDictionary Warning: 9 : Resource not found; ResourceKey='ClientAreaBackgroundBrush'
System.Windows.ResourceDictionary Warning: 9 : Resource not found; ResourceKey='ClientAreaBackgroundBrush'
System.Windows.ResourceDictionary Warning: 9 : Resource not found; ResourceKey='ClientAreaBackgroundBrush'
System.Windows.ResourceDictionary Warning: 9 : Resource not found; ResourceKey='ClientAreaBackgroundBrush'
System.Windows.ResourceDictionary Warning: 9 : Resource not found; ResourceKey='ClientAreaBackgroundBrush'
System.Windows.ResourceDictionary Warning: 9 : Resource not found; ResourceKey='ClientAreaBackgroundBrush'
System.Windows.ResourceDictionary Warning: 9 : Resource not found; ResourceKey='ClientAreaBackgroundBrush'
System.Windows.Data Error: 4 : Cannot find source for binding with reference 'RelativeSource FindAncestor, AncestorType='System.Windows.Controls.Primitives.Track', AncestorLevel='1''. BindingExpression:(no path); DataItem=null; target element is 'DoubleAnimation' (HashCode=66916835); target property is 'Target' (type 'DependencyObject')
System.Windows.Data Error: 4 : Cannot find source for binding with reference 'RelativeSource FindAncestor, AncestorType='System.Windows.Controls.Primitives.Track', AncestorLevel='1''. BindingExpression:(no path); DataItem=null; target element is 'DoubleAnimation' (HashCode=65380607); target property is 'Target' (type 'DependencyObject')
System.Windows.Data Error: 4 : Cannot find source for binding with reference 'RelativeSource FindAncestor, AncestorType='System.Windows.Controls.Primitives.Track', AncestorLevel='1''. BindingExpression:(no path); DataItem=null; target element is 'DoubleAnimation' (HashCode=51554558); target property is 'Target' (type 'DependencyObject')
System.Windows.Data Error: 4 : Cannot find source for binding with reference 'RelativeSource FindAncestor, AncestorType='System.Windows.Controls.Primitives.Track', AncestorLevel='1''. BindingExpression:(no path); DataItem=null; target element is 'DoubleAnimation' (HashCode=61337839); target property is 'Target' (type 'DependencyObject')
System.Windows.Data Error: 4 : Cannot find source for binding with reference 'RelativeSource FindAncestor, AncestorType='System.Windows.Controls.Primitives.Track', AncestorLevel='1''. BindingExpression:(no path); DataItem=null; target element is 'DoubleAnimation' (HashCode=15169645); target property is 'Target' (type 'DependencyObject')
System.Windows.Data Error: 4 : Cannot find source for binding with reference 'RelativeSource FindAncestor, AncestorType='System.Windows.Controls.Primitives.Track', AncestorLevel='1''. BindingExpression:(no path); DataItem=null; target element is 'DoubleAnimation' (HashCode=2309080); target property is 'Target' (type 'DependencyObject')
System.Windows.Data Error: 4 : Cannot find source for binding with reference 'RelativeSource FindAncestor, AncestorType='System.Windows.Controls.Primitives.Track', AncestorLevel='1''. BindingExpression:(no path); DataItem=null; target element is 'DoubleAnimation' (HashCode=20781721); target property is 'Target' (type 'DependencyObject')
System.Windows.Data Error: 4 : Cannot find source for binding with reference 'RelativeSource FindAncestor, AncestorType='System.Windows.Controls.Primitives.Track', AncestorLevel='1''. BindingExpression:(no path); DataItem=null; target element is 'DoubleAnimation' (HashCode=52817761); target property is 'Target' (type 'DependencyObject')
System.Windows.Data Error: 4 : Cannot find source for binding with reference 'RelativeSource FindAncestor, AncestorType='System.Windows.Controls.Border', AncestorLevel='3''. BindingExpression:Path=ActualHeight; DataItem=null; target element is 'ContentPresenter' (Name=''); target property is 'NoTarget' (type 'Object')
The thread 0x3d9c has exited with code 0 (0x0).
The thread 0x9358 has exited with code 0 (0x0).
The thread 0x8598 has exited with code 0 (0x0).
The thread 0xab5c has exited with code 0 (0x0).
The thread 0x6aa0 has exited with code 0 (0x0).
The thread 0xb934 has exited with code 0 (0x0).
The thread 0x3f6c has exited with code 0 (0x0).
The thread 0xb1fc has exited with code 0 (0x0).
The thread 0x8898 has exited with code 0 (0x0).
The thread 0x679c has exited with code 0 (0x0).
The thread 0xb04c has exited with code 0 (0x0).
The thread 0x3320 has exited with code 0 (0x0).
The thread 0x523c has exited with code 0 (0x0).
The thread 0x3a40 has exited with code 0 (0x0).
The thread 0x91dc has exited with code 0 (0x0).
The thread 0x2ccc has exited with code 0 (0x0).
The thread 0x8cac has exited with code 0 (0x0).
The thread 0xb518 has exited with code 0 (0x0).
The thread 0x70a8 has exited with code 0 (0x0).
The thread 0x32e4 has exited with code 0 (0x0).
The thread 0xad18 has exited with code 0 (0x0).
The thread 0xa8ec has exited with code 0 (0x0).
The thread 0x181c has exited with code 0 (0x0).
The thread 0x5b64 has exited with code 0 (0x0).
The thread 0x8a24 has exited with code 0 (0x0).
The thread 0x971c has exited with code 0 (0x0).
The thread 0x3880 has exited with code 0 (0x0).
The thread 0x7b68 has exited with code 0 (0x0).
The thread 0x7e80 has exited with code 0 (0x0).
The thread 0x914c has exited with code 0 (0x0).
The thread 0x48f0 has exited with code 0 (0x0).
The thread 0x858c has exited with code 0 (0x0).
The thread 0xb228 has exited with code 0 (0x0).
The thread 0xb654 has exited with code 0 (0x0).
The thread 0xb8a4 has exited with code 0 (0x0).
The thread 0x7ad0 has exited with code 0 (0x0).
The thread 0xb628 has exited with code 0 (0x0).
The thread 0x86f0 has exited with code 0 (0x0).
The thread 0xa128 has exited with code 0 (0x0).
The thread 0xbb38 has exited with code 0 (0x0).
The thread 0x7ab4 has exited with code 0 (0x0).
'Revit.exe' (CLR v4.0.30319: DefaultDomain): Loaded 'C:\Windows\Microsoft.Net\assembly\GAC_64\System.Transactions\v4.0_4.0.0.0__b77a5c561934e089\System.Transactions.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
The program '[42560] Revit.exe' has exited with code 3221226525 (0xc000041d).
I ended up using ExcelDataReader nugget package which made this a walk in the park and with extremely fast results
https://github.com/ExcelDataReader/ExcelDataReader