完美打开EXCEL进程和关闭进程

最新推荐文章于 2022-07-11 07:35:00 发布

juge001

最新推荐文章于 2022-07-11 07:35:00 发布

阅读量2.9k

点赞数

文章标签： excel integer object string exception date

本文链接：https://blog.csdn.net/juge001/article/details/3173577

版权

网络上经常有人在提问说为什么加了在ＶＢ.net里，加了　　System.Runtime.InteropServices.Marshal.ReleaseComObject

和

GC.Collect()
进行资源回收了，ＥＸＣＥＬ的进程还是删不掉．这几天由于工作需要也遇到了同样的问题．终于解决了，所以贴出来共享一下．

一般关闭excel进程及回收资源的代码．在office 2003下应该这样就可以了．但如果服务器端装的是office 2000 的话，会出问题

Dim xlsObj As Object 'New Excel.Application
Dim xlsWbk As Object 'Excel.Workbook
Dim xlsSht As Object 'Excel.Worksheet

        xlsObj = CreateObject("Excel.Application")
        xlsObj.Visible = False
        xlsWbk = xlsObj.Workbooks.Open("D:/Document/2008-05-22/ATT00059.xls")
        xlsSht = xlsWbk.Worksheets(1)

xlsSht.Name = "Test"

        xlsWbk.Save()
        xlsWbk.Close()
        xlsObj.Quit()

        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsSht)
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsWbk)
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsObj)

        xlsSht = Nothing
        xlsWbk = Nothing
        xlsObj = Nothing

        GC.Collect()
        GC.WaitForPendingFinalizers()
        GC.Collect()
        GC.WaitForPendingFinalizers()
        GC.Collect()

通过这样的回收，在office2000里，有时也会回收失败的．找了好久的原因终于发现是由于回收资源的时候还在一部分的资源没有被回收掉，所以又加了一个函数

        Private Sub NAR(ByVal o As Object)
            Try
                System.Runtime.InteropServices.Marshal.ReleaseComObject(o)
            Catch
            Finally
                o = Nothing
            End Try
        End Sub

比如复制粘贴时，就要进行回收资源：

                    xlsSht.Rows(CStr(nExcelStartRowIndex) + ":" + CStr(nExcelStartRowIndex)).Copy()
                    xlsSht.Rows(CStr(nExcelRowIndex) & ":" & CStr(nExcelRowIndex)).PasteSpecial(xlPasteFormats)
                    xlsSht.Rows(CStr(nExcelRowIndex) & ":" & CStr(nExcelRowIndex)).PasteSpecial(xlPasteFormulas)
                    NAR(xlsSht.Rows(CStr(nExcelStartRowIndex) + ":" + CStr(nExcelStartRowIndex)))

这样就能够完完全全的把ＥＸＣＥＬ的进程结束掉

2008/07/17

今天继续研究，发现没有必要回收所以的资源他也可以完全关闭进程的．担是有一点非常重要．就是对一个sheet设置值的时候，必须放到一个函数里．再把xlssht传过去．这样就可以方便的关闭进程了．提供已完成的可以正常关闭进程的部分代码：

#Region "setExcel"
Public Function setExcel() As Boolean

            Dim xlsObj As Object = Nothing
            Dim xlsWbk As Object = Nothing
            Dim xlsSht As Object = Nothing

Try
m_ExcelInFileName = "NouhinDenpyouEntry"

Dim inPath As String = m_ExcelInPath & "/" & m_ExcelInFileName & ".xls"
Dim outPath As String = m_ExcelOutPath & "/" & m_ExcelInFileName & "-" & Now.ToString("yyyyMMdd-HHmmss") & ".xls"

m_ExcelUrl = m_ExcelUrlPath & "/" & m_ExcelInFileName & "-" & Now.ToString("yyyyMMdd-HHmmss") & ".xls"

                Try
                    If File.Exists(outPath) Then
                        File.Delete(outPath)
                    End If

Catch ex As Exception
End Try

File.Copy(inPath, outPath)

xlsObj = CreateObject("Excel.Application")

xlsObj.Visible = False
xlsObj.DisplayAlerts = False

'get Workbooks
xlsWbk = xlsObj.Workbooks.Open(outPath) 'EXCELフォーマットの読取

                Dim obj As Object
                'delete other sheet
                If Me.ShopType = "1" Then '仕入
                    m_ExcelSheetName = "仕入"

                    obj = xlsWbk.Worksheets("経費")
                    obj.Delete()
                    NAR(obj)

                    obj = xlsWbk.Worksheets("振替")
                    obj.Delete()
                    NAR(obj)

xlsSht = xlsWbk.Worksheets("仕入")

Me.SetSheetShiiri(xlsSht)

ElseIf Me.ShopType = "2" Then '経費
m_ExcelSheetName = "経費"

                    obj = xlsWbk.Worksheets("仕入")
                    obj.Delete()
                    NAR(obj)

                    obj = xlsWbk.Worksheets("振替")
                    obj.Delete()
                    NAR(obj)

xlsSht = xlsWbk.Worksheets("経費")

Me.SetSheetKeihi(xlsSht)

ElseIf Me.ShopType = "3" Then '振替
m_ExcelSheetName = "振替"

                    obj = xlsWbk.Worksheets("仕入")
                    obj.Delete()
                    NAR(obj)

                    obj = xlsWbk.Worksheets("経費")
                    obj.Delete()
                    NAR(obj)

xlsSht = xlsWbk.Worksheets("振替")

Me.SetSheetHurikae(xlsSht)
End If

                'save
                xlsWbk.Save()
                xlsWbk.Close(True)
                xlsObj.Quit()

Return True

            Catch ex As Exception
                m_ErrNumber = Err.Number
                m_ErrDescription = Err.Description
                m_ErrSection = ex.ToString
                m_ErrMethodName = "setExcel"
                Return False
            Finally
                Try
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsSht)
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsWbk)
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsObj)

                    xlsSht = Nothing
                    xlsWbk = Nothing
                    xlsObj = Nothing

                    GC.Collect()
                    GC.WaitForPendingFinalizers()
                    GC.Collect()
                    GC.WaitForPendingFinalizers()
                    GC.Collect()
                Catch

                Finally
                    'KillProcess()
                End Try
            End Try
        End Function
#End Region

#Region "SetSheetShiiri"
        ''' <summary>
        ''' 仕入
        ''' </summary>
        ''' <param name="xlsSht"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Private Function SetSheetShiiri(ByVal xlsSht As Object) As Object

'Set SheetName
xlsSht.Name = Me.m_ExcelSheetName

xlsSht = SetSheetCorpData(xlsSht)

            If Me.FunSysDate() = True Then
                If Me.TSVData <> "" Then
                    Dim dataAll As String = Me.TSVData
                    Dim dataRow As String() = dataAll.Replace("~|~", "|").Split("|")

                    Dim strLastMstGyoushaID As String = ""
                    Dim nExcelColIndex As Integer = 5
                    Dim timeStartDate As New Date(Me.YearMonth.Substring(0, 4), Me.YearMonth.Substring(4, 2), 1)

                    For rowIndex As Integer = 0 To dataRow.Length - 1
                        Dim dataRowData As String() = dataRow(rowIndex).Split(vbTab)
                        If dataRowData(3) <> strLastMstGyoushaID Then
                            nExcelColIndex += 1

If rowIndex <> dataRow.Length - 1 Then
xlsSht.Columns(nExcelColIndex).Insert()

                                xlsSht.Range(xlsSht.Cells(1, 5), xlsSht.Cells(5000, 5)).Copy()
                                xlsSht.Range(xlsSht.cells(1, nExcelColIndex), xlsSht.cells(5000, nExcelColIndex)).PasteSpecial(xlPasteFormats)
                                xlsSht.Range(xlsSht.cells(1, nExcelColIndex), xlsSht.cells(5000, nExcelColIndex)).PasteSpecial(xlPasteFormulas)

End If

                            strLastMstGyoushaID = dataRowData(3)
                            'GyoushaName
                            xlsSht.Cells(9, nExcelColIndex).value = dataRowData(4)

                            'set back color
                            If dataRowData(5) = "1" Then
                                xlsSht.Cells(9, nExcelColIndex).Interior.ColorIndex = 15
                            End If

End If

If dataRowData(1) <> "" AndAlso dataRowData(1) <> "#null#" Then 'TargetDate
Dim timeDBYearMonth As Date = Convert.ToDateTime(dataRowData(1))

Dim nExcelRowIndex As Integer = DateDiff(DateInterval.Day, timeStartDate, timeDBYearMonth) + 11

                            xlsSht.Cells(nExcelRowIndex, nExcelColIndex).value = dataRowData(2)
                        End If
                    Next

xlsSht.Columns(5).Delete()

End If
End If

            xlsSht.Cells(11, 5).Select()
            Return xlsSht
        End Function
#End Region

#Region "SetSheetCorpData"
Private Function SetSheetCorpData(ByVal xlsSht As Object) As Object

            If GetCorpData() = False Then
                Return xlsSht
            End If

Dim data As String = Me.TSVData

Dim timeStartDate As New Date(Me.YearMonth.Substring(0, 4), Me.YearMonth.Substring(4, 2), 1)

xlsSht.Cells(6, 2).value = "店舗名称：" + Me.ShopName
xlsSht.Cells(7, 2).value = "対象年月：" + timeStartDate.ToString("yyyy年MM月", System.Globalization.DateTimeFormatInfo.InvariantInfo)

Dim nExcelStartRowIndex As Integer = 11
Dim nExcelRowIndex As Integer = 11

            If Me.ShopType = "1" Then
                nExcelRowIndex = 11
                nExcelStartRowIndex = 11
            ElseIf Me.ShopType = "2" Then
                nExcelRowIndex = 12
                nExcelStartRowIndex = 12
            ElseIf Me.ShopType = "3" Then
                nExcelRowIndex = 12
                nExcelStartRowIndex = 12
            End If

If data = "" Then

Dim timeEndDate As Date = timeStartDate.AddMonths(1).AddDays(-1)

Dim timeCurrentDate As Date = timeStartDate
While timeCurrentDate <= timeEndDate

xlsSht.Cells(nExcelRowIndex, 3).value = timeCurrentDate.ToString("yyyy/MM/dd", System.Globalization.DateTimeFormatInfo.InvariantInfo)
xlsSht.Cells(nExcelRowIndex, 4).value = GetWeekDayString(timeCurrentDate.DayOfWeek)

nExcelRowIndex += 1

timeCurrentDate = timeCurrentDate.AddDays(1)
End While

            Else
                Dim dataRow As String()
                data = data.Replace("~|~", "|")

dataRow = data.Split("|")

                For index As Integer = 0 To dataRow.Length - 1
                    xlsSht.Rows(CStr(nExcelStartRowIndex) + ":" + CStr(nExcelStartRowIndex)).Copy()
                    xlsSht.Rows(CStr(nExcelRowIndex) & ":" & CStr(nExcelRowIndex)).PasteSpecial(xlPasteFormats)
                    xlsSht.Rows(CStr(nExcelRowIndex) & ":" & CStr(nExcelRowIndex)).PasteSpecial(xlPasteFormulas)
                    NAR(xlsSht.Rows(CStr(nExcelStartRowIndex) + ":" + CStr(nExcelStartRowIndex)))

                    Dim dataRowData As String() = dataRow(index).Split(vbTab)
                    xlsSht.Cells(nExcelRowIndex, 3).value = Me.YearMonth.Substring(4, 2) + "月" + dataRowData(0).Substring(3, 2) + "日"
                    xlsSht.Cells(nExcelRowIndex, 4).value = dataRowData(1)

                    If dataRowData(1) = "祝" Then
                        xlsSht.Cells(nExcelRowIndex, 4).Font.ColorIndex = 3 'red
                    Else
                        xlsSht.Cells(nExcelRowIndex, 4).Font.ColorIndex = 0 'auto
                    End If
                    nExcelRowIndex += 1

End If

Return xlsSht
End Function
#End Region