.Net 服务器端写Excel，并关闭Excel进程示例

最新推荐文章于 2022-09-07 11:24:14 发布

wangmj518

最新推荐文章于 2022-09-07 11:24:14 发布

阅读量648

点赞数

分类专栏：办公类 OFFICE C#

C# 同时被 3 个专栏收录

52 篇文章

订阅专栏

OFFICE

19 篇文章

订阅专栏

办公类

5 篇文章

订阅专栏

本文介绍如何使用VBScript创建并操作Excel文件，包括新建工作簿、添加工作表、填充数据、保存文件及释放资源等关键步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Imports Microsoft.Office.Interop

Public Class VBExcell
    Inherits System.Web.UI.Page

    Dim myExcel As Excel.Application
    Dim myBook As Excel.Workbook
    Dim mySheet As Excel.Worksheet
    Dim strPath As String
    Dim pApp As Integer
    Dim strLiter As New System.Web.UI.LiteralControl

    Protected WithEvents Button1 As System.Web.UI.WebControls.Button
    Protected WithEvents PlaceHolder1 As System.Web.UI.WebControls.PlaceHolder
    Protected WithEvents btnNow As System.Web.UI.WebControls.Button
    Dim i As Integer

#Region " Web 窗体设计器生成的代码 "

    '该调用是 Web 窗体设计器所必需的。
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

    End Sub

    '注意: 以下占位符声明是 Web 窗体设计器所必需的。
    '不要删除或移动它。
    Private designerPlaceholderDeclaration As System.Object

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: 此方法调用是 Web 窗体设计器所必需的
        '不要使用代码编辑器修改它。
         InitializeComponent()
    End Sub

#End Region

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        '在此处放置初始化页的用户代码
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
         myExcel = CreateObject("Excel.Application")
         myBook = myExcel.Workbooks.Add(Server.MapPath("/WebDemo/DocTemp/") + "Test.xls")
         mySheet = myBook.Worksheets(1)
         myExcel.Visible = False
         pApp = System.GC.GetGeneration(myExcel)

        For i = 1 To 10
             mySheet.Cells(2, i) = i.ToString()
        Next

        Dim temp As String
         temp = DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls"
         strPath = Server.MapPath("/WebDemo/DocTemp/") + temp

         myExcel.ActiveWorkbook.SaveAs(Filename:=strPath, ReadOnlyRecommended:=False, CreateBackup:=True)

         strLiter.Text = "保存路径为：" + strPath + "<br>"

         mySheet = Nothing
        If Not myBook Is Nothing Then
             myBook.Close(SaveChanges:=True)
             myBook = Nothing
        End If

         strLiter.Text = strLiter.Text + "释放myBook

<br>"

        If Not myExcel Is Nothing Then
             myExcel.Quit()
             myExcel = Nothing
        End If
         System.GC.Collect(pApp)

         strLiter.Text = strLiter.Text + "释放myExcel

<br>"
        Me.PlaceHolder1.Controls.Add(strLiter)

    End Sub

    Private Sub Page_Error(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Error
        Dim curError As Exception = Server.GetLastError()
        Dim msg As String

         msg = "<h1>Page Error</h1><hr/>" + _
         msg = msg + "错误发生在: " + Request.Url.ToString() + "<br/>"
         msg = msg + "错误信息如下: <font color='#656565'>" + curError.Message.ToString() + "</font><hr/>" + _
         msg = msg + "<b>错误跟踪:</b><br/>" + curError.ToString()

         strLiter.Text = msg
        Me.PlaceHolder1.Controls.Add(strLiter)

    End Sub

    Private Sub btnNow_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNow.Click
         strLiter.Text = DateTime.Now.ToString("yyyyMMddhhmmss")
        Me.PlaceHolder1.Controls.Add(strLiter)
    End Sub
End Class

小结:
        可能是因为是com组件的原因,虽然在程序中写了如下语句
        myExcel.Quit()
        myExcel = Nothing
        但是在服务器进程中依然可以看见Excel进程并没有退出.因此必须使用如下语句
        Dim pApp As Integer
        pApp = System.GC.GetGeneration(myExcel)
        System.GC.Collect(pApp)