excel - vb.net 自动化使用来自 OLAP 多维数据集的数据刷新 Excel 文件

Question

我正在 VB.NET 中开发一个 exe 程序，它在一个循环中处理多个 Excel 文件（包含来自 OLAP 多维数据集的数据）（处理位于 exe 所在文件夹内的所有 Excel 文件）：

• 打开文件
• 启用内容
• 写在 2 个单元格中
• 调用 RefreshAll（刷新数据连接）
• 保存并关闭

通常它可以工作，但是对于大型 Excel 文件（有很多 CUBEVALUE 公式）我得到了随机错误，可能是因为 Excel 没有响应。我认为问题不在于 RefreshAll 调用，因为我的程序没有控制回来，它正在等待它完成，但在某些文件中，计算模式设置为“自动”，所以只要打开文件或之后刷新所有公式都会计算出来，但是（在这些计算完成之前）我的程序会重新获得控制权，并且当它尝试编辑单元格或尝试保存文件时出现错误。

我试图等到计算状态= xlDone，但没有成功。

刷新数据连接后，在状态栏中我读到：CALCULATING PROCESSORS (2) or EXECUTING Background QUERY

我也尝试设置手动计算模式，但没有更新带有公式的单元格。

你有什么建议吗？

Imports Excel = Microsoft.Office.Interop.Excel
Imports Microsoft.Office.Interop
Imports System.IO


Public Class Form1

    Dim rootPath As String = System.Windows.Forms.Application.StartupPath

    Private Sub Form1_Load(sender As Object, e As System.EventArgs) Handles Me.Load
    
        Dim di As New IO.DirectoryInfo(rootPath)
        Dim aryFi As IO.FileInfo() = di.GetFiles("*.xls*")
        Dim fi As IO.FileInfo
        Dim filename As String

        For Each fi In aryFi
            filename = fi.Name

            processaFile(filename)

            ' pulizia per non avere istanze di Excel aperte nel task manager
            GC.Collect()
            GC.WaitForPendingFinalizers()
            ' pulizia per non avere istanze di Excel aperte nel task manager
            GC.Collect()
            GC.WaitForPendingFinalizers()
        Next

        Me.Close()

    End Sub


    Private Sub processaFile(filenameX)
        Dim oExcel As Excel.Application
        Dim oBook As Excel.Workbook
        Dim oBooks As Excel.Workbooks
        Dim foglio As Excel.Worksheet
        Dim calculationType As Excel.XlCalculation

        oExcel = CreateObject("Excel.Application")
        oExcel.Visible = True ' ''''''''''''False/True
        oBooks = oExcel.Workbooks

        oExcel.AutomationSecurity = Microsoft.Office.Core.MsoAutomationSecurity.msoAutomationSecurityLow ' abilita le macro

        oBook = oBooks.Open(rootPath & "\" & filenameX, UpdateLinks:=True, [ReadOnly]:=False, IgnoreReadOnlyRecommended:=True)

        oBook.Activate()

        oBook.EnableConnections()

        foglio = oBook.Worksheets("Sheetname")
        foglio.Range("C7").Value = "12"
        foglio.Range("C8").Value = "2020"


        oBook.RefreshAll()
        'oExcel.CalculateFullRebuild()
        'oExcel.Application.CalculateUntilAsyncQueriesDone()


        Do While oExcel.Application.CalculationState.ToString <> "xlDone"
            ' wait
        Loop

        'oBook.Close(SaveChanges:=True)
        oBook.Save()
        oBook.Close()
        oBook = Nothing

        oBooks.Close()
        oBooks = Nothing

        oExcel.Quit()
        oExcel = Nothing
    End Sub


End Class

标签： excelvb.netolap-cube