I'm writing a program which uses "Excel Sheets".
I am loading them into a DataGridView via a Button. If I'm finished working with the Sheet I can press the same button to load in another Sheet. The problem is even though I am clearing and refreshing my DataGridView the old Sheet isn't going away and the new Sheet just gets added beneath the old one. Why is that?
My Code for the Button Click Event:
private void button1_Click(object sender, EventArgs e)
{
//Excell Prozess kill (wenn "Datei öffnen" geklickt)
Process[] excelProcs = Process.GetProcessesByName("EXCEL");
foreach (Process proc in excelProcs)
{
proc.Kill();
}
//Preparing Excel
Excel.Workbook theWorkbook = null;
Excel.Application ExcelObj = null;
Excel.Sheets sheets = null;
Excel.Worksheet worksheet = null;
if (dataGridView1.DataSource != null)
{
dataGridView1.DataSource = null;
dataGridView1.Refresh();
}
else
{
dataGridView1.Columns.Clear();
}
//Deklaration
string filePath = string.Empty;
string fileExt = string.Empty;
//File dialog
OpenFileDialog dialog = new OpenFileDialog();
dialog.Filter = "Excel Datei (*.xlsx *.xls)|*.xlsx; *.xls; |All Files (*.*)|*.*";
if (dialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
filePath = dialog.FileName; //get the path of the file
fileExt = Path.GetExtension(filePath); //get the extension from the file
//File Path zeigen
label1.Text = filePath;
//Nur .xls und .xlsx
if (fileExt.CompareTo(".xls") == 0 || fileExt.CompareTo(".xlsx") == 0)
{
try
{
ExcelObj = new Excel.Application();
//open ExcelObj and load it into theWorkbook
theWorkbook = ExcelObj.Workbooks.Open(filePath);
sheets = theWorkbook.Worksheets;
//Get the reference of second worksheet
worksheet = (Excel.Worksheet)sheets.get_Item(1);
//Get the name of worksheet
string strWorksheetName = worksheet.Name;
//string for database con
String constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
filePath
";Extended Properties='Excel 12.0 XML;HDR=NO;';";
OleDbConnection con = new OleDbConnection(constr);
//select sheet from excel file
OleDbCommand oconn = new OleDbCommand("Select * From [" strWorksheetName "$]", con);
//open db
con.Open();
OleDbDataAdapter sda = new OleDbDataAdapter(oconn);
sda.Fill(data);
//insert data into datagridview
dataGridView1.DataSource = data;
//Select all from table date
DataRow[] rows = data.Select();
//close db
con.Close();
//close ExcelObj
ExcelObj.Quit();
}
//if error -> show
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
finally
{
//ComObject release
if (sheets != null) Marshal.ReleaseComObject(sheets);
if (ExcelObj != null) Marshal.ReleaseComObject(ExcelObj);
if (worksheet != null) Marshal.ReleaseComObject(worksheet);
if (theWorkbook != null) Marshal.ReleaseComObject(theWorkbook);
}
I believe that the problem is caused by the "Excel File" not being released properly inside the "Preparing Excel" section, but "Excel" is being closed in many situations via the "Excel Prozess Kill" Code. I watched it a couple of times via Task Manager. So what's causing the problem? Any Opinions?
CodePudding user response:
There are a couple of head scratching things in your current code that you may want to reconsider.
First… killing ALL the “Excel” processes is well… odd. Your code possibly did NOT start these processes; however the code is going to kill them. To heck with the person who started running Excel and stepped away for coffee.
The code “appears” to be releasing the Excel process properly, however, it seems logical to release the Excel app “after” you have released the workbook. Specifically, release the sheets
, then worksheet
, then theWorkbook
and finally the app ExcelObj
. Because the code “appears” to properly release the Excel com objects, I would dump all the code that kills the Excel processes.
Next, I suggest looking for a third-party solution when working with Excel files. For non-commercial use I find EPPlus to be a good alternative as opposed to OleDb
and/or Interop
and can be found in NuGet. Just a thought.
And finally I DO have an easy solution to your issue. From what I can decipher as you do not show its definition, is the variable data
. It appears to me that this data
object is a DataTable
. And in my small tests, as you describe, when you select a second Excel file, it does indeed “append” itself to the existing data
DataTable
. Since the code “appears” to never clear the data
table before filling it, then when you call…
sda.Fill(data);
a second time, then the adapter obligingly “appends” the data.
To fix this, simply clear the data
table before you fill it…
data = new DataTable();
sda.Fill(data);