My code is as follows
我的代码如下
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(file);
Excel.Worksheet xlSheet = xlWorkbook.Sheets[1]; // get first sheet
Excel.Range xlRange = xlSheet.UsedRange;
These are the only variables used in my function
这些是我的函数中使用的唯一变量
foreach (Excel.Worksheet XLws in xlWorkbook.Worksheets)
{
// do some stuff
xlApp.UserControl = false;
if (xlRange != null)
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlRange);
if (xlSheet != null)
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlSheet);
if (xlWorkbook != null)
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkbook);
xlRange = null;
xlSheet = null;
xlWorkbook = null;
xlApp.Quit();
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlApp);
}
But still I get EXCEL.EXE in Task Manager
但我仍然在任务管理器中获得EXCEL.EXE
Please help?
4 个解决方案
#1
4
You've got an implicit object left open. Try this
你有一个隐含的对象保持打开状态。试试这个
Excel.Application xlApp = new Excel.Application();
Excel.Workbooks xlWorkbooks = xlApp.Workbooks;
Excel.Workbook xlWorkbook = xlWorkbooks.Open(file);
....
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlApp);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkbooks);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkbook);
....
#2
1
Kill the excel process which has empty value for MainWindowTitle. Below is an example source code.
Microsoft.Office.Interop.Excel.Application oXL;
Microsoft.Office.Interop.Excel._Workbook oWB;
Microsoft.Office.Interop.Excel._Worksheet oSheet;
Microsoft.Office.Interop.Excel.Range oRng;
object misvalue = System.Reflection.Missing.Value;
try
{
//Start Excel and get Application object.
oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.Visible = true;
//Get a new workbook.
oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(""));
oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;
//Add table headers going cell by cell.
oSheet.Cells[1, 1] = "First Name";
oSheet.Cells[1, 2] = "Last Name";
oSheet.Cells[1, 3] = "Full Name";
oSheet.Cells[1, 4] = "Salary";
//Format A1:D1 as bold, vertical alignment = center.
oSheet.get_Range("A1", "D1").Font.Bold = true;
oSheet.get_Range("A1", "D1").VerticalAlignment =
Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
// Create an array to multiple values at once.
string[,] saNames = new string[5, 2];
saNames[0, 0] = "John";
saNames[0, 1] = "Smith";
saNames[1, 0] = "Tom";
saNames[4, 1] = "Johnson";
//Fill A2:B6 with an array of values (First and Last Names).
oSheet.get_Range("A2", "B6").Value2 = saNames;
//Fill C2:C6 with a relative formula (=A2 & " " & B2).
oRng = oSheet.get_Range("C2", "C6");
oRng.Formula = "=A2 & \" \" & B2";
//Fill D2:D6 with a formula(=RAND()*100000) and apply format.
oRng = oSheet.get_Range("D2", "D6");
oRng.Formula = "=RAND()*100000";
oRng.NumberFormat = "$0.00";
//AutoFit columns A:D.
oRng = oSheet.get_Range("A1", "D1");
oRng.EntireColumn.AutoFit();
oXL.Visible = false;
oXL.UserControl = false;
oWB.SaveAs("c:\\test505.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
oWB.Close(null, null, null);
oXL.Quit(); //MainWindowTitle will become empty afer being close
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(oXL);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(oWB);
Process[] excelProcesses = Process.GetProcessesByName("excel");
foreach (Process p in excelProcesses)
{
if (string.IsNullOrEmpty(p.MainWindowTitle)) // use MainWindowTitle to distinguish this excel process with other excel processes
{
p.Kill();
}
}
}
catch (Exception ex2)
{
}
#3
0
OK... I hope this helps... it took me forever to tweak this to get it to work just so...
好的...我希望这会有所帮助......我花了很长时间来调整它以使其正常工作......
Here is my entire function (VB -- but the C# code for the tricky stuff is in there (thanks to too many other * giants who helped me get this far!)
这是我的整个函数(VB - 但是那些棘手的东西的C#代码就在那里(感谢太多其他的*巨头帮助我做到这一点!)
Private Function ImportWorksFile() As Integer
Dim EndofSheet As Boolean
Dim BlankRowCounter As Integer
Dim rr As RowResult
Dim SecCount As Integer = 0
Dim SecRow As SecurityRow
Dim uf As New UtilFunctions
'If this has already been run, the instance of the excel object would have been 'killed' and needs to be reinstantiated
If blnExcelProcessKilled Then 'Global boolean var
xlApp = New Excel.Application()
blnExcelProcessKilled = False
End If
Dim excelProcess(0) As Process
excelProcess = Process.GetProcessesByName("excel")
Dim tmp As Excel.Workbooks
Try
tmp = xlApp.Workbooks
xlWorkBook = tmp.Open(WorkingFileName)
Catch ex As Exception
MessageBox.Show("There was a problem opening the workbook - please try again", CurAFLApp.AppName, MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
Return 0
End Try
Using dc As New AFLData(CurAFLApp, True)
Dim cmd As SqlCommand = DefineCommand()
cmd.CommandType = CommandType.StoredProcedure
For Each ws As Excel.Worksheet In xlWorkBook.Worksheets
Dim row As Integer = 1
EndofSheet = False
BlankRowCounter = 0
If ImpCols.ContainsKey(ws.Name) Then
SecRow = New SecurityRow(ImpCols(ws.Name))
Do Until EndofSheet
Try
SecRow.NewRow(ws.Rows(row))
rr = SecRow.IsValidRow
If rr = RowResult.Valid Then
' read this row and process
With cmd
.Parameters("@AcctDate").Value = FileDate
.Parameters("@NewSub").Value = SecRow.GetStrCell("newsub")
RunProcedure(cmd)
End With
SecCount += 1
BlankRowCounter = 0
Else
BlankRowCounter += rr
End If
Catch ex As Exception
MessageBox.Show("There was a problem with row: " & row & " in workbook " & ws.Name)
End Try
' if we've counted 50 blank A column values in a row, we're done.
If BlankRowCounter <= -50 Then
EndofSheet = True
End If
row += 1
Loop
End If
Next
End Using
Try
xlWorkBook.Close(SaveChanges:=False)
xlApp.Workbooks.Close()
xlApp.Quit()
'// And now kill the process. C# Version (for reference)
'if (processID != 0)
'{
' Process process = Process.GetProcessById(processID);
' process.Kill();
'}
' Reversed the order of release per http://*.com/questions/12916137/best-way-to-release-excel-interop-com-object
Catch ex As Exception
MessageBox.Show("There was a problem CLOSING the workbook - Please double check that the data was imported correctly. ", CurAFLApp.AppName, MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
Return 0
Finally
releaseObject(tmp)
releaseObject(xlWorkBook)
releaseObject(xlApp)
If Not excelProcess(0).CloseMainWindow() Then
excelProcess(0).Kill()
blnExcelProcessKilled = True
End If
End Try
Return SecCount
End Function
Public Sub releaseObject(ByVal obj As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
obj = Nothing
Catch ex As Exception
obj = Nothing
Finally
GC.Collect()
'Not sure if the following line helps or hinders -- seems to lock things up once in a while
'GC.WaitForPendingFinalizers()
End Try
End Sub
#4
0
Try :
xlWorkbook.Close(false); // if you Workbook should not be saved
instead of :
代替 :
if (xlWorkbook != null)
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkbook);
xlWorkbook = null;
#1
4
You've got an implicit object left open. Try this
你有一个隐含的对象保持打开状态。试试这个
Excel.Application xlApp = new Excel.Application();
Excel.Workbooks xlWorkbooks = xlApp.Workbooks;
Excel.Workbook xlWorkbook = xlWorkbooks.Open(file);
....
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlApp);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkbooks);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkbook);
....
#2
1
Kill the excel process which has empty value for MainWindowTitle. Below is an example source code.
Microsoft.Office.Interop.Excel.Application oXL;
Microsoft.Office.Interop.Excel._Workbook oWB;
Microsoft.Office.Interop.Excel._Worksheet oSheet;
Microsoft.Office.Interop.Excel.Range oRng;
object misvalue = System.Reflection.Missing.Value;
try
{
//Start Excel and get Application object.
oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.Visible = true;
//Get a new workbook.
oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(""));
oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;
//Add table headers going cell by cell.
oSheet.Cells[1, 1] = "First Name";
oSheet.Cells[1, 2] = "Last Name";
oSheet.Cells[1, 3] = "Full Name";
oSheet.Cells[1, 4] = "Salary";
//Format A1:D1 as bold, vertical alignment = center.
oSheet.get_Range("A1", "D1").Font.Bold = true;
oSheet.get_Range("A1", "D1").VerticalAlignment =
Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
// Create an array to multiple values at once.
string[,] saNames = new string[5, 2];
saNames[0, 0] = "John";
saNames[0, 1] = "Smith";
saNames[1, 0] = "Tom";
saNames[4, 1] = "Johnson";
//Fill A2:B6 with an array of values (First and Last Names).
oSheet.get_Range("A2", "B6").Value2 = saNames;
//Fill C2:C6 with a relative formula (=A2 & " " & B2).
oRng = oSheet.get_Range("C2", "C6");
oRng.Formula = "=A2 & \" \" & B2";
//Fill D2:D6 with a formula(=RAND()*100000) and apply format.
oRng = oSheet.get_Range("D2", "D6");
oRng.Formula = "=RAND()*100000";
oRng.NumberFormat = "$0.00";
//AutoFit columns A:D.
oRng = oSheet.get_Range("A1", "D1");
oRng.EntireColumn.AutoFit();
oXL.Visible = false;
oXL.UserControl = false;
oWB.SaveAs("c:\\test505.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
oWB.Close(null, null, null);
oXL.Quit(); //MainWindowTitle will become empty afer being close
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(oXL);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(oWB);
Process[] excelProcesses = Process.GetProcessesByName("excel");
foreach (Process p in excelProcesses)
{
if (string.IsNullOrEmpty(p.MainWindowTitle)) // use MainWindowTitle to distinguish this excel process with other excel processes
{
p.Kill();
}
}
}
catch (Exception ex2)
{
}
#3
0
OK... I hope this helps... it took me forever to tweak this to get it to work just so...
好的...我希望这会有所帮助......我花了很长时间来调整它以使其正常工作......
Here is my entire function (VB -- but the C# code for the tricky stuff is in there (thanks to too many other * giants who helped me get this far!)
这是我的整个函数(VB - 但是那些棘手的东西的C#代码就在那里(感谢太多其他的*巨头帮助我做到这一点!)
Private Function ImportWorksFile() As Integer
Dim EndofSheet As Boolean
Dim BlankRowCounter As Integer
Dim rr As RowResult
Dim SecCount As Integer = 0
Dim SecRow As SecurityRow
Dim uf As New UtilFunctions
'If this has already been run, the instance of the excel object would have been 'killed' and needs to be reinstantiated
If blnExcelProcessKilled Then 'Global boolean var
xlApp = New Excel.Application()
blnExcelProcessKilled = False
End If
Dim excelProcess(0) As Process
excelProcess = Process.GetProcessesByName("excel")
Dim tmp As Excel.Workbooks
Try
tmp = xlApp.Workbooks
xlWorkBook = tmp.Open(WorkingFileName)
Catch ex As Exception
MessageBox.Show("There was a problem opening the workbook - please try again", CurAFLApp.AppName, MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
Return 0
End Try
Using dc As New AFLData(CurAFLApp, True)
Dim cmd As SqlCommand = DefineCommand()
cmd.CommandType = CommandType.StoredProcedure
For Each ws As Excel.Worksheet In xlWorkBook.Worksheets
Dim row As Integer = 1
EndofSheet = False
BlankRowCounter = 0
If ImpCols.ContainsKey(ws.Name) Then
SecRow = New SecurityRow(ImpCols(ws.Name))
Do Until EndofSheet
Try
SecRow.NewRow(ws.Rows(row))
rr = SecRow.IsValidRow
If rr = RowResult.Valid Then
' read this row and process
With cmd
.Parameters("@AcctDate").Value = FileDate
.Parameters("@NewSub").Value = SecRow.GetStrCell("newsub")
RunProcedure(cmd)
End With
SecCount += 1
BlankRowCounter = 0
Else
BlankRowCounter += rr
End If
Catch ex As Exception
MessageBox.Show("There was a problem with row: " & row & " in workbook " & ws.Name)
End Try
' if we've counted 50 blank A column values in a row, we're done.
If BlankRowCounter <= -50 Then
EndofSheet = True
End If
row += 1
Loop
End If
Next
End Using
Try
xlWorkBook.Close(SaveChanges:=False)
xlApp.Workbooks.Close()
xlApp.Quit()
'// And now kill the process. C# Version (for reference)
'if (processID != 0)
'{
' Process process = Process.GetProcessById(processID);
' process.Kill();
'}
' Reversed the order of release per http://*.com/questions/12916137/best-way-to-release-excel-interop-com-object
Catch ex As Exception
MessageBox.Show("There was a problem CLOSING the workbook - Please double check that the data was imported correctly. ", CurAFLApp.AppName, MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
Return 0
Finally
releaseObject(tmp)
releaseObject(xlWorkBook)
releaseObject(xlApp)
If Not excelProcess(0).CloseMainWindow() Then
excelProcess(0).Kill()
blnExcelProcessKilled = True
End If
End Try
Return SecCount
End Function
Public Sub releaseObject(ByVal obj As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
obj = Nothing
Catch ex As Exception
obj = Nothing
Finally
GC.Collect()
'Not sure if the following line helps or hinders -- seems to lock things up once in a while
'GC.WaitForPendingFinalizers()
End Try
End Sub
#4
0
Try :
xlWorkbook.Close(false); // if you Workbook should not be saved
instead of :
代替 :
if (xlWorkbook != null)
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkbook);
xlWorkbook = null;