public void ReadExcel(string conn_string)
{
_xlApp = new Excel.Application();
try
{
_xlWorkbook = _xlApp.Workbooks.Open(_path);
}
catch (Exception e)
{
MessageBox.Show("Invalid file name entered");
_xlApp.Quit();
Marshal.ReleaseComObject(_xlApp);
return;
}
int totalSheetsNum = _xlWorkbook.Sheets.Count;
//error killing com objects with foreach
for (int currentSheetNum = 1; currentSheetNum <= totalSheetsNum; currentSheetNum++)
{
//just progress bar update values in range 1 to 100 integer
// MainWindow.ChangePB(100 * (++currentSheetNum) / totalSheetsNum);
_currentSheet = _xlWorkbook.Sheets[currentSheetNum];
_xlRange = _currentSheet.UsedRange;
int colCount = _xlRange.Columns.Count;
int rowCount = _xlRange.Rows.Count;
//send to the server
DB_Handler.ConnectAndInsertConnectAndInsert(ref _xlRange, conn_string, _currentSheet.Name);
//cleanup
GC.Collect();
GC.WaitForPendingFinalizers();
//release com objects to fully kill excel process from running in the background
Marshal.ReleaseComObject(_xlRange);
Marshal.ReleaseComObject(_currentSheet);
}
_xlWorkbook.Close();
Marshal.ReleaseComObject(_xlWorkbook);
_xlApp.Quit();
Marshal.ReleaseComObject(_xlApp);
}