excel - programa - los dias mas dificiles despues de dejar de fumar

La aplicación no se cierra después de llamar a dejar de fumar

Question

Hola chicos, tengo un pequeño problema que parece que no puedo resolver. Estoy guardando un DataGridView (su contenido) en un archivo xls. No tengo ningún problema para hacerlo, excepto que en mi administrador de tareas sigue apareciendo que se está ejecutando. He llamado:

 xlApp.Application.Quit() 

Esto se declara como:

 Dim xlApp As New excel.Application

Esto parece no funcionar, PERO esta es la misma forma en que lo dejo cuando dejo que el usuario elija exportarlo a un documento de Word. No estoy seguro de dónde me estoy equivocando ...

Aquí está mi código completo

Imports Word = Microsoft.Office.Interop.Word
Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
For x As Integer = 1 To 3500
DataGridView1.Rows.Add(New Object() {"r" & x.ToString & "c1", "r" & x.ToString & "c2", "r" & x.ToString & "c3", "r" & x.ToString & "c4", "r" & x.ToString & "c5"})
Next
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
exportToWord (DataGridView1)
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim xlApp As New Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
''Dim misValue As Object = System.Reflection.Missing.Value
xlWorkBook = xlApp.Workbooks.Add
xlWorkSheet = DirectCast(xlWorkBook.Sheets("sheet1"), Excel.Worksheet)
xlApp.Visible = True
Dim headers = (From ch In DataGridView1.Columns _
Let header = DirectCast(DirectCast(ch, DataGridViewColumn).HeaderCell, DataGridViewColumnHeaderCell) _
Select header.Value).ToArray()
Dim headerText() As String = Array.ConvertAll(headers, Function(v) v.ToString)
Dim items() = (From r In DataGridView1.Rows _
Let row = DirectCast(r, DataGridViewRow) _
Where Not row.IsNewRow _
Select (From cell In row.Cells _
Let c = DirectCast(cell, DataGridViewCell) _
Select c.Value).ToArray()).ToArray()
Dim table As String = String.Join(vbTab, headerText) & Environment.NewLine
For Each a In items
Dim t() As String = Array.ConvertAll(a, Function(v) v.ToString)
table &= String.Join(vbTab, t) & Environment.NewLine
Next
table = table.TrimEnd(CChar(Environment.NewLine))
Clipboard.SetText (table)
Dim alphabet() As Char = "abcdefghijklmnopqrstuvwxyz".ToUpper.ToCharArray
Dim range As excel.Range = xlWorkSheet.Range("B2:" & alphabet(headerText.Length) & (items.Length + 2).ToString)
range.Select()
xlWorkSheet.Paste()
range.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.XlLineStyle.xlLineStyleNone
range.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.XlLineStyle.xlLineStyleNone
With range.Borders(Excel.XlBordersIndex.xlEdgeLeft)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 1 ''black
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlMedium
End With
With range.Borders(Excel.XlBordersIndex.xlEdgeTop)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 1 ''black
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlMedium
End With
With range.Borders(Excel.XlBordersIndex.xlEdgeBottom)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 1 ''black
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlMedium
End With
With range.Borders(Excel.XlBordersIndex.xlEdgeRight)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 1 ''black
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlMedium
End With
With range.Borders(Excel.XlBordersIndex.xlInsideVertical)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 1 ''black
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With
With range.Borders(Excel.XlBordersIndex.xlInsideHorizontal)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 1 ''black
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With
''xlApp.Visible = True
xlWorkBook.SaveAs("C:/Users/CoDeXeR/Desktop/Word1.xls", True)
xlWorkBook.Close()
xlApp.Application.Quit()
ReleaseObject(xlWorkSheet) ''<~~~ Added as per comment from deleted post
ReleaseObject (xlWorkBook)
ReleaseObject (xlApp)
End Sub
Public Sub exportToWord(ByVal dgv As DataGridView)
'' Create Word Application
Dim oWord As Word.Application = DirectCast(CreateObject("Word.Application"), Word.Application)
'' Create new word document
Dim oDoc As Word.Document = oWord.Documents.Add()
Dim headers = (From ch In dgv.Columns _
Let header = DirectCast(DirectCast(ch, DataGridViewColumn).HeaderCell, DataGridViewColumnHeaderCell) _
Select header.Value).ToArray()
Dim headerText() As String = Array.ConvertAll(headers, Function(v) v.ToString)
Dim items() = (From r In dgv.Rows _
Let row = DirectCast(r, DataGridViewRow) _
Where Not row.IsNewRow _
Select (From cell In row.Cells _
Let c = DirectCast(cell, DataGridViewCell) _
Select c.Value).ToArray()).ToArray()
Dim table As String = String.Join(vbTab, headerText) & Environment.NewLine
For Each a In items
Dim t() As String = Array.ConvertAll(a, Function(v) v.ToString)
table &= String.Join(vbTab, t) & Environment.NewLine
Next
table = table.TrimEnd(CChar(Environment.NewLine))
Clipboard.SetText (table)
Dim oTable As Word.Table = oDoc.Tables.Add(oDoc.Bookmarks.Item("/endofdoc").Range, items.Count + 1, headers.Count)
oTable.Range.Paste()
''make the first row bold, fs 14 + change textcolor
oTable.Rows.Item(1).range.Font.Bold = &H98967E
oTable.Rows.Item(1).range.Font.Size = 14
oTable.Rows.Item(1).range.Font.Color = Word.WdColor.wdColorWhite
''change backcolor of first row
oTable.Rows.Item(1).range.Shading.Texture = Word.WdTextureIndex.wdTextureNone
oTable.Rows.Item(1).range.Shading.ForegroundPatternColor = Word.WdColor.wdColorAutomatic
oTable.Rows.Item(1).range.Shading.BackgroundPatternColor = Word.WdColor.wdColorLightBlue
''''set table borders
''With oTable.Range.Tables(1)
'' With .Borders(Word.WdBorderType.wdBorderLeft)
'' .LineStyle = Word.WdLineStyle.wdLineStyleSingle
'' .LineWidth = Word.WdLineWidth.wdLineWidth100pt
'' .Color = Word.WdColor.wdColorAutomatic
'' End With
'' With .Borders(Word.WdBorderType.wdBorderRight)
'' .LineStyle = Word.WdLineStyle.wdLineStyleSingle
'' .LineWidth = Word.WdLineWidth.wdLineWidth100pt
'' .Color = Word.WdColor.wdColorAutomatic
'' End With
'' With .Borders(Word.WdBorderType.wdBorderTop)
'' .LineStyle = Word.WdLineStyle.wdLineStyleSingle
'' .LineWidth = Word.WdLineWidth.wdLineWidth100pt
'' .Color = Word.WdColor.wdColorAutomatic
'' End With
'' With .Borders(Word.WdBorderType.wdBorderBottom)
'' .LineStyle = Word.WdLineStyle.wdLineStyleSingle
'' .LineWidth = Word.WdLineWidth.wdLineWidth100pt
'' .Color = Word.WdColor.wdColorAutomatic
'' End With
'' With .Borders(Word.WdBorderType.wdBorderHorizontal)
'' .LineStyle = Word.WdLineStyle.wdLineStyleSingle
'' .LineWidth = Word.WdLineWidth.wdLineWidth050pt
'' .Color = Word.WdColor.wdColorAutomatic
'' End With
'' With .Borders(Word.WdBorderType.wdBorderVertical)
'' .LineStyle = Word.WdLineStyle.wdLineStyleSingle
'' .LineWidth = Word.WdLineWidth.wdLineWidth050pt
'' .Color = Word.WdColor.wdColorAutomatic
'' End With
'' .Borders(Word.WdBorderType.wdBorderDiagonalDown).LineStyle = Word.WdLineStyle.wdLineStyleNone
'' .Borders(Word.WdBorderType.wdBorderDiagonalUp).LineStyle = Word.WdLineStyle.wdLineStyleNone
'' .Borders.Shadow = False
''End With
'' Save this word document
oDoc.SaveAs("C:/Users/CoDeXeR/Desktop/Word1.doc", True)
oDoc.Close()
oWord.Application.Quit()
''oWord.Visible = True
End Sub
Public Sub exportToExcel(ByVal dgv As DataGridView)
End Sub
Private Sub ReleaseObject(ByVal obj As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject (obj)
obj = Nothing
Catch ex As Exception
obj = Nothing
Finally
GC.Collect()
End Try
End Sub
End Class

Answer 1

He utilizado la capacidad de cerrar un documento EXCEL en script muchas veces junto con la ocultación haciendo visible y ahora cerrando si es el único libro de trabajo abierto más cerca de esta hoja de trabajo. Aquí está mi

Sub ExitWorkBook()
Dim wb As Workbook
Dim c As Integer
c = 0
For Each wb In Application.Workbooks
c = c + 1
Next wb
If c = 1 Then
Application.Quit ''--Quit this worksheet but keep excel open.
Else
Workbooks("(excel workbook name).xls").Close ''-- Close Excel
End If
''
End Sub

Answer 2

For Each w In Application.Workbooks w.Save Next w Application.Quit

http://msdn.microsoft.com/en-us/library/office/ff839269.aspx

Answer 3

Just Calling .Quit() no eliminará la aplicación de la memoria. Es muy importante cerrar los objetos una vez que haya terminado con su codificación. Esto garantiza que todos los objetos se liberen correctamente y que no quede nada en la memoria.

Mira este ejemplo

Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
''~~> Define your Excel Objects
Dim xlApp As New Excel.Application
Dim xlWorkBook As Excel.Workbook
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
''~~> Add a New Workbook
xlWorkBook = xlApp.Workbooks.Add
''~~> Display Excel
xlApp.Visible = True
''~~> Do some stuff Here
''~~> Save the file
xlWorkBook.SaveAs(Filename:="C:/Tutorial/SampleNew.xlsx", FileFormat:=51)
''~~> Close the File
xlWorkBook.Close()
''~~> Quit the Excel Application
xlApp.Quit()
''~~> Clean Up
releaseObject (xlApp)
releaseObject (xlWorkBook)
End Sub
''~~> Release the objects
Private Sub releaseObject(ByVal obj As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject (obj)
obj = Nothing
Catch ex As Exception
obj = Nothing
Finally
GC.Collect()
End Try
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
Me.Close()
End Sub
End Class

También vale la pena mencionar es la regla 2 DOT .

Si te encanta automatizar Excel desde VB.Net, entonces también deberías echarle un vistazo a este enlace .

SEGUIR

El problema es la Regla 2 DOT como mencioné anteriormente. Cuando usa la regla 2 DOT (Ej: Excel.XlBordersIndex.xlDiagonalDown ), entonces tiene que hacer la recolección de basura utilizando GC.Collect() . Entonces, todo lo que tienes que hacer es agregar esta parte

 Finally
GC.Collect()

en Private Sub ReleaseObject(ByVal obj As Object)

Private Sub ReleaseObject(ByVal obj As Object)
Try
Dim intRel As Integer = 0
Do
intRel = System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
Loop While intRel > 0
MsgBox("Final Released obj # " & intRel)
Catch ex As Exception
MsgBox("Error releasing object" & ex.ToString)
obj = Nothing
Finally
GC.Collect()
End Try
End Sub

CÓDIGO FINAL (Probado y probado)

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim xlApp As New excel.Application
Dim xlWorkBook As excel.Workbook
Dim xlWorkSheet As excel.Worksheet
Dim xlRange As excel.Range
''Dim misValue As Object = System.Reflection.Missing.Value
xlWorkBook = xlApp.Workbooks.Add
xlWorkSheet = DirectCast(xlWorkBook.Sheets("sheet1"), excel.Worksheet)
xlApp.Visible = True
Dim headers = (From ch In DataGridView1.Columns _
Let header = DirectCast(DirectCast(ch, DataGridViewColumn).HeaderCell, DataGridViewColumnHeaderCell) _
Select header.Value).ToArray()
Dim headerText() As String = Array.ConvertAll(headers, Function(v) v.ToString)
Dim items() = (From r In DataGridView1.Rows _
Let row = DirectCast(r, DataGridViewRow) _
Where Not row.IsNewRow _
Select (From cell In row.Cells _
Let c = DirectCast(cell, DataGridViewCell) _
Select c.Value).ToArray()).ToArray()
Dim table As String = String.Join(vbTab, headerText) & Environment.NewLine
For Each a In items
Dim t() As String = Array.ConvertAll(a, Function(v) v.ToString)
table &= String.Join(vbTab, t) & Environment.NewLine
Next
table = table.TrimEnd(CChar(Environment.NewLine))
Clipboard.SetText(table)
Dim alphabet() As Char = "abcdefghijklmnopqrstuvwxyz".ToUpper.ToCharArray
xlRange = xlWorkSheet.Range("B2:" & alphabet(headerText.Length) & (items.Length + 2).ToString)
xlRange.Select()
xlWorkSheet.Paste()
xlRange.Borders(excel.XlBordersIndex.xlDiagonalDown).LineStyle = excel.XlLineStyle.xlLineStyleNone
xlRange.Borders(excel.XlBordersIndex.xlDiagonalUp).LineStyle = excel.XlLineStyle.xlLineStyleNone
With xlRange.Borders(excel.XlBordersIndex.xlEdgeLeft)
.LineStyle = excel.XlLineStyle.xlContinuous
.ColorIndex = 1 ''black
.TintAndShade = 0
.Weight = excel.XlBorderWeight.xlMedium
End With
With xlRange.Borders(excel.XlBordersIndex.xlEdgeTop)
.LineStyle = excel.XlLineStyle.xlContinuous
.ColorIndex = 1 ''black
.TintAndShade = 0
.Weight = excel.XlBorderWeight.xlMedium
End With
With xlRange.Borders(excel.XlBordersIndex.xlEdgeBottom)
.LineStyle = excel.XlLineStyle.xlContinuous
.ColorIndex = 1 ''black
.TintAndShade = 0
.Weight = excel.XlBorderWeight.xlMedium
End With
With xlRange.Borders(excel.XlBordersIndex.xlEdgeRight)
.LineStyle = excel.XlLineStyle.xlContinuous
.ColorIndex = 1 ''black
.TintAndShade = 0
.Weight = excel.XlBorderWeight.xlMedium
End With
With xlRange.Borders(excel.XlBordersIndex.xlInsideVertical)
.LineStyle = excel.XlLineStyle.xlContinuous
.ColorIndex = 1 ''black
.TintAndShade = 0
.Weight = excel.XlBorderWeight.xlThin
End With
With xlRange.Borders(excel.XlBordersIndex.xlInsideHorizontal)
.LineStyle = excel.XlLineStyle.xlContinuous
.ColorIndex = 1 ''black
.TintAndShade = 0
.Weight = excel.XlBorderWeight.xlThin
End With
xlWorkBook.SaveAs(Filename:="C:/Users/Siddharth Rout/Desktop/Word1.xls", FileFormat:=56)
xlWorkBook.Close()
xlApp.Quit()
ReleaseObject(xlRange)
ReleaseObject(xlWorkSheet)
ReleaseObject(xlWorkBook)
ReleaseObject(xlApp)
End Sub
Private Sub ReleaseObject(ByVal obj As Object)
Try
Dim intRel As Integer = 0
Do
intRel = System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
Loop While intRel > 0
MsgBox("Final Released obj # " & intRel)
Catch ex As Exception
MsgBox("Error releasing object" & ex.ToString)
obj = Nothing
Finally
GC.Collect()
End Try
End Sub

Answer 4

Resolví el problema usando:

Set xlApp = Nothing

Puede verificar el administrador de tareas TaskManager.

Answer 5

Por favor usa esto

Private Sub releaseObject(ByVal obj As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
obj = Nothing
Catch ex As Exception
obj = Nothing
Finally
GC.Collect()
Try
''Dim MSExcelControl() As Process
Dim iID As Integer
Dim lastOpen As DateTime
Dim obj1(10) As Process
obj1 = Process.GetProcessesByName("EXCEL")
lastOpen = obj1(0).StartTime
For Each p As Process In obj1
If lastOpen < p.StartTime Then
iID = p.Id
Exit For
End If
Next
For Each p As Process In obj1
If p.Id = iID Then
p.Kill()
Exit For
End If
Next
Catch ex As Exception
End Try
End Try
End Sub

Answer 6

Ninguna de las recomendaciones anteriores funcionó para mí hasta que seguí el comentario anterior de SiddharthRout. " A partir de hoy, ¿cuál es la forma correcta de trabajar con objetos COM? "

Señala que las referencias de objeto com se mantienen activas bajo el depurador. Una solución alternativa es llamar a GC desde el procedimiento que llama al procedimiento com. Funcionó para mí

Ejecuta GC desde Finalmente en un bloque TRY Catch.

copiado de: post by "Govert" on what is the right way to work with COM objects?

using System;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;
namespace TestCsCom
{
Class Program
{
static void Main(string[] args)
{
// NOTE: Don''t call Excel objects in here...
// Debugger would keep alive until end, preventing GC cleanup
// Call a separate function that talks to Excel
DoTheWork();
// Now let the GC clean up (repeat, until no more)
do
{
GC.Collect();
GC.WaitForPendingFinalizers();
}
while (Marshal.AreComObjectsAvailableForCleanup());
}
static void DoTheWork()
{
Application app = new Application();
Workbook book = app.Workbooks.Add();
Worksheet worksheet = book.Worksheets["Sheet1"];
app.Visible = true;
for (int i = 1; i <= 10; i++) {
worksheet.Cells.Range["A" + i].Value = "Hello";
}
book.Save();
book.Close();
app.Quit();
// NOTE: No calls the Marshal.ReleaseComObject() are ever needed
}
}
}

Answer 7

Yo tuve el mismo problema. Sin embargo, el problema solo persistió durante la depuración. Todo lo que debes hacer es

xlWorkBook.Close
xlApp.Quit

Entonces solo deja que el código se ejecute. Es posible que deba llamar al recolector de basura después de que Button1_Click finalice, pero ni siquiera lo necesitaba. Parece ser que recorrer el código o no dejar que termine por completo arroja cosas y deja Excel abierto.

Consulte Proceso de Excel que no se cierra en VB.net

Answer 8

Encontré que cada instancia de una referencia a un objeto de Excel tenía que ser explícitamente lanzada:

 xlApp = New Excel.Application
xlWorkBooks = xlApp.Workbooks
xlWorkBook = xlWorkBooks.Open(Me.txtFilePath.Text)
xlWorkSheets = xlWorkBook.Worksheets
xlWorkSheet = CType(xlWorkSheets(1), Excel.Worksheet)
xlWorkBook.Close()
xlWorkBooks.Close()
xlApp.Quit()
releaseObject(xlWorkSheet)
xlWorkSheet = Nothing
releaseObject(xlWorkSheets)
xlWorkSheets = Nothing
releaseObject(xlWorkBook)
xlWorkBook = Nothing
releaseObject(xlWorkBooks)
xlWorkBooks = Nothing
releaseObject(xlApp)
xlApp = Nothing
Private Sub releaseObject(ByVal obj As Object)
Try
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(obj)
obj = Nothing
Catch ex As Exception
obj = Nothing
Finally
GC.Collect()
End Try
End Sub